Problem executing stored proc within DTS SQL task 1

[ChuckGann]

I am trying to execute a stored proc with DTS (SQL 2000) in a SQL task. My problem is in trying to set one of the parameters with a variable. My script from the SQL task is below.

declare @CutOffDate as datetime
set @CutOffDate = convert(char(10),dateadd(day,-1*datepart(day,getdate()),getdate()),101)
exec SAIspGetHATBDataDTS;1 ?,?,@CutOffDate,?,?,?

Basically, this just tries to set a date param to the end of the previous month as calculated by todays date. This works OK if I just create a global variable and assign another ?, but I can't seem to force this global vairable to my date even if I run it outside of this task.

Thanks for looking.

 

[SQLDenis]

on the execute SQL task click parameters then map the parameters on the next screen

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ChuckGann]

Thanks for the reply Denis. However, I get a syntax error when trying to map unless I replace the @CutOffDate with a ?. If i do that, I can map them. My problem is that I can't seem to update the variable associated with it even within another script, The script will run fine and return the date of 5/31/07, but it does not update the variable. This is the reason in was trying to do it within the exec portion of the SQL Task.

Any other ideas?

 

[SQLDenis]

I know that is a bug here is a workaround

for example if you have this

exec prProc ?

change that to

select * from sysobkects where id =?

map the variables

paste back the proc code and save the package

it also doesn't like inserts or updates either so i would map with the select and then paste the code I needed

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ChuckGann]

That did it. Thanks for your help!!!!!

 

[SQLDenis]

no problem, thanks for the star

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions