Microsoft KB Archive/294153

= 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.



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:
 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 
 * 6) include 

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<<&quot;ERROR[&quot;<<looper<<&quot;]::STATE[&quot;<<state<<&quot;]::MESSAGE: \&quot;&quot;<<message<<&quot;\&quot;&quot;<<endl; printf(&quot;ERROR[%i]::STATE[%s]::MESSAGE: \&quot;%s (%d)\&quot;\n&quot;, 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*)&quot;DSN=reidwpridsn;UID=sa;PWD=;&quot;, 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, &quot;SET IDENTITY_INSERT new_employees ON&quot;); ret = SQLExecDirect(hstmt1, SQLStmt, SQL_NTS); strcpy((char *) SQLStmt, &quot;SELECT id_num, fname, minit, lname FROM new_employees&quot;); 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, &quot;TEST&quot;); EmpInfoArray[0].lname_LI = SQL_NTS; strcpy((char *) EmpInfoArray[0].fname, &quot;FNAME&quot;); EmpInfoArray[0].fname_LI = SQL_NTS; strcpy((char *) EmpInfoArray[0].minit, &quot;C&quot;); 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 << &quot;success&quot; <<endl; else OutputMessages(SQL_HANDLE_STMT, &hstmt1); ret = SQLCloseCursor(hstmt1); strcpy((char *) SQLStmt, &quot;SET IDENTITY_INSERT new_employees OFF&quot;); ret = SQLExecDirect(hstmt1, SQLStmt, SQL_NTS); SQLFreeStmt(hstmt1, SQL_DROP); SQLDisconnect(hdbc1); SQLFreeConnect(hdbc1);

SQLFreeEnv(henv1); }                   </li> Compile and then run the application.</li></ol>

Additional query words: IDENTITY

Keywords: kbbug kbpending KB294153

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.