Script to update GL Posting Date on Receivable Transactions
Tom asked 5 years ago
I have a map that import RM Transctions (mostly invoices…occasional Credit Memos). If the import suceeds I want to set the batch’s GL posting date to the current date.
So, I store the batch ID (let’s say the batch id is “RM_20180619”) in a global variable named GBL_BATCH_ID. I have a Map Post Task that I’ll copy below. IT returns an error “Invalid column name RM_20180619” essentially treating my global variable as a column name even though I’ve enclosed it in single quotes.
Here the SQL:
‘*****************************************************************************************
DECLARE @BatchID char(15)
SET @BatchID = GBL_BATCH_ID
DECLARE @SQL NVARCHAR(255)
SET @SQL = ‘UPDATE SY00500 SET GLPOSTDT = CAST(GETDATE() AS DATE) WHERE BACHNUMB = ”’ + @BatchID + ”’ and SERIES= 3′
EXECUTE sp_executesql @SQL
*****************************************************************************************/
I can copy the the same SQL into Management Studio, change the SET @Batch ID line so that it doesn’t reference the global variable, and it updates the field correctly.
Does SmartConnect not support this type of Update Statement?
So, I store the batch ID (let’s say the batch id is “RM_20180619”) in a global variable named GBL_BATCH_ID. I have a Map Post Task that I’ll copy below. IT returns an error “Invalid column name RM_20180619” essentially treating my global variable as a column name even though I’ve enclosed it in single quotes.
Here the SQL:
‘*****************************************************************************************
DECLARE @BatchID char(15)
SET @BatchID = GBL_BATCH_ID
DECLARE @SQL NVARCHAR(255)
SET @SQL = ‘UPDATE SY00500 SET GLPOSTDT = CAST(GETDATE() AS DATE) WHERE BACHNUMB = ”’ + @BatchID + ”’ and SERIES= 3′
EXECUTE sp_executesql @SQL
*****************************************************************************************/
I can copy the the same SQL into Management Studio, change the SET @Batch ID line so that it doesn’t reference the global variable, and it updates the field correctly.
Does SmartConnect not support this type of Update Statement?
Answers
Patrick Roth Staff answered 5 years ago
“.. IT returns an error “Invalid column name RM_20180619” …
In this case “IT” is SQL, and if “it” says “invalid column name XXXX”, that means there isn’t single quotes around the executed string that you are executing.
SmartConnect itself isn’t checking or validating anything that you enter in the SQL window – you can put whatever you want there. The only thing that SC is doing is checking to see if you have global variables or column data here so that it can replace it.
Wrapping your SQL up in another pass through SQL statement for the sp_executesql statement seems a bit unnecessary, just put in your statement
DECLARE @BatchID char(15)
SET @BatchID = ‘GBL_BATCH_ID’
UPDATE SY00500 SET GLPOSTDT = CAST(GETDATE() AS DATE) WHERE BACHNUMB = @BatchID and SERIES= 3
Notice that in the variable declaration, I included the single quotes around the variable since SC only replaces the variables and doesn’t do anything else to your sql statement.
Without building the sql exec statement, the code is simpler to follow and works fine.
In this case “IT” is SQL, and if “it” says “invalid column name XXXX”, that means there isn’t single quotes around the executed string that you are executing.
SmartConnect itself isn’t checking or validating anything that you enter in the SQL window – you can put whatever you want there. The only thing that SC is doing is checking to see if you have global variables or column data here so that it can replace it.
Wrapping your SQL up in another pass through SQL statement for the sp_executesql statement seems a bit unnecessary, just put in your statement
DECLARE @BatchID char(15)
SET @BatchID = ‘GBL_BATCH_ID’
UPDATE SY00500 SET GLPOSTDT = CAST(GETDATE() AS DATE) WHERE BACHNUMB = @BatchID and SERIES= 3
Notice that in the variable declaration, I included the single quotes around the variable since SC only replaces the variables and doesn’t do anything else to your sql statement.
Without building the sql exec statement, the code is simpler to follow and works fine.