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()

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

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()

Error Could establish connection to the database

Hi,

I encounter few errors when I try to create a login page using tool in Microsoft Visual Studio 2005, please refer to below :

'--------------------------

I create a Login screen from Toolbox.

When I try to configure the setting for Provider Configuration in the ASP.NET Website Administration Tool using Select a single provider for all site management data or Select a different provider for each feature (advanced) options, the error 'Could not establish a connection to the database' appear.

I have tried to run aspnet_regsql to configure the server and database and return to this tool to configure the Provider configuration but the same error 'Could not establish a connection to the database.' appear.

When I click the Security Configuration link in ASP.NET Configuration, the error as shown below appear :

'There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the below button to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact under the default settings SQL Server does not allow remote connections.(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)'

When I try to choose Data Store in the Security Configuration screen(Figure 4), the same error('Could not establish a connection to the database') appear.

'--------------------

I also have tried to configure the SQL Server Surface Area Configuration but the same error still appear….

Is there any other configuration I need to set before I can create this login page?

Any advices or help is much appreciate.

Thank you.

Regards,

Vicky

Hi Vickie,

Based on the errors, have you verified that you can manually connect to the data source with the connection string information you are using? You can use the information at this link to see if the 'role manager' feature is enabled,http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000013.asp. And, you can use this link to verify that your server will allow remote connections,http://support.microsoft.com/kb/265808/en-us. I hope this helps.

~des

Error connecting to SQL from within same machine

I am trying to move my SharePoint db's from on drive on a SQL Server to
another. Apparently, This cannot be done with the Management Tool and must
be done with the sqlcmd.
The first comand needed is: >sqlcmd -S
\\.\pipe\mssql$microsoft##ssee\sql\query -E
This yields the follwoing error:
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
establi
shing a connection to the server. When connecting to SQL Server 2005, this
failu
re may be caused by the fact that under the default settings SQL Server does
not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
TCP, Named Pipes and Shared Memory are enabled and configuration allows for
remote connection (even thought this local)
I have tried to modify the command with the specific instance and
server\instance all result in the same error.
I am at a loss as what to do to move these db's
Can anyone help with this issue.
Thanks,
Mike Altman
Hello Mike,
Do not use sqlcmd. Just stop your SharePoint services. Detach your databases
using Management Studio, go to the operating system and move all your
database files to the new drive and then attach your databases using
Management Studio again.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"travldc" wrote:

> I am trying to move my SharePoint db's from on drive on a SQL Server to
> another. Apparently, This cannot be done with the Management Tool and must
> be done with the sqlcmd.
> The first comand needed is: >sqlcmd -S
> \\.\pipe\mssql$microsoft##ssee\sql\query -E
> This yields the follwoing error:
> HResult 0x2, Level 16, State 1
> Named Pipes Provider: Could not open a connection to SQL Server [2].
> Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
> establi
> shing a connection to the server. When connecting to SQL Server 2005, this
> failu
> re may be caused by the fact that under the default settings SQL Server does
> not
> allow remote connections..
> Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
> TCP, Named Pipes and Shared Memory are enabled and configuration allows for
> remote connection (even thought this local)
> I have tried to modify the command with the specific instance and
> server\instance all result in the same error.
> I am at a loss as what to do to move these db's
> Can anyone help with this issue.
> Thanks,
> Mike Altman
>
>
|||Hi Ben,
I had read that this was not best practice, but it worked just fine. Thanks
for the help.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hello Mike,
> Do not use sqlcmd. Just stop your SharePoint services. Detach your databases
> using Management Studio, go to the operating system and move all your
> database files to the new drive and then attach your databases using
> Management Studio again.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "travldc" wrote:

Error connecting to SQL from within same machine

