network username in Sysprocess tablee?

[jsteph]

Hi all,
Here is my issue:
In sql-2005, I have an audit table, to which I write when certain changes to certain fields are made. This happens in a trigger. The key piece of info for the audit table is the hostname which I get from Sysprocesses. The problem is that it is NOT the actual computername of the computer doing the change. It appears to be 'cached', which to me is useless for this purpose.

In the front-end I'm completely deleting and closing connections in this app and re-initiating them but that does not seem to work.

The front-end is an Access 2003 application. When the user opens the app, all linked-tables are *deleted* (the access odbc-link is deleted) and a brand new access odbc link is made. There is also an adodb connection that this app uses. The connection is closed, set to nothting, and reopened.

So I was working on this app at home, and sysprocesses had my home machine as hostname, which was correct last night. I copied from home to my work machine, (my home machine is not even on our domain, but i'm vpn'd in) and restarted the app--which does all the closing, re-linking,etc. So this morning on my office machine, my home machine shows up in the audit table when I change fields to the table with the trigger. This is really troubling.

So my question is what do I have to do (prefferably within the Access app--I have an 'initialize' function) to make sure that the spid for either the ado connection or linked tables is giving me accurate information for these audit tables?
Thanks,
--Jim

 

[Mich]

What is the code for your trigger?

-If it ain't broke, break it and make it better.

 

[simian101]

You could alway insert ip and hostname into a table in SQL Server when you connect thru Access.

Simi

 

[jsteph]

Here is the trigger:

ALTER  TRIGGER [trLogDrvSTATChange_CER] ON [dbo].[tblDriverInfo]
FOR  UPDATE --Log changes to status field
AS
declare @SQLusr varchar(50)
declare @Netuser varchar(50) 
declare @mach varchar(50)
declare @app varchar(50)
declare @mac varchar(20)
declare @tmp varchar(255)
IF UPDATE(DR_STAT) 	BEGIN
	--Get some audit info--login and machine
	SELECT  @mach =rtrim( isnull(hostname,'')),@app = rtrim(isnull(program_name,'')),@SQLusr = rtrim(isnull(loginame,'')),@mac = rtrim(isnull(net_address,'')),@netuser = rtrim(isnull(nt_username,''))
	FROM master.dbo.sysprocesses WHERE spid = @@spid;

	INSERT INTO tblAudit (ClockNum,audTable,audField,audSQLlogin,audnetUser,audmach,audmsg,audapp,audmacaddr)
	SELECT rtrim(isnull(i.ClockNum,'')),'tblDriverInfo','DR_STAT',@SQLusr,@netuser,@mach,
	rtrim(@mach + ' changed DR_STAT for ' + rtrim(isnull( i.dr_name,'noname')) + ' ID# ' + isnull(i.ClockNum,'') + ' from ' + rtrim(isnull(d.Dr_STAT,'<Blank>')) + ' to ' + rtrim(isnull(i.Dr_STAT,'<Blank>')))
	,@APP,@mac
	FROM inserted i INNER JOIN deleted d ON d.ClockNum = i.ClockNum 
	WHERE isnull(i.dr_stat,'') <> isnull(d.Dr_Stat,'');
END

It's the NT_Username that doesn't seem to be changing when I open the Access app and do an update on this table after I've moved the app from my home machine to the office machine.

Program_name is set when I do the connection string for passthru queries--on startup of the app I loop over every passthru query and set the connection string including the application name (in my shorthand) and the user machine, so that's somewhat redundant but it's to help knowing when/if I need to kill a connection so they don't all say "Microsoft Access".

Thanks for any help,
--Jim