Re: System Functions seem intermittant at best

#1

Hi

Having the PK just on datetime may cause problems with the granularity. If
this table is populated by triggers you may want to check that it is working
as you expect. As the machine column is nullable the default may not come
into play unless you specify DEFAULT in the INSERT statement i.e. you may be
passing NULL as a legitimate value,

John

"Andy" <andymdsmith (AT) hotmail (DOT) com> wrote

Quote:

I have an audit table which includes some columns with defaults based
on system functions - but these do not always work.. table
definition..

CREATE TABLE dbo.Audithistory (
event_time datetime NOT NULL DEFAULT getdate(),
event_type int NOT NULL,
dbname varchar(10) NOT NULL DEFAULT db_name(),
severity integer NOT NULL,
event_details varchar(500) NOT NULL,
machine varchar(15) NULL DEFAULT host_name(),
[systemuser] varchar(15) NULL DEFAULT system_user,
CONSTRAINT PK_Audithistory PRIMARY KEY CLUSTERED
(
event_time
)
)
GO

column machine [based on host_name()] is generally null. I was also
previously having problems with the user and suser_name() functions (i
was only expecting one to fire not both), but I'll see what happens
with system_user

If you have any ideas why this is happenning, I'm all ears..

Many thanks in advance,
Andy

#2

Andy (andymdsmith (AT) hotmail (DOT) com) writes:

Quote:

I have an audit table which includes some columns with defaults based
on system functions - but these do not always work.. table
definition..

This question was also posted to microsoft.public.sqlserver.programming,
but in Andy's defence it should be said that he made that post almost
24 hours later. I chanced to see it and answered it there.

Recap for the readers of this forum:

Quote:

column machine [based on host_name()] is generally null.

host_name() relies on what the client passes, so if the client passes
nothing, you will see NULL. And if the client passes XYZ despites its
name being ABC, you will see XYZ.

Quote:

I was also previously having problems with the user and suser_name()

suser_name() is an obsolete function and always returns NULL.

John's remark about the primary key is very relevant.

--
Erland Sommarskog, SQL Server MVP, sommar (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: System Functions seem intermittant at best

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: System Functions seem intermittant at best - 07-30-2003 , 03:38 AM

Re: System Functions seem intermittant at best - 07-30-2003 , 03:43 PM