Hi all,
This is something stange .Hope someone can help with some idea.
I have ETL process running on SQL server 2000.This captures data from AS/400 server and load in to SQL server and after making some modifications , loading back to AS/400.This process was working fine for several months. A couple of weeks back one of the INSERT statement failed in Execute SQL Task.
The INSERT staements just add records to the AS/400 table with AUDIT LOG. I am attaching the statement below.This happens only when I schedule the job in SQL server agent.If I am running this interactively,it works fine.
INSERT INTO RMAUDITDTL (PROCESSID, STEPID, STEPDSCR, RUN#, STEPTYPE, TOTABLE, RECORDS#, RUNSTRDTE, RUNSTRTIM, RUNENDDTE, RUNENDTIM)
VALUES ( 'DM', 'PAX MST', 'DAILY PAX MASTER', ? , 'D', 'PAXMSTDLY', 0, CURRENT_DATE , DEC(SUBSTR(Char(CURRENT_TIME),1,2)|| SUBSTR(Char(CURRENT_TIME),4,2)|| SUBSTR(Char(CURRENT_TIME),7,2)), NULL, 0)
The question mark in the VALUE is the global variable which gets value while running.
The SQL package log gives me this error.
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution. (Microsoft OLE DB Provider for ODBC Drivers (80004005): [IBM][Client Access Express ODBC Driver (32-bit)]Error in host server data stream.)
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Let me explain some more things here.I have 2 instances(Test and Production) of SQL server running on the same machine.In order to test certain things I saved the MAin Process( which call a lot packages ) to Test.So most of the calling packages are pointing to Production except a few modified packages.I have a XML connection file which controls the Test and Production tables.
Note:- One other thing, this INSERT will work When I recreate with the exact same statement.
Thank you very much for your help.This is really aproduction issue.If someone can help it will be great.
NK
This is something stange .Hope someone can help with some idea.
I have ETL process running on SQL server 2000.This captures data from AS/400 server and load in to SQL server and after making some modifications , loading back to AS/400.This process was working fine for several months. A couple of weeks back one of the INSERT statement failed in Execute SQL Task.
The INSERT staements just add records to the AS/400 table with AUDIT LOG. I am attaching the statement below.This happens only when I schedule the job in SQL server agent.If I am running this interactively,it works fine.
INSERT INTO RMAUDITDTL (PROCESSID, STEPID, STEPDSCR, RUN#, STEPTYPE, TOTABLE, RECORDS#, RUNSTRDTE, RUNSTRTIM, RUNENDDTE, RUNENDTIM)
VALUES ( 'DM', 'PAX MST', 'DAILY PAX MASTER', ? , 'D', 'PAXMSTDLY', 0, CURRENT_DATE , DEC(SUBSTR(Char(CURRENT_TIME),1,2)|| SUBSTR(Char(CURRENT_TIME),4,2)|| SUBSTR(Char(CURRENT_TIME),7,2)), NULL, 0)
The question mark in the VALUE is the global variable which gets value while running.
The SQL package log gives me this error.
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution. (Microsoft OLE DB Provider for ODBC Drivers (80004005): [IBM][Client Access Express ODBC Driver (32-bit)]Error in host server data stream.)
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Let me explain some more things here.I have 2 instances(Test and Production) of SQL server running on the same machine.In order to test certain things I saved the MAin Process( which call a lot packages ) to Test.So most of the calling packages are pointing to Production except a few modified packages.I have a XML connection file which controls the Test and Production tables.
Note:- One other thing, this INSERT will work When I recreate with the exact same statement.
Thank you very much for your help.This is really aproduction issue.If someone can help it will be great.
NK
