dcsimg
Results 1 to 3 of 3

Thread: pass linked serverName to a cursor issue...

  1. #1
    David Guest

    pass linked serverName to a cursor issue...

    Need to loop through a Cursor to linked server:
    -----------------------------------------------
    Declare Cursor_Loop_serverName Cursor for
    select cast(name as varchar(30)) name, cast(dbID as varchar(5)) dbID,
    cast(crdate as varchar(25)) crdate
    from ServerName_A.master.dbo.sysdatabases

    ***How could I pass @serverName to change the from to
    from @RemoteServer.master.dbo.sysdatabases?
    I have tried dynamic sql, it did not work after the Declare Cursor for...

    thanks for the help
    David


  2. #2
    Join Date
    Aug 2011
    Posts
    1

    Hi Try This SP.

    Create Procedure usp_UseAllLinkedServers as
    Begin
    Begin try


    DECLARE @instance VARCHAR(100)
    DECLARE @sqlcmd nVARCHAR(1000)

    --Create Cursor for All the LinkedServer Instances

    declare cur_ForEachServer CURSOR FOR
    SELECT DISTINCT instancename FROM [LinkedServerName].[Dbname].SchemaName.TableName
    WHERE instancename IN(select srvname from sys.sysservers )

    OPEN cur_ForEachServer

    FETCH NEXT FROM cur_ForEachServer INTO @instance

    WHILE @@FETCH_STATUS=0
    BEGIN

    --Your SQL script

    FETCH NEXT FROM cur_ForEachServer INTO @instance

    END


    CLOSE cur_ForEachServer
    DEALLOCATE cur_ForEachServer
    End try

    Begin catch
    Select @@ERROR
    End catch

    End

  3. #3
    Join Date
    Apr 2011
    Location
    Dallas, TX
    Posts
    50
    Jey - thanks for posting... Please note that the question was posted on-> 06-18-2002

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •