Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Norway
    Posts
    8

    Unanswered: Execute sp_OAMethod Fails

    Hi,

    I am struggeling with the execution of DTS packages from within T-SQL scripts on one of many SQL server instances on my Network (SQL 7.0 and SQL 2000 on Win 2000). The DTS Packages will execute, but one of the Tasks will always fail for some reason. The Tasks that fails are:

    Execute SQL Task
    Transform Data Task

    I am using the OLE stored procedures, because DTSRUN on SQL 7.0 does not support passing global variables to the DTS package.

    All the global variables that are sent to the DTS Packages are validated under execution and causes no problems to my knowledge. The DTS packages executes with no problems from the Enterprise Manager.

    The computer where I get this strange behaviour is named KL-PC979. The "-" character is not included on any othe machine on the nettwork and I suspects that It is the cause for all my troubles.

    Here is how I load:

    'LoadFromSQLServer("KL-PC979", "MyUser", "UserPWD", 0, "", , , "MY_PKG")'

    have tried both "." AND "local" for the SERVERNAME, but no luck.

    The error logs gives me this information:

    Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
    Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
    Step 'DTSStep_DTSExecuteSQLTask_3' failed

    Step Error Source: Microsoft OLE DB Provider for SQL Server
    Step Error Description:No value given for one or more required parameters.
    Step Error code: 80040E10
    Step Error Help File:
    Step Error Help Context ID:0

    I have not been able to find some useful information about this, but hopfully there is someone out there...

    Thanks

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Question Re: Execute sp_OAMethod Fails

    RE:
    The computer where I get this strange behaviour is named KL-PC979. The "-" character is not included on any othe machine on the network and I suspect that it is the cause for all my troubles.
    Question I
    Perhaps, and perhaps not; one might consider testing to see if an execution using an alias (without a '-') succeeds (or at least, does not fail at the same point / in the same manner)???

    {An alias would be expected to address the issue if indeed the '-' in the name is the source of the issue (not tested).}

    RE:
    one of the Tasks will always fail for some reason. The Tasks that fails are:
    Execute SQL Task
    Transform Data Task

    The error logs give me this information:

    Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
    Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
    Step 'DTSStep_DTSExecuteSQLTask_3' failed

    Step Error Source: Microsoft OLE DB Provider for SQL Server
    Step Error Description:No value given for one or more required parameters.
    Step Error code: 80040E10
    Question II
    This suggests a missing parameter; what is in the Execute SQL Task?

    Question III
    Does the tsql in the the Execute SQL Task run from QA, (if not, consider posting it with the applicable ddl, sample data, etc., etc.)?

  3. #3
    Join Date
    Dec 2002
    Location
    Norway
    Posts
    8
    Answer to Question I
    I tried this through the Client Network Utility, but it did not work. Is it possible to make an alias on the server side?

    Answer to Question II
    The SQL Tasks execute a single procedure with a couple of parameters (globals). I have validated the globals in earlier Tasks. The procedures either selects or inserts a smal amount of data from or to a table. Query Timeout is set to 0.

    Answer to Question III
    The Procedure runs without problems from QA on this machine.

    Any ideas?

    essal

  4. #4
    Join Date
    Oct 2002
    Posts
    369
    RE:
    Answer to Question I
    Q1 I tried this through the Client Network Utility, but it did not work. Is it possible to make an alias on the server side?
    A1 The approach is correct the Client Network Utility is the correct tool to make an alias (generally on clients, but the Client Network Utility works on servers in a similar {client} fashion).

    RE:
    Answer to Question II
    The SQL Tasks execute a single procedure with a couple of parameters (globals). I have validated the globals in earlier Tasks. The procedures either selects or inserts a small amount of data from or to a table. Query Timeout is set to 0.
    Question IV
    If a simple tsql statement is substituted e.g.(Select 'TestResult' As 'TestOfTask') for the SQL Tasks (that seem to be failing per the error message content) are the same errors still returned?

    RE:
    Q2 Any ideas?
    A2 Yes. But unfortunately, few good ones.

    Idea 1:
    If not currently using named pipes:
    a enable the named pipe protocol with a default pipe name e.g.(\\.\pipe\NoDash\query)
    b Create another alias (named pipes) to that pipe
    c Using the alias name attempt to reproduce the KL-PC979 error

    Idea 2:
    Since both "." AND "local" have been tried:
    a try "(local)" and "(.)"

    Idea 3:
    Install a development (test) server with:
    a A similar dashed '-' name e.g.(KL-PC979Test)
    b Make the test server, KL-PC979Test otherwise identical to KL-PC979
    c Reproduce the KL-PC979 error on the KL-PC979Test server
    d Change the name of the test server to KLPC979Test
    e Attempt to reproduce the KL-PC979 error on the renamed KLPC979Test server
    f If e fails at least the issue is definitively identified (along with a potentially painful remedy)

  5. #5
    Join Date
    Dec 2002
    Location
    Norway
    Posts
    8

    Wink

    Question IV
    Through this simple test I managed to determine that it was the globals that tricked me. I tried, as You mentioned, to use a simple hardcoded Query, and the Package executed without errors. The reason I was sure the globals where read correctly is because the Package write them to a file using VBscript during execution.

    The SQL Tasks where I discovered the problem was calling procedures like this
    sp_MyProc ?, ?, ?, ?
    and I had done the Parameter mapping against the Global variables.

    This did not work in my enviorment when I was calling the Package through OLE. (????)

    Since the ActiveX Script Task could read and write the globals correctly I removed the question marks (?) and build up the query from such a Task. Works perfect.

    Thanks

  6. #6
    Join Date
    Oct 2002
    Posts
    369
    RE:
    Question IV
    Through this simple test I managed to determine that it was the globals that tricked me. I tried, as You mentioned, to use a simple hardcoded Query, and the Package executed without errors. The reason I was sure the globals where read correctly is because the Package write them to a file using VBscript during execution.

    The SQL Tasks where I discovered the problem was calling procedures like this
    sp_MyProc ?, ?, ?, ?
    and I had done the Parameter mapping against the Global variables.

    This did not work in my enviorment when I was calling the Package through OLE. (????)

    Since the ActiveX Script Task could read and write the globals correctly I removed the question marks (?) and build up the query from such a Task. Works perfect.

    Thanks
    You are quite welcome.

    I am pleased that Question IV at least spared you from further fruitless efforts (especially Idea 3).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •