September 23, 2008 at 10:39 am
Ok, if I run this package in the DTS Designer, it runs fine. If I run this package from the DTSrun prompt, it fails and gives the following output.
This has been running fine for a long time, so I know it's nothing to do with security at the dos level on my end. I am pulling from another ODBC datasource (non SQL Server) and dumping to a text file.
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000
DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000
. . . (omitted lines here)
DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 19000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 19000
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147221499 (80040005)
Error string: Need to run the object to perform this operation
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100
Error Detail Records:
Error: -2147221499 (80040005); Provider Error: 0 (0)
Error string: Need to run the object to perform this operation
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100
Error: -2147221499 (80040005); Provider Error: 0 (0)
Error string: Need to run the object to perform this operation
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4700
Error: -2147221499 (80040005); Provider Error: 0 (0)
Error string: Code execution exception: EXCEPTION_ACCESS_VIOLATION
Error source: Microsoft Data Transformation Services (DTS) Data Pump
Help file: sqldts80.hlp
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
Error: -2147220440 (80040428); Provider Error: 0 (0)
Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 700
The log file of SQL Agent has the following error row :
2008-09-23 11:37:38 - ! [298] SQLServer Error: 446, Cannot resolve collation conflict for equal to operation. [SQLSTATE 42000]
I am not doing any kind of join, just a simple, single table select statement, with no joins or even a where clause. I'm just pulling all the rows with no calculations on the columns. I am also dumping to a txt file, so I know there's no problem with an table on my end.
There are multiple char columns in the table I'm pulling from, but I've got it isolated down to one column. If I include that column, the job fails. If I don't include that column, the job succeeds.
September 23, 2008 at 11:23 am
Sounds like there could be some kind of funny character in that column somewhere...
I ran into a similar problem using an RMS database as an ODBC datasource. There were a number of non printable Ascii characters that caused me issues that I had to remove before I could load it up into my database.
Could be perhaps it's a character that the ODBC driver just can't handle. any chance you can query that column for records that contain things other than alpha numeric values and then exclude those rows to see if it works? If you said this is something that's just begun to happen you'd think you'd be able to narrow it down to rows recently added /updated.
-Luke.
September 23, 2008 at 11:26 am
I just remembered the bit about it working fine in DTS Designer but not DTSRUn. any chance they are using different versions of the same driver? when I haad issues liek this it was when getting data from RMS and pushing it to a PostgreSQL db. I upgraded my Postgres driver and it started having issues I'd never seen before. I rolled the driver back and all was well...
According to MS it was something with ODBC conformance levels or some such and they basically said they couldn't support it.
-Luke.
September 23, 2008 at 11:35 am
First off, thanks for the quick replies.
I have looked at the column after pulling it down into a table, a text file and into an access database. The field is a char(1) and there is just a 'Y' in the field. It doesn't look any different than the row before or after.
I have only installed one version of the driver on that server. I don't think I have ever even done a uninstall of the driver either. Only one installation.
If I go through the DTS designer, I can pull the whole table no problem into a text or sql server table. If I exclude the column, then everything runs fine in the designer and through the DTSrun prompt.
September 23, 2008 at 11:36 am
Luke L (9/23/2008)
Sounds like there could be some kind of funny character in that column somewhere...
This was my first guess, but why would it work in the designer and not the DTSrun?
September 23, 2008 at 11:44 am
September 23, 2008 at 1:09 pm
I have pulled the whole table to a text file and a sql server table through the DTS designer and the data looks fine.
November 13, 2009 at 8:58 am
Here's a suggestion:
The problem may arise if you have different versions of SQLServer installed on the same box, or even if you have different versions of DTSRun in different directories from installing a backwards compatibility patch.
So, when you run the package from Designer, it executes with the correct DTSRun version, but when you call it from SQLServer it executes with a different version.
You can try changing the order of the directories in the PATH system parameter or you can include the full path to the DTSRun.exe in the job step. I find it easier to include the full path in the job step.
Kurt Soutendijk
Sr SQL Server DBA/Developer
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply