Monday, March 19, 2012

Error Creating Data Source View in VS 2005 BI Tool for SQL Server 2005 View with > 32 Columns

I keep getting an error message in Visual Studio 2005 (SQL Server Business Intelligence Development Studio) = "Cannot have more than 32 columns" when I use the wizard to create a data source view using a view I created in a SQL Server 2005 database, which I use as a data source. The view does have more than 32 columns; however, I have been successful in creating data source views for other DB views with more than 32 columns. It seems that for some DB views with more than 32 columns the wizard ignores this problem, but for others - admittedly with a larger number of columns - it does not.

Any insights and potential workarounds? Thanks!

Please file a bug on http://connect.microsoft.com/SQLServer/Feedback and we'll look into this. If possible, please include a SQL DDL script that will create the source database which causes problems.

As a possible work around, you can try creating the DSV without this table and then adding the table in the DSV editor and see if that works.

Thanks

|||

Thanks. I will file a bug report, as you suggested. Regarding your suggestion, I forgot to mention that I tried that too and got the same error message. But it gave me another idea to try, which worked. I created the data source view using the wizard without selecting a table or a view from the data source, and then I used the New Named Query menu option to re-create the view (i.e., using the same SQL statement to create the view in the SQL Server 2005 DB). That did the trick! (BTW, there is another issue with the New Named Query pop-up window when you enter a SQL query and click on the OK button without entering a name for the query.)

FYI, below are the message and the program location details for the error related to the maximum number of columns in a data source view:

Message:

Cannot have more than 32 columns. (Microsoft Visual Studio)

Program Location:

at System.Data.DataKey..ctor(DataColumn[] columns, Boolean copyColumns)
at System.Data.UniqueConstraint.Create(String constraintName, DataColumn[] columns)
at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at System.Data.DataTable.Load(IDataReader reader)
at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType)
at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV()

No comments:

Post a Comment