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

Question

Post reply

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

Warwick Francis

SSC-Addicted

Points: 485
More actions
January 4, 2007 at 5:44 pm

#122217

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.

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

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

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]

Warwick Francis SSC-Addicted Points: 485 More actions · Answer 2

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.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

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]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

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]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Kenneth Wilhelmsson

SSC-Dedicated

Points: 30043

January 5, 2007 at 3:01 am

#681415

Yup... Forgot to mention that

/Kenneth

Stewart Draper-331992 SSC Journeyman Points: 87 More actions · Answer 7

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 8

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]

Fred Williams-134192 SSCrazy Points: 2482 More actions · Answer 9

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'

Warwick Francis SSC-Addicted Points: 485 More actions · Answer 10

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''].

Robert Davis One Orange Chip Points: 28027 More actions · Answer 11

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]

Warwick Francis SSC-Addicted Points: 485 More actions · Answer 12

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.