HELP: Linked SQL servers issue - cannot call ServerName.Database.dbo.TableName

[Nicholas]

We recently upgraded our production SQL Server to SQL Server 64-bit
Yukon on Windows 2K3 however our development SQL server is still SQL
Server v7.

We have linked servers.

The problem is all the stored procedures fail as well as T-SQL
commands in SQL Query Analyzer because we may no longer call the
server with the 4-part naming convention (server.database.dbo.table)
while OPENQUERY still works such as:

FAILURE:

SELECT ISNULL(SUM(CAST(Reproduction_Cost1 AS money)), 0)
FROM SERVER2.DATABASE2.dbo.TABLE2
WHERE LRSN=34534534

OPENQUERY WORKS:

SELECT ISNULL(SUM(CAST(Reproduction_Cost1 AS money)), 0)
FROM
OPENQUERY(SERVER2, 'SELECT * FROM DATABASE2.dbo.TABLE2 WHERE
LRSN=34534534')

The failure message is:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider did not
give any information about the error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IUnknown::QueryInterface returned 0x80004005: The provider did not
give any information about the error.].

Any help is appreciated!

[Cindy Gross (MSFT)]

One possibility:
From Enterprise Manager go the linked servers and go to Properties of the server in question, Then go to OPTION and uncheck all settings
except "AllowInProcess". If they are all unchecked and still have the problem go the registry (HKLM\Software\Microsoft\Mssqlserver
\providers\SQLOLEDB ) and make sure all except "AllowInProcess" have a value of 0.

You could also try reinstalling MDAC. And check the SQL errorlogs and the event logs to see if anything is reported there that might be useful.

Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.