April 26, 2007 at 12:59 pm
Please help!
I am troubleshooting a stored procedure that inserts data from a linked server into a local table. Both servers are SQL Server 2000 SP4
When the statement,
INSERT INTO [LocalTable]
SELECT TOP 100000 WITH TIES 1 iSiteID,*
FROM [LinkedServer].[Database].dbo.[View]
WHERE dtModifyDate > @dtLastModifyDate ORDER BY dtModifyDate'
is executed (either alone or from the stored procedure), the error message
Server: Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' returned an unexpected data length for the fixed-length column '[LinkedServer].[Database].[dbo].[View].Result'. The expected data length is 40, while the returned data length is 80.
OLE DB error trace [Non-interface error: Unexpected data length returned for the column: ProviderName='SQLOLEDB', TableName='[LinkedServer].[database].[dbo].[view]', ColumnName='Result', ExpectedLength='40', ReturnedLength='80'].
The column "Result" is defined as an NVARCHAR(40) on both the local and linked server (datalength=80) as well as the source tables for the view on the linked server.
A google search led me to Microsoft's two solutions, first is to install a hotfix for SP4 http://support.microsoft.com/kb/920930 (I have a red tape policy preventing me for putting this in for the next few months, so this solution isn't an immediate option).
The other option from MS is to use the OPTION(HASH JOIN) query hint as a work around, however this still yeilds the same results.
Does anyone know of another work around I can try. (Converting the logic from DTC (linked Servers) to DTS Packages has already been rejected so I have to work with the Linked Servers)
It's worth noting that this statement is executed on the local server from nine different linked servers, this failure is only occuring on one. There are not immediately noticable differences but then again, I obviously don't know what differences to be looking for.
-
April 26, 2007 at 1:09 pm
Can you insert into a temp table first with that field being 80 chars and then truncate field width in another insert to LIVE data. I know it sucks, but you cannot install a patch that will fix the issue.
April 26, 2007 at 1:26 pm
Since the local table is my temp table this doesn't suck so bad.
I just did as you suggested and increased the length of the column to 80, 255, and 4000... each still returned the same error message. The error is eroneous, the column defintion for Result (the failed column) is NVARCHAR(40) on both the source tables that make up the view used by linked server. the column definition for Result on the view is also NVARCHAR(40) and the column defintion for Result on the Local table that is NVARCHAR(40).
I'm still confused, any thing else I can try?
-
April 26, 2007 at 2:03 pm
In the link that you posted, it's annoying that MS doesn't tell you the reason for the issue and just says to upgrade to SP4... oh well... Lets try to think about possible reasons:
There can be several issues:
1. Have you tried SELECT INTO instead of INSERT INTO? Shouldn't help but worth a shot.
2. A straightforward workaround (which is less than appealing and sucks) is to convert the NVARCHAR to BINARY or SQL_VARIANT (or any other data type for that matter) when bringing the data in from the remote server and the convert the data back to NVARCHAR on the local server.
3. The schema for the remote server is tracked (depending on your server config) on the local server. Have you changed the schema on the remore server recently? If so, we may be able to flush some stale schema that was recorded on the local server.
#2 would work for sure. #1 does worth a shot. #3 - will take more efforts so I would recommend to stick to 1 and 2.
May 2, 2007 at 11:22 am
It took some time and doing, but the solution was found using your first and second suggestion combined. Thank you...
I had use a select .. into from statement and convert to binary data here. This got the data across the Linked Server then I just needed to update the datatype back to VARCHAR... I found that this error was happening on all VARCHAR fields so I had to do the same thing with all...
Thanks again for your assistance.
-
May 14, 2007 at 5:54 pm
DBCC TRACEON(8765) before the sql will help
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply