August 25, 2008 at 4:24 am
i have a strange issue cannot figure out why is that so...i have a DTS package developed by some other developer, in excute sql task the syntex (code ) is like
if (select object_id('tempdb..##SLICE_CONTAINER')) is not null
drop table ##SLICE_CONTAINER
create table ##SLICE_CONTAINER (
[SLICE_VALUE] [int] not null
) on [PRIMARY]
insert into ##SLICE_CONTAINER (SLICE_VALUE) values (? )
when i click on the paramete button it gives me the following error...
"Syntex error or Access violation"
and then another window appears gives msg like this
"An error occured while parsing sql statement for parameters. Please check the syntex of SQL statement and verfiy it is valid for connection selected"
connection is fine i have tested it by different means..i have created even temporary tables before parsing this syntex but still problem persist...however package is executing fine.....i just want to check the parameter mapping against the above query ...bcoz a global variable is declared.
can any body suggest me why is that so or i m missig something
Thanks
August 27, 2008 at 5:09 pm
Execute SQL Tasks don't like SQL statements that have parameters and also do things like create & drop tables.
Easiest fix is to create a stored procedure that executes these statements, then pass your parameter to that stored procedure in the Execute SQL task.
August 28, 2008 at 3:24 am
Erik Kutzler (8/27/2008)
Execute SQL Tasks don't like SQL statements that have parameters and also do things like create & drop tables.Easiest fix is to create a stored procedure that executes these statements, then pass your parameter to that stored procedure in the Execute SQL task.
I agree with Erik but don't think it will fix the problem as I think it's the passing of the parameter that causes the error.
If I'm right you can find a workaround in this article : http://www.sqldts.com/234.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply