June 28, 2002 at 3:18 pm
I have written a very useful script which ouptputs sql's I can execute. These sql's insert all the database names and servers on my network into a table.
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
Any help would be greatly appreciated.
-Kevin
June 30, 2002 at 8:53 am
This is not the problem of the cursor...
The problem lies with the way you are creating the SQL query in the SET @Statement.
Check out this
SET @Statement = 'select ''INSERT INTO db_LOCATIONS (database_name,server) select name,''''' + @server + ''''' from [' + @server + '].MASTER.dbo.sysdatabases''' + CHAR(10)+ 'GO' + ' from sysservers'
Using this gives the exact output as given by your first SQL.
Hope this works for you.
IF U DON'T SEEK PERFECTION, U CAN NEVER REACH EXCELLENCE!!!
SD
Regards,
Sachin Dedhia
July 1, 2002 at 10:05 am
SD,
Thankyou for the syntax help but I still have to run the output sql. I cannot schedule this to return the results to a table.
-K
July 1, 2002 at 2:49 pm
This should take care of what you want. There should be a better way of doing this than what you are shooting for.
Also, this code is set up in the event you have non-SQL Server Linked servers. And assuming that all of the servers linked to your "Run" server are accurate (up to date) you should be able to run with this.
DECLARE @server VARCHAR(128)
DECLARE @product VARCHAR(128)
DECLARE @Statement VARCHAR(300)
DECLARE @Completed VARCHAR(500)
DECLARE User_Cursor CURSOR FOR
select srvname, srvproduct
from sysservers
OPEN User_Cursor
FETCH User_Cursor INTO @server, @product
WHILE @@FETCH_STATUS = 0
BEGIN
Print @server
If @product = 'SQL Server'
Begin
Declare RunStmt Cursor For
select 'INSERT INTO db_LOCATIONS (database_name,server)
select name,''' + @server + '''
from [' + @server + '].MASTER.dbo.sysdatabases'
from sysservers
open RunStmt
Fetch RunStmt Into @Completed
Print @Completed
exec (@Completed)
Fetch RunStmt Into @Completed
Close RunStmt
Deallocate RunStmt
End
FETCH NEXT FROM User_Cursor INTO @server, @product
END
CLOSE User_Cursor
DEALLOCATE User_Cursor
July 2, 2002 at 5:49 am
THANKS!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply