Conversion failed error 7341

[tdrclan]

Error msg:
"[MSDASQL].CLMCSP" from OLE DB provider "MSDASQL" for linked server "claimfs3". Conversion failed because the data value overflowed the data type used by the provider. [SQLSTATE 42000] (Error 7341) OLE DB provider "MSDASQL" for linked server "claimfs3" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". [SQLSTATE 01000] (Error 7412). The step failed.

we have two sql servers running the following SP query

insert ......

insert into Claims_temp select * ,
CLYEAR + CLMONTH + CLDAY + CLNO
from dbo.v_FR0901
where clyear not like '19%'

insert into Claims_temp select * ,
CLYEAR + CLMONTH + CLDAY + CLNO
from dbo.v_FR0903
where clyear not like '19%'

insert ......
(the SP has over 300 table to read )

we are using linked servers to connect to the AIX system running a claims database system.

linked server info:
we are using "MS ole db provider for ODBC driver"
product name (DBC/FS3 for server 1 and DBC/FS4 for server 2)

sample view:
CREATE VIEW [dbo].[v_FR0946]
AS SELECT * FROM OPENQUERY(claimfs3,
'select * from clmFR094600') Rowset_1

both server have the same views, linked server info and SP code.

sever1 has never had any problem reading the data from the AIX system.
server1:
win 2000
sql server 2000

server2 has the problem every two or three days. (we tried sql 2005 same issue)
server 2
Win 2003 se
sql server 2008



the column is different each time
and the table it's reading is different (sometimes it's the table is same but more often it's a different table.)

we did use FS3 on server2 but upgraded to FS4 on the vendors recommendation.

we went from "full recovery" to "simple" which stopped the problem for about a month, now it's back. ( 6 errors in the last two weeks) (server2 was always on simple)

if I restart the job in the morning it will complete without any errors.

server 1 starts at 1:30 am. runs about 4:00 hours.
server 2 starts at 4:45 pm. also runs about 4:00 hours.
so no over lapping
the job on server 2 fails about 1 1/2 hours to 2 1/2 hours after is starts.


I'm thinking it's a communication issue and not a data issue, because the sql 2000 Db has no issue reading the same data 4 hours later.
unless sql 2005 and sql 2008 are less forgiving then sql 2000.
(data on the AIX system does not change at night)




TIA

Tim

 

[SQLBill]

check the columns in each of the tables. You might have something like VARCHAR(10), but one has VARCHAR(5). Or NVARCHAR and VARCHAR (NVARCHAR takes two characters for each VARCHAR). Or you might have a SMALLDATETIME when the rest are DATETIME.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[tdrclan]

I'll look at this, but I don't think that is the problem here, unless sql 2008 is nore like to reject the data than sql 2000.

 

[tdrclan]

also,
the code on server1 is the same as server2
they read the same data and use the same table schema.
only different is sql and OS version.