Solved

problem with querying linked server

Posted on 2002-06-17
6
1,972 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 5

Accepted Solution

by:
spcmnspff earned 200 total points
ID: 7083829
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
ID: 7084227
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
ID: 7086475
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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 7

Expert Comment

by:lozzamoore
ID: 7086478
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
ID: 7093352
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
ID: 7093364
Unless anyone has any other things I can try, I will consider this closed and give the points to spcmnspff
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

751 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