Microsoft KB Archive/307559

= INFO: IRowsetFastLoad Usage and Limitations =

Article ID: 307559

Article Last Modified on 10/31/2003



This article was previously published under Q307559



SUMMARY
This article discusses the usage and limitations of the IRowsetFastLoad interface, a bulk copy command that provides a fast, write-only way to insert records into SQL Server.

Note Many OLE DB providers do not support this interface.



MORE INFORMATION
In SQL Server, the IRowsetFastLoad::InsertRow function inserts a row to an in-memory buffer. A batch is processed when the size limit of the batch is exceeded or when the IRowsetFastLoad::Commit function is explicitly called. The SSPROP_FASTLOADOPTIONS property specifies the maximum number of records before an implicit commit occurs.

Session Limitations
An OLE DB session that is opened for bulk copy using SSPROP_FASTLOADOPTIONS exposes only the following interfaces:
 * IDBSchemaRowset
 * IGetDataSource
 * IOpenRowset
 * ISupportErrorInfo
 * ITransactionJoin (not supported for SQL Server 6.5)

You cannot use calls to the QueryInterface method for other interfaces (for instance, IDBCreateCommand) after a session is enabled for bulk copy.

OLE DB Services
To use IRowsetFastLoad when OLE DB services is enabled, you must set SSPROP_IRowsetFastLoad in the Rowset properties.

The following code sample demonstrates how to use IRowsetFastLoad both with and without services enabled (respectively).

Note You must change the User ID value and the Password = value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database. #include   #include 
 * 1) define DBINITCONSTANTS

//Open Datasource with no cursor library (CE). HRESULT OpenDSNoSvcs(CDataSource & ds) {  return ds.OpenFromInitializationString(L&quot;Provider=SQLOLEDB.1;Password= ;User ID= ;Initial Catalog=pubs;Data Source=MyServerName;OLE DB Services=-5&quot;); }  //Open Datasource with CE. HRESULT OpenDSWithSvcs(CDataSource & ds) {  return ds.OpenFromInitializationString(L&quot;Provider=SQLOLEDB.1;Password= ;User ID= ;Initial Catalog=pubs;Data Source=MyServerName&quot;); }

int main(int argc, char* argv[]) {  CoInitialize(NULL); CDataSource ds; DBID dbid; //NOTE: For brevity, no clean up and minimal error checking are done in this code.

//To get successful IRowsetFastLoad, enable the following line. //HRESULT hr =OpenDSNoSvcs(ds); HRESULT hr =OpenDSWithSvcs(ds); if(SUCCEEDED(hr)) {  CSession sess; hr= sess.Open(ds);

if(SUCCEEDED(hr)) {  IRowsetFastLoad * pFastLoad=NULL; WCHAR TableName[] = L&quot;dbo.jobs&quot;; dbid.eKind = DBKIND_NAME; dbid.uName.pwszName = new WCHAR[wcslen(TableName)+2]; wcscpy( dbid.uName.pwszName, TableName);

DBPROP rgRowsetProps[1]; DBPROPSET RowsetPropSet;

VariantInit(&rgRowsetProps[0].vValue);

rgRowsetProps[0].dwOptions = DBPROPOPTIONS_REQUIRED; rgRowsetProps[0].colid = DB_NULLID; rgRowsetProps[0].dwStatus = DBPROPSTATUS_OK; rgRowsetProps[0].dwPropertyID = SSPROP_IRowsetFastLoad; rgRowsetProps[0].vValue.vt = VT_BOOL; rgRowsetProps[0].vValue.boolVal = VARIANT_TRUE;

RowsetPropSet.rgProperties = rgRowsetProps; RowsetPropSet.cProperties = 1; RowsetPropSet.guidPropertySet = DBPROPSET_SQLSERVERROWSET;

hr = sess.m_spOpenRowset->OpenRowset(NULL, &dbid, NULL,   IID_IRowsetFastLoad,   1, &RowsetPropSet,    (LPUNKNOWN*) &pFastLoad);

if(SUCCEEDED(hr)) printf(&quot;Successfully created IRowsetFastLoad \n&quot;); else printf(&quot;FAILED to create IRowsetFastLoad \n&quot;);

}  }   return 0; }

SQL Server Security
If you are using a SQL Server login account, make sure that the account has bulk-copy permissions to the database.

You Cannot Use IRowsetFastLoad from ActiveX Data Objects
ActiveX Data Objects (ADO) does not have a means to expose the IRowsetFastLoad interface because this is a provider-specific interface.

You Cannot Insert into Multiple Tables
You cannot use the IRowsetFastLoad::InsertRow function to insert data into more than one table.

Errors
An error condition (for instance, a data integrity violation) in one row will fail the entire batch and return one error for the entire batch.

Additional query words: RowsetFastLoad Bulk Copy SQLOLEDB

Keywords: kbinfo KB307559

-

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

© Microsoft Corporation. All rights reserved.