February 6, 2013 at 5:31 pm
Quoted_identifier
February 7, 2013 at 10:26 am
Thanks for the reply..
Quoted identifier is already on. turning it off is throwing incorrect syntax error. I believe it has something to do with the Oledb provider seetings but what I dont understand is how can the provider work fine for the linked server when queried through the client machine instead of directly on server.. 🙁
___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:
February 7, 2013 at 5:36 pm
Do you connect with the same user (Login) to sql server in both cases? Is SSMS started with "run as admin" ?
February 11, 2013 at 9:00 am
Thanks for the reply. I did find the soultion. The allow_inprocess option in the properties dialogue box for the OLEDB provider should be checked. I didn't completely understand for what this option is meant for but by trial and error, I found that if I uncheck this option, queries against the linked server on the server machine are not fetching the data throwing the above mentioned error. If I check this option (sometimes need to restart the sql server) queries against the linked server on the server machine work fine. Another thing I found while playing with this option is that this setting doesn't effect the sql server on 32 bit machines but only for 64 bit machines.
To answer your question above I do connect with the same login and the ssms is ran as admin. I am absolutely positive that allow_inprocess is the culprit (unless there is a bug) and now everything works fine for me.
___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:
February 11, 2013 at 4:25 pm
Bravo! I used "allow inprocess" to make some db links to work (e.g. when connecting to oracle), but completely forgot that maybe that can help your case.
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply