|
You may want to investigate the stored procedure called sys.sp_databases
It returns all of the databases that are online, along with their size, and
any remarks about the database.
I hope this is a starting point for you 
Paul
|
|
|
|
|
Thank you very much. This is exactly what I needed!
|
|
|
|
|
You're very welcome
|
|
|
|
|
Hello
I would like to know more on how you call Stored Procedures to fill a datagrid?
Very new to the SQL / Dev world but would like to try the sys.sp_databases mentioned in this thread. If there is am example that would be great.
Thanks..
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
This is off topic for this thread - You may wish to start your own thread to start a new topic.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
I was relating it to your original reply regarding sys.sp_databases...
But your point it well taken...
Thanks for the fast response...
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
You can fill a datagrid with a stored procedure like the one I mentioned to theaton. Instead of using an SQL query, you'd use the stored procedure and instead of commandType being query, you'd specify that you are calling a stored procedure.
I hope this helps some,
Paul
|
|
|
|
|
Hi there,
I've installed SQL Server 2005 and SQL Server 2000 side by side.
Now when I open SQL Server Management Studio it runs the SQL version 8 that belongs to SQL Server 2000.
How can I start with SQL Server 2005 (SQL 9.0) to create new database under the new features?
thank you.
|
|
|
|
|
Majid Shahabfar wrote: Now when I open SQL Server Management Studio it runs the SQL version 8 that belongs to SQL Server 2000.
When I open SQL Server Management Studio it gives me the option of which server I'd like to connect to. The default instance is the default option. And because the default instance is SQL Server 2000 that is selected. However, I can pull down the list and select the SQL Server 2005 database. Do you not see your SQL Server 2005 in that list?
Majid Shahabfar wrote: How can I start with SQL Server 2005 (SQL 9.0) to create new database under the new features?
It may be that SQL Server 2005 service is not running.
In the SQL Server 2005 folder in the Start-->All programs menu find Configuration-->SQL Server Configuration Manager and ensure the service is running.
Does this help?
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
No, I don't see SQL Server in that list.
In SQL Server Manager I have only SQL 2000 service and SQL Express edition service.
I can use SQL Server Express Edition but I really want to use the SQL Server 2005 Enterprise edition.
-- modified at 7:30 Saturday 25th February, 2006
|
|
|
|
|
Curious...Which one did you install first?
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I've installed SQL Server 2000 first.
|
|
|
|
|
Now I can't tell what you reall have installed! You said you installed SQL 2000 first, but what was installed second? SQL Server 2005 Express Edition or Enterprise??
Enterprise would have installed its own Manangement Console. The Express Edition doesn't come with one, but you can download one here[^].
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hi
Looking for a DAL to develop windows applications using vb.net 2.0
Jr
|
|
|
|
|
Ummm.... That is something I've always written myself. What exactly are you looking for?
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Just to develop windows application in vb.net2.0, want to use MS Access.
jr
-- modified at 23:50 Friday 24th February, 2006
|
|
|
|
|
You usually write that yourself, wrapping tables, views, records, and stored procedures in classes that will make it easier to for your main app to reuse the database.
There's no generic data access layer for every application. There are DAL Frameworks that make it easier to write a DAL, but nothing will completely wrap your database for you.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
basically i have 4 columns in the table called Forum. There are TopicId, Subject, Date and RelTopicId. For every new thread and reply on the thread, the TopicId is unique, which means for every record saved has an unique ID. For every new thread the RelTopicId will set to 0 and for every reply on the topic will set to the TopicId of the thread. This is how it look like.
TopicId Subject Date RelTopicId
1 asp 2/12/2006 11:40:40AM 0 (This is a new thread)
2 RE asp 2/12/2006 11:56:10 PM 1 (A reply on subject asp)
3 RE asp 2/12/2006 12:45:12 PM 1 (A reply on subject asp)
4 Vb.net 2/13/2006 13:40:54 PM 0 (A new thread)
5 RE Vb.net 2/13/2006 13:59:51 PM 4 (A reply on Vb.net)
6 RE asp 2/14/2006 10:20:30 AM 1 (A replt on asp)
TopicId 1 and RelTopicId 0 is the parent, and all the RelTopicId 1 is the child to the Parent which is TopicId 1."note all RelTopicId = 0 is a new thread"
here is the sql statement to retrive data
select * from Forum where Date >='2006-2-12' and Date <'2006-2-13 and TopicId = "0"
this is to get the parent.
my question is how to retrive the parent and the child data. This is to get the Parent(TopicId = 1, RelTopicId = 0) and the child which is (all RelTopicId = 1)
i have try this "select * from Forum where Date >='2006-2-13' and Date <='2006-2-14 and TopicId = "0" and RelTopicId = TopicId"
i got no luck. any help appreciate thanks.
|
|
|
|
|
You have to use a JOIN.
SELECT parent.*, child.*
FROM Forum AS parent
INNER JOIN Forum AS child ON parent.TopicId = child.RelTopicId
WHERE [Date] >= '2006-02-13'
AND [Date] <= '2006-02-14'
AND parent.RelTopicId = 0
Please note that it is bad practice to use SELECT * in production code. Its find for doing quick queries in the Query Analyzer, but not here. Also be aware that as it stands, because of the use of the * there will be duplicate column names because parent and child refer to the same real table. You may want to write something like:
SELECT parent.TopicId AS parent_TopicId,
parent.Subject AS parent_Subject,
...
child.TopicId = child_TopicId,
...
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
I have some C# code that is handling some sql calls using stored procedures.
the way I am currently handling things I am opening and closing the connection for each call to the stored procedure, like this:
<br />
com.Connection.Open();<br />
com.CommandText = "exec sp_thing 0";<br />
com.ExecuteNonQuery();<br />
com.Connection.Close();<br />
<br />
com.Connection.Open();<br />
com.CommandText = "exec sp_thing 1";<br />
com.ExecuteNonQuery();<br />
com.Connection.Close();<br />
<br />
com.Connection.Open();<br />
com.CommandText = "exec sp_thing 2";<br />
com.ExecuteNonQuery();<br />
com.Connection.Close(); <br />
I am having to do this because each call to the procedure is handling close to 300,000 records at a time.
My question (for any ado.net masters out there) is if this is more efficient (memory usage-wise) than declaring a new connection every time, which would allow me to set a new timeout every time, but this seems to be a much cleaner use of code. Am I correct in thinking that i may already be using it the best way?
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Do this:
private void MyMethod(string MyQuery)
{
com.Connection.Open();
com.CommandText = MyQuery;
com.ExecuteNonQuery();
com.Connection.Close();
}
Then call that method like this
MyMethod("exec sp_thing 0");
MyMethod("exec sp_thing 1");
MyMethod("exec sp_thing 2");
You could also process everything and then close the database and the very end using try-accepts.
|
|
|
|
|
I cant believe I spaced doing that.... Im usually a littl enutty about reusable code.
I have another question. I know that when data is put ina a dataset, it is basically being stored in memory as XML. This was the root off my original problems with this app because the shear volume of data, coupled with being used in a dataset, ate up all of the memory on the server and crashed the app.
I was wondering, If anyone happens to know, if DataTables use an xml layout to store that data, or perhaps something simpler (and less memory intensive)?
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Vodstok wrote: I was wondering, If anyone happens to know, if DataTables use an xml layout to store that data, or perhaps something simpler (and less memory intensive)?
Data reader is the most efficient way to get data from a database. Datasets and datatables are relatively costly.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Technically
com.Connection.Open();
com.CommandText = "exec sp_thing 1;exec sp_thing 2;exec sp_thing 3";
com.ExecuteNonQuery();
com.Connection.Close();
is the most efficient, but it only really makes a difference if the statements batched together are relatively fast. If the statements return a lot of data or are slow to execute, managing the connection isn't going to make a lot of difference.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
thanks for the responses. We (okay, one of the other programmers) discovered a REALLY quick way to upload what we need (although It was nice to get my questions answered, since that is something any programmer who uses sql should know)
He stumbled over the BULK INSERT statement. We devised a way for the users to uppload the MASSIVE csv files (almost 300,000 records, and over 20 columns) to upload the file to the webserver's filesystem, and for the sql server to then use bulk insert to upload the file to the db (which takes roughly 10 seconds, as opposed to the 8-10 minutes the old fashioned way worked)
If i get a chance to weed out any potentially compromising information, I will post how we accomplished this. it is really pretty cool. 
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
