Solved

problem with querying linked server

Posted on 2002-06-17
6
1,922 Views
Last Modified: 2008-02-26
I have SQL 7 on one machine and have set up a linked server to SQL 6.5. When I try to run just a simple SELECT * FROM statement in query analyzer I get the following error
Server: Msg 7356, Level 16, State 1, Line 1
  OLE DB provider 'SQLOLEDB' supplied inconsistent metadata for a column.
  Metadata information was changed at execution time.

If I create a view on the SQL 6.5 server and do a SELECT * from the view I get the data without any errors, likewise If I create a sproc that reutrns records I get the records without error. It is only when I try to directly query the 6.5 from the 7. Is there something I am missing, or is this some kind of bug/known issue.
0
Comment
Question by:blakeh1
  • 3
  • 2
6 Comments
 
LVL 5

Accepted Solution

by:
spcmnspff earned 200 total points
Comment Utility
I extracted this bit of info:

"In SQL Server version 6.5 and earlier, OLE DB applications had to use the OLE DB Provider for ODBC layered over the Microsoft SQL Server ODBC driver. While OLE DB applications can still use the OLE DB Provider for ODBC with the SQL Server ODBC driver, it is more efficient to use only the OLE DB Provider for SQL Server."

From this site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ad_1qzm.asp

So, rather than using the oledb for sql server driver  to link to the 6.5 server . . . use the oledb for ODBC driver and setup a DSN poining to the 6.5 server.

That should do it. =)



0
 
LVL 6

Author Comment

by:blakeh1
Comment Utility
I have set up my linked server using OLEDB for ODBC
I can no perform a straight select on the table instead of having to create a view on the 6.5 server, however I am still getting an error when I query a particular field(s) that have been defined on the 6.5 server as a user datatype. The user datatype is set as Char (however in the table design it shows (StandardID(varchar)). Bottom line is these fields are now causing the error
Server: Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[SQL_Prod01].[SlxPCH].[sysdba].[PCHAccountData].CreateUser'. The expected data length is 12, while the returned data length is 5.
If we pad these fields with spaces to be a true fixed lenght there is no problem querying.
I suspect this is also the cause of the original error.
Using the OLEDB for ODBC at least lets me directly query the tables, so in part, is has solved most of my problem, the rest I will have to find some way of dealing with (most likely I will have to stick with views anyway, as I will need to pad the fields)
Any other insight would be appreciated
0
 
LVL 7

Expert Comment

by:lozzamoore
Comment Utility
I have seen issues of this kind before, to do with the level of the system catalog.

Normally after every SP, there is the instcat.sql script that runs to update this. (I hope incidentally you are running SP5 on the SQL 6.5 machine?)

Check the table master..spt_server_info, SYS_SPROC_VERSION field.

If this is less than the number reported by @@version, this may be your problem, and you have to re-run the system catalog update script again.

Hope this helps,
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 7

Expert Comment

by:lozzamoore
Comment Utility
I have seen issues of this kind before, to do with the level of the system catalog.

Normally after every SP, there is the instcat.sql script that runs to update this. (I hope incidentally you are running SP5 on the SQL 6.5 machine?)

Check the table master..spt_server_info, SYS_SPROC_VERSION field.

If this is less than the number reported by @@version, this may be your problem, and you have to re-run the system catalog update script again.

Hope this helps,
0
 
LVL 6

Author Comment

by:blakeh1
Comment Utility
I have checked out the version and it is OK. It has to do with the user datatype defined as char, but getting converted to varchar and being truncated, since the error happens as soon as a field is not the number of characters defined by the length of the field in the user datatype. I.e. If all records have 12 chars no problem, but when it encounters a record with 5 chars it causes the err. Padding with spaces in a view is the only way I can get it to work.
0
 
LVL 6

Author Comment

by:blakeh1
Comment Utility
Unless anyone has any other things I can try, I will consider this closed and give the points to spcmnspff
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now