MS SQL Server ODBC - SQL Profiler: Attention Event class on DELETE statement

Author

SQL Profiler: Attention Event class on DELETE statement - why?

j.a. harriman

2005-11-04, 8:23 pm

Hello,
I have a C++ app that is connecting (ODBC 3) to a MSSQL db.
I am issuing a DELETE statement where a "last update date" is less than the
program begin date/time.

The problem is that all of the rows on the table that are supposed to be
deleted, are not being deleted. This isn't an issue for deleting a handful
of records, but when it gets to be in the hundreds or 1000's - it's an issue.

This table has 2 triggers on it, 1 for security auditing (what changed) and
the other is used to populate a table that provides info to our mainframe.

The function (in our DLL) creates the connection, issues the delete
statement using SQLExecDirect. A check is made of the return code. As long
as the return code is SQL_SUCCESS or SQL_SUCCESS_WITH_INF
O, the DLL function
returns TRUE.

After the SQLExecDirect is called, the DLL function releases the connection.

What appears to be occurring is that the return code is "good" and the C++
program is moving on to release the connection even though the the triggers
haven't completed. When the connection is released, MSSQL performs a
rollback and the rows it should have deleted are "restored".

As a "test", I put a long Sleep from the time the delete statement is called
to the time that the conenction is released and the rows were deleetd.

I am not considering the Sleep to be an option to solve this issue.

Are there other ODBC calls I should be using before I decide to realease my
connection, e.g. some sort of activity on the conenction?

Thanks. Jeff

Michael Cheng [MSFT]

2005-11-08, 9:23 am

Hi Jeff,

From your descriptions, I understood you would like to wait the
successfully response from an ODBC call before you submit it

You may use SQLExecute instead. Make tiny modification in your codes and
get the return set before SQLExecute submit success.

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

j.a. harriman

2005-11-08, 9:23 am

Michael,

Can you elaborate a bit on this: "get the return set before SQLExecute
submit success" from your answer.

Are there other ODBC callls that are needed prior to calling the SQLExecute?
i.e specific environment, handle, etc. settings that need to be set.

Also, could you include a pseudo-code example to accomplish what I'm trying
to do? I need to know that all triggers have completed before my C++ app
closes the connection.

Thanks. Jeff

"Michael Cheng [MSFT]" wrote:

> Hi Jeff,
>
> From your descriptions, I understood you would like to wait the
> successfully response from an ODBC call before you submit it
>
> You may use SQLExecute instead. Make tiny modification in your codes and
> get the return set before SQLExecute submit success.
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
>
> Sincerely yours,
>
> Michael Cheng
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ====================
====================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

j.a. harriman

2005-11-08, 9:23 am

Michael,

See my earlier reponse as well.

I wanted to add this: on the following page:
http://msdn.microsoft.com/library/d...0crtap_8qb4.asp
for "Prepared Execution", it states:
"Prepared execution should not be used for statements executed a single
time. Prepared execution is slower than direct execution for a single
execution of a statement because it requires an extra network roundtrip from
the client to the server."

What our DB Admins would like to know is why is the return code coming back
as SQL_SUCCESS, when all the triggers haven't even completed. Shouldn't
SQLServer be waiting until the triggers are completed before saying
"everything's ok"?

Jeff

"Michael Cheng [MSFT]" wrote:

> Hi Jeff,
>
> From your descriptions, I understood you would like to wait the
> successfully response from an ODBC call before you submit it
>
> You may use SQLExecute instead. Make tiny modification in your codes and
> get the return set before SQLExecute submit success.
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
>
> Sincerely yours,
>
> Michael Cheng
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ====================
====================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Michael Cheng [MSFT]

2005-11-08, 9:23 am

Hi Jeff,
[color=darkred]
back[color=darkred]

Since your SQLExecDirect is executing DELETE statement, when DELETE was
done, SQL_SUCCESS will be returned. Trigger was only launched internal SQL
Server and you cannot decide whether trigger was done or not.

I recheck the original post. For your scenario(using trigger to do the
update), you will have to redesign the business logic to avoid the update's
roll back.

For example, create a stored procedure, let the stored procedure to the
DELETE, AUDIT and UPDATE in a transcation. Let you program call the stored
procedure

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

j.a. harriman

2005-11-08, 3:56 pm

Michael,

I think the stored procedure is a last option; before I go that route I want
to exahaust all the ODBC solutions.

When I run "SQL Profiler" and capture the deletes (called from isqlw.exe) I
can see that the delete starts with a "SQL: Batch Starting", followed by a
"SQL: StmtStrarting". I can see that "SQL: StmtCompleted" occurs, followed
by "SQL: BatchCompleted".

What I assume is happening is that the return code my C++ app is getting is
that the statement completed, but what I need is when the **batch** completes.

When I get the return code back (SQL_SUCCESS) that the **statement**
completed, aren't there any ODBC functions I can call to determine that
additional proceses/activity are running on that handle, i.e. the triggers??

Jeff

"Michael Cheng [MSFT]" wrote:

> Hi Jeff,
>
> back
>
> Since your SQLExecDirect is executing DELETE statement, when DELETE was
> done, SQL_SUCCESS will be returned. Trigger was only launched internal SQL
> Server and you cannot decide whether trigger was done or not.
>
> I recheck the original post. For your scenario(using trigger to do the
> update), you will have to redesign the business logic to avoid the update's
> roll back.
>
> For example, create a stored procedure, let the stored procedure to the
> DELETE, AUDIT and UPDATE in a transcation. Let you program call the stored
> procedure
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
>
> Sincerely yours,
>
> Michael Cheng
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ====================
====================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Michael Cheng [MSFT]

2005-11-09, 7:23 am

Hi Jeff,

No, I am sorry to say that I am afraid we cannot trace such cascade calling
via ODBC. You will have to redesign the architecture of this logic.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

j.a. harriman

2006-01-19, 1:23 pm

I am replying to my own post, as I recieved an answer from my MSDN ticket
that I opened.

There are 2 solutions to the issue I described:
1.)
rc = SQLExecDirect(hstmt,
(unsigned char*)"SET NOCOUNT ON",
SQL_NTS);
// *** the code here ***
rc = SQLExecDirect(hstmt,
(unsigned char*)"DROP TABLE REPRO",
SQL_NTS);

2.) Put the "SET NOCOUNT ON" directly in the triggers.

We chose the 2nd solution as it was easier to implement.

Related kb article:

BUG: Incorrect Number of Rows Inserted Using MS ODBC Driver and Stored
Procedure on SQL Server
http://support.microsoft.com/defaul...kb;EN-US;293901

Hope this helps anyone who runs across this. Jeff

Michael Cheng [MSFT]

2006-01-20, 3:23 am

Hi Jeffery,

Thanks so much for your contribution. I believe will all benefit from this
invalueable inputs and summaries.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Sponsored Links