SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 String or binary data would be truncated problem..		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

korssane
Posting Yak Master

104 Posts
Posted - 01/16/2010 :  10:59:43  Show Profile  Reply with Quote
Hi Folks,

i am trying to insert data into a local table ( SQL 2K5) and i am getting this error message :
"String or binary data would be truncated problem.."

As per many replies it says to set the ANSI_WARNINGS to OFF and then it wil insert anyway..thing has been done but now i am getting another error message :
" heterogenuous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for...".


any help how can i :

1- identify which column is affected which is the best solution for me..
2- or Just ignore the inserted string ..


thanks for the help

Kristen
Test

United Kingdom
22191 Posts
Posted - 01/16/2010 :  11:39:27  Show Profile  Reply with Quote
"String or binary data would be truncated problem.."

I don't think ANSI_WARNINGS to OFF etc. is going to help.

You have to find the data that is too long to fit in the target column.

One thing which may help is something like this:

Lets say your query is:

INSERT INTO TargetTable (T_Col1, T_Col2, ...)
SELECT S_Col1, S_Col2, ...
FROM SourceTable
WHERE ...

change that to:

SELECT MAX(DATALENGTH(S_Col1)) AS S_Col1_LEN, MAX(DATALENGTH(S_Col2)) AS S_Col2_Len, ...
FROM SourceTable
WHERE ...

to see what the actual lengths of the data column are, and then compare that to the Target Table's columns.

You only need to do this for Char data, not for Dates / INTs etc. (although you may need to check that you are not trying to stor a large INT into a SMALLINT datatype etc.)
Go to Top of Page

korssane
Posting Yak Master

104 Posts
Posted - 01/16/2010 :  13:02:05  Show Profile  Reply with Quote
hi ,
thanks for the reply,,,
the select statement is done using an Openquery statement pulling data from Oracle db ?
is that gonna be the same?

thanks
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 01/16/2010 :  13:17:34  Show Profile  Reply with Quote 

SELECT MAX(DATALENGTH(S_Col1)) AS S_Col1_LEN, MAX(DATALENGTH(S_Col2)) AS S_Col2_Len, ...
FROM OPENQUERY(...

should be fine, I think.

Better to do the DATALENGTH tests at the SQL end (i.e. outside the OPENQUERY function), rather than at the Oracle end (which would be inside the OPENQUERY SQL statement) because maybe MS SQL is converting the Oracle data in some way that is increasing the length of the fields (compared to how Oracle sees them)

You might also try:

SELECT	*
INTO	#TEMP
FROM	OPENQUERY(...

but beware this will place a Create Table Lock on TEMPDB for the duration of the OPENQUERY (in case that blocks other people!!)

That should give you the #TEMP table to play around and interrogate - which may be useful if the OPENQUERY takes ages and you want to try several different things.

Either that or put a LIMIT on the rows within the Oracle query to debug your tests with only a few rows:

SELECT ...
FROM	OPENQUERY(MyOracleConnection, 
    'SELECT *
    FROM
    (
        SELECT Col1, Col2, ...
        FROM   MyOracleTable
        WHERE  ....
    ) X WHERE ROWNUM <= 100'
)
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 01/16/2010 :  13:19:59  Show Profile  Reply with Quote
P.S. I know *next-to-nothing* about Oracle, so that code may be horrible!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000