DTS Designer does not parse sql but query analyser does!

[MrPeds]

Hi,

This is a bit of an unusual error.

I have some code that looks like the following:

SELECT @defaultOrgName, @defaultOrgName, CONVERT(CHAR(8),GETDATE(),112), CONVERT(CHAR(8),GETDATE(),112), -2, -2

UNION ALL
SELECT DISTINCT OrgDescription =
CASE
WHEN SupervisorSurName IN (SELECT [name] FROM mytable) THEN SupervisorSurName
WHEN SupervisorGivenName + ' ' + SupervisorSurName IN (SELECT [name] FROM mytable) THEN SupervisorGivenName + ' ' + SupervisorSurName

END,

ParentDescription = @defaultOrgName ,
CONVERT(CHAR(8),GETDATE(),112) LoadDate,
CONVERT(CHAR(8),GETDATE(),112) EndDate,
AIMSupervisorUserID KeyID, -2 KeyID

FROM myTable
WHERE Type='P'
AND (SupervisorSurName IN ('', 'sdf')
OR SupervisorGivenName + ' ' + SupervisorSurName ='' )

The problem seems to be where i am using a sub query within the CASE statement, e.g. when i am not using hard coded values.

The code syntax is fine when i check it in the stored procedure window, and the syntax is also fine when i check it in query analyser.

However, when i try in the DTS designer within an execute sql task the following error is displayed:

Error Source: Microsoft OLE DB Provider for sql server.
Error Descrition: Internal SQL Server error.

I am calling the code using this syntax from the dts execute sql task:
exec myStoredProc

I really dont understand why the sp can execute and have valid syntax outside of the dts designer but inside the designer it comes up with this error!

any suggestions appreciated on how to get around this.

MrPeds

 

[DaOtH]

Had a bit of a similar issue where i created a temp table in a DTS package and tried using that very same temp table.

DTS didn't want to accept it because the table was non-existent. Looks like DTS doesn't handle this variable stuff very well.

But, if you say it runs fine from your stored procedure, why don't you just call your stored procedure from DTS.

Execute_Sql task, exec <stored proc name>

&quot;In three words I can sum up everything I've learned about life: it goes on.&quot;
- Robert Frost 1874-1963