Urgent..Please help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Urgent..Please help

Author

Topic

catchvaas
Starting Member

22 Posts

Posted - 2010-12-19 : 02:50:47

Hi team,
we are receiving the below error on one of jobs in our prod server which is running sql server 2000.

Here's the error from error log file.
DTSRun OnProgress: Copy Data from ef_mstr to [CHNImport].[dbo].[ef_mstr] Step; 69000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 69000

DTSRun OnProgress: Copy Data from ef_mstr to [CHNImport].[dbo].[ef_mstr] Step; 70000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 70000

DTSRun OnProgress: Copy Data from ef_mstr to [CHNImport].[dbo].[ef_mstr] Step; 71000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 71000

DTSRun OnProgress: Copy Data from ef_mstr to [CHNImport].[dbo].[ef_mstr] Step; 72000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 72000

DTSRun OnProgress: Copy Data from ef_mstr to [CHNImport].[dbo].[ef_mstr] Step; 73000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 73000

DTSRun OnError: Copy Data from ef_mstr to [CHNImport].[dbo].[ef_mstr] Step, Error = -2147467259 (80004005)

Error string: Error at Destination for Row number 73571. Errors encountered so far in this task: 1.

Error source: DTS Data Pump

Help file:

Help context: 0

Error Detail Records:

Error: -2147467259 (80004005); Provider Error: 3621 (E25)

Error string:

Error source:

Help file:

Help context: 0

Error: -2147467259 (80004005); Provider Error: 2627 (A43)

Error string:

Error source:

Help file:

Help context: 0

DTSRun OnProgress: Copy Data from ef_mstr to [CHNImport].[dbo].[ef_mstr] Step; 73571 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 73571

DTSRun OnError: Copy Data from ef_mstr to [CHNImport].[dbo].[ef_mstr] Step, Error = -2147220421 (8004043B)

Error string: The task reported failure on execution.

Error source: Microsoft Data Transformation Services (DTS) Package

Help file: sqldts80.hlp

Help context: 1100

Error Detail Records:

Error: -2147220421 (8004043B); Provider Error: 0 (0)

Error string: The task reported failure on execution.

Error source: Microsoft Data Transformation Services (DTS) Package

Help file: sqldts80.hlp

Help context: 1100

Please HELP

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-19 : 09:06:41

This could be due to locking or "bad" column data for the transformation in the specified row number. Are there updates happening to the source column during the transformation? or the destination?

Poor planning on your part does not constitute an emergency on my part.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-19 : 09:41:23

Log the DTS execution to a file. It will give verbose error messaging. Post the exact error here...though I agree with dataguru that it is likely bad data/type mismatch.

catchvaas
Starting Member

22 Posts

Posted - 2010-12-22 : 23:34:53

Hi Dataguru, Yes, the updates are happening to the source column and I did a DBCC checktable on table ef_mstr and it returned no errors.

what do you think can resolve this issue..Please help.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-23 : 01:26:09

If you log it to a file, like I said, no one will have to guess what it is, it will tell you exactly what it is.

catchvaas
Starting Member

22 Posts

Posted - 2010-12-23 : 14:46:52

@Russell...Thank you for the info..but can you give me the steps to log the DTS execution to a file please???

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-23 : 14:57:54

1. Open the package in the DTS designer
2. Right-click a blank spot on the workspace
3. Click Package Properties from the context menu
4. Go to the logging tab in the package properties window
5. Type in a valid path in the error file line.

When the package runs, you'll get detailed logging.

catchvaas
Starting Member

22 Posts

Posted - 2010-12-23 : 16:15:42

Thank you Russell...I did that and here's the error log file of DTS package.

The execution of the following DTS Package succeeded:

Package Name: CHN_Import_Fixed_Asset_Tables
Package Description: CHN_Import_Fixed_Asset_Tables
Package ID: {08CD6533-9EB7-412F-AE04-6FF8D2AAA648}
Package Version: {7F119C8F-ED0F-4214-8B2E-2BE433987FCC}
Package Execution Lineage: {DB867940-12A7-4EAB-A0CB-BDC3DD592B60}
Executed On: USMKEDB043
Executed By: db_admin
Execution Started: 12/24/2010 5:12:44 AM
Execution Completed: 12/24/2010 5:12:48 AM
Total Execution Time: 3.343 seconds

Package Steps execution information:

Step 'Delete from Table [CHNImport].[dbo].[ef_mstr] Step' succeeded
Step Execution Started: 12/24/2010 5:12:44 AM
Step Execution Completed: 12/24/2010 5:12:44 AM
Total Step Execution Time: 0.015 seconds
Progress count in Step: 0

