I have a strange problem.
I have built a DTS which runs fine when each step is executed individually,
but when the DTS is run as a whole, the last step fails.
I have checked the workflow and nothing seems amiss there. Now I'm stumped
and don't know what else could be the problem.

The DTS has an SQL connection and steps as follows:
1. Execute SQL Task - runs T-SQL Update statement - runs fine
2. Execute SQL Task - runs T-SQL Insert statement - runs fine
3. Execute SQL Task - runs T-SQL Insert statement - runs fine
4. ActiveX Script Task - Sets Global variables - runs fine
5. Execute SQL Task - runs Stored Procedure with 2 input parameters from
global variables - runs fine if executing step on it's own, but when DTS run
as a whole step fails with error "Could not find stored procedure 'SP'.
Syntax error or access violation"

I'd appreciate any suggestions.

Thanks in advance.

Paula

What's inside the Step 5? Could you post here please?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/


"PaulaPompey" <PaulaPompey (AT) discussions (DOT) microsoft.com> wrote

I have a strange problem.
I have built a DTS which runs fine when each step is executed individually,
but when the DTS is run as a whole, the last step fails.
I have checked the workflow and nothing seems amiss there. Now I'm stumped
and don't know what else could be the problem.

The DTS has an SQL connection and steps as follows:
1. Execute SQL Task - runs T-SQL Update statement - runs fine
2. Execute SQL Task - runs T-SQL Insert statement - runs fine
3. Execute SQL Task - runs T-SQL Insert statement - runs fine
4. ActiveX Script Task - Sets Global variables - runs fine
5. Execute SQL Task - runs Stored Procedure with 2 input parameters from
global variables - runs fine if executing step on it's own, but when DTS run
as a whole step fails with error "Could not find stored procedure 'SP'.
Syntax error or access violation"

I'd appreciate any suggestions.

Thanks in advance.

Paula

Thanks

Step 5 is Execute SQL Task:
exec CheckSpace_DriveUpdate 'SERVER\DRIVE','45.5'

The second input parameter is supplied via DTS Global Variable which is
populated via ActiveX Script using File System Object to get free space from
a drive. The ActiveX works fine and global variable value is fine

SP code is as follows:
CREATE PROCEDURE CheckSpace_DriveUpdate @Source varchar(50),@SpaceFree
varchar(50) AS
SET @SpaceFree = REPLACE (@SpaceFree,',','')
INSERT INTO CheckSpace (Source,Type,SpaceFreeGb,RecInsertDate,Latest)
SELECT @Source As Source,
'DRIVE' As Type,
CAST(@SpaceFree As decimal(19,2)) As SpaceFreeGb,
GetDate() As RecInsertDate,
1 As Latest

"Narayana Vyas Kondreddi" wrote:

I had about 10 steps in a DTS package. The step would execute fine
individually but not when I ran the entrie package at once. I seperated them
into packages of 3 and everything has been working fine. Hope this helps.

"PaulaPompey" wrote: