a string literal in the expression exceeds the maximum allowed length

Question

Post reply

a string literal in the expression exceeds the maximum allowed length

sirzatkoc

SSC Veteran

Points: 217
More actions
January 29, 2010 at 2:39 am

#209552

Hi everyone,i have a problem which i'm trying to find a solution more than 2 days
first i wanna explain my issue,
i'm getting data from ORACLE servers into SQL server 2005 with SSIS packages.
First important thing i should say,i dont have any rights ON ORACLE servers, i cant create stored procs or changing data types etc...
So far everything was great,but they recently updated ORACLE servers to a newer version and my nightmare started. Our old SSIS packages are working good,but take too much time,i found another way to make things faster.But new version of Oracle causes headaches.
First data type on ORACLE NUMBER was first problem.SSIS 2005 says, column xxx has a precision is not valid,the precision must between 1 and 38.
First i was 'whats this a new data type?or something like that!!!'
i fastened my seatbelt opened Toad checked table and column properties and thats there,NUMERIC data type but no precision.I found a solution for this,i changed data type with CAST(column_name as NUMARIC(x,y)) column_name
i was happy,my job was going on,but i have another headache now,i have a table that has so many columns and about 20 columns has no precision on oracle side, ,made casts, coppied it to my variable and the final part,crash
'a string literal in the expression exceeds the maximum allowed length 4000 characters etc...' :crazy:
anyone had a problem like this.I searched on net but all suggestions are create proc (i dont have rights)
and some more funny solutions were update your SQL to 2008 etc (i'm not working for my own company and sure noone will update my servers )

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

wschampheleer SSCertifiable Points: 5504 More actions · Answer 1

Check this.

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

wschampheleer SSCertifiable Points: 5504 More actions · Answer 2

A solution could be to create a linked server to the Oracle database and create your view/procedures on the SQL server box.

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

surya-434952 SSC Eights! Points: 952 More actions · Answer 3

I guess u need change the column data type to text or ntext if your using sql server 2005 onwards

Hope this will solve ur problem

Thanks,

Surya

sirzatkoc SSC Veteran Points: 217 More actions · Answer 4

surya-434952 (1/29/2010)
I guess u need change the column data type to text or ntext if your using sql server 2005 onwards
Hope this will solve ur problem
Thanks,
Surya

I tried this with advanced editor,

turn Numeric(0,0) To Numeric(x,y)

when i press refresh button , SSIS chenges it to NUMERIC(0,0) again , i messed up,

i also tried to make those columns string on advanced editor,but turns back to NUMERIC(0,0) when i refresh the columns. I made something wrong ?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

First data type on ORACLE NUMBER was first problem.SSIS 2005 says, column xxx has a precision is not valid,the precision must between 1 and 38.
First i was 'whats this a new data type?or something like that!!!'

If SSIS says it is not valid that is because it is not, reason is Oracle Number data type is not like SQL Server Numeric which like Decimal Oracle Number is a bucket used to store all numerical data types which includes Double, Float and Real which comes without precision and scale so use Oracle SQL Developer to find out what Oracle is storing in the 20 columns, then you can convert each to Microsoft type.

Kind regards,
Gift Peddie

sirzatkoc SSC Veteran Points: 217 More actions · Answer 6

i have found a solution for this,actually i found my mistake.

i changed the connection from oracle provider for ole db to microsoft oledb provider for oracle :w00t:

what a silly mistake i did :blush:

thanks for help