BUG & test case: ODBC client hangs or gets an error when INSERT intoa table with a Trigger

[Database Administrator]

Hi,

the application below runs into Error 24000 "Invalid Cursor State" after
executing the INSERT a few times.

If I change the Trigger to

CREATE TRIGGER Trigger1 ON [Tabelle1]
FOR INSERT NOT FOR REPLICATION
AS
BEGIN
INSERT INTO Tabelle2
SELECT DISTINCT Text1
FROM inserted l
LEFT JOIN Tabelle2 r ON l.Text1 = r.[Key]
WHERE r.[Key] IS NULL
END

the ODBC client hangs with 100% CPU somewhere in the ODBC stack. No
Network activity. The last command executed in the Enterprise Manager
says "dbcc inputbuffer(54)" while the client hangs.

Inserts from other sources like Query Analyzer or a native JDBC driver
work as expected in any case (at least so far).


System environment
==================
ODBC 2000.85.1132.00 <-> SQL Server 8.00.760 SP3
ODBC 2000.85.1132.00 <-> SQL Server 8.00.2039 SP4
ODBC 3.70.11.46 <-> SQL Server 8.00.2039 SP4

I did not find any differences between the three scenarios except that
the first one crashed a bit faster than the others (usually no more than
5 lines).

The application screnario is a bit more complicated than shown, but the
principal structure and the behavior is the same.

I have no quantitative numbers but it seems that a higher performance
clients and/or network connections raises the probability of this error.
This might be the explanation why the application has run successfully
for about 6 years and half a million inserts.


Marcel Müller


Table Structure
===============
CREATE TABLE [dbo].[Tabelle1] (
[text1] [varchar] (255) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tabelle2] (
[key] [varchar] (255) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tabelle2] WITH NOCHECK ADD
CONSTRAINT [PK_Tabelle2] PRIMARY KEY CLUSTERED
(
[key]
) ON [PRIMARY]
GO

CREATE TRIGGER Trigger1 ON [Tabelle1]
FOR INSERT NOT FOR REPLICATION
AS
IF @@ROWCOUNT = 1
BEGIN
DECLARE @key AS VARCHAR(255)
SET @key = (SELECT TOP 1 Text1 FROM inserted)
IF NOT EXISTS (SELECT * FROM Tabelle2 WHERE [Key] = @key)
INSERT INTO Tabelle2 VALUES (@key)
END
ELSE
BEGIN
INSERT INTO Tabelle2
SELECT DISTINCT Text1
FROM inserted l
LEFT JOIN Tabelle2 r ON l.Text1 = r.[Key]
WHERE r.[Key] IS NULL
END
/*
Tabelle 2 holds a fast and consistent version of SELECT DISTINCT of
Tabelle 1. Tabelle1 typically contains only some hundrets of distinct
values in the related column while the total number of lines is in the
order of 10^7 to 10^8, which makes even a stream aggregate on the index
slow.
*/
===============

Application
===============
// Test.cpp : Defines the entry point for the console application.

#include
#include
#include
#include

const char* sConnect =
"DRIVER={SQL SERVER};SERVER=yamato;UID=sa;PWD=***;Database=Stat istik";
const char* sStmt = "INSERT INTO Tabelle1 (Text1) VALUES (?)";

// db-Handles for odbc-access (Owned by the connection thread)
HENV hEnv;
HDBC hDBC;
HSTMT hStmt;

char col1[255];
SQLINTEGER len1;


RETCODE evalSQLFunc(RETCODE result, const char* text)
{ switch (result)
{case SQL_INVALID_HANDLE:
printf("\n%s SQL Error: invalid handle.\n", text);
exit(1);
case SQL_ERROR:
UCHAR szErrMsg[4000];
UCHAR szSqlState[6];
SQLINTEGER errnr;
SQLSMALLINT errmsg;
SQLError(hEnv, hDBC, hStmt, szSqlState, &errnr, szErrMsg, sizeof
szErrMsg, &errmsg);
printf("\n%s SQLError: %.*s\nSQLState: %s\n", text, sizeof szErrMsg,
szErrMsg, szSqlState);
exit(2);
}
return result;
}

int main(int argc, char* argv[])
{
evalSQLFunc(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv),
"SQLAllocHandle(ENV...)");
evalSQLFunc(SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER), "SQLSetEnvAttr");
evalSQLFunc(SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDBC),
"SQLAllocHandle(DBC...)");
evalSQLFunc(SQLSetConnectAttr(hDBC, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)
1, SQL_INTEGER), "SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT...)");

SQLCHAR szOutConn[2048];
SQLSMALLINT cbOutConn;
evalSQLFunc(SQLDriverConnect(hDBC, 0, (UCHAR*)sConnect, SQL_NTS,
szOutConn, sizeof(szOutConn), &cbOutConn, SQL_DRIVER_NOPROMPT),
"SQLDriverConnect");

/* SQL INSERT-STATEMENT */
evalSQLFunc(SQLAllocHandle(SQL_HANDLE_STMT, hDBC, &hStmt),
"SQLAllocHandle(STMT...)");
evalSQLFunc(SQLPrepare(hStmt, (UCHAR*)sStmt,
(SQLINTEGER)strlen(sStmt)), "SQLPrepare");

evalSQLFunc(SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_VARCHAR, sizeof col1, 0, col1, 0, &len1), "SQLBindParameter(1...)");

for (int i = 0; i < 100000; ++i)
{ len1 = sprintf(col1, "Test %i\n", i);
evalSQLFunc(SQLExecute(hStmt), "SQLExecute");
printf("\r%s", col1);
}

return 0;
}