BUG: SQLBulkOperations and SQLSetPos Don't Insert Identity Values

Article translations Article translations
Article ID: 294153 - View products that this article applies to.
This article was previously published under Q294153
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

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.

Properties

Article ID: 294153 - Last Review: February 28, 2014 - Revision: 2.1
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
Keywords: 
kbnosurvey kbarchive kbbug kbpending KB294153

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com