sql server dts: Steps run individually but not when run as DTS -- sql server dts

Re: Steps run individually but not when run as DTS

PaulaPompey
6/13/2005 3:49:02 AM

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

[quoted text, click to view]

Re: Steps run individually but not when run as DTS

Narayana Vyas Kondreddi
6/13/2005 11:35:47 AM

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/

[quoted text, click to view]
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

Re: Steps run individually but not when run as DTS

Mike
6/23/2005 10:05:03 AM

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.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.