Solved

Global Variable not working in Execute SQL Task

Posted on 2009-05-15
7
347 Views
Last Modified: 2013-11-30
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???
0
Comment
Question by:FMabey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 24397601
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
 
LVL 3

Author Comment

by:FMabey
ID: 24409636
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24411405
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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 3

Author Comment

by:FMabey
ID: 24411606
How do I 'define' POStart as an integer?

When I look in Package -> Properties -> Global Variables, POStart has type Integer next to it.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 350 total points
ID: 24414211
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
 
LVL 3

Author Comment

by:FMabey
ID: 24419437
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24422559
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question