BUG: SQLBulkOperations and SQLSetPos Don't Insert Identity Values

This article was previously published under Q294153
This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
When you use the SQLBulkOperations or SQLSetPos ODBC call to insert explicit values into a SQL Server identity column, the call may fail with the following error:
DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Server]Explicit value must be specified for identity column in table 'new_employees' when IDENTITY_INSERT is set to ON. (545)
CAUSE
The ODBC SQL Server driver does not send the bound information for the identity column to the server for insertion.
RESOLUTION
Do not use SQLBulkOperations or SQLSetPos to insert data into SQL Server identity columns. Instead, consider using parameterized INSERT statements with SQLPrepare and SQLExecute.
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.
MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a table using the following schema:
    create table new_employees (	id_num int identity(1,1),	fname char(20),	minit char(1),	lname char(30))					
  2. Create a new Microsoft Visual C++ Console application and paste the following code:
    #include <windows.h>#include <sql.h>#include <sqlext.h>#include <tchar.h>#include <iostream.h>#include <stdio.h>typedef struct {	SQLUINTEGER  id_num;	SQLINTEGER   id_num_LI;	SQLCHAR      fname[21];	SQLINTEGER   fname_LI;	SQLCHAR      minit[2];	SQLINTEGER   minit_LI;	SQLCHAR      lname[31];	SQLINTEGER   lname_LI;} EMP_INFO;EMP_INFO EmpInfoArray[2];void OutputMessages(SQLSMALLINT handletype, SQLHANDLE* handlepointer) {		int looper = 1;	SQLCHAR state[10];	SQLCHAR message[100];	SQLINTEGER nativeError;		while (SQL_NO_DATA != SQLGetDiagRec(handletype, *handlepointer, looper, state, &nativeError,		message, 100, NULL)) {		//cout<<"ERROR["<<looper<<"]::STATE["<<state<<"]::MESSAGE: \""<<message<<"\""<<endl;		printf("ERROR[%i]::STATE[%s]::MESSAGE: \"%s (%d)\"\n", looper, state, message, nativeError);		looper++;	}	}void main(){	SQLRETURN ret;	SQLHENV henv1;	SQLHDBC hdbc1;	SQLHSTMT hstmt1;	SQLPOINTER sqlp = NULL;		SQLUSMALLINT  RowStatusArray[12];	SQLCHAR       SQLStmt[255];		ret = SQLAllocEnv(&henv1);		ret = SQLAllocConnect(henv1, &hdbc1);		ret = SQLDriverConnect(hdbc1, NULL, (SQLCHAR*)"DSN=reidwpridsn;UID=sa;PWD=;", SQL_NTS,		NULL, SQL_NTS, NULL, SQL_DRIVER_NOPROMPT);		ret = SQLAllocStmt(hdbc1, &hstmt1);		ret = SQLSetStmtAttr(hstmt1, SQL_ATTR_CONCURRENCY, 		(SQLPOINTER) SQL_CONCUR_ROWVER, 0);		    ret = SQLSetStmtAttr(hstmt1, SQL_ATTR_CURSOR_TYPE, 		(SQLPOINTER) SQL_CURSOR_DYNAMIC, 0);		    // Use row-wise binding.    ret = SQLSetStmtAttr(hstmt1, SQL_ATTR_ROW_BIND_TYPE,		(SQLPOINTER) sizeof(EMP_INFO), 0);			ret = SQLSetStmtAttr(hstmt1, SQL_ATTR_ROW_ARRAY_SIZE, 		(SQLPOINTER) 1, 0);		ret = SQLSetStmtAttr(hstmt1, SQL_ATTR_ROW_STATUS_PTR, 		RowStatusArray, 0);		strcpy((char *) SQLStmt, "SET IDENTITY_INSERT new_employees ON");		ret = SQLExecDirect(hstmt1, SQLStmt, SQL_NTS);		strcpy((char *) SQLStmt, "SELECT id_num, fname, minit, lname FROM new_employees");		ret = SQLExecDirect(hstmt1, SQLStmt, SQL_NTS);		ret = SQLBindCol(hstmt1, 1, SQL_C_SLONG, 		&EmpInfoArray[0].id_num, sizeof(EmpInfoArray[0].id_num),		&EmpInfoArray[0].id_num_LI);		    ret = SQLBindCol(hstmt1, 2, SQL_C_CHAR,   		EmpInfoArray[0].fname, 		sizeof(EmpInfoArray[0].fname),		&EmpInfoArray[0].fname_LI);		    ret = SQLBindCol(hstmt1, 3, SQL_C_CHAR, 		&EmpInfoArray[0].minit,		sizeof(EmpInfoArray[0].minit),		&EmpInfoArray[0].minit_LI);		    ret = SQLBindCol(hstmt1, 4, SQL_C_CHAR,   		EmpInfoArray[0].lname, 		sizeof(EmpInfoArray[0].lname),		&EmpInfoArray[0].lname_LI);		ret = SQLFetchScroll(hstmt1, SQL_FETCH_NEXT, 0);			EmpInfoArray[0].id_num = 200;	EmpInfoArray[0].id_num_LI = SQL_NTS;	strcpy((char *) EmpInfoArray[0].lname, "TEST");	EmpInfoArray[0].lname_LI = SQL_NTS;	strcpy((char *) EmpInfoArray[0].fname, "FNAME");	EmpInfoArray[0].fname_LI = SQL_NTS;	strcpy((char *) EmpInfoArray[0].minit, "C");	EmpInfoArray[0].minit_LI = SQL_NTS;	   // The following line generates the error.   ret = SQLBulkOperations(hstmt1, SQL_ADD);   // Alternatively, you can use the following line and you will get the same error.   //ret = SQLSetPos(hstmt1, 0, SQL_ADD, SQL_LOCK_NO_CHANGE);      if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) 	   cout << "success" <<endl;   else	   OutputMessages(SQL_HANDLE_STMT, &hstmt1);      ret = SQLCloseCursor(hstmt1);      strcpy((char *) SQLStmt, "SET IDENTITY_INSERT new_employees OFF");      ret = SQLExecDirect(hstmt1, SQLStmt, SQL_NTS);      SQLFreeStmt(hstmt1, SQL_DROP);      SQLDisconnect(hdbc1);      SQLFreeConnect(hdbc1);      SQLFreeEnv(henv1);}					
  3. Compile and then run the application.
IDENTITY
Properties

Article ID: 294153 - Last Review: 02/28/2014 04:26:59 - Revision: 2.1

  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
  • kbnosurvey kbarchive kbbug kbpending KB294153
Feedback