I am trying to move my SharePoint db's from on drive on a SQL Server to
another. Apparently, This cannot be done with the Management Tool and must
be done with the sqlcmd.
The first comand needed is: >sqlcmd -S
\\.\pipe\mssql$microsoft##ssee\sql\query -E
This yields the follwoing error:
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
establi
shing a connection to the server. When connecting to SQL Server 2005, this
failu
re may be caused by the fact that under the default settings SQL Server does
not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
TCP, Named Pipes and Shared Memory are enabled and configuration allows for
remote connection (even thought this local)
I have tried to modify the command with the specific instance and
server\instance all result in the same error.
I am at a loss as what to do to move these db's
Can anyone help with this issue.
Thanks,
Mike AltmanHello Mike,
Do not use sqlcmd. Just stop your SharePoint services. Detach your databases
using Management Studio, go to the operating system and move all your
database files to the new drive and then attach your databases using
Management Studio again.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"travldc" wrote:
> I am trying to move my SharePoint db's from on drive on a SQL Server to
> another. Apparently, This cannot be done with the Management Tool and must
> be done with the sqlcmd.
> The first comand needed is: >sqlcmd -S
> \\.\pipe\mssql$microsoft##ssee\sql\query -E
> This yields the follwoing error:
> HResult 0x2, Level 16, State 1
> Named Pipes Provider: Could not open a connection to SQL Server [2].
> Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
> establi
> shing a connection to the server. When connecting to SQL Server 2005, this
> failu
> re may be caused by the fact that under the default settings SQL Server does
> not
> allow remote connections..
> Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
> TCP, Named Pipes and Shared Memory are enabled and configuration allows for
> remote connection (even thought this local)
> I have tried to modify the command with the specific instance and
> server\instance all result in the same error.
> I am at a loss as what to do to move these db's
> Can anyone help with this issue.
> Thanks,
> Mike Altman
>
>|||Hi Ben,
I had read that this was not best practice, but it worked just fine. Thanks
for the help.
"Ben Nevarez" wrote:
> Hello Mike,
> Do not use sqlcmd. Just stop your SharePoint services. Detach your databases
> using Management Studio, go to the operating system and move all your
> database files to the new drive and then attach your databases using
> Management Studio again.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "travldc" wrote:
> > I am trying to move my SharePoint db's from on drive on a SQL Server to
> > another. Apparently, This cannot be done with the Management Tool and must
> > be done with the sqlcmd.
> >
> > The first comand needed is: >sqlcmd -S
> > \\.\pipe\mssql$microsoft##ssee\sql\query -E
> >
> > This yields the follwoing error:
> >
> > HResult 0x2, Level 16, State 1
> > Named Pipes Provider: Could not open a connection to SQL Server [2].
> > Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
> > establi
> > shing a connection to the server. When connecting to SQL Server 2005, this
> > failu
> > re may be caused by the fact that under the default settings SQL Server does
> > not
> > allow remote connections..
> > Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
> >
> > TCP, Named Pipes and Shared Memory are enabled and configuration allows for
> > remote connection (even thought this local)
> >
> > I have tried to modify the command with the specific instance and
> > server\instance all result in the same error.
> >
> > I am at a loss as what to do to move these db's
> >
> > Can anyone help with this issue.
> >
> > Thanks,
> >
> > Mike Altman
> >
> >
> >
> >

Error connecting to SQL from within same machine

I am trying to move my SharePoint db's from on drive on a SQL Server to
another. Apparently, This cannot be done with the Management Tool and must
be done with the sqlcmd.
The first comand needed is: >sqlcmd -S
\\.\pipe\mssql$microsoft##ssee\sql\query -E
This yields the follwoing error:
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
establi
shing a connection to the server. When connecting to SQL Server 2005, this
failu
re may be caused by the fact that under the default settings SQL Server does
not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
TCP, Named Pipes and Shared Memory are enabled and configuration allows for
remote connection (even thought this local)
I have tried to modify the command with the specific instance and
server\instance all result in the same error.
I am at a loss as what to do to move these db's
Can anyone help with this issue.
Thanks,
Mike AltmanHello Mike,
Do not use sqlcmd. Just stop your SharePoint services. Detach your databases
using Management Studio, go to the operating system and move all your
database files to the new drive and then attach your databases using
Management Studio again.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"travldc" wrote:

