Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Global Variable not working in Execute SQL Task

Posted on 2009-05-15
7
Medium Priority
?
349 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
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 1400 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

885 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