January 23, 2002 at 9:00 am
Hi All,
Can u help me on this...
I have an application accessing SQL Server.
When the application is terminated (basically abnormal termination), I want to do certain operations in the database.
How will I come to know in the backend that a connection has been terminated to a particular machine on a network?
Sachin
Regards,
Sachin Dedhia
January 23, 2002 at 10:31 am
You could do a server side trace and log connect/disconnect events to a table, put a trigger on the table (or a job) to do your cleanup. Might do almost as well by just having the app log users in/out, then you'd just search for logins with no exit with a given time period.
Curious - what actions will be you performing when you detect this?
Andy
January 23, 2002 at 11:13 am
the other alterative is to write something that stores the connection info and then compares that with the current info and takes action. Then run this every minute or so. Not as clean as Andy's solution.
Steve Jones
January 24, 2002 at 3:56 am
Hi Andy, Steve
Thank you for the solution. But I have just started with SQL Server. So I find it difficult to figure out how do 'server side trace'.
Well, while starting my application i make an entry in a table with the Number assigned to user and the machine from where the application is executed.
And while exiting the application, delete the record.
But in case of abnormal termination the record does is not deleted.
Basically I want to keep track of the users.
Your solution is what I have been looking for but as I said I have just started with SQL Server...
On what action in SQL Server will I come to know that I am connecting or disconnecting?
Thanking you once again.
Sachin
Regards,
Sachin Dedhia
January 24, 2002 at 5:01 am
To do a server trace the easiest (its not hard, but will take some experimenting) way is to set up a trace in SQL Profiler, then save it as a script that can be run on the server. The catch to this is that it won't log directly to a table when run on the server, you have to import after the trace is done or "rolls over".
Before you spend a lot of time on this I'd take a look at what you really need to meet your requirements. If you're tracking connect time...say for billing, trace is the only viable option. If you just want to come close, have the app do the logging via sp and just live with the occasional unrecorded disconnect.
Andy
January 24, 2002 at 9:46 am
In the event of an abnormal termination, there isn't any "event" necessarily.
What you could do is write a sproc to compare the results of sp_who with your table and look for items in the table that are not in sp_who. These would likely be abnormal terminations.
Steve Jones
February 16, 2002 at 4:08 am
Hi Andy, Steve
I was hooked to another task, so had to switch back to Access...
Just got some time n did some R&D on Profiling & Tracing...
"To do a server trace the easiest (its not hard, but will take some experimenting) way is to set up a trace in SQL Profiler, then save it as a script that can be run on the server. The catch to this is that it won't log directly to a table when run on the server, you have to >import after the trace is done or "rolls over"."
There is an option when creating a trace for saving to a table?
Well, let me first tell u what is that I am doing. I have an Employee database. I want to allow each employee to login using their name or code and have access to their respective details...
Is is feasible to create a User for each employee? No. of Employees can go in thousands!
If the SQL Server is Shut down and then started again, how to enable the trace? Also how to make sure that the trace is not modified or deleted by anyone?
One more query I have is that is there something like Timer in SQL Server, on which we can call procedures?
Regards,
Sachin
Regards,
Sachin Dedhia
February 16, 2002 at 5:13 am
Yes, in profiler there is an option to save to a table. Very handy.
You can modify a stored procedure to auto start to enable your trace. Or have a job that runs every x minutes to see if the trace is running and if not, start it. The SQL Agent is a robust scheduler that will let you do most of what you could do with a timer in VB.
Im not sure that it's NOT feasable to have a userid for each user. Kind of the nature of a network. For your purposes though I'd look at putting all the valid NT users in a group, then adding that group as a login.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply