SQL script not finishing and causing exit without warning

[TylerMitton]

I have an SQL script command as follows:

IF NOT EXISTS (SELECT 1 FROM MyTable WHERE TimeStamp = '$CurrentDate$' AND Plant = $CurrentPlant$)
BEGIN

INSERT INTO MyTable
(TimeStamp_UTC, Plant, LoadKWh, LoadKVarh, GenerationKWh, GenerationKVarh)
VALUES
(
'$CurrentDate$',
$CurrentPlant$,
$Filedata Column(3)$,
$Filedata Column(4)$,
$Filedata Column(5)$,
$Filedata Column(6)$
)

SELECT 1

END
ELSE
BEGIN

SELECT 0

END

It checks to see if a record exists, and if it doesn't, it will insert it then return a dataset containing a single row with a single field with the value of 1.

If it does already exist, it will not insert anything and it will return a dataset with a single row with a single field with the value of 0.

The insert works properly and records are being inserted into my database. However, the dataset of "SELECT 1" after the insert is not being returned. If I create a loop to access the dataset return from that script, it will cause my task to exit this loop as well as all loops above this command (in my case I have 3 levels of loops and it exits all three... continuing the task on the next iteration of the upper most loop).

However, if the insert is not required and the "SELECT 0" dataset is returned, then there are no problems and I can access the value of 0 from $Dataset Column(1)$.

Is there a problem with doing an insert followed by a select in my script? I have tested the code in SQL Server Management studio and it runs fine and returns 1 if a record was inserted and 0 if a record was not inserted.

[forumsupport]

Hello,

What we understand, you have created a stored procedure with mentioned SQL commands and are calling it from Automation Anywhere using 'Run Stored Procedure' Database command. Please correct if we are wrong.

In this case, unfortunately, currently 'Run Stored Procedure' Database command does not return value(s) however, the upcoming version (will release shortly) does support this feature. To get around this issue, you can log the return value to a text file, from where Automation Anywhere can fetch the results.

Hope that helps.

[TylerMitton]

Hi, sorry, no I am not running a stored procedure. I am using the SQL Query option in the Database command. The query I am running is the code I pasted above. It does the insert fine if the record didn't already exist (which is what it is supposed to do). It also does the SELECT 0 fine if the record did already exist. But it wont do the SELECT 1 after doing an insert.

When it does do the SELECT 0 it returns the record properly... so it is able to return the dataset... just not after doing an insert.

[forumsupport]

Hello,

Can you please try executing the same set of statements from your database manually? If it runs successfully from database then try inserting one more ‘Execute SQL’ command with ‘If Exist….’ statement. There would be total two ‘Execute SQL’ command one for ‘If Not Exist….’ and another one for ‘If Exist….’

Hope that helps.

[TylerMitton]

Yes, the SQL runs successfully on my database manually. There is something about AA that is not receiving the dataset after the insert has occurred.

I've removed the insert from the SQL completely and left everything else the same. That allows me to get a value which I put into a parameter. If that value = 1, then I run another SQL INSERT to put the data into the database... not a great solution but it's the best I could do considering the bug with AA.