September 8, 2004 at 11:42 pm
I'm attempting to pass in a global variable as a parameter to an "Execute SQL Task". The object of the routine is to assign a string to another global variable, the format of which is dependant on the input parameter. The examples in BOL usually show something like this:
SELECT CCYYMMDD
FROM RefDates
WHERE FullDate = ?
This parses fine, as you would expect. The parameter is loaded with the chosen global variable, & all is well.
My situation is a tad more complicated: I'm not using the parameter as the value in a WHERE clause, I just want to assign it to a variable, like so:
DECLARE@DateTime DATETIME
DECLARE@DateParm INT
SELECT@DateTime = GETDATE()
SELECT@DateParm = ?
SELECTCASE @DateParm
WHEN 1 THEN REPLACE(CONVERT(CHAR(10), @DateTime, 120), '-', '')
WHEN 2 THEN REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),@DateTime,120), '-', ''),' ',''),':','')
WHEN 3 THEN REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19),@DateTime,120), '-', ''),' ',''),':','')
END
This gets me an ADO error "Syntax error or access violation".
If I change the ? to an 1,2 or 3 it parses fine, so it seems that the parameter placeholder is the problem. I've even tried loading the values into a table variable, and selecting from that so the placeholder is part of a WHERE clause, like so:
DECLARE@DateTime DATETIME
DECLARE@DateParm INT
DECLARE@tabNumbers TABLE (Number INT)
SELECT@DateTime = GETDATE()
INSERT INTO @tabNumbers VALUES (1)
INSERT INTO @tabNumbers VALUES (2)
INSERT INTO @tabNumbers VALUES (3)
SELECT@DateParm = Number
FROM@tabNumbers
WHERENumber = ?
SELECTCASE @DateParm
WHEN 1 THEN REPLACE(CONVERT(CHAR(10), @DateTime, 120), '-', '')
WHEN 2 THEN REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),@DateTime,120), '-', ''),' ',''),':','')
WHEN 3 THEN REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19),@DateTime,120), '-', ''),' ',''),':','')
END
But this fails with the same error.
Am I missing something obvious here? Any ideas appreciated.
Thanks!
David Cook
September 8, 2004 at 11:45 pm
Sorry, I should have said: SQL Server 2000 SP3a.
September 9, 2004 at 1:32 am
For those interested, I have an answer ...
This article at http://www.sqldts.com/?234 (great site, BTW) mentions that the syntax validation routine for the design-time parser is less forgiving that the run-time validation routine, and needs to be stooged with a disconnected edit.
I followed the steps in that article, and it works fine.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply