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...

ThanksRE:
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.)?|||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|||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)|||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|||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).