Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sschrupp
Starting Member
7 Posts |
 Posted - 2010-12-03 : 09:27:19
|
| I tried searching around for this without luck. If my search skills are a failure and someone knows a good spot to look just let me know, please.Here's my issue. Our IT people won't let us use Access as a front end to the SQL Server that they've given us space on. So since they didn't specify Excel as a no-no I've been using Excel instead of Access. Which makes me laugh by the way since it's basically doing the same thing. Shhh, don't tell anybody. Anyhow...I'm trying to find a way to let the user know when various queries have finished running so that they can then go on to get some reporting data from the results.One of the processes has SQL Server importing a few million records from flat files and doing all sorts of data modification. This process generally takes somewhere between 15-45 minutes it seems depending on various things.What I thought I could try is to have a table on the server to track the status of different processes. So at the start of the "Account Walk" process I could have it write to the status table the start time for that process and that it's currently "Processing".Then when the process is finished I could have in the DTS package a query to mark the end time and that the process is "Finished".Meanwhile, in my Excel front-end I was thinking I could have a query looping on a DoEvents wait or something that checks the Status table maybe every minute until it sees that the Account Walk process is marked "Finished".I'm assuming that the DTS package won't run the final "Finished" query until the rest of the steps in the package have completed.This seems pretty newbie-ish to me but I don't know of another way of doing this, as I AM a newbie to SQL Server. I should also mention that we currently are not allowed to use xp_cmdshell which means I can use Excel to interact with the SQL Server, but SQL Server can not interact to anything outside besides me making DTS packages. So no openrowset type commands are available.Which is why I was thinking I'd have to go this route of trying to figure out when queries were finished.So is there a more sophisticated approach I can take for Excel to know when queries are finished? Is there something about marking a Status table field "Finished" that could backfire in any way?Thanks! |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-03 : 09:39:10
|
| That status table will be invaluable for monitoring performance - by that I mean whether the system is fit for purpose. I would log the start time and the end time + number of rows affected.You might want to update the last completion date in another smaller table and use that for the check.This should be built into a generic monitoring system rather than a bespoke implementation.Polling a table is a reasonable way to detect completion - it doesn't sound like you would have access to anything on the server that would alert.Think your main issue is the relationship with the IT people. Sounds like you're not working together which is going to cause trouble.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sschrupp
Starting Member
7 Posts |
Posted - 2010-12-03 : 09:52:18
|
| Ok great, glad I wasn't totally off on my thinking!And yes, we're in a very large corporation so IT is pretty strict. It wouldn't be quite as bad if my department was considered "IT" instead of a business related department. Our department is sort of an experimental hybrid Business/IT department that is unique in the company so it's quite the fight to get the tools we need and due to politics we kind of walk a fine line of doing fabulous things while trying not to attract too many worried glances from the real IT department locally. Good times! :) |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-03 : 09:59:37
|
| WHich version of sql server are you using?And I would be concerned about a process taking 15-45 mins. Sounds like something that is going to grow and cause issues.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Know when query finished in Excel?