Link to home
Create AccountLog in
Avatar of mte01
mte01Flag for Lebanon

asked on

Executing SP from SQL Server Linked server

Hey experts,

  I have a sql server 2005 from which I've created a linked server on an sql server 2008 DB using the following connection string:

'Data Source=192.168.168.14,1433;Network Library=DBMSSOCN;Initial Catalog=EmeraldCalls;Enlist=false'

Note that I'm doing it this way in order to be able to add the Enlist=false flag to be able to execute a stored procedure without being enlisted in a txn (if I create a regular linked server, it won't allow me to execute a SP without setting the 'remote proc transaction promotion' option to false, and since my primary server is 2005, I can't set this option).

The connection is working successfully, and I'm using a user login that has all privileges (similar to sa), but I'm not able to execute any stored procedure (it errors out with "could not find stored procedure..etc")

Any idea on how to fix this?
Avatar of Nakul Vachhrajani
Nakul Vachhrajani
Flag of India image

Because it's a linked server, have you tried using the EXECUTE...AT option? http://beyondrelational.com/modules/2/blogs/77/posts/11274/executing-queries-against-linked-servers.aspx
Can you post how are you trying to execute the SP?
Avatar of mte01

ASKER

This is how I'm trying to execute it:

EXEC [192.168.168.14].EmeraldCalls.dbo.IIIGUsersConsumptionByMonth @yearn, @monthn
If you have a linked server you should use the linked server name. Why are you using an IP address?
Avatar of mte01

ASKER

This is the linked server name (we name them by IP to be able to recognize them).
Everything else is working (selecting from tables, views..etc)
I see.
Then check if the remote access is enabled on both instances:
EXECUTE sp_configure 'remote access'

Open in new window

If returns 1 then it is. If not you need to reconfigure it to allow remote access.
ASKER CERTIFIED SOLUTION
Avatar of mte01
mte01
Flag of Lebanon image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of mte01

ASKER

And apparently Enlist=false comes by default, so that's how it worked.
Avatar of mte01

ASKER

I was able to fix the issue in the connection string myself