> I am trying to move my SharePoint db's from on drive on a SQL Server to
> another. Apparently, This cannot be done with the Management Tool and mus
t
> be done with the sqlcmd.
> The first comand needed is: >sqlcmd -S
> \\.\pipe\mssql$microsoft##ssee\sql\query -E
> This yields the follwoing error:
> HResult 0x2, Level 16, State 1
> Named Pipes Provider: Could not open a connection to SQL Server [2].
> Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
> establi
> shing a connection to the server. When connecting to SQL Server 2005, this
> failu
> re may be caused by the fact that under the default settings SQL Server do
es
> not
> allow remote connections..
> Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
> TCP, Named Pipes and Shared Memory are enabled and configuration allows fo
r
> remote connection (even thought this local)
> I have tried to modify the command with the specific instance and
> server\instance all result in the same error.
> I am at a loss as what to do to move these db's
> Can anyone help with this issue.
> Thanks,
> Mike Altman
>
>|||Hi Ben,
I had read that this was not best practice, but it worked just fine. Thanks
for the help.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hello Mike,
> Do not use sqlcmd. Just stop your SharePoint services. Detach your databas
es
> using Management Studio, go to the operating system and move all your
> database files to the new drive and then attach your databases using
> Management Studio again.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "travldc" wrote:
>

Error connecting to OLEDB for DB2

I'm attempting to configure a new Microsoft OLE DB Provider for DB2 connection using the provided Data Access Tool and Wizard. I'm configured for DB2\MVS TCP/IP and have my Catalog and other defaults set. When I go to connect I get the following:

Could not connect to data source 'New Data Source':
An internal network library error has occurred. A network level conversational protocol error has occurred. SQLSTATE: HY000, SQLCODE: -343

The port I've configured is what is used when we use the IBM OLE DB and ODBC drivers. Where can I look to see what the problem is?

Thanks,

Mike

I'm moving your thread to the Data Access forum as you'll have a greater chance of having an OLEDB question answered.|||

Hi,

some OLEDB providers have the functionality for logging implemented which can be turned on with a flag on the client side, this should give you more information about the error you ar egetting. The Provider flags are very specific so you should look in the documentation of the used provider.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Error Check tool

We have a report where we do a whole bunch of calculations.

Sometimes we get #div0 err,when you try to divide a number by 0, or NaN Error, when you are trying to divide 0 in the numerator.. Is there a tool, that can sopt check these errors on a report in reporting services, and generate a log that we can check daily for errs?Pl advise

I'm hoping this may help you:

http://blogs.msdn.com/bwelcker/archive/2006/09/26/End-of-Amnesia-_2800_Avoiding-Divide-By-Zero-Errors_2900_.aspx

|||I do have this IIF check on the textbox expression. Howe ver, I have abt 35 fields on the report, that has these calculations.. Its pretty timeconsumin to put this check on all the 35 fields. Hence I was lookin for a tool, that will capture such similar errors.|||This is the only tool I am aware of, not sure of its capabilities
http://www.ssw.com.au/ssw/SQLReportingServicesAuditor/

Maybe putting it in a code block would make it less time consuming|||

That tool appears to be the one I was looking for. However, I dont find any reviews on anybody using it and dont know of anyone who ahs used it. Would like to get some feedback on that before I can decide to use it.

When you say putting it in a code block, what exactly do you mean?

|||You can add code to a report as follows.

On the 'report' menu, select 'report properties' (if you can't see this, you may need to switch out of the 'preview' tab in the report designer.

From here you can see a 'code' tab.

You could add something like

NB substitute 'number' for whatever numeric datatype you are using, you might have to have multiple functions for this
one for int32, one for float etc

Function x(num1,num2) as number

If (num2 is nothing) or num2 = 0 then
return 0 'or whatever means something useful like 'n/a', but then you'd need to return a string
else
return num1 / num2
end if

end function

Then in the expression, you can reference the code thus:
=Code.x(num1,num2)

At least it is less typing than an IIF