Step 'Copy Data from ef_mstr to [CHNImport].[dbo].[ef_mstr] Step' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution. ( (80004005): ) ( (80004005): )
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

Step Execution Started: 12/24/2010 5:12:44 AM
Step Execution Completed: 12/24/2010 5:12:48 AM
Total Step Execution Time: 3.203 seconds
Progress count in Step: 73875

Step 'Delete from Table [CHNImport].[dbo].[efl_mstr] Step' succeeded
Step Execution Started: 12/24/2010 5:12:44 AM
Step Execution Completed: 12/24/2010 5:12:44 AM
Total Step Execution Time: 0.015 seconds
Progress count in Step: 0

Step 'Copy Data from efl_mstr to [CHNImport].[dbo].[efl_mstr] Step' succeeded
Step Execution Started: 12/24/2010 5:12:44 AM
Step Execution Completed: 12/24/2010 5:12:44 AM
Total Step Execution Time: 0.094 seconds
Progress count in Step: 330

Step 'Delete from Table [CHNImport].[dbo].[fa_mstr] Step' succeeded
Step Execution Started: 12/24/2010 5:12:44 AM
Step Execution Completed: 12/24/2010 5:12:44 AM
Total Step Execution Time: 0.078 seconds
Progress count in Step: 0

Step 'Copy Data from fa_mstr to [CHNImport].[dbo].[fa_mstr] Step' succeeded
Step Execution Started: 12/24/2010 5:12:44 AM
Step Execution Completed: 12/24/2010 5:12:45 AM
Total Step Execution Time: 0.234 seconds
Progress count in Step: 3871

Step 'Delete from Table [CHNImport].[dbo].[facls_mstr] Step' succeeded
Step Execution Started: 12/24/2010 5:12:44 AM
Step Execution Completed: 12/24/2010 5:12:45 AM
Total Step Execution Time: 0.297 seconds
Progress count in Step: 0

Step 'Copy Data from facls_mstr to [CHNImport].[dbo].[facls_mstr] Step' succeeded
Step Execution Started: 12/24/2010 5:12:45 AM
Step Execution Completed: 12/24/2010 5:12:48 AM
Total Step Execution Time: 3.015 seconds
Progress count in Step: 197

Step 'Delete from Table [CHNImport].[dbo].[fad_det] Step' succeeded
Step Execution Started: 12/24/2010 5:12:44 AM
Step Execution Completed: 12/24/2010 5:12:45 AM
Total Step Execution Time: 0.031 seconds
Progress count in Step: 0

Step 'Copy Data from fad_det to [CHNImport].[dbo].[fad_det] Step' succeeded
Step Execution Started: 12/24/2010 5:12:45 AM
Step Execution Completed: 12/24/2010 5:12:45 AM
Total Step Execution Time: 0.172 seconds
Progress count in Step: 3880

Step 'Delete from Table [CHNImport].[dbo].[faloc_mstr] Step' succeeded
Step Execution Started: 12/24/2010 5:12:45 AM
Step Execution Completed: 12/24/2010 5:12:45 AM
Total Step Execution Time: 0.031 seconds
Progress count in Step: 0

Step 'Copy Data from faloc_mstr to [CHNImport].[dbo].[faloc_mstr] Step' succeeded
Step Execution Started: 12/24/2010 5:12:45 AM
Step Execution Completed: 12/24/2010 5:12:45 AM
Total Step Execution Time: 0.063 seconds
Progress count in Step: 867
****************************************************************************************************

Please let me know what need to be done..

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-23 : 16:26:01

What is the source of this step?
What type of task is it? (data pump, execute sql etc.)

Copy Data from ef_mstr to [CHNImport].[dbo].[ef_mstr]

Looks like a connection error.

catchvaas
Starting Member

22 Posts

Posted - 2010-12-23 : 19:56:23

Hi Russell...The source is another instance on the same server.

The task is jus select the data from the table..here's the query of task
select [ef_asset],[ef_p_code],[ef_desc2],[ef_subclass],[ef_dept],[ef_model],[ef_owner],[ef_invnbr],[ef_manuf],[ef_status],[ef_condition],[ef_book1],[ef_book2],[ef_remark],[ef_vend],[ef_vend_tel],[ef_contract_nbr],[ef_warranty_from],[ef_warranty_to],[ef_warranty_fee],[ef_ins_co],[ef_ins_tel],[ef_ins_nbr],[ef_ins_from],[ef_ins_to],[ef_ins_value],[ef_lease_co],[ef_lease_tel],[ef_lease_agr],[ef_lease_from],[ef_lease_rental],[ef_lease_remark],[ef_borrower],[ef_loan_reason],[ef_loan_from],[ef_cust],[ef_lease_to],[ef_loc],[ef_prod_grp],[ef_3rd],[ef_effdate],[ef_loan_to],[country_code],[database_id],[timestamp] from [ProdDB].[dbo].[ef_mstr]

