A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Thursday, October 8, 2009

Defaults

I need some cheesy title for this post

At any rate, we have some semi-standard fields on our tables that correspond to who and when was a record last updated/inserted.  One of the challenges I have is these are self-reporting fields.  We have to play by SOX rules so there's not as much opportunity to do things that won't be noticed but there is opportunity for people to be lazy and or ignorant of what should be populated.  To address this, I suggested we use some defaults in our database.  I know, a trigger would ensure it's done right but there is this misguided perception that trigger=MASSIVE PERFORMANCE HIT.  I'd wrap that sucker in a blink tag if I thought browsers still supported it.  So baby steps.

Given this table let's examine how we can create defaults to populate when, who and what is acting on a table.


CREATE TABLE dbo.DEFAULTS
(
col1 varchar(50) NOT NULL
, last_user sysname NOT NULL
, last_mod_date datetime NOT NULL
, program_name sysname NOT NULL
)
GO


The easy ones are grabbing the user name associated with the connection account and the date when the insert is happening.


ALTER TABLE
dbo.DEFAULTS
ADD CONSTRAINT
DF_DEFAULTS_last_user DEFAULT system_user FOR last_user

ALTER TABLE
dbo.DEFAULTS
ADD CONSTRAINT
DF_DEFAULTS_last_mod_date DEFAULT getdate() FOR last_mod_date
GO

The interesting one is what is the name of the program that is talking to SQL Server.  sp_who2 shows that info, but how do you actually get it out?  sys.dm_exec_sessions is part of the answer.  Great DMV by the way, lots of good stuff in there about the current sessions.  For me, that program_name field was what I wanted.  All I needed to know was what my connection's session id was and I could filter the DMV down to one row and acquire the correct program name.  I am embarrassed to say, I finally had to call our DBA and ask how I determine my session id.  Google results had things like people calling the non-existant get_sid() method.  The answer, elementary dear Watson.  @@spid

ALTER TABLE
dbo.DEFAULTS
ADD CONSTRAINT
DF_DEFAULTS_program_name DEFAULT (SELECT top 1 E.program_name from sys.dm_exec_sessions E WHERE E.session_id = @@SPID) FOR program_name
GO

However, one cannot use a query in a constraint.  SQL Server will balk and say
Msg 1046, Level 15, State 1, Line 5
Subqueries are not allowed in this context. Only scalar expressions are allowed.

That's easy enough to rectify, wrap it into a scalar function and assign the function to the default.

CREATE FUNCTION
    dbo.ProgramNameGet()
RETURNS
    sysname
AS
BEGIN
    DECLARE @program_name sysname
    SELECT
        @program_name = E.program_name
    FROM
        sys.dm_exec_sessions E
    WHERE
        E.session_id = @@spid
    RETURN @program_name
END
GO
ALTER TABLE
dbo.DEFAULTS
ADD CONSTRAINT
DF_DEFAULTS_program_name DEFAULT dbo.ProgramNameGet() FOR program_name
GO

[edit]Or, I could just notice that program_name turned pink when I was typing it and have simply used the following but then I wouldn't have known about the DMV so not all is wasted
ALTER TABLE
dbo.DEFAULTS
ADD CONSTRAINT
DF_DEFAULTS_program_name DEFAULT dbo.ProgramNameGet() FOR program_name
GO
[/edit]

I then set about inserting rows into my table in as many different ways as I could think of.  Attached are the things I knocked out while I was listening to people debate the merits of handling orphan accounts from advisors using one trading platform vs another.


Method
program_name
ADONET SSIS
.Net SqlClient Data Provider
osql
OSQL-32
dataflow SSIS
Microsoft SQL Server
ODBC mdac driver from VS
Microsoft (R) Visual Studio (R) 2005
ODBC mdac driver from console app
SimpleDatabaseWriter
ODBC native SQL driver from VS
Microsoft (R) Visual Studio (R) 2005
ODBC native SQL driver from console
SimpleDatabaseWriter
OLEDB driver from VS
Microsoft (R) Visual Studio (R) 2005
OLEDB driver from console
SimpleDatabaseWriter
SSMS
Microsoft SQL Server Management Studio - Query
sqlcmd
SQLCMD
OLEDB SSIS
Microsoft SQL Server
WebPage OLEDB
Microsoft (R) Visual Studio (R) 2005
WebPage OLEDB
Microsoft® .NET Framework

I know isql and things like BCP are still outstanding as means of getting data into a database but otherwise, I'm getting low on ideas.  Perhaps it's just the lateness of the hour.

No comments: