November 11, 2009 at 1:35 pm
I am having trouble with the query below. What I am trying to do is query every database on a instance and find the last login date in a user table. I then want to store that info and the database name in a table. The trouble that I am having is when I insert the data, I am only getting the query text and not the results in the table.
Any help on this would be greatly appreciated.
DECLARE @dbname varchar(100)
DECLARE GetDB CURSOR FOR
SELECT name FROM master..sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
OPEN GetDB
FETCH GetDB INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql varchar (300)
SET @sql = 'SELECT TOP(1) login_date FROM ' + @dbname + '.dbo.User_Session ORDER BY login_date'
INSERT INTO DBA..unused_Db_Log
VALUES (@dbname, @sql)
FETCH GetDB INTO @dbname
END
CLOSE GetDB
DEALLOCATE GetDB
November 11, 2009 at 1:39 pm
Change this:
SET @sql = 'SELECT TOP(1) login_date FROM ' + @dbname + '.dbo.User_Session ORDER BY login_date'
INSERT INTO DBA..unused_Db_Log
VALUES (@dbname, @sql)
To:
SET @sql = 'SELECT TOP(1) ''' + @dbname + ''', login_date FROM ' + @dbname + '.dbo.User_Session ORDER BY login_date'
INSERT INTO DBA..unused_Db_Log
EXEC (@sql);
The problem is that your code doesn't actually execute the select anywhere. It defines it, but doesn't run it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2009 at 1:56 pm
Perfect!!!
Thanks!!!
November 12, 2009 at 6:59 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply