October 21, 2005 at 2:02 pm
I setup a stored procedure to backup all of the databases on this MSDE SQL Server. In the procedure, I build a temp table that holds the output of the sp_databases.
When I run sp_databases in Query analyzer, it returns all of the databases on the server. When I run the procedure, the command:
INSERT INTO #temp
EXEC sp_databases
I only get 5 of the 10 databases. I get one user defined database I setup and all of the system databases.
I have copied the procedure code into Query Analyzer and test the sp_databases command and the script from the same window and I always get the same error. It won't populate my temp table but @@ERROR is 0.
Here is the code that is giving me problems:
-- Build a temp table to hold all databases on the server
CREATE TABLE #thedatabases(
dbname varchar(128),
dbsize int,
remarks varchar(254))
-- Gather all databases into a temp table for processing
INSERT INTO #thedatabases
EXEC dbo.sp_databases
By the way, I have this same setup on almost all of my servers and it works like a charm on every server except this one. Could this be related to MSDE somehow? If so, can anyone explain why it works on one user database but not others?
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
October 21, 2005 at 2:10 pm
Never mind. If I would just wait five minutes whenever I think I might need to post a question, I will probably figure it out.
Somehow it had something to do with the Auto Close feature on the databases. Once I removed that option, it is working fine.
I still don't understand why it worked when just running the command but not on the INSERT but I don't have time to think about it right now.
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
October 21, 2005 at 2:11 pm
Hi,
could you tell us what databases are missing from the list?
What databases you call system and what user? I mean, why 10 databases on MSDE with only 1 user among them? MSDE should have like, 4 system databases Master, Model, MSDB, TempDB and 1 user, 5 total
I normally say if you don't see the result that you are expecting to see it may be a wrong server or wrong database.
Regards,Yelena Varsha
October 21, 2005 at 3:38 pm
You can have more than 5 databases on MSDE. There are the 4 system databases, five small databases supplied by the application, and two databases I have installed. One is for some administration (such as backups). The other is for another small app.
That totals 11 but of course I don't include tempdb in the backups.
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
October 21, 2005 at 4:44 pm
Yes, sure that explains.
Sure you can have more databases, I just misread your original question where you say that you get all system databases and 1 user database (that gives 5) , I misread it that you have only 1 user database and total should be 10. Sorry about that
Regards,Yelena Varsha
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply