Solved

Global Variable not working in Execute SQL Task

Posted on 2009-05-15
7
345 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Where clause to fliter varchar with Characters 12 56
Remove () 10 38
SQL Percentage Formula 7 32
Use SSRS to email customers? 4 29
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

737 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