Error using Global Variables in Execute SQL Task

[Sweenster]

Hi, I am trying to change a DTS package so that it uses global variables rather than 'hardcoded' dates but the step that deletes the data from the tables does not work.
The way the pkg works is that it deletes the last 15 days worth of data & then goes and re-retrieves it again. I want to be able to run it for a specific period if necessary.

The Global Variables (StartDate & EndDate) are set up & it works fine for the steps that retrieve data (DATA PUMPS) but NOT for the step that deletes data (Execute SQL Task). It gives the Error message "The column prefix 'sessions' does not match with a table name or alias used in the query"

Do I need to modify my SQL some more?

THIS IS THE CURRENT SET UP AND IT WORKS FINE
delete summline
from sessions, summline
where sessions.sessdate >dateadd (d,-15,getdate()) and
sessions.sessionid = summline.sessionid and
sessions.siteid = summline.siteid

THIS IS WHAT I HAVE CHANGED IT TO (DOES NOT WORK)
delete summline
from sessions, summline
where sessions.sessdate BETWEEN ? AND ? and
sessions.sessionid = summline.sessionid and
sessions.siteid = summline.siteid

AND AS AN FYI THIS DOES WORK OK IN THE DATAPUMP TASKS!!!!?
select summline.*
from sessions, summline
where sessions.sessdate BETWEEN ? AND ? and
sessions.sessionid = summline.sessionid and
sessions.siteid = summline.siteid

 

[SQLDenis]

Just hit OK do NOT parse the query, that always gives an error except for selects

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Sweenster]

I have also been unable to set the parameters so I have tried tricking it letting me set these by starting with a select statement, setting the params and then overwriting this with my delete statements. However, I still get an error when I try and run it.

This SQL step clears out data from multiple tables using separate delete statements so I would like double check if it possible to have multiple delete steps using the same params within the same task? e.g.

delete summprom
from sessions, summprom
where sessions.sessdate BETWEEN ? AND ? and 
sessions.sessionid = summprom.sessionid and
sessions.siteid = summprom.siteid
go
delete summline
from sessions, summline
where sessions.sessdate BETWEEN ? AND ? and 
sessions.sessionid = summline.sessionid and
sessions.siteid = summline.siteid
go
+ many more..

I suspect the answer will be a NO! as I guess it will assume there should be 1 parameter for each question mark?

 

[gradley]

Hi Sweenster,

What I usually do is move the parameters into variables and then use the variables in the delete statement.

You still won't be able to parse the statement though.

Ex:

Declare @dteFromDate as datetime,
        @dteToDate as datetime

SET @dteFromDate = ?
SET  @dteToDate = ?

delete summprom
from sessions, summprom
where sessions.sessdate BETWEEN @dteFromDate  AND @dteToDate  and 
sessions.sessionid = summprom.sessionid and
sessions.siteid = summprom.siteid
go
delete summline
from sessions, summline
where sessions.sessdate BETWEEN @dteFromDate  AND @dteToDate  and 
sessions.sessionid = summline.sessionid and
sessions.siteid = summline.siteid
go

 

[Sweenster]

Cool, that will do the job a treat. Cheers.