Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
korssane
Posting Yak Master
104 Posts |
 Posted - 2010-01-16 : 10:59:43
|
| 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
22859 Posts |
Posted - 2010-01-16 : 11:39:27
|
"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 SourceTableWHERE ... change that to:SELECT MAX(DATALENGTH(S_Col1)) AS S_Col1_LEN, MAX(DATALENGTH(S_Col2)) AS S_Col2_Len, ...FROM SourceTableWHERE ... 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.) |
 |
|
|
korssane
Posting Yak Master
104 Posts |
Posted - 2010-01-16 : 13:02:05
|
| 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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-16 : 13:17:34
|
| [code]SELECT MAX(DATALENGTH(S_Col1)) AS S_Col1_LEN, MAX(DATALENGTH(S_Col2)) AS S_Col2_Len, ...FROM OPENQUERY(...[/code]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:[code]SELECT *INTO #TEMPFROM OPENQUERY(...[/code]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:[code]SELECT ...FROM OPENQUERY(MyOracleConnection, 'SELECT * FROM ( SELECT Col1, Col2, ... FROM MyOracleTable WHERE .... ) X WHERE ROWNUM <= 100')[/code] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-16 : 13:19:59
|
| P.S. I know *next-to-nothing* about Oracle, so that code may be horrible!! |
|
|
|
|
|
|
|
|
String or binary data would be truncated problem..