Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an Execute SQL Task that is (hopefully) going to receive a global variable called startDate, which is set to a Date type. A shortened version of the code looks like this:

DECLARE @startDate DATETIME,
        @endDate DATETIME

SET @startDate = ?
SET @endDate = DATEADD(DAY, 6, @startDate)

INSERT INTO destinationTable
SELECT  * 
FROM    sourceTable
WHERE   startDate = @startDate 
AND     endDate = @endDate

When I replace the parameter with a date (e.g. '08-01-2012'), the statement parses correctly; however, as soon as the parameter is added back in, the statement errs.

I use DTS very rarely, so I'm basing this code entirely on Microsoft's Using Parameterized Queries. Can anyone enlighten me on how to correctly use a parameter in this situation?


Edit: The error detail reads "Syntax error or access violation" which, considering the circumstances, I assume to read as a syntax error due to the addition of the "?".

share|improve this question
    
What is the error? –  Eric Higgins Aug 3 '12 at 19:23
    
"Syntax error or access violation". Sorry, should have mentioned that. –  Davenport Aug 3 '12 at 19:25
    
That's the actual code you're running, with the "?" in it? How are you intending to pass the value for @startDate in? –  Eric Higgins Aug 3 '12 at 19:56
    
I'm assuming that a correctly parsed statement allows you to specify the variables to be used as parameters. Correct me if I'm wrong, but don't question marks identify parameters in DTS? –  Davenport Aug 3 '12 at 20:19
    
I think I understand you. What you've got above is a T-SQL statement which needs to be comprised of valid T-SQL to be used as an execute task. Some more info please: Do you want the start time to be execution time, or do you intend to let the user choose the start time? –  Eric Higgins Aug 3 '12 at 20:56
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.