August 24, 2010 at 9:43 pm
OK, I have a linked server called HOUSEOFPAIN.
There is a DB on it called BRUISE.
There is a stored proc on it called sp_SmackDaFoo and it requires params to call like so...
EXEC sp_SmackDaFoo 'coworker', '7/4/2010', 'cried'
This should return all the coworkers I smacked on July 4th, 2010 and cried. Works fine and returns the recordset.
On the remote sql server rpc & in/out is set to true.
How do I call the SP that's on the linked server "HOUSEOFPAIN" and return the reults? The fully qualified path to the SP would be...
HOUSEOFPAIN.BRUISE.dbo.sp_SmackDaFoo 'coworker', '7/4/2010', 'cried'
I followed this guys examples but no dice: http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling-stored-procedure-on-linked-server/
Thanks in advance folks!
/ok, those aren't the real names but the names were changed to protect the innocent. :hehe:
August 24, 2010 at 11:23 pm
first you need to check the connectivity with
select * from [linkedserver].[databasename].[databaseoowner name].tablename
Additionally, did you get any error when u executed your Sp ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 25, 2010 at 10:47 am
The linked server works properly. I am able to query tables with no probs.
I can also execute a stored proc that exists on the linked server like so....
EXEC [HOUSEOFPAIN].BRUISE..sp_SmackDaFoo 'coworker', '7/4/2010', 'cried'
But that doesn't return the results back to my server the linked server is connected to.
I tried this...
Select * From OPENQUERY([HOUSEOFPAIN], 'EXEC HOUSEOFPAIN.BRUISE..sp_SmackDaFoo '''coworker''', '''7/4/2010''', '''cried'''')
And this...
Select * From OPENQUERY([HOUSEOFPAIN], 'EXEC HOUSEOFPAIN.BRUISE..sp_SmackDaFoo '''coworker''' '''7/4/2010''' '''cried'''')
and I get the error...
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'coworker'.
The SP should return a list of records. Also the SP runs fine on its local server.
August 25, 2010 at 10:44 pm
Warren Peace (8/25/2010)
Select * From OPENQUERY([HOUSEOFPAIN], 'EXEC HOUSEOFPAIN.BRUISE..sp_SmackDaFoo '''coworker''', '''7/4/2010''', '''cried'''')
i guess you have added extra quotes. play with single quotes
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 22, 2015 at 6:49 am
I am newbie to linked server concept, I am looking for help to resolve my issue. Please suggest.
My requirement:
In DB2 data base I have a stored procedure and from SQL 2008 R2 using Linked server I need to be able to execute stored procedure. I am getting the error while I am trying to execute.
Created SP as below with db2admin user:
CREATE OR REPLACE PROCEDURE SP_MyStoredProcedure
LANGUAGE SQL
SPECIFIC SP_MyStoredProcedure
-- EXTERNAL ACTION
BEGIN
.......
END
select * from [MyDb2LinkedServer].[db2DB].[db2admin].TLS_MyDb2Table and I get the results, which means my linked server DB2 connectivity is fine.
Then while executing as below SP got the error
exec [MyDb2LinkedServer].[db2DB].[db2admin].SP_MyStoredProcedure
Error:
OLE DB provider "DB2OLEDB" for linked server "MyDb2LinkedServer" returned message "Routine "*rocedure"?SQL150518145704050?...erver"."SP_MyStoredProcedure"?*?4" (specific name "") is implemented with code in library or path "", function "" which cannot be accessed. Reason code: "". SQLSTATE: 42724, SQLCODE: -444".
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'SP_MyStoredProcedure' on remote server 'MyDb2LinkedServer'.
Here in the error
specific name ""
library or path ""
function ""
Reason code: ""
all are empty, no much information is available.
Need help on this and how this can be resolved? Any help is appreciated.
Note:
"rpc", "rpc out" both options are true.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply