The ISV was attempting to use pass through sanScript to populate a SQL temp table.
What he found was that the first SQL_Execute() statement was OK but the subsequent statements would fail with the error:
SQL Errror: 0 [Microsoft][SQL Server Native Client 10.0]Invalid cursor state
The Dexterity code I'd potentially use (omitting error checking and variable declarations).
What I would find is that my table wouldn't be populated with the Vendor ID values.
Debugging this (or adding the call to SQL_GetError()) I'd find that the second SQL_Execute() function is what is failing with the error noted.
Why does this happen?
The reason is that I have returned a recordset from the second call.
We note that the first call actually set my database context with the "use TWO" statement.
Because no recordset was generated, the call to the RM00101 table does not fail.
However as the second SQL_Execute() statment reading the RM00101 can return a recordset (even if it doesn't due to a where clause), the next SQL_Execute() statement will fail with the 'invalid cursor state' error.
How do we fix this? Do we have to terminate the connection?
No - nothing that drastic and the fix is rather easy.
We just need to clear the previous results - Dexterity has the function SQL_Clear() to do this.
So to resolve our issue, we would just need to add this to the above code.
Revised Code Snippet
Hope this helps out,
Senior Escalation Engineer
Dynamics GP Developer Support
PS. Yes I know, the queries are rather lame and would be better performed as one UNION query. And of course I'm ignoring the possibility of duplicates here. And yes the BEST way to fill the temp table would be to do an insert query into a temp table. But those are the types of things you ignore in order to get a simple example.