here ProdDB is the DB on another instance usmkedb043\dw05.

Any thoughts?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-23 : 22:01:55

It's an Execute SQL Task? Are you inserting the result to a table?

For a linked server, you need to use 4 part naming convention:

SELECT <field list> FROM from [usmkedb043\dw05].[ProdDB].[dbo].[ef_mstr]

catchvaas
Starting Member

22 Posts

Posted - 2010-12-27 : 13:34:48

Hi Russell...

Well..this job was running without errors till now.

its failing at Step 'Copy Data from ef_mstr to [CHNImport].[dbo].[ef_mstr] Step' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution. ( (80004005): ) ( (80004005): )
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

any thoughts to resolve this?..please

catchvaas
Starting Member

22 Posts

Posted - 2010-12-27 : 13:38:02

I ran the DBCC DBREINDEX and ran the job again..n got this

The execution of the following DTS Package succeeded:

Package Name: CHN_Import_Fixed_Asset_Tables
Package Description: CHN_Import_Fixed_Asset_Tables
Package ID: {08CD6533-9EB7-412F-AE04-6FF8D2AAA648}
Package Version: {7F119C8F-ED0F-4214-8B2E-2BE433987FCC}
Package Execution Lineage: {AE872865-3F89-402B-B455-878E754C1DD5}
Executed On: USMKEDB043
Executed By: db_admin
Execution Started: 12/27/2010 11:48:26 PM
Execution Completed: 12/27/2010 11:48:51 PM
Total Execution Time: 24.562 seconds

Package Steps execution information:

Step 'Delete from Table [CHNImport].[dbo].[ef_mstr] Step' succeeded
Step Execution Started: 12/27/2010 11:48:26 PM
Step Execution Completed: 12/27/2010 11:48:27 PM
Total Step Execution Time: 0.328 seconds
Progress count in Step: 0

Step 'Copy Data from ef_mstr to [CHNImport].[dbo].[ef_mstr] Step' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution. ( (80004005): ) ( (80004005): )
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

Step Execution Started: 12/27/2010 11:48:27 PM
Step Execution Completed: 12/27/2010 11:48:50 PM
Total Step Execution Time: 23.547 seconds
Progress count in Step: 75016

Step 'Delete from Table [CHNImport].[dbo].[efl_mstr] Step' succeeded
Step Execution Started: 12/27/2010 11:48:26 PM
Step Execution Completed: 12/27/2010 11:48:27 PM
Total Step Execution Time: 0.328 seconds
Progress count in Step: 0

Step 'Copy Data from efl_mstr to [CHNImport].[dbo].[efl_mstr] Step' succeeded
Step Execution Started: 12/27/2010 11:48:27 PM
Step Execution Completed: 12/27/2010 11:48:27 PM
Total Step Execution Time: 0.125 seconds
Progress count in Step: 330

Step 'Delete from Table [CHNImport].[dbo].[fa_mstr] Step' succeeded
Step Execution Started: 12/27/2010 11:48:26 PM
Step Execution Completed: 12/27/2010 11:48:27 PM
Total Step Execution Time: 0.359 seconds
Progress count in Step: 0

Step 'Copy Data from fa_mstr to [CHNImport].[dbo].[fa_mstr] Step' succeeded
Step Execution Started: 12/27/2010 11:48:27 PM
Step Execution Completed: 12/27/2010 11:48:51 PM
Total Step Execution Time: 24.203 seconds
Progress count in Step: 3871

Step 'Delete from Table [CHNImport].[dbo].[facls_mstr] Step' succeeded
Step Execution Started: 12/27/2010 11:48:26 PM
Step Execution Completed: 12/27/2010 11:48:27 PM
Total Step Execution Time: 0.39 seconds
Progress count in Step: 0

Step 'Copy Data from facls_mstr to [CHNImport].[dbo].[facls_mstr] Step' succeeded
Step Execution Started: 12/27/2010 11:48:27 PM
Step Execution Completed: 12/27/2010 11:48:27 PM
Total Step Execution Time: 0.11 seconds
Progress count in Step: 197

