How do I call a stored procedure from a linked server?

  • Hi,

    Have a SQL Server MSDB1 (2000 Standard edition) linked to another SQL Server MSDB4 (also 2000 Standard edition) and I have not been able to work out how to execute a stored procedure to the linked server (executing a stored procedure on MSDB4 from MSDB1) ?

    The linked server connection is ok as I've been able to select tables by using the OPENQUERY command.

    Any help would be greatly appreciated

    Thanks Warwick.

     

  • Use the 4 part name:  [Server].[Database].[Owner].[Procedure]

    Exec MSDB4.MyDatabase.dbo.MyProcedure


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Thanks I tried that but I get the error message below:-

    Server: Msg 7357, Level 16, State 2, Line 2

    Could not process object 'exec pubs..wf_test_sp'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

    OLE DB error trace [Non-interface error:  OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=exec pubs..wf_test_sp'].

    the stored procedure contains :-

    ALTER DATABASE msv_prod_ed SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    restore database msv_prod_ed

    from disk = '\\msfile\sql_backups$\MSCARESQL\MSCare_Prod_ED\MSCare_Prod_ED_db_200701041830.BAK'

    with NORECOVERY,

       MOVE 'MSCare_Prod_ED_Data' TO 'E:\Pivotal\Data\MSV_PROD_ED_Data.MDF'

    RESTORE DATABASE msv_prod_ed WITH RECOVERY

    ALTER DATABASE msv_prod_ed SET MULTI_USER

    If I run the stored procedure from the MSDB4 server it works fine.

    Thanks Warwick.

     

     

     

  • Post your code where you call the procedure. You're not calling it right.

     


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Another idea instead of messing with remote proc calls of a user proc, could be to set up a job that execs the restore on MSDB4.. Then you could just start the job to get it going..?

    /Kenneth

  • And how would you start the job from TSQL on the remote server?

    By executing sp_startjob using the 4 part name.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Yup... Forgot to mention that

    /Kenneth

  • When you say that it works on MSDB4 have you tested it using the same user account the calling server is using?

  • It's not a permissions issue. It's the way he s calling the procedure. Until he posts that code, we can only speculate.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Try adding a harmless line that returns something to the "client" to the end of the stored procedure, such as:

    select convert(varchar(5), case @@error when 0 then 'OK' else 'Error' end) as 'Alter_Status'

    or just:

    select cast(1 as int) as 'whatever'

  • Thank you for the feedback Guys.

     

    Below is the copy of the stored procedure I’m trying to run on the remote server (MSDB4) and the results to you suggestions. Unfortunately not quite there!!!

     

     

    ALTER     procedure wf_test_sp

     

    as

     

     

    ALTER DATABASE msv_prod_ed SET SINGLE_USER WITH ROLLBACK IMMEDIATE

     

    restore database msv_prod_ed

    from disk = '\\msfile\sql_backups$\MSCARESQL\MSCare_Prod_ED\MSCare_Prod_ED_db_200701071833.BAK'

    with NORECOVERY,

       MOVE 'MSCare_Prod_ED_Data' TO 'E:\Pivotal\Data\MSV_PROD_ED_Data.MDF'

     

     

    RESTORE DATABASE msv_prod_ed WITH RECOVERY

     

     

    ALTER DATABASE msv_prod_ed SET MULTI_USER

     

     

    select convert(varchar(5), case @@error when 0 then 'OK' else 'Error' end) as 'Alter_Status'

     

     

     

    GO

     

    RESULTS:-

     

    select * from OPENQUERY(msdb4, 'exec pubs..wf_test_sp')

     

    The above SQL now restores the database with the added line in the stored procedure (select convert(varchar(5), case @@error when 0 then 'OK' else 'Error' end) as 'Alter_Status')

    But it still returns the error message below:-

     

    Server: Msg 7357, Level 16, State 1, Line 1

    Could not process object 'exec pubs..wf_test_sp'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

    OLE DB error trace [Non-interface error:  OLE DB provider unable to process the objectroviderName='SQLOLEDB', Query=exec pubs..wf_test_sp'].

     

     

     

     

    I tried calling the job on the remote server (see below) but with no success.

     

    EXEC msdb..sp_start_job @job_name ='msdb4.msdb.dbo.''LS - Restore Database'''

    Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 61

    The specified @job_name ('msdb4.msdb.dbo.'LS - Restore Database'') does not exist.

     

     

    select * from OPENQUERY(msdb4, 'exec msdb.dbo.sp_start_job @job_name = ''LS - Restore Database''')

    Server: Msg 7357, Level 16, State 2, Line 2

    Could not process object 'exec msdb.dbo.sp_start_job @job_name = 'LS - Restore Database''. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

    OLE DB error trace [Non-interface error:  OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=exec msdb.dbo.sp_start_job @job_name = 'LS - Restore Database''].

  • To run the restore job remotely, call it like this:

    EXEC msdb4.msdb.dbo.sp_start_job @job_name ='LS - Restore Database'

    To run the procedure remotely, don't use OpenQuery. OpenQuery is not a multiple dataset reader. The output returned by the restore is being returned and causing OpenQuery to fail. Call it like I said to in my first post:

    Exec msdb4.pubs.dbo.wf_test_sp

     

    Be sure to include the owner name. When using the 4 part name, the owner name is required, not optional.

     


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Fantastic!! Thank you for your help it worked from Query Analyzer... Now I'll just have to get it working SQL Server Agent / Jobs.

    Warwick.

     

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply