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?
LVL 3
mte01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post how are you trying to execute the SP?
0
mte01Author Commented:
This is how I'm trying to execute it:

EXEC [192.168.168.14].EmeraldCalls.dbo.IIIGUsersConsumptionByMonth @yearn, @monthn
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
If you have a linked server you should use the linked server name. Why are you using an IP address?
0
mte01Author Commented:
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)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
mte01Author Commented:
I was able to fix it myself.

It turns out the connection string was wrong as it should be like this:

EXEC master.dbo.sp_addlinkedserver @server = N'192.168.168.14', @srvproduct=N'EmCyb', @provider=N'SQLNCLI', @provstr=N'SERVER=192.168.168.14'

with the text in @provstr replacing the one that I had put above (Data Source...etc).
I assumed it was working, but it turns out that it created linked server to the same server itself (1st server), and since the 2 servers had the same login credentials and the 2 DBs had many similar tables, I had thought that my selects were working while actually they were just doing it on the same server (so of course any new SP call won't work)

Thx for your help in all cases guys!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mte01Author Commented:
And apparently Enlist=false comes by default, so that's how it worked.
0
mte01Author Commented:
I was able to fix the issue in the connection string myself
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.