FireDAC error 310 Cannot execute command returning result sets
Hint Ref: 021608190013
Hint Date: 19/08/2016
Hint Details:
PROBLEM
When running the SEPA script against SM V3 for the first time in their tests, a customer experienced the following error:
FireDac Phys ASA 310 Cannot execute command returning results set. Hint: Use Open method for SELECT-like commands.
ISSUE
After some extensive diagnostics, the error was determined to be in a <SQLExec> statement where a SELECT was being redirected INTO a temporary table.
Specifically, a statement where a double quote character (as opposed to two single quotes) was being replaced with an empty string thus (for example):
Select replace('abcde','"','') as newst into #ttemp
SOLUTION
The solution was to replace the double quote character with the ascii equivalent of char(34):
Select replace('abcde',char(34),'') as newst into #ttemp
(Please Note: This Procedure can be destructive and should only be used by Advanced Users. RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure, XML Script or SQL command. Responsibility resides solely with the user.
IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)