Calling a Procedure on a Linked Server

• caddywonkus

  Right there with Babe

  Points: 763

  More actions

  September 9, 2008 at 11:23 am

  #869184

  I even set the "remote access" to 1, But still cannot call the procedure on linked server. 😉

  I hate to ask the "is it plugged in" question but... did you restart SQL after setting this (and using reconfigure)? The "remote access" option requires a restart to take effect.

• harmonica1313

  Mr or Mrs. 500

  Points: 511

  More actions

  September 9, 2008 at 10:20 pm

  #869400

  caddywonkus (9/9/2008)

  ---

  I even set the "remote access" to 1, But still cannot call the procedure on linked server. 😉

  I hate to ask the "is it plugged in" question but... did you restart SQL after setting this (and using reconfigure)? The "remote access" option requires a restart to take effect.

  Yes, I did.

• Newbie2LinkedServer

  SSC Enthusiast

  Points: 167

  More actions

  May 22, 2015 at 6:43 am

  #1799977

  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.