Step 'Delete from Table [CHNImport].[dbo].[fad_det] Step' succeeded
Step Execution Started: 12/27/2010 11:48:27 PM
Step Execution Completed: 12/27/2010 11:48:27 PM
Total Step Execution Time: 0 seconds
Progress count in Step: 0

Step 'Copy Data from fad_det to [CHNImport].[dbo].[fad_det] Step' succeeded
Step Execution Started: 12/27/2010 11:48:27 PM
Step Execution Completed: 12/27/2010 11:48:29 PM
Total Step Execution Time: 2.141 seconds
Progress count in Step: 3880

Step 'Delete from Table [CHNImport].[dbo].[faloc_mstr] Step' succeeded
Step Execution Started: 12/27/2010 11:48:27 PM
Step Execution Completed: 12/27/2010 11:48:27 PM
Total Step Execution Time: 0.031 seconds
Progress count in Step: 0

Step 'Copy Data from faloc_mstr to [CHNImport].[dbo].[faloc_mstr] Step' succeeded
Step Execution Started: 12/27/2010 11:48:27 PM
Step Execution Completed: 12/27/2010 11:48:27 PM
Total Step Execution Time: 0.047 seconds
Progress count in Step: 867
****************************************************************************************************

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-27 : 15:53:08

Reindexing isn't going to solve a connection error.

1. Is usmkedb043\dw05 a linked server? if it is, you need to use 4 part naming convention as I showed above.
2. What type of task is is it? Is it a Data Transformation task (pump task)? Is it an Execute SQL task?

If it's a pump task, make sure the connection object is valid, and that the source tab in the task references the correct connection object.

If it's an Execute SQL Task, then you need to make sure the server is linked, use 4 part naming convention and make sure that the SQL Agent Service account has permission on the linked server.

catchvaas
Starting Member

22 Posts

Posted - 2010-12-27 : 16:17:17

Hi,

It is just a Data transformation task (pump task)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-27 : 17:29:33

Did you make sure the source is correct? And that the user in the connection object has permission? Is it using windows or sql authentication?

In query analyzer, connect to [usmkedb043\dw05].

Then, what happens if you SELECT TOP 1 * FROM [ProdDB].[dbo].[ef_mstr]

catchvaas
Starting Member

22 Posts

Posted - 2010-12-27 : 17:51:31

Yes...Source is correct..and user in connection object has the permission and using sql authentication.

when I run query SELECT TOP 1 * FROM [ProdDB].[dbo].[ef_mstr] in [usmkedb043\dw05]..I receive

ef_asset ef_p_code ef_desc2 ef_subclass ef_dept ef_model ef_owner ef_invnbr ef_manuf ef_status ef_condition ef_book1 ef_book2 ef_remark ef_vend ef_vend_tel ef_contract_nbr ef_warranty_from ef_warranty_to ef_warranty_fee ef_ins_co ef_ins_tel ef_ins_nbr ef_ins_from ef_ins_to ef_ins_value ef_lease_co ef_lease_tel ef_lease_agr ef_lease_from ef_lease_rental ef_lease_remark ef_borrower ef_loan_reason ef_loan_from ef_cust ef_lease_to ef_loc ef_prod_grp ef_3rd ef_effdate ef_loan_to country_code database_id timestamp
------------ ------------ ------------------------------------------------ ----------- ------- ------------ ------------ --------- ------------ --------- ------------ -------- -------- ------------------------------------------------ -------- ---------------- ------------------------ ------------------------------------------------------ ------------------------------------------------------ ----------------------------------------------------- ------------------------ ---------------- ------------ ------------------------------------------------------ ------------------------------------------------------ ----------------------------------------------------- ------------------------ ---------------- ------------------------ ------------------------------------------------------ ----------------------------------------------------- ------------------------------------------------ ------------------------ ------------------------------------------------ ------------------------------------------------------ ------------ ------------------------------------------------------ ------------ ----------- ------ ------------------------------------------------------ ------------------------------------------------------ ------------ ----------- ------------------------------------------------------
000000100 HP notebook 4150 PIII/50 XXX buy 00XXXXX Active G NULL NULL 0.0 NULL NULL 0.0 NULL 0.0 NULL ikylau NULL 33 MPS 0 2000-02-01 00:00:00 NULL CHN HKG 2010-12-28 06:00:00

(1 row(s) affected)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-27 : 17:56:38

When you ran that query, did you connect as the user in the dts connection?

catchvaas
Starting Member

22 Posts

Posted - 2010-12-27 : 22:01:14

Yes..I did

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-27 : 22:43:03

do the field definitions match on both sides?