INFO: IRowsetFastLoad Usage and Limitations (307559)

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 <username> value and the Password =<strong 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.

#define DBINITCONSTANTS
   #include <SQLOLEDB.h>
   #include <atldbcli.h>

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

   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"dbo.jobs";
   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("Successfully created IRowsetFastLoad \n");
   else
   printf("FAILED to create IRowsetFastLoad \n");

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