MS SQL Server DTS - Steps run individually but not when run as DTS

Author

Steps run individually but not when run as DTS

PaulaPompey

2005-06-13, 7:23 am

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

Narayana Vyas Kondreddi

2005-06-13, 7:23 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/

"PaulaPompey" < PaulaPompey@discussi
ons.microsoft.com> wrote in message
news:4E71811E-206E-42B4-8829- CB8C6BB43C24@microso
ft.com...
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

PaulaPompey

2005-06-13, 7:23 am

Thanks

Step 5 is Execute SQL Task:
exec CheckSpace_DriveUpda
te '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_DriveUpda
te @Source varchar(50),@SpaceFr
ee
varchar(50) AS
SET @SpaceFree = REPLACE (@SpaceFree,',','')
INSERT INTO CheckSpace (Source,Type,SpaceFr
eeGb,RecInsertDate,L
atest)
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:

> 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@discussi
ons.microsoft.com> wrote in message
> news:4E71811E-206E-42B4-8829- CB8C6BB43C24@microso
ft.com...
> 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
>
>
>

Mike

2005-06-23, 1:23 pm

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:
[color=darkred]
> Thanks
>
> Step 5 is Execute SQL Task:
> exec CheckSpace_DriveUpda
te '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_DriveUpda
te @Source varchar(50),@SpaceFr
ee
> varchar(50) AS
> SET @SpaceFree = REPLACE (@SpaceFree,',','')
> INSERT INTO CheckSpace (Source,Type,SpaceFr
eeGb,RecInsertDate,L
atest)
> 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:
>

Sponsored Links