|
I don't believe there is.
Generally, rolling up data is an operation best performed on the client in procedural code.
You can reduce the cost of your cursors by ensuring that you use a LOCAL FAST_FORWARD STATIC cursor.
|
|
|
|
|
If you're satisfied with adding them to a single column, I recently discovered that this actually worked (atleast on MS SQL 2000):
DECLARE @t varchar(8000)
SET @t = ''
SELECT @t = columnname + @t FROM thetable
SELECT @t
Have a look at my latest article about Object Prevalence with Bamboo Prevalence.
|
|
|
|
|
Thanks for the responses.
Arjan: I read on some other site article that this way of adding the values to single column is a bug in SQL. Probably, will be taken care of in future updates.. ..thought of passing this information to you and readers of this question .
Though, I am still looking for a neat solution for this problem.
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
Hi,
I am writing a small program to test the scalability of SQL Server. My code is:
class MyClass<br />
{<br />
string sql = "select * from table1";<br />
string conn_s = "...";<br />
<br />
private bool threadFail = false;<br />
private bool ThreadFail <br />
{<br />
get <br />
{<br />
lock ( this ) <br />
{<br />
return threadFail;<br />
}<br />
}<br />
set<br />
{<br />
lock ( this ) <br />
{<br />
threadFail = value;<br />
}<br />
}<br />
}<br />
<br />
private void DoQuery()<br />
{<br />
DataSet ds = new DataSet();<br />
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn_s);<br />
<br />
while ( !ThreadFail ) <br />
{<br />
try <br />
{<br />
da.Fill(ds);<br />
Thread.Sleep(100);<br />
} <br />
catch ( Exception e ) <br />
{<br />
ThreadFail = true;<br />
}<br />
}<br />
}<br />
<br />
public void Test()<br />
{<br />
int i = 0;<br />
while ( !ThreadFail ) <br />
{<br />
i++;<br />
Thread t = new Thread(new ThreadStart(DoQuery));<br />
t.Start();<br />
Thread.Sleep(100);<br />
}<br />
ShowMessage( "Total threads = " + i );<br />
}<br />
}
However, there's a problem. It threw an exception of without enough memory. I think it's because of "da.Fill(ds)". It occupied a lot of memory every time. What I want to know is: can I use OleDbCommand.ExecuteReader() instead of OleDbDataAdapter.Fill()? And I don't get the data from sqlserver. But I am not sure if the OleDbCommand.ExecuteReader() already run the sql command in SQL Server? Because I need the query to be executed but I don't want the return data.
Anyone has any idea? Thanks in advance!
|
|
|
|
|
Why don't you try using OleDbCommand.ExecuteNonQuery. It will execute the query, but won't return nothing.
Free your mind...
|
|
|
|
|
|
Is it possible to do the following query?
declare @Year varchar(5)
set @Year='02/03'
SELECT
case
when @Year='02/03' then AccountNumber between '50' and '59'
when @Year='01/02' then AccountNumber between '40' and '49'
else AccountNumber end
FROM Master
WHERE (Number = '71')
|
|
|
|
|
Hi everyone,
Is somebody able to answer my question? - I'm trying to have access to the MSysObjects table (in MSAccess database file) to get a list of all user-defined tables it holds. As the subject suggests I'm using ADO.NET. My select command (SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0) query gives me familiar to many of us "...no read permission in MSysObjects" exception, that (in my case) means that I didn't configue MSAccess to allow anyone to see any system/hidden tables. And it's Ok, but the funny thing is that I can create and open connection to the same *.mdb file from the VS.NET IDE (using Data Connections in the Server Explorer) without any changes of the database properties from the MSAccess application. So, VS.NET knows some other way to get (unlimited?) access to any *.mdb file (by either programmaticaly changing database properties, or using some other tricks).
I've found a workarround for this issue at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vcmfc98/html/_mfcnotes_tn054.asp[^]
but they're usind direct calls to DAO to change internall database setting from the user's code. So:
- am I able to use direct DAO calls from inside my C# code?
- is there any good method to do it by means of just C# itself?
- is there probably another way to get the list of user tables without pulling MSysObjects table?
Any ideas will be highly appreciated.
Thanks,
Armen.
|
|
|
|
|
 I have this little problem:
