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, the step fails with error "Could not find stored procedure 'SP'.
Syntax error or access violation"

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


I'd appreciate any suggestions.

Thanks in advance.

Paula

What is SP?

You could use a Datapump task to do this or even a straight TSQL statement

I would also look to qualify with the owner, the SP and the table into which
you are inserting.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


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

Thanks for the response Allan,

'SP' represents the name of the stored procedure - 'CheckSpace_DriveUpdate'

All tables & stored procedures etc have owner dbo

If I were to use a Datapump task or TSQL statment, how do I include the
global variable values? (Please see below SP for insert code) I could
hard code the value for @Source, but @SpaceFree is set using VBScript File
System Object to get the amount of space free on HD.

Thanks for you help so far

"Allan Mitchell" wrote: