March 12, 2019 at 8:30 am
I have an agent job which runs everyday and it often fails because the excel file (source) is open. I have a user who leaves the file open when he/she leaves. Now I am thinking about adding another step into my ssis package which will save the file, close the file so the job doesn't fail. Does anyone have a script I can use or someone can help me with a script? I don't even know where to begin from.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 12, 2019 at 12:20 pm
I don't know where to start with trying to close another user's Excel app. Even if you tried some dark sysadmin magic to close the process remotely, it's probably going to be stuck at a dialog asking whether she wants to save it.
Can you put the Excel file in a folder where the other user does not have write permissions, so they have to open in in shared read-only mode?
Can you add some kind of watchdog timer function to the spreadsheet so it will auto-close after a period of inactivity?
The only other alternative I can think of is to have increasingly severe consequences for somebody leaving critical resources locked up when they leave for the day, until the behavior is changed.
March 12, 2019 at 12:24 pm
There should be some way to copy the file even with the "busy" flag on. Then open the copy, not the original.
That is, the logic becomes:
check for "busy" flag on
if on,
copy file, open file copy
if off
open original file as planned
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 12, 2019 at 12:28 pm
Hmm.... don't give them access directly the exported file. Either email the results to them or copy the file over after creating it with a date stamp.
March 12, 2019 at 12:30 pm
Scott Coleman - Tuesday, March 12, 2019 12:20 PMI don't know where to start with trying to close another user's Excel app. Even if you tried some dark sysadmin magic to close the process remotely, it's probably going to be stuck at a dialog asking whether she wants to save it.
Hmm... you might be able to, if you had full access to their system to run a process that would kill all the excels they have open.
March 12, 2019 at 1:58 pm
i would lean more towards copying the file as a new name, with name featuring the timestamp to the millisecond, and manipulate that.
also, i would use an isolated copy, and not one that was accessible to other uses or even DBA's, if this is going to be processed with SSIS.
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply