Global Variable not working in Execute SQL Task

Hi all,

I have a DTS package with an ActiveX Script Task and an Execute SQL Task. My ActiveX script (for testing purposes) reads:

Function Main()        
      DTSGlobalVariables("POStart").value = 10
      Main = DTSTaskExecResult_Success
End Function

My Execute SQL Task reads:

INSERT INTO SUMMIT_STAGE_PO (ORDER_NO, LINE_NO, CREATE_DATE, Type)
SELECT * FROM VW_SUMMIT_AUDIT_PO
WHERE CREATE_DATE > DATEADD([HOUR], -?, GETDATE())

I have placed the question mark in as this value should be taken from global variable POStart. However, when I click on the parameters button to assign the parameter I get the following error:

Error Source: Microsoft OLE DB Provider for SQL Server

Error Description: Deferred prepare could not be completed.
Statement(s) could not be prepared.
Invalid operator for data type. Operator equals minus, type equals datetime.

Any ideas guys???
LVL 3
FMabeyAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
I managed to duplicate your problem.  I suggest you wrap your code in a stored procedure as follows:

Create procedure usp_AddPO
            @POStart integer

AS

SET NOCOUNT ON

INSERT INTO SUMMIT_STAGE_PO (ORDER_NO, LINE_NO, CREATE_DATE, Type)
SELECT * FROM VW_SUMMIT_AUDIT_PO
WHERE CREATE_DATE > DATEADD([HOUR], @POStart, GETDATE())



And then call it from your Execute SQL Task as follows:
exec usp_AddPO ?
0
 
reb73Commented:
You cannot assign a sign to the parameter like that..  

Change the value assignment in your ActiveX script as follows -

DTSGlobalVariables("POStart").value = -10

And change your Execute SQL Task statement as follows -

INSERT INTO SUMMIT_STAGE_PO (ORDER_NO, LINE_NO, CREATE_DATE, Type)
SELECT * FROM VW_SUMMIT_AUDIT_PO
WHERE CREATE_DATE > DATEADD([HOUR], ?, GETDATE())
0
 
FMabeyAuthor Commented:
Hi reb73,

Thanks for your reply. Unfortunately, I've already tried that and I get the following error:

Error Description: Deferred prepare could not be completed.
Statement(s) could not be prepared.
Arguement data type datetime is invalid for argument 2 of datadd function.

This happens when I click on the 'Parameters' button.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Anthony PerkinsCommented:
Are your sure your Global Variable "POStart" in your DTS Package is defined as integer.  It sounds like you have defined it as datetime.

Either that or you do not understand how the DATEADD function operates.
0
 
FMabeyAuthor Commented:
How do I 'define' POStart as an integer?

When I look in Package -> Properties -> Global Variables, POStart has type Integer next to it.
0
 
FMabeyAuthor Commented:
acperkins,

Thanks for your suggestion. Have you any ideas why my problem is occuring?

I have actually managed to get around the issue now by using the code attached. However, I have a similar DTS package that uses a SQL Query in a Transform Data Task. Have you any ideas how I'd ammend my code to change the SQL in the Transform Data Task?

Cheers
Option Explicit
 
Function Main()
	Dim oPkg, oExecSQL, sSQLStatement
 
             DTSGlobalVariables("POStart").Value = DateDiff("n", Now, DTSGlobalVariables("POEnd").Value)
 
	' Build new SQL Statement
	sSQLStatement = "INSERT INTO SUMMIT_STAGE_PO (ORDER_NO, LINE_NO, CREATE_DATE, Type) SELECT * FROM VW_SUMMIT_AUDIT_PO WHERE CREATE_DATE > DATEADD([minute], " & DTSGlobalVariables("POStart").Value & ", GETDATE())"
 
	' Get reference to the Exec SQL Task
	Set oPkg = DTSGlobalVariables.Parent
	Set oExecSQL = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask
 
	' Assign SQL Statement to Exec SQL Task
	oExecSQL.SQLStatement = sSQLStatement
 
	' Clean Up
	Set oExecSQL = Nothing
	Set oPkg = Nothing
 
	Main = DTSTaskExecResult_Success
End Function

Open in new window

0
 
Anthony PerkinsCommented:
I am afraid I do not know.  I only used Stored Procedures.  One workaround would be to build the SQL Statement on the fly together with the parameter and assign it to the SQLStatement property for the Execute SQL Task.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.