Linked Server query - "Invalid Object Name"

  • marykdba

    Mr or Mrs. 500

    Points: 574

    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?

  • happycat59

    One Orange Chip

    Points: 29195

    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

  • marykdba

    Mr or Mrs. 500

    Points: 574

    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?

  • happycat59

    One Orange Chip

    Points: 29195

    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 ?

  • marykdba

    Mr or Mrs. 500

    Points: 574

    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.

  • IGW

    Valued Member

    Points: 57

    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.

  • marykdba

    Mr or Mrs. 500

    Points: 574

    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.

  • Fal

    SSCrazy

    Points: 2984

    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.

  • crazy4sql

    SSCoach

    Points: 19590

    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

  • UMG Developer

    SSChampion

    Points: 13482

    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.

  • IGW

    Valued Member

    Points: 57

    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 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply