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 MorrisI 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.aspx?scid=kb;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