January 26, 2012 at 3:19 pm
My company's accounting department works with excel files that are connected to database tables to pull data from on occasion. They have been using this for some time now, but recently we ran into an issue where a spreadsheet was left open for an extended period of time, which somehow locked up key tables, which prevented users from accessing the application tied to this database. What is an effective way to prevent this from happening?
One co-worker advised that I should create a procedure to copy the tables used queried within the spreadsheet and insert it into another virtually empty database, point the spreadsheet's query to this new DB, and schedule the procedure to run every 10 minutes.
But I'm not sure if this is the best way to approach this, as some of the tables are rather large. Any suggestions would be appreciated!
January 26, 2012 at 3:37 pm
For a responsible answer I would have to see your connection string(s).
However, as a rule of thumb, make sure that Mode=Read and MaintainConnection=False.
January 26, 2012 at 5:15 pm
Here's are the connection strings:
DRIVER=SQL Server;SERVER=CLER-SQL;UID=sqluser;;APP=Microsoft Data Access Components;WSID=VM-PAB;DATABASE=DMD
DSN=DTrac;UID=sqluser;;APP=Microsoft Office 2003;WSID=SHP;DATABASE=DMD
January 27, 2012 at 12:21 pm
elvinkhatri (1/26/2012)
Here's are the connection strings:DRIVER=SQL Server;SERVER=CLER-SQL;UID=sqluser;;APP=Microsoft Data Access Components;WSID=VM-PAB;DATABASE=DMD
DSN=DTrac;UID=sqluser;;APP=Microsoft Office 2003;WSID=SHP;DATABASE=DMD
Sorry, Vinkhatri, I am afreaid I am not able to help you with this one: I do not have Excel 2003 and I have not worked with it for over five years now.
January 27, 2012 at 12:48 pm
Gianluca Sartori (1/27/2012)
Why not use a snapshot?
If you're not on Enterprise, it's not an option.
But a warehouse type solution would be, regardless of SQL edition.
Snapshot's definitely a great idea if you're on Enterprise Edition.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply