Connect Excel Pivot Table to SQL Server Stored Procedure

Friends,
Hi ...
I have been connecting Excel pivot tables to SQL server tables and views
through an ODBC DSN connection.
I know there is a way to connect a pivot table to a SQL Server stored
procedure, but can't figure it out. The Excel pivot table connection wizard
doesn't seem to offer stored procedures as one of the connection choices.
Anyone out there know how to do this?
Thanks, Bill Morgan
Thanks ...
Hi, again ...
Got answer from SQL Server programmer, and was able to connect Excel pivot
table to SQL Server stored procedure:
In Excel, go to Data \ Get External Data \ New Database Query. Choose SQL
Server connection and then select the appropriate DSN connection to your SQL
Server (and in the DSN options, be sure to set the DSN default to the right
database).
Follow the dialog box prompts. You may not see the right tables/views to
choose from, but select any table in order to keep going through the
successive dialog boxes. When given the choice, you want to "View Data or
Edit Query in Microsoft Query" (dialog box with 3 radio buttons - you want
the middle button in Excel 2000)
When you get to the end of the dialog boxes, go into View/SQL (or click on
SQL icon on the toolbar) and delete the SQL code that is in the SQL box. In
it's place, type EXEC your_database_name.Owner.your_stored_procedure_nam e.
Click OK when the dialog box says you cannot view your query graphically.
Once you see the query result (rows and columns of data) go to File \ Export
results to Excel. Then be sure to check the Pivot Table radio button on the
dialog box that comes up.
The pivot table is refreshable, and as long as the UserName in the DSN has
ReadOnly priveledges (and as long as you have granted EXEC permission on the
stored procedure) you can share the pivot table with other users.
"bill_morgan_3333" wrote:

> Friends,
> Hi ...
> I have been connecting Excel pivot tables to SQL server tables and views
> through an ODBC DSN connection.
> I know there is a way to connect a pivot table to a SQL Server stored
> procedure, but can't figure it out. The Excel pivot table connection wizard
> doesn't seem to offer stored procedures as one of the connection choices.
> Anyone out there know how to do this?
> Thanks, Bill Morgan
> Thanks ...
>

Connect Excel Pivot Table to SQL Server Stored Procedure

Friends,
Hi ...
I have been connecting Excel pivot tables to SQL server tables and views
through an ODBC DSN connection.
I know there is a way to connect a pivot table to a SQL Server stored
procedure, but can't figure it out. The Excel pivot table connection wizard
doesn't seem to offer stored procedures as one of the connection choices.
Anyone out there know how to do this?
Thanks, Bill Morgan
Thanks ...Hi, again ...
Got answer from SQL Server programmer, and was able to connect Excel pivot
table to SQL Server stored procedure:
In Excel, go to Data \ Get External Data \ New Database Query. Choose SQL
Server connection and then select the appropriate DSN connection to your SQL
Server (and in the DSN options, be sure to set the DSN default to the right
database).
Follow the dialog box prompts. You may not see the right tables/views to
choose from, but select any table in order to keep going through the
successive dialog boxes. When given the choice, you want to "View Data or
Edit Query in Microsoft Query" (dialog box with 3 radio buttons - you want
the middle button in Excel 2000)
When you get to the end of the dialog boxes, go into View/SQL (or click on
SQL icon on the toolbar) and delete the SQL code that is in the SQL box. In
it's place, type EXEC your_database_name.Owner.your_stored_procedure_name.
Click OK when the dialog box says you cannot view your query graphically.
Once you see the query result (rows and columns of data) go to File \ Export
results to Excel. Then be sure to check the Pivot Table radio button on the
dialog box that comes up.
The pivot table is refreshable, and as long as the UserName in the DSN has
ReadOnly priveledges (and as long as you have granted EXEC permission on the
stored procedure) you can share the pivot table with other users.
"bill_morgan_3333" wrote:

> Friends,
> Hi ...
> I have been connecting Excel pivot tables to SQL server tables and views
> through an ODBC DSN connection.
> I know there is a way to connect a pivot table to a SQL Server stored
> procedure, but can't figure it out. The Excel pivot table connection wizar
d
> doesn't seem to offer stored procedures as one of the connection choices.
> Anyone out there know how to do this?
> Thanks, Bill Morgan
> Thanks ...
>

