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
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?
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
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?
Nakul Vachhrajani
Because it's a linked server, have you tried using the EXECUTE...AT option? http://beyondrelational.co
Can you post how are you trying to execute the SP?
ASKER
This is how I'm trying to execute it:
EXEC [192.168.168.14].EmeraldCa
EXEC [192.168.168.14].EmeraldCa
If you have a linked server you should use the linked server name. Why are you using an IP address?
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)
Everything else is working (selecting from tables, views..etc)
I see.
Then check if the remote access is enabled on both instances:
Then check if the remote access is enabled on both instances:
EXECUTE sp_configure 'remote access'ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
And apparently Enlist=false comes by default, so that's how it worked.
ASKER
I was able to fix the issue in the connection string myself