Linked Server issue: DBSCHEMA_COLUMNS

Hi All -
I'm hoping someone can help me with this recurring problem. I have a
server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
me the error.
Server: Msg 7351, Level 16, State 2, Line 1
OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
columns of object 'DBSCHEMA_COLUMNS'.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
ORDINAL=-1]].
In the past, I rebuilt the server and it worked for a couple of months.
When the problem started again, I contacted Microsoft PSS and Chandra
determined the
"instcat.sql
script in the service pack did not run correctly on my
system. We backed up the master database, ran the script,
and I now have access to the linked servers.
"
The same error has occurred again on the same server! None of my other
5 servers(same configuration) has ever received this error.
I have all the servers linked, using sa username and password to
connect so I have full admin rights.
I saw a thread on this issue from April 23, 2003 between Gunter Van
Hauwe and Vikram Jayaram(MS).
I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
the same output as I receive with the query. It happens no matter
which database or table I choose. It happens from all the linked
servers.
If I run the query with OpenQuery, I receive data and not the error,
but this isn't practical for all of our applications.
Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
that and I receive all the tables and columns from the master database
on sql02.
I'm not sure what to do next. I know I can fix it by reinstalling, but
reattaching 100 databases after the reinstall is getting a bit tiring.
Thanks for any help you can provide.
Michelle Morris
I have seen similar issues when the MDAC or other service packs between the
servers were too far apart, say 2.1 to 2.7 (I know, too much a spread, but it
has been a while.)
You might check that compatibility level.
"mimorr" wrote:

> Hi All -
> I'm hoping someone can help me with this recurring problem. I have a
> server, Windows 2000 Advanced Server with SP3, SQL2000 Enterprise
> Edition with SP3a, 8GB RAM, 4 Intel Procs, which repeatedly is giving
> me the error.
> Server: Msg 7351, Level 16, State 2, Line 1
> OLE DB provider 'SQLOLEDB' could not map ordinals for one or more
> columns of object 'DBSCHEMA_COLUMNS'.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> IColumnsInfo::MapColumnIDs returned 0x40eda: [COLUMN_NAME=IS_COMPUTED
> ORDINAL=-1]].
>
> In the past, I rebuilt the server and it worked for a couple of months.
> When the problem started again, I contacted Microsoft PSS and Chandra
> determined the
> "instcat.sql
> script in the service pack did not run correctly on my
> system. We backed up the master database, ran the script,
> and I now have access to the linked servers.
> "
> The same error has occurred again on the same server! None of my other
> 5 servers(same configuration) has ever received this error.
> I have all the servers linked, using sa username and password to
> connect so I have full admin rights.
> I saw a thread on this issue from April 23, 2003 between Gunter Van
> Hauwe and Vikram Jayaram(MS).
> I ran Vikram Jayaram's suggested dbcc traceon(3604, 7300) and received
> the same output as I receive with the query. It happens no matter
> which database or table I choose. It happens from all the linked
> servers.
> If I run the query with OpenQuery, I receive data and not the error,
> but this isn't practical for all of our applications.
> Vikram Jayaram then suggested sp_tables_ex and sp_columns_ex. I tried
> that and I receive all the tables and columns from the master database
> on sql02.
> I'm not sure what to do next. I know I can fix it by reinstalling, but
> reattaching 100 databases after the reinstall is getting a bit tiring.
> Thanks for any help you can provide.
> Michelle Morris
>
|||OK, I gave that a try using Microsoft's cc_pkg.exe
http://support.microsoft.com/default...b;en-us;301202
and here are my results:
My client workstation where I run QA: MDAC 2.7 SP1 on Windows XP SP1
SQL02 that is the target in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
SQL03 that is the source in the Linked server: MDAC 2.7 SP1 MDAC 2.7
SP1 Refresh
Since the servers are the same, I launched Query Analyzer on SQL03 and
ran a query to a SQL02 database. I got the same error as reported
above.
JRPM, thank you for the suggestion.
Michelle
sql