June 18, 2009 at 12:38 pm
I'm trying to insert data from an Oracle table using a linked server into a SQL Server 2005 table, using:
insert into tblDirectory
SELECT
E_ID
,N_ID
,LAST_NAME_PRIMARY AS LAST_NAME
,FIRST_NAME_PRIMARY AS FIRST_NAME
,MIDDLE_NAME_PRIMARY AS MIDDLE_NAME
,NAME_PRIMARY as NAME
,convert(datetime, birthdate) as BIRTHDATE
,MAIL_ADDRESS1 AS ADDRESS1
,MAIL_ADDRESS2 AS ADDRESS2
,MAIL_CITY AS CITY
,MAIL_STATE AS STATE
,MAIL_POSTAL_ZIP AS POSTAL
,MAIL_COUNTRY_LDESC AS COUNTRY
,MAIL_PHONE_NBR AS PHONE
,EMAIL_PREFERRED AS EMAIL_ADDR
,C_ID
FROM
dwaredb..oracleschema.oracletable A
WHERE
EXISTS (
SELECT
B.E_ID
FROM
dwaredb..oracleschema2.oracletable2 B
WHERE
B.E_ID = A.E_ID
)
and am receiving the error:
Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.
Removing the 'insert into' statement, I can select from those tables w/o a problem. I've quadruple checked that my data is not too large for any of the fields, using length statements from both the SQL Server and Oracle sides. I've seen the posts concerning the SQL Server 2000 problem with running this thru various user perms, or trying to use the Agent; I'm running this from SSMS using my sysadmin account and the user for the linked server has dbowner privs on the destination database.
I set the connection up using the Oracle OLE DB.
The Oracle column definitions are varchar(x) and are not exactly the same size as the SQL Server column definitions, which are varchar(x) also,
SQL Server: N_ID(varchar(20)
Oracle: N_ID(varchar2(60))
is this a problem? I don't have a lot of control over those definitions. As I stated earlier, I've confirmed the data is not too large for the destination table.
My questions are:
1. Can someone help me trace the steps that SQL Server goes thru to convert/insert this data? Reading those other posts, it sounds like this error could be either misleading, or is referencing a system table that I'm overloading.
2. Has anyone seen this before?
TIA.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
June 18, 2009 at 12:43 pm
dh (6/18/2009)
I'm trying to insert data from an Oracle table using a linked server into a SQL Server 2005 table, using:insert into tblDirectory
SELECT
E_ID
,N_ID
,LAST_NAME_PRIMARY AS LAST_NAME
,FIRST_NAME_PRIMARY AS FIRST_NAME
,MIDDLE_NAME_PRIMARY AS MIDDLE_NAME
,NAME_PRIMARY as NAME
,convert(datetime, birthdate) as BIRTHDATE
,MAIL_ADDRESS1 AS ADDRESS1
,MAIL_ADDRESS2 AS ADDRESS2
,MAIL_CITY AS CITY
,MAIL_STATE AS STATE
,MAIL_POSTAL_ZIP AS POSTAL
,MAIL_COUNTRY_LDESC AS COUNTRY
,MAIL_PHONE_NBR AS PHONE
,EMAIL_PREFERRED AS EMAIL_ADDR
,C_ID
FROM
dwaredb..oracleschema.oracletable A
WHERE
EXISTS (
SELECT
B.E_ID
FROM
dwaredb..oracleschema2.oracletable2 B
WHERE
B.E_ID = A.E_ID
)
and am receiving the error:
Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.
Removing the 'insert into' statement, I can select from those tables w/o a problem. I've quadruple checked that my data is not too large for any of the fields, using length statements from both the SQL Server and Oracle sides. I've seen the posts concerning the SQL Server 2000 problem with running this thru various user perms, or trying to use the Agent; I'm running this from SSMS using my sysadmin account and the user for the linked server has dbowner privs on the destination database.
I set the connection up using the Oracle OLE DB.
The Oracle column definitions are varchar(x) and are not exactly the same size as the SQL Server column definitions, which are varchar(x) also,
SQL Server: N_ID(varchar(20)
Oracle: N_ID(varchar2(60))
is this a problem? I don't have a lot of control over those definitions. As I stated earlier, I've confirmed the data is not too large for the destination table.
My questions are:
1. Can someone help me trace the steps that SQL Server goes thru to convert/insert this data? Reading those other posts, it sounds like this error could be either misleading, or is referencing a system table that I'm overloading.
2. Has anyone seen this before?
TIA.
I'd actually say this is your problem:
SQL Server: N_ID(varchar(20)
Oracle: N_ID(varchar2(60))
Both fields should be declared the same, and iirc from other threads, varchar2 in Oracle is nvarchar in SQL Server, so you probably should change the definition of N_ID from varchar(20) to nvarchar(60), Any other differences should also be addressed as well.
June 18, 2009 at 2:35 pm
Thanks for the input, Lynn. It doesn't appear to be a problem to mismatch those column definitions, based on some tests I did, though:
create table test_n_id2
(
n_idvarchar(20)
)
----------------------------
insert into test_n_id2
select
national_id
from
dwaredb..oracleschema.oracletable
----------------------------
oracletable:
national_id varchar2(60 char)
It appears that the problem was with the datetime field, 'birthdate'. When I ran an output of the table structure, using 'sp_columns', I found that the 'length' of the column was 16, but 'scale' was 3. I had done a 'convert(datetime, birthdate)' on that column, but it appears the fix was:
substring(convert(varchar(16),birthdate), 1, 16) as BIRTHDATE
The other forum posts that I had referenced stated that the only culprit could be a text or char field. Anyways, figured it out.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
June 18, 2009 at 3:07 pm
Change this:
insert into tblDirectory
SELECT
E_ID
,N_ID
,LAST_NAME_PRIMARY AS LAST_NAME
,FIRST_NAME_PRIMARY AS FIRST_NAME
,MIDDLE_NAME_PRIMARY AS MIDDLE_NAME
,NAME_PRIMARY as NAME
,convert(datetime, birthdate) as BIRTHDATE
,MAIL_ADDRESS1 AS ADDRESS1
,MAIL_ADDRESS2 AS ADDRESS2
,MAIL_CITY AS CITY
,MAIL_STATE AS STATE
,MAIL_POSTAL_ZIP AS POSTAL
,MAIL_COUNTRY_LDESC AS COUNTRY
,MAIL_PHONE_NBR AS PHONE
,EMAIL_PREFERRED AS EMAIL_ADDR
,C_ID
FROM
to this:
--insert into tblDirectory
SELECT
E_ID
,N_ID
,LAST_NAME_PRIMARY AS LAST_NAME
,FIRST_NAME_PRIMARY AS FIRST_NAME
,MIDDLE_NAME_PRIMARY AS MIDDLE_NAME
,NAME_PRIMARY as NAME
,convert(datetime, birthdate) as BIRTHDATE
,MAIL_ADDRESS1 AS ADDRESS1
,MAIL_ADDRESS2 AS ADDRESS2
,MAIL_CITY AS CITY
,MAIL_STATE AS STATE
,MAIL_POSTAL_ZIP AS POSTAL
,MAIL_COUNTRY_LDESC AS COUNTRY
,MAIL_PHONE_NBR AS PHONE
,EMAIL_PREFERRED AS EMAIL_ADDR
,C_ID
into #TestTable -- new table that will be created
FROM
Then do this:
;with
TargetTable as
(select name, system_type_id, max_length
from sys.columns
where object_id = object_id(N'tblDirectory'))
TestTable as
(select name, system_type_id, max_length
from tempdb.sys.columns
where object_id = object_id(N'tempdb..#TestTable')
select *
from TargetTable
inner join TestTable
on TargetTable.name = TestTable.name
and
(TargetTable.system_type_id != TestTable.system_type_id
or
TargetTable.max_length != TestTable.max_length);
Assuming the column aliases in the select are the same as the column names in tblDirectory, that will tell you very, very rapidly exactly which column(s) don't match.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply