I have a stored procedure that perform an insert and then returns the newly inserted row.
When I execute the procedure from SP Builder it works fine. When
I run the procedure through ADO, it inserts the same row multiple times. Three to be exact!?!
Has anybody seen this behavior before? If I comment out the OPEN of the cursor and re run, I get only one record inserted.
It seems that I don't have the procedure coded properly to perform the insert and return the result set when execute from ADO.
Here is an example of the procedure:
-- Declare temp timestamp var
DECLARE v_dtime TIMESTAMP;
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT ID as ID,
DCT_NAME as DCT_NAME,
USER_ID as USER_ID,
CRE_DTIME as CRE_DTIME,
LAST_CNG_DTIME as LAST_CNG_DTIME,
LAST_CNG_ID as LAST_CNG_ID,
MODEL_IND as MODEL_IND
WHERE ID = IDENTITY_VAL_LOCAL();
-- Value temp timestamp var
SET v_dtime = CURRENT TIMESTAMP;
In my original post, I failed to mention that we were generating XML from ADO. This is where the issue arises.
On the Connection object, we did not specify a CursorLocation. Default is UseServer. We added a CursorLocation = UseClient and it resolved our problem.
Apparently when ADO prepares to generate XML it re-runs the query again if you are using a Server Side Cursor Service. Since we were running an insert and returning a record set, multiple records were generated.
Once the Client Sider Cursor Service was used, ADO doesn't need to re-execute the query again. That is what we are thinking.