February 20, 2002 at 3:34 pm
I'm using a SQL 2000 (SP2) server to sp_addlinkedserver to a SQL 7.0 (SP3) server, same LAN. I get the following error when SELECTing data from a table that has had columns removed and added: "OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time." All other tables in the linked server and database work fine. Is this a known issue? What is the solution for this?
Thanks!
Mike
February 20, 2002 at 5:29 pm
What is the error number. I went looking and thhe only information I find similar is for error 7355 which was taken care of with SP2 for 2k. I will look some more.
February 20, 2002 at 5:33 pm
The error number is 7353.
February 20, 2002 at 6:05 pm
I find no details on this and why it occurrs. Can you post the table CREATE statement so I can test it in my environment to see if I can reproduce. I am at a loss otherwise.
February 20, 2002 at 6:35 pm
Selecting via code or a stored proc?
Andy
February 20, 2002 at 6:50 pm
Andy, it happens in both SQL statements run through query analyzer and in stored procedures referencing the table on the linked server.
Antares868, just create a table with some varchar, char, bit, columns on the linked server. Delete one of the columns except the last one. Add a new column on to the end. Then try to access it from the first server (through the linked server).
February 20, 2002 at 7:25 pm
Ok I will give it a wirl tomorrow and let you know what I come up with.
February 21, 2002 at 5:47 am
Ok I know what causes this and am working on a quick fix plus reporting to Microsoft. Will post today the fix.
February 21, 2002 at 7:19 am
BASIC UNDERSTANDING OF THE PROBLEM:
OK here is what I discovered, in SQL 7 when a column is deleted the syscolumns table keeps the original ordinal position listed instead of adjusting to what the table looks like now.
Ex:
Table1
ColName OrdPos
--------------
Col1 1
Col2 2
Col3 3
Col4 4
Col5 5
Col6 6
In SQL 7 when you remove Col3, Col4 a result of Ordinal Position would look like this
ColName OrdPos
--------------
Col1 1
Col2 2
Col5 5
Col6 6
SQL 2000 though will actually reset the values and the output looks like this
ColName OrdPos
--------------
Col1 1
Col2 2
Col5 3
Col6 4
OK when you link from SQL 2000 to SQL 7 the link will run several request one being
sp_columns_rowset N'yourTblNamehere', N'dbo', NULL
and it apparently reads the ORDINAL POSITION column which when is first built is in order by when you delete items it is no longer with SQL 7 so it outputs
THE ERROR MESSAGE:
Server: Msg 7353, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.
WHAT I DID:
So I opened Profiler and watched the calls to the remote server and looked at the items it ran, testing each until I saw sp_columns_rowset as the only column related item.
Then running I noticed the ORDINAL POSITION output was not consecutive which made me curious so I deleted and re-added the table with the final version the table was in and ran the process again.
This time the ORDINAL POSITION output was consecutive and when I ran my query from the other server it worked fine.
So I removed another column from before then end of the table and ran query again and got output again.
Then I went back and look at the ORDINAL POSITION output of sp_columns_rowset and again not consecutive.
So I decided to try under SQL 2000, and noticed that whenever I changed the columns that the ORDINAL POSITION reset and remained consecutive.
Now I decided must be that output for is the cause of the error and decided to manually change the values in the syscolumns table myself.
Ran my remote query again, which ran without error.
OK so I know the problem and potential how to fix, but the fix is a bit of a headache.
That is where the following came from.
I decided the best way to quick fix was to build a process I can run to handle it quickly for me.
To use just load into master (as you will probably use it often to clean up) and run like so:
USE YourDBHere
GO
sp_FixColOrder YourTableNameHere
GO
OR
YourDBHere..sp_FixColOrder YourTableNameHere
Anyway here is the fix code and I will send this entire message to Microsoft to follow up on.
Hopefully they will provide a better fix for this in SP4 if I can get in soon enough.
THE PROCEDURE:
-------------------------------------Procedure Begins Here----------------------------------------
/*
* Procedure Name: sp_FixColOrder
* Full Name: Fix Column Order
* Created: 2/21/2002
* Created By: James Travis
*
* Desc: Fix column order listing in syscolumns table for specified table.
* Notes: For use with SQL 7 only, not tested elsewhere. Procedure makes use of
* changing value in system table which can cause issues and even though
* I have tested I make no guarantees on the effects of this procedure.
*
*/
CREATE PROCEDURE sp_FixColOrder
@table sysname
AS
DECLARE @SQLState VARCHAR(2000)
--Configure server to allow ad hoc updates to system tables
EXEC master.dbo.sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
/*Build string to update object, the only reason I build a string is the allow updates exec does not allow straight SQL to occurr.*/
SET @SQLState = 'UPDATE
syscolumns
SET
colid = TruePos,
colorder = TruePos
FROM
syscolumns
INNER JOIN
(
SELECT
[name],
[id],
colorder,
(SELECT COUNT(*) + 1 FROM syscolumns ic WHERE ic.colorder < c.colorder AND ic.[id] = c.[id]) as TruePos
FROM
syscolumns c
WHERE
[id] = OBJECT_ID(''' + @table + ''')
) AS CalcVals
ON
syscolumns.[name] = CalcVals.[name] AND
syscolumns.[id] = CalcVals.[id] AND
syscolumns.colorder = CalcVals.colorder'
EXEC (@SQLState)
--Configure server to disallow ad hoc updates to system tables
EXEC master.dbo.sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
February 21, 2002 at 5:04 pm
Bravo! Great job of running it down AND documenting how you get there.
Andy
February 21, 2002 at 5:36 pm
Thanks it was enjoyable to go to the level I did.
February 21, 2002 at 6:56 pm
Thanks Antares686 for your solution. It works now. I had determined it must have to do with the Ordinal Number of the Column Position in the syscolumns table. However, I wasn't aware that SQL 2000 automatically re-numbered the Ordinal Position in syscolumns when a column is deleted from a table.
Thanks again!
Mike
February 21, 2002 at 7:12 pm
Again it was fun, I enjoy trying to find out exactly what SQL Server does.
Don't roll your eyes at me. I will tape them in place.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply