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:
Post a Comment