January 4, 2007 at 5:44 pm
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.
January 4, 2007 at 10:27 pm
Use the 4 part name: [Server].[Database].[Owner].[Procedure]
Exec MSDB4.MyDatabase.dbo.MyProcedure
January 4, 2007 at 10:48 pm
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.
January 4, 2007 at 10:55 pm
Post your code where you call the procedure. You're not calling it right.
January 5, 2007 at 2:53 am
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
January 5, 2007 at 2:58 am
And how would you start the job from TSQL on the remote server?
By executing sp_startjob using the 4 part name.
January 5, 2007 at 3:01 am
Yup... Forgot to mention that
/Kenneth
January 5, 2007 at 4:24 am
When you say that it works on MSDB4 have you tested it using the same user account the calling server is using?
January 5, 2007 at 11:32 am
It's not a permissions issue. It's the way he s calling the procedure. Until he posts that code, we can only speculate.
January 5, 2007 at 1:20 pm
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'
January 7, 2007 at 10:07 pm
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''].
January 7, 2007 at 11:44 pm
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.
January 8, 2007 at 8:03 pm
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