Configuring Connection Manager for 6.5 database

Greetings once again SSIS friends,

I am trying to configure a connection manager for a database that is still using SQL Server 6.5 But it is not working properly. I put the server name, user name and pass word, I select the Native OLE DB\Microsoft OLE DB Provider for SQL Server as my provider.

When I click Test Connection it says the connection is fine but then the drop down list for the list of databse names hows nothing!

Am I using the wrong provider?! I'm confused!

You have to use datareader source component as you need ODBC connection. At least that was the only way I could get it to work; as far as I remember.|||

Hi mate,

I have tried using the DataReader Source but I can't seem to be able to configure it correctly. When I try and refresh the component I get the following error :

Cannot acquire a managed connection from the run-time connection manager.

Please advise.

Thanks.

p.s.

I did add a new ODBC Data source for my SQL Server 6.5 database but I still don't know how to configure the component.

|||

First at all; you need to create a DSN to point to the 6.5 DB in the server/computer that would run the package. This is Control Panel->Administrative Tools-->ODBC Data Source Administrator. Make sure you create that DSN as 'System DSN' so is available to any user running the package.

Then in BIDS, create a package with a connection manager using .Net Provider\Odbc Data Provider and in 'Use user or system data source name' chose from the dropdown list the DSN you created in the previous step. If the Test connection button says is ok; you should be able to use it in the data reader source component.

Is this how you are doing it?

|||

First at all; you need to create a DSN to point to the 6.5 DB in the server/computer that would run the package. This is Control Panel->Administrative Tools-->ODBC Data Source Administrator. Make sure you create that DSN as 'System DSN' so is available to any user running the package.

Yes.. I had already done that

Then in BIDS, create a package with a connection manager using .Net Provider\Odbc Data Provider and in 'Use user or system data source name' chose from the dropdown list the DSN you created in the previous step. If the Test connection button says is ok; you should be able to use it in the data reader source component.

You are right. That's what I had wrong. I was using the naitive ODBC data provider which apparently only works if I am using a SQL Query Task but not when creating a connection manager. I did just that and now my data source works just fine.

Thanks for your help mate. Much appreciated!

Configuring Connection Manager for 6.5 database

Greetings once again SSIS friends,

I am trying to configure a connection manager for a database that is still using SQL Server 6.5 But it is not working properly. I put the server name, user name and pass word, I select the Native OLE DB\Microsoft OLE DB Provider for SQL Server as my provider.

When I click Test Connection it says the connection is fine but then the drop down list for the list of databse names hows nothing!

Am I using the wrong provider?! I'm confused!

You have to use datareader source component as you need ODBC connection. At least that was the only way I could get it to work; as far as I remember.|||

Hi mate,

I have tried using the DataReader Source but I can't seem to be able to configure it correctly. When I try and refresh the component I get the following error :

Cannot acquire a managed connection from the run-time connection manager.

Please advise.

Thanks.

p.s.

I did add a new ODBC Data source for my SQL Server 6.5 database but I still don't know how to configure the component.

|||

First at all; you need to create a DSN to point to the 6.5 DB in the server/computer that would run the package. This is Control Panel->Administrative Tools-->ODBC Data Source Administrator. Make sure you create that DSN as 'System DSN' so is available to any user running the package.

Then in BIDS, create a package with a connection manager using .Net Provider\Odbc Data Provider and in 'Use user or system data source name' chose from the dropdown list the DSN you created in the previous step. If the Test connection button says is ok; you should be able to use it in the data reader source component.

Is this how you are doing it?

|||

First at all; you need to create a DSN to point to the 6.5 DB in the server/computer that would run the package. This is Control Panel->Administrative Tools-->ODBC Data Source Administrator. Make sure you create that DSN as 'System DSN' so is available to any user running the package.

Yes.. I had already done that

Then in BIDS, create a package with a connection manager using .Net Provider\Odbc Data Provider and in 'Use user or system data source name' chose from the dropdown list the DSN you created in the previous step. If the Test connection button says is ok; you should be able to use it in the data reader source component.

You are right. That's what I had wrong. I was using the naitive ODBC data provider which apparently only works if I am using a SQL Query Task but not when creating a connection manager. I did just that and now my data source works just fine.

Thanks for your help mate. Much appreciated!