Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006
    Posts
    5

    Unanswered: Execute SP on linked server?

    I've created a stored procedure on several servers. All with the same name and all in a database with same names.
    Now Im trying to execute the stored procedures by executing a script.... I dont get it to work!
    I can execute it successfully by starting it manually:
    exec [CIMPDB01\CIMT1].zz_am.dbo.usp_SpaceMon
    exec [CIMPDB01\CIMT2].zz_am.dbo.usp_SpaceMon
    and so on..... no problem

    But when I try to execute it by using a script (and substitute the instance names with values taken from a table) it wont:

    declare @x int
    declare @dbname varchar(500)
    declare @SQL nvarchar(600)
    set @x = 1

    create table #databases
    (ID int IDENTITY,name varchar(500))

    insert #databases select instancelongname from instances where actief='J'

    while @x <= (select max(id) from #databases)
    begin
    select @dbname = name from #databases where id = @x
    print @dbname
    select @SQL='exec ' + @dbname + '.zz_am.dbo.usp_SpaceMon'
    print @SQL
    execute @SQL

    set @x = @x + 1
    end

    drop table #databases


    Msg 203, Level 16, State 2, Line 17
    The name 'exec [CIMPDB01\CIM].zz_am.dbo.usp_SpaceMon' is not a valid identifier.

    Please help....

  2. #2
    Join Date
    Apr 2006
    Posts
    33
    declare @x int
    declare @dbname varchar(500)
    declare @SQL nvarchar(600)
    set @x = 1

    create table #databases
    (ID int IDENTITY,name varchar(500))

    insert #databases select instancelongname from instances where actief='J'

    while @x <= (select max(id) from #databases)
    begin
    select @dbname = name from #databases where id = @x
    print @dbname
    select @SQL= @dbname + '.zz_am.dbo.usp_SpaceMon'
    print @SQL
    execute @SQL

    set @x = @x + 1
    end

    drop table #databases
    Don't need to use 'exec'.

    Hope this helps,

    David
    David Wiseman
    MCSE, MCSA, MCDBA

    www.wisesoft.co.uk

Posting Permissions

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