SQL Server Q&A

Symptoms
When you use a Sybase Adaptive Server Enterprise OLE DB provider, the Microsoft SQL Server 2008 Integrated Services (SSIS) OLE DB Source adapter may obtain only the first row of a source table. This problem may occureven if the following conditions are true:More rowsare stored in the source table on the Sybase server.The Integrated Services package seems to execute successfully.
Resolution
This problem may occur when the DBPROP_MAXROWS property is used to specify the maximum size of a rowset. In the SQL Server validation phase, the DBPROP_MAXROWS property is setto help performance. This is because the whole dataset is not needed for validation. However, the DBPROP_MAXROWS property may not be supported, or it may cause unintended consequences in SQL Server statement execution for certain versions of Sybase OLE DB providers. The unintended consequences in SQL Server statement execution may cause data corruption even though an Integrated Services package seems to execute successfully. For more information about when this can be a problem, see the “More Information” section.
For more information about the DBPROP_MAXROWSproperty, visit the following Microsoft Web site:
http://technet.microsoft.com/en-us/library/ms131271(SQL.100).aspx(http://technet.microsoft.com/en-us/library/ms131271(SQL.100).aspx)

Symptoms
When you try to insert an empty string into Memo, Text, nText or Blob columns by using the OLE DB .NET data provider, you receive the following exception:

An unhandled exception of type ‘System.InvalidOperationException’ occurred in system.data.dll If you handle this exception within a try-catch block, you receive the following information:

System.InvalidOperationException
System.Data.OleDb.OleDbException: Multiple-Step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Resolution
To work around this problem: For strings, insert a space that has a length greater than zero (such as ” “), instead of using an empty string.If the database has an Allow Nulls setting, select Allow Nulls for the columns in the database, and treat nulls as empty strings.

Symptoms
You create or edit a Microsoft SQL Server 2008 Integration Services (SSIS) package in Business Intelligence Development Studio (BIDS). You edit an OLE DB source in a Data Flow task. When you click the Columns tab, you receive the following error message:

TITLE: Microsoft Visual Studio
——————————
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR.An OLE DB error has occurred. Error code: 0×80040E21.
——————————
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)Note The DTS_E_OLEDBERROR error code has many causes. This article describes only one rare problem that has a specific cause. To make sure that you are encountering this problem, see the “More Information” section for a troubleshooting method.
Note This problem does not occur in SQL Server 2005.
Resolution
In SQL Server 2008 SSIS, a specific check determines whether the identifiers that are used by an OLE DB provider are case-sensitive. This check allows for SSIS to follow the case-sensitivity that is defined by an OLE DB provider configuration when SSIS lists object names, table names, and column names. This check is performed by implementing an OLE DB property that is named DBPROP_IDENTIFIERCASE.However, not all OLE DB providers support and implement this property. If the property is not implemented, an error may occur in the BIDS.