I have created a Installation disk to install a suite of applications along with installing MSDE (Single workstation so using MSDE for databsae). My problem is that I don't know how to import a stored procedure into my SQL database??
Does anyone know how to import a stored procedure from a txt file through Install shield, or through visual c++??
I am really struggling on this one.
Please could someone help me with this matter.
Will be eternally grateful.
|
|
|
|
|
I believe InstallShield has the capability of running an external program. If so, use the osql tool.
|
|
|
|
|
I have a table1 and I wanted to add a foreign key from table2 for 2 fields:id,grpid. How do I do this?
|
|
|
|
|
Is there any way to kill sleeping database connections of SQL programmatically to avoid memory leaks.Help on this topic will be encouraged.
Thanks
Altaf-Ur-Rehman
|
|
|
|
|
First, find active connection by using the procedures:
sp_who or sp_who2
this will return a list of all active connections,
then to stop a non-needed session, get the SPID of the session you want to end and use the KILL command like so..
KILL {SPID of connection you want terminated)
and there you go... to test this, use Query analyzer.
- Sage
|
|
|
|
|
Thanks for your reply, but i want to ask that how could we close sleeping database connection in Visual C++ (i.e programatically) by spid.Please tell if you have some knowledge about it.
|
|
|
|
|
I have added a component that access database using ADO into an existiong application which used Pro-C to connect to an oracle database. I have tested with both MS Access and MS SQL Server are working fine, however when I tried to connect to oracle with the ADO component, the Pro-C which also connect to oracle database will give an exception error while disconnecting, the error is something like access voilation of NTDLL.dll. I noticed that both the connection with Pro-C and ADO seems not to be allow to exist at the same time. Any idea ?
|
|
|
|
|
I have done some research on some different newsgroups
and website and think I have figured out how to upload a
document file into my SQL Server. below is the code I am
using, but it never store the data into the database. I
do not get any error messages so I assume it works, but when
I check the table, there is no data. Is there something I am missing?
Thanks for your help.
<br />
Public Function StoreFile(ByVal FileData As Byte(), ByVal ServerName As String, ByVal SqlPassword As String, ByVal SqlUser As String)<br />
'Make the Connection to the SQL server<br />
Dim ConnectionString As String = ""<br />
ConnectionString = ConnectionString & "data source=" & ServerName & ";"<br />
ConnectionString = ConnectionString & "password=" & SqlPassword & ";"<br />
ConnectionString = ConnectionString & "persist security info=False;"<br />
ConnectionString = ConnectionString & "user id=" & SqlUser & ";"<br />
'ConnectionString = ConnectionString & "packet(Size = 4096)"<br />
<br />
Try<br />
'Declare the Connection Object.<br />
Dim Conn As SqlConnection = New SqlConnection(ConnectionString)<br />
'Open the Connection.<br />
Conn.Open()<br />
<br />
<br />
'Declare a DataAdapter.<br />
Dim DA As SqlDataAdapter = New SqlDataAdapter()<br />
<br />
'Set the Select,Delete,Insert, and Update Command for this DataAdapter<br />
DA.SelectCommand = New SqlCommand("SELECT DOC_Type, DOC_Data, DOC_ID FROM EDIAPPS.dbo.Documents_All", Conn)<br />
DA.InsertCommand = New SqlCommand("INSERT INTO EDIAPPS.dbo.Documents_All(DOC_Type, DOC_Data) VALUES (@DOC_Type, @DOC_Data); SELECT DOC_Type, DOC_Data, DOC_ID FROM EDIAPPS.dbo.Documents_All WHERE (DOC_ID = @@IDENTITY)", Conn)<br />
DA.UpdateCommand = New SqlCommand("UPDATE EDIAPPS.dbo.Documents_All SET DOC_Type = @DOC_Type, DOC_Data = @DOC_Data WHERE (DOC_ID = @Original_DOC_ID) AND (DOC_Type = @Original_DOC_Type); SELECT DOC_Type, DOC_Data, DOC_ID FROM EDIAPPS.dbo.Documents_All WHERE (DOC_ID = @DOC_ID)", Conn)<br />
DA.DeleteCommand = New SqlCommand("DELETE FROM EDIAPPS.dbo.Documents_All WHERE (DOC_ID = @Original_DOC_ID) AND (DOC_Type = @Original_DOC_Type)", Conn)<br />
<br />
'Delclare a dataSet Object.<br />
Dim DS As New DataSet()<br />
<br />
'Fill the Dataset with the Table data from the Database.<br />
DA.Fill(DS)<br />
<br />
'Delclare a New DataRow Object.<br />
Dim DR As DataRow = DS.Tables(0).NewRow<br />
<br />
'Populate the new row with our information.<br />
DR("DOC_Type") = 1<br />
DR("DOC_Data") = FileData<br />
<br />
'Add New Row to out DataSet.<br />
DS.Tables(0).Rows.Add(dr)<br />
DS.AcceptChanges()<br />
<br />
'Update the Database with our changes.<br />
DA.Update(DS)<br />
Conn.Close()<br />
Catch Ex As Exception<br />
MessageBox.Show(Ex.Message, "Application Error", MessageBoxButtons.OK)<br />
Return False<br />
Catch DBE As SqlClient.SqlException<br />
MessageBox.Show(DBE.Message, "SQL Error", MessageBoxButtons.OK)<br />
Return False<br />
Finally<br />
End Try<br />
<br />
End Function<br />
=================================
When I was in school, all I wanted was to get out into the real world.
Now that I'm in the real world, all I want is to go back to school.
|
|
|
|
|
Here's my situation. I'm binding a DataView to a DataGrid, but I only want the first n number of rows to show up. As far as I can see there's no expression I could put in the RowFilter expression that would allow me to select, say, only rows 10 - 22. I can think of a couple other ways of doing this, but they seem way more complicated than this should be.
I would greatly appreciate any suggestions or advice.
Travis Merkel
---------------------------------
"Many times when you start on a project you think, "Boy, this is never going to be finished," but then it is finished, and you think, "Wow, it wasn't even worth it." - Jack Handey
|
|
|
|
|
If there is no proper expression for the RowFilter available why don't you make a function to produce a temporary table to use as a DataGrid's source. It's pretty simple (even though not too subtile ) and, if you don't need to edit you data in the DataGreed, it will not leed to any further complications.
Good luck.
|
|
|
|
|
Hi all,
I'm new to the SQL world, just getting comfortable with stored procedures now, so forgive me if this is a newbie question.
Is there a way to know on the client machine when a change has been made to a table in the server's SQL database? My C# app is running on multiple machines, accessing the same SQL database on the server, and each machine must know when a change has been made to the database. Could someone point me in the right direction?
The graveyards are filled with indispensible men.
|
|
|
|
|
If you are looking for automatic updates to data in datatables on the client, you are out of luck. ADO.Net is a disconnected framework: once a dataset is populated with copies of database data, it disconnects from the server...
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
|
I have following query:
Insert table2(field2,field3,field4)
select field2,field3,field4 from table1
My field1 is identity. I wanted to insert data and increment identity by 1 for spesific field2 value.
I was able to do if I inserted by 1 record:
SET IDENTITY_INSERT field1 ON
DECLARE @rID int
SELECT @rID = max(field1) + 1 from table2 where field2 = @field2
insert referral_main (field2,field3,field4)
values (field2,field3,field4)
SET IDENTITY_INSERT field1 OFF
Please help me do for more then 1.
|
|
|
|
|
OK, what happens if I have already inserted the following:
field1 field2 field3 field4
------ ------ ------ ------
1 A B C
2 A D E
3 B C E and I try to run your query setting @field2 to 'A'? I already have a row where field1 is 3. If there's a primary key (or a UNIQUE constraint) on field1, the INSERT will fail.
Do not use IDENTITY in cases where you need to manipulate values like this. It's just a simple way to get a unique identifier for a row which otherwise cannot be identified uniquely - if it needs some higher meaning, you need to think more closely about your data.
Also, you need to think about locking for this query. What happens if two processes execute this procedure simultaneously? If the situation happens as follows:
Process 1 Process 2
--------- ---------
SELECT - @rID = 4
SELECT - @rID = 4
INSERT - success
INSERT - failureone of your inserts will fail. This is even more likely if running on a multiprocessing-capable server (either a server with multiple processors, or potentially on a server with hyperthreading enabled).
If you need to impose an ordering on results, it's better to use ORDER BY in a SELECT. You can always order by multiple columns - for example, ORDER BY field2, field1 to get results in the order of insertion. IIRC, IDENTITY will always add new rows with higher identifiers than are currently present in the table, so long as you haven't reseeded the column with DBCC CHECKIDENT.
|
|
|
|
|
Is there a way, without buying a third party tool, to monitor SQL Server programatically using stored procedure calls or even via straight ODBC/JDBC calls? 
I need to gain access to a SQL Server 2000 box's information such as how much a given database has grown, how much space is available for it to grow, etc., and I need to be able to do this from a web application.
If there is a way to do gain acces to this sort of information via a stored procedure or ODBC/JDBC, then I'm in business!
Thanks. 
Darrin
|
|
|
|
|
Lookup sp_helpdb in the SQL Server Books Online. It provides exactly what you are looking for.
OldFartRant: Is it really so long ago that the sp_help series of sprocs typed into an ISQL session were the primary means of ad-hoc administration? Kids and their GUI tools these days... when we were young we had to walk a mile up hill in the snow to school, both ways.
--
-Blake (com/bcdev/blake)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
