Post reply

[sql_server] Strange Error in INSERT using DTS Exc

  • March 12, 2003 at 6:48 am

    #105473

    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

  • March 17, 2003 at 5:29 pm

    #451292

    Have you checked that all the permissions are correct? That's usually the cause of a lot of problems when you try and schedule a DTS job.

    Are you getting any other error messages?

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply