Skip to content
+1-888-319-3663

COMMUNITY FORUM

SQL Task – passing global variable

Sherry Whitten asked 4 years ago
I have two Before Map tasks.  The first one prompts the user to input a date and stores it in global variable GBL_ASOFDATE.  Right now I have this task populating a message box at the end so I can see the date the user input, so I know it’s getting a valid date.  The second task runs a SQL script:
insert into zrmhatb
exec createzrmhatb
@asofdate=GBL_ASOFDATE
I can run that script fine in SQL.  Also, if I use an actual date instead of the GBL_ASOFDATE in the last line, it will execute the query and populate the table using the value provided for the @asofdate parameter in the stored proc. 
But if I use the script as written above, I get an error.   Incorrect syntax near ‘=’. 
I also tried putting ‘GBL_ASOFDATE’ in single quotes.  Then I get ‘no data returned’ – like it is using the text instead of the value of the global variable.  I’m so close to having this working…..  Thanks in advance.
 
 
 
 
 
 
Sherry Whitten replied 4 years ago

I also tried @asofdate = ‘ ” + GBL_ASOFDATE + ” ‘ . I think that’s closer, but I get this error: Error converting data type varchar to datetime. It’s like it thinks my global variable is a varchar. But the script to get the user to input the date should be storing it as CDate.

This is a snippet from my input date script:
‘Store the input dates in Date datatypes
dDate = CDate(AgingDate)

‘ Set the value into the global variables
GBL_ASOFDATE = dDate

Ethan Sorenson Staff replied 4 years ago

The issue Sherry was encountering was a bug with out 20.17.0.11 release of SmartConnect. This was fixed with our 20.17.0.17 release of SmartConnect.

Sherry Whitten replied 4 years ago

I upgraded to the .17 version.

What is the correct Syntax for the Run SQL Command task?

I tried:
insert into zpmhatb
exec createzpmhatb
@asofdate = GBL_ASOFDATE

and
insert into zpmhatb
exec createzpmhatb
@asofdate=GBL_ASOFDATE

Both get me this error:
Incorrect syntax near ‘/’.

Then I tried:
insert into zpmhatb
exec createzpmhatb
@asofdate = ‘ ” + GBL_ASOFDATE + ” ‘

That gets me this error:
Error converting data type varchar to datetime.

My global variable contains a date formatted like this: 03/02/2017

Here’s a snippet of the code that gathers user input date:

‘ Prompt for the As Of Date
AgingDate = Microsoft.VisualBasic.Interaction.InputBox(“Enter the document date (mm/dd/yyyy):”, “Aging Date”)

‘Match the input against the regex
match = dateRegex.Match(AgingDate)
Loop

‘Store the input dates in Date datatypes
dDate = CDate(AgingDate)

‘ Set the value into the global variables
GBL_ASOFDATE = dDate

Sherry Whitten replied 4 years ago

Any follow up? I applied the update, and I still can’t get this to work. Thanks.


If you would like to submit an answer or comment, please sign in to the eOne portal.