March 22, 2011 at 7:46 pm
I have 3 servers that each have a link to "SERVER4". On two of the servers I can run this query:
SELECT
regions_id,
region_description
FROM SERVER4.mydatabase.dbo.regions
However, on the third server when I try this I get the error "Invalid object name mydatabase.dbo.regions".
When I use OPENQUERY it is successful:
SELECT
regions_id,
region_description
FROM OPENQUERY(
[SERVER4],
'SELECT
regions_id,
region_description
FROM mydatabase.dbo.regions'
)
All three server's links are set up the same way. Why would I need to use OPENQUERY on one of the servers when I don't have to use it on the other two? Is there a setting somewhere on the third server that I need to look for?
March 22, 2011 at 8:35 pm
The query
SELECT
regions_id,
region_description
FROM mydatabase.dbo.regions
does not use a linked server. The queries that work must actually be getting the data from a database on those servers.
To use a linked server, the query should look like
SELECT
regions_id,
region_description
FROM [LinkedServerName].mydatabase.dbo.regions
March 23, 2011 at 2:21 pm
SS,
My original question was written incorrectly. I have revised it. It does work with the four part name, as I have corrected, above, on two of the three servers. However, on the third server, I have to use OPENQUERY. My question is - why do I need to use OPENQUERY on one of the servers and not on the other two?
March 24, 2011 at 4:16 am
All I can think of that there is a permission problem. What credentials are being used when connecting to the linked server from each of the 3 servers ?
March 24, 2011 at 7:14 am
I am using the same credentials on all three servers. The only difference with this third server is that it's a 64-bit and the others are 32. The linked server itself is a 64-bit as well, and this particular server also has a link back to the third server which I can query normally (both are 64-bits) if you can follow my logic.
March 24, 2011 at 8:09 am
Not an answer, but a thought...
I had a similar situation a few years back at another site... I am sorry, the specifics are not coming back to me but I believe I had to run a script on the 32bit machine to update the catalogs...Instcat.sql, perhaps?
good luck.
March 24, 2011 at 7:43 pm
Actually, the 32 bit machines ARE able to query the linked server without using OPENQUERY. It is the 64-bit that must use OPENQUERY. And I have another 64-bit server that does not have this problem.
March 24, 2011 at 8:23 pm
I'd also be inclined to think it is a permission issue. Running OPENQUERY is likely using a different login to connect to the server than the linked server is.
You could prove this one way or the other by defining the linked server to map all logins to a SysAdmin login. If you still get the error then it's not a permission issue. (Of course, you don't run like that. Once proved, remove the mapping.)
Also have you checked the server logs for other errors, particularly failed logins, that might help refine your problem?
Steve.
March 24, 2011 at 9:52 pm
i suspect this is the problem of data source.
Can you please confirm that all the server which u using in linked server are of similar type....i mean all sql server and same version??
Also if your server is properly configured in linked server list and reporting problem in query only then i dont think it should be permission problem. Try to expand your linked server from ssms, are you able to see the required objects? If not then its going to be permission issue. Configure your security tab accordingly.
----------
Ashish
March 25, 2011 at 12:17 am
I suspect that there is a setting that is different on the third server. Try checking all the settings by right clicking on the linked server and scripting it out on one of the servers that work as well as the one that doesn't and compare them.
Also, is the collation the same on all of the servers? I don't know if it could be a problem but maybe there is a case sensitivity issue with the collations.
March 25, 2011 at 6:42 am
I looked back in my notes last night. The situation I had faced was that I had a SQL Server 2005 x64 installed on a x64 version of windows server. Linked server queries would not work against SQL Server 2000 servers. This was only applicable to that particular combination (SQL Server X64 querying against SQL Server 2000 x86), all other linked server connections to the x86 server worked as expected.
After some digging, I found that the catalogs on the 2000 machine had not been properly updated by the service pack 3 application.
The solution was to manually run the instcat.sql file against the x86 SQL Server 2000 instance so that the x64 SQL Server 2005 instance could communicate with it.
The problem was with the listening end rather than with the calling end.
Since you have told us very little about your environment this still qualifies as 'just a thought'.
If you have not already, you may want to look at MS KB 906954 to see if it is similar to your situation.
Good luck.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply