Laptop Battery One of my recent projects was to implement an audit trail for an existing application running on SQL Server. I decided to use triggers to implement the audit trail and created an audit database containing the audit trail tables. (Something similar to what is described in Using triggers to track database action history )
9 Downloadable content January 2008 33 pages US $6.99 Purchase This Wrox Blox Errata Description Table of Contents Author Information fly in Windows PowerShell scripts. Topics include how to create Windows PowerShell scripts that use SQL Management Objects (SMO) to manage and program SQL Server. In addition, you will learn about areas of potential difficulty and see how to avoid them—for example, the use of '$' in instance name, quote nesting rules, connection pooling, and schemata.?/p>
Thinkpad The structure of the audit trail tables duplicated the structure of the application table except that:
- All constraints were removed
- All not null fields were made null able
- All identity fields were made into their respective data-type fields.
In cases where you don't have access to Visual Studio .NET 2005 but need to debug ASP.NET AJAX pages and associated scripts, the Microsoft Script Debugger can be used to view and debug scripts and step through code line by line. alone product that can run on a variety of Microsoft operating systems, including Windows NT 4, Windows 2000, Windows Server 2003, Windows XP, and Windows Vista. It can be downloaded from //www.microsoft. details.aspx FamilyID= dffdf19ccd99&DisplayLang=en (this URL is, of course, subject to change).
Microsoft For e.g. If the application table was structured like this:
CREATE TABLE [dbo].[ EMPLOYEE] ( [EMP_CODE] numeric](18, 0) IDENTITY (1, 1) NOT NULL, [EMP_NAME] [varchar] (50) NOT NULL , [DB_FLAG] [char] (1) NOT NULL , [CUSERID] [varchar] (10) NOT NULL , [CDATE] [datetime] NOT NULL , [MUSERID] [varchar] (10) NULL , [MDATE] [datetime] NULL ) ON [PRIMARY] GO
The VBScript standard has changed over time, and several new things have been introduced since this book first published in 1999. The current standard for VBScript is 5.6. The script debugger, script control, and script encoder have all changed and the Windows Script Component Wizard, regular expressions, and remote scripting have been introduced. Windows Script Host technology has also matured over time and gained in both effectiveness and popularity.
Laptop Computers The audit table (that resides in a different database) would be structured like this :
/*new table name prefixed with AUDIT_*/ CREATE TABLE [dbo].[ AUDIT_EMPLOYEE] ( /*new columns*/ [AUDIT_ID] int identity(1,1) not null, [AUDIT_MODIFIER] varchar(10) null, [AUDIT_HOSTNAME] varchar(255) null, [AUDIT_DATETIME] datetime null, [AUDIT_ACTION] varchar (10) null, /*end of new columns*/ /*All the other fields identical to the main table */ /*identity property has been stripped */ /*all not null properties have been converted to NULL*/ [EMP_CODE] numeric](18, 0) NULL , [EMP_NAME] [varchar] (50) NULL , [DB_FLAG] [char] (1) NULL ,, [CUSERID] [varchar] (10) NULL , [CDATE] [datetime] NULL , [MUSERID] [varchar] (10) NULL , [MDATE] [datetime] NULL ) ON [PRIMARY] GO
Q) Do you support international domains A) We are able to host most international domains. Q) Can I get a dial up account with you A) Yes. Please for rmation on our dialup service. Q) SQL support A) Yes, SQL Server 2000 database support. Q) Do you offer ASP.NET scripting support Q) Can I send bulk, yet target email using your servers
Laptop Computer As you can see the audit table duplicates the fields of the application table and has a few more additional fields. The audit trail had to cover around 60 tables and time was at a premium. I started by using the "Generate SQL Script" option in SQL Enterprise Manager on the specific tables and manually modidfying each generated script into the audit table script. Doing this for a lot of tables can turn out to be very tedious & time consuming! But, could there be an easier way? Enter SQL-DMO
..and many more other useful features. SQL server 2000 and SQL server 2005 are the best and the most complex database management tools on the market, and they can do much more than just managing and administrating databases, so they are the best choice if your company needs database administration and maintenance. A good source for rmation on how to install, customize, and make the most use of a SQL Server implementation, visit the Microsoft practice section of www.unitekconsulting.com.
Desktop Computer The SQL Distributed Management Objects (SQL-DMO) are a collection of COM interfaces that allow the management of SQL Server. A good introduction to SQL-DMO is this article by Merkin ( An Introduction to SQL-DMO ). SQL-DMO also allows scripting of individual database objects via a Script() method. Enter Windows Scripting
Notebooks Windows Scripting is meant to be a replacement for the somewhat limited windows batch file language. Windows 2000 installations ship with the scripting engine and command-line interpreter for running windows scripts the default scripting installation supports scripts written in VBScript and Jscript (a variant of JavaScript). For details on using windows scripting see: msdn.microsoft.com/scripting/ and www.win32scripting.com . The scripts used in this article make use of JScript. The Modus Operandi
Lenovo Lets see how we use Windows Scripting to generate the audit table scripts:
Hard Drive The audit tables have some extra fields that are used for the audit, these are in addition to the fields they inherit from the application tables. The extra fields are identical across all the tables. Create a text file called audit_new_field.txt with the following code in it:
[AUDIT_ID] int identity(1,1) not null, [AUDIT_MODIFIER] varchar(10) null, [AUDIT_HOSTNAME] varchar(255)null, [AUDIT_DATETIME] datetime null, [AUDIT_ACTION] varchar (10) null,
Travelstar As you can see it contains just the TSQL definitions for the new fields that appear in all the audit tables.
Gateway Finally: Save this file in a sub-directory called settings. The windows script appends these fields into every audit table script that it generates. The windows script picks up the field definitions from the audit_new_field.txt file. Now we are all set to dive into the code generator script. I pass in the connection information as parameters to the table builder script:
C:\>cscript codegen.wsf //job:tablebuild server-name db_name username password
Laptop Parts (cscript.exe is the command line interpreter for windows scripting. codegen.wsf is the windows script file. tablebuild is the task name that I want to execute within the script file. Type cscript/? to see detailed cscript usage.) The Table Builder script
Software The table builder script connects to a SQL Server, using the SQLDMO.Server objects Connect() function. The script makes use of the connection information passed as parameters from the command line to make the connection. The Connect() function returns a SQLServer object.
sqServer = new ActiveXObject("SQLDMO.SQLServer"); sqServer.Connect(server0, user3, pass4); //pass in server name, user name & password. (I use SQL authentication)
Hard Drives The SQLServer object has a Databases collection; we get an object handle to the desired database from this collection.
sqDb = new ActiveXObject("SQLDMO.Database"); sqDb = sqServer.Databases(db1); Iterating through the tables
Electronics Now we enumerate through the Tables collection (found within a Database object) and Script() only the User created tables, by checking the Table objects SystemObject property.
tableColl = new Enumerator(sqDb.Tables); for (; !tableColl.atEnd(); tableColl.moveNext()) { var strTableScript; sqTable = tableColl.item(); if (!sqTable.SystemObject) { --table.script() code after this-- Scripting the tables
Canon The Script method of the Table object takes the following syntax:
TableObj.Script( [ ScriptType ] [, ScriptFilePath ] [, NewName ] [, Script2Type ] )
- ScriptType this field can be used to override the default behavior of the Script() function
- ScriptFilePath the script can be output to a file on disk, specified in this parameter.
- NewName This parameter specifies a new name for the referenced table object.
- Script2Type this parameter is used to specify SQL Server 2000 specific parameters.
Desktop Pc The script calls the Script() function in the following manner :
var lngParams = iSQLDMOScript_Drops | iSQLDMOScript_IncludeIfNotExists| iSQLDMOScript_IncludeHeaders | iSQLDMOScript_Default | iSQLDMOScript_NoIdentity|iSQLDMOScript_NoDRI; sqTable.Script(lngParams , "", "AUDIT_"+sqTable.Name, iSQLDMOScript2_NoCollation);
Desktop Computers Lets examine the parameters one by one:
Think Pad Param #1 : The lngParams parameters customizes the generated script, by combining various attribute constants :
- iSQLDMOScript_Drops: I want the script to have a if exists() drop table before every create table.
- iSQLDMOScript_IncludeIfNotExists : I want to include an existence check on the object.
- iSQLDMOScript_IncludeHeaders : I want to include header comments containing name of object & time of creation.
- iSQLDMOScript_Default : this parameter is required to generate the create table script
- iSQLDMOScript_NoIdentity : I want to disable generation of definitions for the identity property, seed or increment .
- iSQLDMOScript_NoDRI : I want to disable generation of TSQL script statements to create DRI constraints.
Repair Param #2: I leave the 2nd parameter blank because I dont want to output the generated script to a file immediately.
Data Recovery Param #3: I generate a new name for the audit table; I simply append AUDIT_ before the existing application table name. For instance: if the application table name were Employee, the audit table name would be AUDIT_Employee.
Cisco Param #4: I pass iSQLDMOScript2_NoCollation as the last parameter. This option disables scripting of the collation clause in SQL 2000 scripts.
Keyboard The Script() method returns the generated script in a string variable. At this stage though, the script is not yet ready for use; the generated script could still have fields with not null criteria (remember that in our audit table all fields are null able). A javascript Search & replace function is applied on the string to convert all instances of not null, to null.
strTableScript = replace(strTableScript,"NOT NULL", "NULL");
Monitor We still need to append the TSQL statements for the extra fields listed in the audit_new_field.txt (described in the Modus Operandi section) to the generated script. How do we do this? We can do this quite easily if we append the script right at the beginning of the table generation script. I can find the starting position for the new fields by doing a search for the endpoint of the string:
Desktop CREATE TABLE [AUDIT_table_name] (
Infosys And insert the text from the audit_new_field.txt file at that point. (Read the inline comments in the code shown below)
//continuing from earlier code. // dynamically build the string to search for strLineToSearchFor = "CREATE TABLE [AUDIT_"+sqTable.Name+"] ("; var i; // search for insertion point within the strTableScript variable // that contains the generated script i = strTableScript.indexOf(strLineToSearchFor); if (i != -1) //insertion point found { var tmp=''; var tsOut; tmp = strTableScript.substring(0,i+strLineToSearchFor.length); tmp = tmp+"\n"+strNewFields; //add the new fields at the correct offset tmp=tmp+strTableScript.substring(i+strLineToSearchFor.length+1, strTableScript.length) tsOut = fso.CreateTextFile("scripts\\"+sqTable.Name+".txt", true); // output the transformed generated script to // a sub-directory called scripts tsOut.Write(tmp); tsOut.Close(); } else WScript.Echo(strLineToSearchFor+" : NOT FOUND"); } } sqServer.Close(); //close the connection The Benefits of using this method
- This method can be used to generate customized scripts for a large number of tables in a short time.
- Transformation of scripts can be automated to an extent, thus reducing possibility of human error.
- Adding new fields to the audit tables is very easy now: add the new field definition to the audit_new_field.txt file and re-rerun the generator script.
- The use of a parameter driven script makes it very portable across different SQL-Server installations.
Download the Source Code
Refurbished Laptops The windows script file (WSF file) mentioned in this article can be downloaded from here . You will need the SQL-DMO libraries installed on your PC for the code to work.
[ Comment, Edit or Article Submission ]
