Knowledge Base

BUG: SQLBulkOperations and SQLSetPos Don't Insert Identity Values

Article ID: 294153

Article Last Modified on 5/12/2003

APPLIES TO

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

This article was previously published under Q294153

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

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)
)

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);
}

Compile and then run the application.

Additional query words: IDENTITY

Keywords: kbbug kbpending KB294153

Microsoft KB Archive/294153

Contents