Output column has a precision that is not valid (loading from Oracle using OraOLEDB.Oracle.1)

Hi!

I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:

TITLE: Microsoft Visual Studio

Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:

1) Union

I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"

Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.

2) SQL command from variable

The select basically looks like this:

"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"

Again, even if I cast all columns (like in the union), I still get the same error.

Any help would be greatly appreciated. Thanks!

Try casting the oracle fields to NUMBER(p,s) with a scale of at least one (1). Even though you look to be working with integers inside Oracle, casting it to a value of nnn.0 won't change that fact. I think then you'll be able to bring it into SSIS and then cast to integer there.|||I've tried to cast as DECIMAL(x, y) and NUMBER(x, y), and it works for "normal" selects, but not when the select contains UNION or is a SQL command variable.|||I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x
|||

JayH wrote:

I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x

For the UNION this seems to work, but I've removed all errors and warnings by casting to char (TO_CHAR(x) as myColumn)). Not pretty, but it works for both the UNION and the SQL command variables. For the UNION I'll change to your suggestion. For the SQL command variable I'll have to stick with TO_CHAR until something better pops up.

Note:

Precision is not valid error is displayed when some function is executed on the numeric column (like SUM, MIN, MAX, AVG, NVL etc) when using the OraOLEDB.Oracle.1. Seems like SSIS can't determine the numeric type when such a function is applied.

Thanks all!

|||Hmm.. Now I got the CAST(x AS NUMBER(w, z)) to work in the SQL command from variable. The meta data is not updated in the source component if you just change the SQL script in the variable (which was causing the problem). You have to change to a table or something else than the SQL command variable. Press OK. Edit the source component to use the SQL command variable again, and then the meta data is updated and it works. Previously I did get that "do you want to update meta data..."-message and pressed OK, but it seems like it was not updated correctly. By changing to a table and then back to the SQL command variable, everything is updated correctly. So now I don't use TO_CHAR anymore.|||

I found everything you said is correct, but I needed to go a bit further. The query against Oracle in the SQL Server 2000 DTS package was adding two numeric fields together, such as "SELECT ..... f1 + f2 AS BALANCE FROM.....". Even though the 2 source fields are the same datatype, decimal(15,2), for some reason SSIS still tripped over the precision and scale of the external and output columns. The work-around was to return both source columns separately, and then add them via a derived column task.

Output column has a precision that is not valid (loading from Oracle using OraOLEDB.Oracle.1)

Hi!

I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:

TITLE: Microsoft Visual Studio

Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:

1) Union

I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"

Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.

2) SQL command from variable

The select basically looks like this:

"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"

Again, even if I cast all columns (like in the union), I still get the same error.

Any help would be greatly appreciated. Thanks!

Try casting the oracle fields to NUMBER(p,s) with a scale of at least one (1). Even though you look to be working with integers inside Oracle, casting it to a value of nnn.0 won't change that fact. I think then you'll be able to bring it into SSIS and then cast to integer there.|||I've tried to cast as DECIMAL(x, y) and NUMBER(x, y), and it works for "normal" selects, but not when the select contains UNION or is a SQL command variable.|||I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x
|||

JayH wrote:

I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x

For the UNION this seems to work, but I've removed all errors and warnings by casting to char (TO_CHAR(x) as myColumn)). Not pretty, but it works for both the UNION and the SQL command variables. For the UNION I'll change to your suggestion. For the SQL command variable I'll have to stick with TO_CHAR until something better pops up.

Note:

Precision is not valid error is displayed when some function is executed on the numeric column (like SUM, MIN, MAX, AVG, NVL etc) when using the OraOLEDB.Oracle.1. Seems like SSIS can't determine the numeric type when such a function is applied.

Thanks all!

|||Hmm.. Now I got the CAST(x AS NUMBER(w, z)) to work in the SQL command from variable. The meta data is not updated in the source component if you just change the SQL script in the variable (which was causing the problem). You have to change to a table or something else than the SQL command variable. Press OK. Edit the source component to use the SQL command variable again, and then the meta data is updated and it works. Previously I did get that "do you want to update meta data..."-message and pressed OK, but it seems like it was not updated correctly. By changing to a table and then back to the SQL command variable, everything is updated correctly. So now I don't use TO_CHAR anymore.|||

I found everything you said is correct, but I needed to go a bit further. The query against Oracle in the SQL Server 2000 DTS package was adding two numeric fields together, such as "SELECT ..... f1 + f2 AS BALANCE FROM.....". Even though the 2 source fields are the same datatype, decimal(15,2), for some reason SSIS still tripped over the precision and scale of the external and output columns. The work-around was to return both source columns separately, and then add them via a derived column task.

sql

osql with Unicode ?

Hi
How can I insert data in sql file(unicode - Thai chars) into sql server
using osql ?
regards
Dishan
Whe I run these files(inserts) using osql, enterprise manager shows
"?" as data
please help..
|||Sorry its my mistake.. I didnt put N( N'unidata' ) in my sql script

OSQL Unicode Insert of ^F (Hex 06) from Registry Multi-String Value is Duplicating in DB

I'm using a Unicode sql script imported using OSQL. One of the values
we are attempting to insert is a Registry Multi-String value by passing
a string to a stored procedure. These Multi-String values appear to be
delimited by a Hex 06 (^F) character. When I import this character,
embedded in a string preceeded by an N, i.e

N'something something2 something3'

I end up with TWO of this character in the db. I get :

something something2 something3

Any help figuring out why or how to fix this? We MUST use Unicode due
to extended character sets, so NOT using Unicode is NOT a solution.Seems to be an encoding issue. We are using SQLXML to retrieve the
data initially. That seems to be UTF-8 encoding the data which creates
an xml stream that contains an odd escape sequence. This escape
sequence is not then being translated into the correct unicode
representation in the insert scripts (which are created by transforming
the xml). We are going to try Unicode encoding on the SQLXmlCommand
object and setting the xml to encoding=UTF-16 to see what happens.

osql and unicode

Hi,
Is anybody aware of any problems with inserting unicode chars via osql. I've
got some unicode text and when it gets inserted it inserts not the character
I am expecting. It works fine from query analyser etc. My collation is set t
o
the generic collation no problems there. Thanks.
Panos.Just some extra information.
The string I have in the input file is:
WIENER B?RSE
and it becomes
WIENER BíRSE
When the same script is run from query analyser then it insert WIENER B?RSE
which is correct. Thanks.
"Panos Stavroulis." wrote:

> Hi,
> Is anybody aware of any problems with inserting unicode chars via osql. I'
ve
> got some unicode text and when it gets inserted it inserts not the charact
er
> I am expecting. It works fine from query analyser etc. My collation is set
to
> the generic collation no problems there. Thanks.
> Panos.