June 28, 2002 at 3:13 pm
I have written a script which ouptputs sql's I can execute. These sql's insert all the database names and servers on my network.
select 'INSERT INTO db_LOCATIONS (database_name,server)select name,'''+srvname+''' from ['+srvname+'].MASTER.dbo.sysdatabases' + CHAR(10)+ 'GO'
from sysservers
I am trying to convert this to a cursor so I can schedule it to run at night. But it doesn't work: (here is my attempt)
DECLARE @server VARCHAR(50),
@Statement VARCHAR(300)
DECLARE User_Cursor CURSOR FOR
select srvname from sysservers
OPEN User_Cursor
FETCH NEXT FROM User_Cursor INTO @server
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Statement = 'INSERT INTO db_LOCATIONS (database_name,server)select name,@server
from @server.MASTER.dbo.sysdatabases from sysservers '
exec (@Statement)
FETCH NEXT FROM User_Cursor INTO @server
END
CLOSE User_Cursor
DEALLOCATE User_Cursor
-Kevin
July 1, 2002 at 1:17 pm
The problem is with the SET statement. Since your @server variable is within the quotes, it does not treat it as as variable, but rather as text. Try something like the following.
BEGIN
SET @statement = 'INSERT INTO db_LOCATIONS (database_name, server) select name,' + @server + ' from ' + @server + '.MASTER.dbo.sysdatabases'
EXEC (@statement)
FETCH NEXT FROM User_Cursor INTO @server
END
Also, this would still be dynamic SQL, it's just dynamic SQL imbedded in a cursor.
-Ken
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply