Error when executing procedures in linked server.

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  January 25, 2012 at 6:57 am

  #255551

  Experts,

  I am getting the below error when executing a procedure from linked server.

  Could not execute procedure on remote server because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL

  able to access tables without any pbm. Checked all the configurations and seems to be fine.

  Pls help.

  Thanks in advance.

  Smith.

• Fraggle-805517

  SSCrazy Eights

  Points: 8907

  More actions

  January 25, 2012 at 10:01 am

  #1438296

  Can you provide a code same. Specifically how you are trying to call the stored procedure?

  Thanks,

  Fraggle

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 25, 2012 at 10:15 am

  #1438309

  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERNAMEGOESHERE', @optname=N'rpc', @optvalue=N'true'

  GO

  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERNAMEGOESHERE', @optname=N'rpc out', @optvalue=N'true'

  GO

  Hope this does the trick.

  Gianluca

  -- Gianluca Sartori

• Fraggle-805517

  SSCrazy Eights

  Points: 8907

  More actions

  January 25, 2012 at 2:55 pm

  #1438473

  Gianluca Sartori (1/25/2012)

  ---

  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERNAMEGOESHERE', @optname=N'rpc', @optvalue=N'true'

  GO

  EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERNAMEGOESHERE', @optname=N'rpc out', @optvalue=N'true'

  GO

  Hope this does the trick.

  Gianluca

  This is the code for creating the linked server. I was referring to the code you are using for the specific procedure call that is erroring out.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 25, 2012 at 3:24 pm

  #1438482

  There is nothing wrong with the call: it'a a linked server property that controls whether stored procedure calls are allowed or not.

  Run the code I posted and let me know.

  -- Gianluca Sartori

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  January 25, 2012 at 11:35 pm

  #1438575

  Thanks for all the responses.

  I have checked all the configurations including "RPC & RPC Out". Both are allready set to TRUE only.

  Settings all are done properly. Any other possibilities ?

  Thanks,

  Smith.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 26, 2012 at 2:32 am

  #1438623

  My bad, I totally misread your initial post. :blush:

  Run this instead:

  exec sp_configure 'remote access', 1

  reconfigure

  -- Gianluca Sartori

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 26, 2012 at 2:35 am

  #1438627

  Almost forgotten: do it on both servers.

  -- Gianluca Sartori

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  January 26, 2012 at 3:10 am

  #1438638

  Hi,

  Thanks. In fact I have checked those settings as well.

  It was allready done on both the servers. I am getting lost now.

  Thanks.

  Smith.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 26, 2012 at 3:22 am

  #1438641

  Can you script out the linked server and post it here please?

  Also, can you post a sample of the failing code?

  -- Gianluca Sartori

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  January 26, 2012 at 3:55 am

  #1438664

  EXEC master.dbo.sp_addlinkedserver @server = N'linkservername', @srvproduct=N'SQL Server'

  GO

  EXEC master.dbo.sp_serveroption @server=N'linkservername', @optname=N'collation compatible', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'linkservername', @optname=N'data access', @optvalue=N'true'

  GO

  EXEC master.dbo.sp_serveroption @server=N'linkservername', @optname=N'dist', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'linkservername', @optname=N'pub', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'linkservername', @optname=N'rpc', @optvalue=N'true'

  GO

  EXEC master.dbo.sp_serveroption @server=N'linkservername', @optname=N'rpc out', @optvalue=N'true'

  GO

  EXEC master.dbo.sp_serveroption @server=N'linkservername', @optname=N'sub', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'linkservername', @optname=N'connect timeout', @optvalue=N'0'

  GO

  EXEC master.dbo.sp_serveroption @server=N'linkservername', @optname=N'collation name', @optvalue=null

  GO

  EXEC master.dbo.sp_serveroption @server=N'linkservername', @optname=N'lazy schema validation', @optvalue=N'false'

  GO

  EXEC master.dbo.sp_serveroption @server=N'linkservername', @optname=N'query timeout', @optvalue=N'0'

  GO

  EXEC master.dbo.sp_serveroption @server=N'linkservername', @optname=N'use remote collation', @optvalue=N'true'

  Is anything wrong it that ?

  Executing procedure for Linkservername.databasename.schema.procedurename.

  Nothing else. Then I get the mentioned error.

  Thanks,

  smith.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 26, 2012 at 4:02 am

  #1438669

  Did you try:

  EXECUTE database.schema.procedurename AT linkedservername

  Doe this work?

  -- Gianluca Sartori

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  January 26, 2012 at 4:14 am

  #1438671

  Msg 102, Level 15, State 1, Line 2

  Incorrect syntax near 'linkedservername'.

  Able to select data from tables.

  Thanks,

  Smith.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 26, 2012 at 4:23 am

  #1438675

  Sorry, try this:

  EXECUTE('EXEC database.schema.procedurename') AT linkedservername

  -- Gianluca Sartori

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  January 26, 2012 at 4:40 am

  #1438683

  Yes......... This is working.

  So what could be the issue and how to resolve it

  Procedure is executed from an application which cannot modify.

  Thanks a lot. Kindly guide how to resolve this issue and make it to work.

  Smith.