Solved

problem with querying linked server

Posted on 2002-06-17
6
1,945 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
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
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.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

770 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