"PaulaPompey" <PaulaPompey@discussions.microsoft.com> wrote in message
news:FD8E2BF6-FFC6-4C0F-BC2F-79D69BFEF21B@microsoft.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, 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
>

"Allan Mitchell" wrote:

> 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@discussions.microsoft.com> wrote in message
> news:FD8E2BF6-FFC6-4C0F-BC2F-79D69BFEF21B@microsoft.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, 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
> >
>
>