changing my dynamic sql to a cursor

• ksexton

  SSCrazy

  Points: 2790

  More actions

  June 28, 2002 at 3:13 pm

  #78960

  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

• loach

  Right there with Babe

  Points: 796

  More actions

  July 1, 2002 at 1:17 pm

  #431936

  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