asked on

Excel connecting to SQL locks up

I have a spread sheet that connects to an outside database and draws data from predetermined views.

This spread sheet has 20 connections.

When I first start the spread sheet up it almost completely locks up. The database slows down horribly as well.

After about 5-10 minutes the spread sheet finally delivers an error message like the following:

The following data range failed to refresh:
datasets.mydomainname.com program 2017 product
Continue to refresh all?

If I click "yes" it finishes its data refresh and all is well.

If I look under data, connections, this connection does not seem to exist. I would love to find this connection and delete it. This spread sheet is supposed to be 2018 and all 2017 data is outdated.

I am using Excel 2013 and am connecting to SQL server 13.0.4451

Thoughts??

Jim Horn

>This spread sheet has 20 connections.
Couple of thoughts..

Brave soul you are expecting Excel to be able to handle 20 outside connections.

How big are these data sets? If we're talking SELECT * FROM {really big table} then your Excel file can swell to a massive size, which would certainly cause slowness.

If this activity is causing locking/blocking in your database, then I personally know DBA's that would threaten violence over a user performing this action, and would take immediate steps to cut off this access.

Sounds like you need to launch a process to automate this activity, and let a smart SQL Server / BI type architect a faster solution that does not involve Excel.

Good luck.

ASKER CERTIFIED SOLUTION

Jason Yousef

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial