Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

problem with querying linked server

Posted on 2002-06-17
6
Medium Priority
?
1,989 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 600 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

670 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