SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Invalid character value for cast specification Code: 0xC020901C


Invalid character value for cast specification Code: 0xC020901C

Author
Message
Tim-153783
Tim-153783
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1139 Visits: 763
We load our data warehouse nightly and everything was working for the last year. Recently the job copying the data from SQL Server to SQL Server started to fail at night. Rerunning the same job in the morning with the exact same data will work. Several nights in a row it works, it then fails, and we rerun the next morning with the same nightly data and it works.

The server is running the 64 version of SQL Server 2005. The data is not showing up in our error table because it is trying to log the inserted date and is dying saying the conversion of that date is invalid. Inserted date was a simple getdate() statement from the previous step.

Any thoughts on how to troubleshoot?



Message
Executed as user: SVR45\CluAdmin. ...0.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:02:46 AM Error: 2008-10-10 02:02:56.41
Code: 0xC0202009
Source: HDW_DFLT_LOB HDW_ERR_TBL [1660]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005. An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "Invalid character value for cast specification". End Error
Error: 2008-10-10 02:02:56.44 Code: 0xC020901C


Source: HDW_DFLT_LOB HDW_ERR_TBL [1660]

Description: There was an error with input column "insertion_date" (2158)
on input "OLE DB Destination Input" (1673).
The column status returned was: "Conversion failed because the data value overflowed
the specified type.". End Error Error: 2008-10-10 02:02:56.44
Code: 0xC0209029


Source: HDW_DFLT_LOB HDW_ERR_TBL [1660] Description: SSI... The package execution fa... The step failed.


Paul-913530
Paul-913530
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 150
Hi there, We have been having the same problem with ssis on sql Server 2005 64 bit version. Sometimes it fails with an invalid cast specification on a datetimefield. When i run the package again after it has failed it will work fine on the same data.

Is there a data conversion step in the package that fails?

We also have another weird problem which a data flow at one of our customers. This one doesn't fail. it just inserts empty ('') rows. The data flow has a sourcestep, which is based on a query. The query selects 6 hard coded rows. Select 'a' as code, 'the first letter' as description union select 'b' etc..
The package does not fail but eventhough hard coded just 6 blank rows are added to the table. We only notice because the database grows for a table that joins on the code field and when empty it gets multiplied by 6.

It seems to me sql server 64 bit has some ssis issues. I find many posts with the same problem.. That is the problem of the cast specification and not finding the solution.

Have you found a solution or a workaround?

Greetings,

P
Tim-153783
Tim-153783
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1139 Visits: 763
Opened up a case with M$ to assist. Hopefully, they will have a better troubleshooting methodology.
brigitte.janssen
brigitte.janssen
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 79
I get the same errors ,please keep me posted when you find a solution.Unsure
Paul-913530
Paul-913530
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 150
Hi Tim,

I hope Microsoft can help too. We were also thinking about opening a case, but the problem is random and haven't been able to reproduce it.

Grts,

P
Birdman-969830
Birdman-969830
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 174
I ran into a similar problem with SQL 2005 64 Bit, 64 GB memory with multiple instances. I verified the service account that SQL was running under had 'lock pages in memory enabled'

I would get an over flow error after running an ETL for an hour.
(Arithmetic overflow error converting nvarchar to data type numeric) When I would restart the server, the query would return good results until I reran the ETL process.

I enabled AWE (yes i know it says it ignored but other say it has it uses in locking memory pages) I also set the max server memory to 15 GB for this instance and re-ran my ETL. So far that has seemed to clear up the error. It appears that it was a memory issue.

my 2 cents worth....
bloggerpillai
bloggerpillai
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 86
Did you find a resolution for this issue? Did MS help?

We are having a pretty much similar situation, and didn't want to reinvent the wheel..
Birdman-969830
Birdman-969830
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 174
I can tell you that we've not had an issue since limiting the memory and max parallel tasks on the 64Bit box. We are running multiple instances and it seems that you must lock pages in memory and set max memory in this situation.
Paul-913530
Paul-913530
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 150
the invalid character for cast specification problem we had only seemed to be on Datetime columns.
first thing we did is remove the data conversion steps in some of the package which were converting varchar to nvarchar. The datetimefields were converted from datetime to datetime.... But removing the conversion to the same datatype did not solve the problem, it did happen a lot less.
It also happens when copying from oledb source to oledb destination without a data conversion step. When the source is not based on a query but just copies a table.
After removing the data conversions we also:
- switched on "Lock pages in memory"
- limited the max memory for sql server
- limited the number of parallel executing packages. We had many packages running at the same time.

Now we haven't seen the conversion error for a while (2 weeks) ... so far so good..
bloggerpillai
bloggerpillai
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 86
Paul, thanks - all these mods you made makes sense. I haven't had a recurrence in a while, but now that I said it, you know what's coming.

But atleast I have a few things I can try out now, if it does happen.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search