Cross Server Query

[JefB]

We have 2 servers; SR1 and SR2. SR1 is running SQL7 on WinNT and SR2 is running SQL2k on WinNT.

Both servers have 'WBData' databases with 'MasterBuild' tables. The tables contain the same data and are designed the same. (We use the SR2.WBData.dbo.MasterBuild table as a backup to the other one). Permissions give all users equal access.

When I try to build a view in SR2.WBData to display data from the SR1.WBData table, I enter:

SELECT *
FROM SR1.WBData.dbo.MasterBuild

Syntax checks out OK, however, when I try to run it, the code changes to:

SELECT *
FROM SR1.WBData.dbo.MasterBuild MasterBuild_1

and I get this error:

"[Microsoft]{ODBC SQL Server Driver][SQL Server]OLE DB provider ‘SQL0LEDB’ supplies inconsistent metadata. An extra column was supplied during execution that was not found at compile time."

I assume that the extra column is the MasterBuild_1, which I did not put in, the system did!

What am I missing?

JefB

 

[MeanGreen]

MasterBuild_1 is simply an alias for the tablename. Can you run the sql and retrive just one column to see if that works?

 

[JefB]

A single column makes no difference. I also tried:

Select MasterBuild_1.Client
...

Same error.

Why would it assign an alias, anyway?

JefB

 

[MeanGreen]

The reason it assigns an alias is he tool you are using is trying to make it more readable or probably because it recognizes a table called MasterBuild within the current mapped schema and requires the alias to identify which MasterBuild you want to deal with.
As for the error, only thing I can figure on that is the schema design between 7.0 and 2K is slightly different causing the error. What service packs do you have applied to each Sql Server? You will probably want to be sure and have the latest Service Packs for both. Also, try to apply the service pack to your local machine if that is where you are running the Query from. This only a wild guess, but I hope it helps.

Good Luck.