The Output Column Has A Precision That's Not Valid
May 5, 2008
Hi,
I'm importing data from and oracle database to an SQL one through a SSIS package, I'm getting this error:
"The output column "earned_hours" has a precision that is not valid. The precision must be between 1 and 38".
the package runs but returns this column as NULL values
earned_hours is of type "NUMBER" in oracle (some of the values are decimals), I tried making it numeric(x,y),float or decimal(x,y), but I'm still getting the same results.
does anybody know why is this happening or have a solution for this error?
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.
Our shop recently upgraded to MS SQL 2005 server from the prior SQL 2000 product.
I receive and error during processing related to inserting a value into a field of datatype real. This worked for years under MS SQL 2000 and now this code throws an exception.
The exception states:
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 15 ("@TEST"): The supplied value is not a valid instance of data type real. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.
This error is caused by inserting several values that fall outside of a range that MS SQL 2005 documentation specifies.
The first value that fails is 6.61242e-039. SQL Server 2005 documentation seems to indicate that values for the datatype real must be - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38.
Why doesn't 6.61242e-039 just default to 0 like it used to?
I saw an article that might apply, even though I just use a C++ float type and use some ATL templates.
Is my question related to this post?http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=201636&SiteID=1
I have a data source that I access via odbc in a DataReader Source component in SSIS. I can access the data fine. However, I am having problems with certain fields that are numeric (specifically home prices ranging from 100,000.00 to 99,999,999.00). In the advanced editor for my data reader source under the input and output properties tab, in data reader output under the external columns and output columns, these fields for some reason default to numeric data types with a precision of 4 and a scale of zero, not large enough to hold the data that is coming in. This causes errors that make the data come in as null (after i specify to ignore the errors).
I can change the precision and scale to 18 and 4 in the external columns, but when I try to change the datatype, precision or scale in the output columns I get the following message:
Property Value is not valid.
The details are:
Error at Import DataReader Source: The data type of output columns on the component "DataReader Source" cannot be changed. Error at DataReader Source: System.Runtime.InteropServices.COMException (0xC020837D) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
I'm getting a strange error. I have created a Datatable and in this table I have multiple Columns. I have given them all a name and now when I want to put data in the DataTable I get a error. All goes well until I try to Update the Datatable
I have inherited a database that has a column defined as bit. Looking at BOL it shows the valid values are 0,1, null. However there are several rows of data which have -1. Is BOL wrong? we think a VB program runs an update which changes this.
Cannot use dynamic sql in current context. So need some help regarding this.I am developing a stored procedure to update a table. Sending Column names as parameters, but not able to use them as given below.INSERT INTO Books (@Column1, @Column2) values.. Any way to execute without using dynamic sql?..Thanx.
I was trying to get my data from olb table to excel. but i got this error my dataflow is as follows: oledb source-> excel destination
i have got a excel file template at the destination all ready. with the column names+ the sheet all ready. but somehow it got stuck... anybody can help? thanks!
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user)) BEGIN DROP PROCEDURE InsertTimeCard END go /********************************************************************************************************* ** PROC NAME : InsertTimeCardHoursWorked ** ** AUTHOR : Demetrius Powers ** ** TODO/ISSUES ** ------------------------------------------------------------------------------------ ** ** ** MODIFICATIONS ** ------------------------------------------------------------------------------------ ** Name Date Comment ** ------------------------------------------------------------------------------------ ** Powers 12/11/2007 -Initial Creation *********************************************************************************************************/ CREATE PROCEDURE InsertTimeCard @DateCreated DateTime, @EmployeeID int, @DateEntered DateTime, @SerializedXML text, @Result int output as declare @NewTimeCardID int select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards -- proc settings SET NOCOUNT ON
-- local variables DECLARE @intDoc int DECLARE @bolOpen bit SET @bolOpen = 0 --Prepare the XML document to be loaded EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML -- check for error IF @@ERROR <> 0 GOTO ErrorHandler --The document was prepared so set the boolean indicator so we know to close it if an error occurs. SET @bolOpen = 1
--Create temp variable to store values inthe XML document DECLARE @tempXMLTimeCardExpense TABLE ( TimeCardExpenseID int not null identity(1,1), TimeCardID int, ExpenseRptID int, ExpenseDate datetime, ProjectID int, ExpenseDescription nvarchar(510), ExpenseAmount money, ExpenseCodeID int, AttachedRct bit, SubmittoExpRep bit ) DECLARE @tempXMLTimeCardWorked TABLE ( TimeCardDetailID int not null identity(1,1), TimeCardID int, DateWorked DateTime, ProjectID int, WorkDescription nvarchar(510), BillableHours float, BillingRate money, WorkCodeID int, Location nvarchar(50) ) -- begin trans BEGIN TRANSACTION insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid) values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0) -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep) SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2) WITH ( ExpenseRptID int 'ExpenseRptID', ExpenseDate datetime 'ExpenseDate', ProjectID int 'ProjectID', ExpenseDescription nvarchar(510) 'ExpenseDescription', ExpenseAmount money 'ExpenseAmount', ExpenseCodeID int 'ExpenseCodeID', AttachedRct bit 'AttachedRct', SubmittoExpRep bit 'SubmittoExpRep') -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
-- remove XML doc from memory EXEC sp_xml_removedocument @intDoc SET @bolOpen = 0
-- commit transaction, and exit COMMIT TRANSACTION set @Result = @NewTimeCardID RETURN 0
-- Error Handler ErrorHandler: -- see if transaction is open IF @@TRANCOUNT > 0 BEGIN -- rollback tran ROLLBACK TRANSACTION END -- set failure values SET @Result = -1 RETURN -1
select hierarchy.hiername,devicefail.deviceid ,sum(DATEDIFF(minute,started,ended)) as duration ,100 - SUM(datediff(minute,started,ended))/(672 * 60.0000)*100 AS Uptime from devicefail LEFT JOIN device ON device.deviceid = devicefail.deviceid LEFT JOIN hierarchy ON device.hierlevel = hierarchy.hierlevel where devicefail.started >= '2013-02-01 00:00:00'and devicefail.ended <='2013-02-28 23:59:59' and devicefail.componentid like 201 or devicefail.componentid like 0 group by devicefail.deviceid,hierarchy.hiername,devicefail. componentid order by hiername
I have two queries I would like to combine the output on. they are as follows:
Select substring(WrkSta.[Name],1,2) 'Location' ,count (aexe.ReturnCode) as '# Patched' from WrkSta, AeXEvt_AeX_SWD_Execution aexe where WrkSta.WrkStaId=aexe.WrkStaId and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%' or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%' or WrkSta.[Name] like 'ET%') and (aexe.returncode='0' or aexe.returncode ='3010') and aexe.AdvertisementName like 'MS05-035-043%' group by substring (WrkSta.[Name], 1,2)
--- Returns : Location # Patched EP 102 ES 1986 ET 19 AB 174 SU 6 EM 506
and the second one:
Select substring(WrkSta.[Name],1,2) 'Location' ,count (coll.WrkStaId ) as '# Workstation' from WrkSta join AeXNSCollectionMembership coll on WrkSta.WrkStaId=Coll.WrkStaId where coll.CollectionGuid = '38F5DAFC-E09D-49A5-A0FD-370983CA7596' and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%' or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%' or WrkSta.[Name] like 'ET%') group by substring (WrkSta.[Name], 1,2)
--- returns:
Location # Workstations EP 178 ES 2299 ET 24 AB 215 SU 13 EM 582
What I need is :
Location # Workstations # Patched EP 178 102 EI 2299 1986 ET 24 19 AB 215 174 SU 13 6 EM 582 582
No mater how I try to do a join to do this in a single query I end up with what looks like a cross-join and # workstations and # Patched jump to huge numbers. I obviously am having a problem understanding how to set up the select statements so that I can do this in one query or am I following the wrong direction and should be trying something else? Once again I appreciate your help in advance....
Hello,I was wondering if anyone can help me figure something out.Is it possible to do a querey in MS SQL server and have the resultsreturned so that each result in each row is preceeded by the columnname?eg. instead of usual output -colName1, colValue1,colName2,colValue2,colName3,colValue3 ?Also I would like to only have this for certain columns ie in theabove example only for columns 2 and 3Thank you! :-)Yas
I would like to get the actual name of the column that has the error. Using the ErrorColumn (int value) I thought there would be some type of lookup collection based on the input (like column names)- if there is, can someone tell me how to get to it?
I have my error output writing to a stored proc, but instead of "32226" as the column name, I need to have the actual name of the column. I am going from Flat File to OLE DB Destination. I have a Script Component getting the output to write to my sproc, and I just need to get the column name.
I have a Data Flow Task that extracts some data using a DataReader Source and loads it to a Raw File Destination. I am getting the following error message:
[DataReader Source [2357]] Error: The value was too large to fit in the output column "LASTCOL" (2558).
I thought that using a Raw File Destination would avoid this type of problem. How can I resolve this issue?
using sql database, i have a smallmoney column. when i enter an amount, 50.00 for example, i have 50.0000 displayed. is there a way to only have 50.00 displayed? same with the smalldatetime, is there a way to limit the display to "mm/dd/yyyy" without the "hh:mms am"
I would like to change the output of a column into a hyperlink however I am not sure that is possible without some further post-processing. I am aware I would have to do:
Code: select '<a href="' + URL + '" target="_blank" nav="web">' + tbl_ID.ID + '</a>' from tbl_ID however this obviously just exports the text <a href="[URL]" target="_blank" nav="web">[ID]</a>
This would work fine if I was exporting it into some sort of HTML table however this doesn't work with excel for example. Is there a way to make this possible or would I have to create a macro in excel to add the url with the ID separately?
I use bcp command to output to excel, it works. But I want to format the excel, some column width are too small,user need adjust the column width, otherwise it shows ######.
How can I set columns width when I use bcp output to excel.
Also, can bcp command output to multiple excel sheets and add report title in each excel sheet?
Is there a way to supress output on one column in a SP, using data from the same row?
Like This: SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP)AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility, Active FROM dbo.tblResidents
But which returns null for some of the columns if DOT is not null? DOT is the Termination Date, so the only columns that have any meaning once there is data in the DOT column are DisReason and DisSummDue. Also, if DOT *is* null, then the above columns also have no meaning. I tried several variations of the following, but I can't figure it out
CREATE PROCEDURE [dbo].[spTesting] AS BEGIN SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP) AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, Facility FROM dbo.tblResidents A WHERE DOT IS NULL UNION SELECT Last, First, DOP, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility FROM dbo.tblResidents I END GO
ID AMT 1001 1234.560 1001 34.560 1001 134.000 1002 45.000 1002 3456.000 1003 5678.999
I need to create a fixed length data file..For example, ID char(6) and amt num(10.3) and sum(23.3)
It should be group and order by ID : 01 Represent ID line and 02 represent amt ( there can be multiple amt records) and 03 represent sum of amt. 01 + ID 02 + AMT 03 + Sum(AMT)
The output should look like this.. How do this either using a t-sql or SSIS?
I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,
Select SerialNumber From SerialNumbers Where OrderID = @OrderID
If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.
When configuring error output, I want everything that is good in the row to make it to the destination, and then the offending column that is causing an error to be set to NULL, and then sent to the destination as well. In addition, I want to take the offending column's data, and route it over to an error holding table. I know about the ability to redirect the whole row, but I just sort of want to redirect just that column. For example....
Have a table with 5 columns
col1 int null,
col2 int null,
col3 char(3) null,
col4 bit null,
col5 int null
My data flow loads data from a flat file and has a record that looks like this
1 5 ABC R 3
I want the row to make it to the destination as follows....
1 5 ABC NULL 3
Then the offending data needs to go over to my error table
Iam redirecting the error output of a OLEDB destination component to a script component. My aim is to create a HTML report having the information about the bad records, the error occuring in the rows and the column name that fails. The error output provided two new columns i.e the errorcode and errorcolumn , the errorcolumn value for a bad record gives the linage id for the column, is there a way to derieve the name of the column by using the lineage id?
I have an ODBC connection manager to a Progress database. In that database there is a column declared as a string of 10 characters long. However, some data in this column is actually up to 15 characters long. This makes my DataReader Source fail everytime I try to run my package because it sets the output column like this :
Datatype : Unicode string [DT_WSTR] Length : 10
Is there any way to solve this without changing the datatype in the Progress database (that is beyond my control) ?
Currently running Sql Server 2005Is it possible to issue the delete command and capture the affected rows asxml types that will be stored in an audit table with an xml column?Something along the lines of:delete from source_tableoutput(deleted.*into audit_table (xml_audit_column)for xml auto)where source_table.column = @delete_value
I ran a SQL statement to get the data from two tables using join. These two tables have a column in the same name €śID€?.
SQL=€?select * from table1 a, table2 b where a.ID=b.ID€? Set RS=DB.Execute(SQL)
The statement worked. But when I output the records in my ASP pages, I got an error: ADODB.Recordset error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal€?
This is the line caused error: <%= RS(€śa.ID€?) %>
It seemed that SQL Server recordset doesn€™t carry the table aliens. If I removed a. from ID the error was gone but it gave me the value of b.ID.
Here is what I get. Is this an install problem or is this how this software works??
===================================
Error at Data Flow Task [Data Conversion [720]]: An output cannot be added to the outputs collection.
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC020800F (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------ Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.InsertOutput(DTSInsertPlacement eInsertPlacement, Int32 lOutputID) at Microsoft.DataTransformationServices.Design.Controls.ComponentMetaDataTreeView.AddOutput()
