Solved

Global Variable not working in Execute SQL Task

Posted on 2009-05-15
7
342 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 32
Querying data from 3 SQL tables 2 32
Need help how to find where my error is in UFD 6 29
sql server query 6 9
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

773 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