August 10, 2015 at 7:03 am
Hello all!
I've created an excel spreadsheet with a data connection. This data connection uses a query that runs against a read-only database.
The issue I'm having is that the query never seems to finish running against the database, whether I open the Excel spreadsheet to view the data or run the query in SSMS.
I created the connection on the Data ribbon by going to From Other Sources --> From SQL Server and using the Data Connection Wizard.
Is there some kind of setting or property I'm missing that would allow this query to finish running?
If this is not the right place for this issue, please let me know and I will try to get the topic removed or shut down.
Thank you for your time.
August 10, 2015 at 7:10 am
What are the Connection Properties set to: ??
[ ] Enable background refresh
[ ] Refresh every XX minutes
[ ] Refresh data when opening the file
How long does the query run natively in SQL Server ?
August 10, 2015 at 7:16 am
Sorry, should have mentioned that.
[x] Enable background refresh
[ ] Refresh every XX minutes
[x] Refresh data when opening the file
These properties with the 'x' are enabled.
Before we took off the read-only property on the database, the query would be running for 35+ minutes and not finishing.
Thanks!
August 10, 2015 at 7:30 am
What exactly the query is doing? I am assuming, this is some type of select query since the database is in read-only mode.
Anyway, plz provide more details.
Regards,
SQLisAwe5oMe.
August 10, 2015 at 7:40 am
The query is getting a start date of 1/1/current year and an end date of the current date.
After this, it aliases two views using the 'WITH' statement( With 'alias' as) and then does the select statement to get the data from the view. This happens on both views.
Then, it gets the data it needs from both views, inner joining them together using their aliases and filtering on the start date and end date using a 'WHERE' clause and sorts the data being returned.
August 10, 2015 at 8:37 am
Try unchecking those options and do a manual refresh.
However, based on your description, it sounds like this query is not a pass-through and therefore not well optimized.
You should create a stored procedure and call that instead of having all of that SQL embedded in the worksheet.
August 10, 2015 at 8:54 am
Thank you, mar.ko.
I will give this a try.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply