December 20, 2002 at 6:45 am
I want to create a DTS with a global variable but I am experiencing the following problem:
example:
1. Created an Execute SQL task with the following:
set language british
declare @date smalldatetime
set @date = '14/09/1994'--getdate()
select @date as inputdate
2. Create a Global Varaible called MyDate (type-Date, value = 08/17/2002)
Use the Output parameter mapping (Row Value type) to assign the MyDate = inputdate.
3. Created a second Execute SQL task.
In this task I am trying to sepcify the ? as a parameter in the where clause of a sql statement however a syntax error message is given everytime I press the Parameters button to specify the input parameters.
e.g sql statement delete from tableX where datediff(day,ord_date,?)=0
Anyone knows why?
December 21, 2002 at 5:40 am
The error message I am receiving is:
Package Error
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Syntax error or access violation
December 22, 2002 at 3:33 pm
Is your connection valid? You might want to check that...also, was your connection object created on a different machine? If so, you might want to drop it and re-create it on the machine you are trying to run the Execute SQL Task from. Another thing to consider is what service pack are you running? If you don't have sp2, you might download that and install and see if that helps.
Michael
Michael Weiss
Michael Weiss
December 23, 2002 at 6:00 am
Thank you for your reply.
1. I think the connection is valid, because if I hard code the date it works.
2. No, the connection object was not created on another machine.
3. Yes, I have sp2 installed (select @@version)
I have since solved the problem, but first, I noticed the following:
a) the error message that I mentioned actuall occurred happened when I tried to assign a variable to the paramter e.g. select @date = ? (Sorry :D)
b)The DTS did not work with a paramter in a datediff function. It worked however when I used the where @date between ? and ?+1
Thank you
Arif Khan
December 23, 2002 at 6:42 am
Just wanted to mention several more things:
1. I didn't think much of the DTS documentation in BOL
2. Brian Knights article about global variables helped me. I used the ACTIVEX object in DTS to print out my global variable values at run-time.
3. The error I came across:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Syntax error or access violation
is mentioned in an article:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply