SQL Task – passing global variable
Sherry Whitten asked 6 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.
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.
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
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.
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
Any follow up? I applied the update, and I still can’t get this to work. Thanks.