Sharing SQL Server connections across Sheets

[Snake]

We have an Excel 2007 worksheet with 44 sheets. Each sheet executes a query
against a view in Sql Server 2000. The user has to enter the password 44
times, once for each sheet! She is going crazy! Is there a way for the
sheets to share one connection, or at least share the entered password? The
ODC files contain the login and password but the password appears to be
ignored. I am a DBA, not an Excel developer so please be kind.

Thanks,

Michael

 

[Jason]

Hi Michael,

I'm an analyst and work primarily in Excel & SQLServer. I write my own
queries in SSMS and export my own data to Excel, but often need to
'embed' query/view data into excel files for other staff to access
(those that cannot use SSMS).

As far as I understand, the ODBC connections need a password for every
query passed to SSMS - hence your user needs to enter multiple
passwords. It's to do with the 'connection' and related .odc file being
tied to the query. There are serious security issues with the
connection being shared - as I'm sure you'll appreciate more than me
with you being a DBA. I may be wrong, but don't think that is possible.

Not being familiar with your data and how different the fields in the 44
sheets are, it's difficult to advise on a workaround. If the sheets
share the same columns, one possible option would be:

- write a query that UNION joins all the results into one view
- create an ODC connection and link this 'combined' data into a sheet
- build the excel file so the 44 sheets lookup their respective data
from the 'master' sheet.

Just a thought !

Jason