Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

problem with querying linked server

Posted on 2002-06-17
6
Medium Priority
?
1,999 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 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's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

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.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

824 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