Wednesday, March 24, 2010

Using the Inserted and Deleted tables outside of a trigger

Just a simple little post about a conversation about triggers I had earlier today with a developer, specifically using them to audit update and delete statements against a table. During our talk I mentioned that, starting in SQL 2005, the inserted and deleted tables were available outside of a trigger. He wasn’t aware of that until I mentioned it. So, for anyone else who’s not familiar with the inserted and deleted tables, here’s a little summary.

Briefly, when you insert, update, or delete records from a table, SQL stores those records in a virtual table; inserted for inserts and deleted for updates and deletes. These tables are available inside of table triggers. Microsoft SQL Books Online has an article explaining the use of the inserted and deleted tables inside of triggers; read that first as a refresher. In our shop we use them for monitoring who is updating and deleting certain critical tables, and we can roll back changes if necessary. 

Starting with SQL 2005, you can use the OUTPUT clause to access the inserted and deleted tables outside of DML triggers. You can read the article in Books On Line.  

Here’s a simple demo script to run. I added a primary key and a non nullable column to show what happens in error conditions. 

USE tempdb;
GO

SET NOCOUNT ON;
GO

-- Create a testing table
USE [tempdb]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestOutput]') AND type in (N'U'))
DROP TABLE [dbo].[TestOutput]
GO

USE [tempdb]
GO

CREATE TABLE [dbo].[TestOutput](
[Field1] [int] NOT NULL,
[Field2] [char](1) NOT NULL,
CONSTRAINT [PK_TestOutput] PRIMARY KEY CLUSTERED
(
[Field1] ASC
)ON [PRIMARY]
) ON [PRIMARY]

GO

--Testing of Outupt statement
DECLARE @TestOutput_Audit TABLE
(
Field1 INT,
Field2 CHAR(1),
[Action] CHAR(1),
Action_by VARCHAR(25),
Action_Date DATETIME
)

-- Testing Insert
INSERT INTO TestOutput (Field1, Field2)
OUTPUT inserted.Field1, inserted.Field2, 'I', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
VALUES (1, 'A')

INSERT INTO TestOutput (Field1, Field2)
OUTPUT inserted.Field1, inserted.Field2, 'I', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
VALUES (1, 'A')

SELECT * FROM TestOutput
SELECT * FROM @TestOutput_Audit

-- Testing Update
UPDATE TestOutput
SET Field2 = 'B'
OUTPUT deleted.Field1, deleted.Field2, 'U', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
WHERE Field1 = 1

UPDATE TestOutput
SET Field2 = NULL
OUTPUT deleted.Field1, deleted.Field2, 'U', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
WHERE Field1 = 1

SELECT * FROM TestOutput
SELECT * FROM @TestOutput_Audit

-- Testing Delete
DELETE TestOutput
OUTPUT deleted.Field1, deleted.Field2, 'D', SUSER_NAME(), GETDATE()
INTO @TestOutput_Audit (Field1, Field2, [Action],Action_by, Action_Date)
WHERE Field1 = 1

SELECT * FROM TestOutput
SELECT * FROM @TestOutput_Audit



The INSERT statement simply inserts a single record into my table. Afterwards you can see the same record in the table as well as my audit table. The second record wasn’t added to either table because it’s a primary key violation.


Msg 2627, Level 14, State 1, Line 18

Violation of PRIMARY KEY constraint 'PK_TestOutput'. Cannot insert duplicate key in object 'dbo.TestOutput'.


The statement has been terminated.



Field1      Field2

----------- ------


1           A



Field1      Field2 Action Action_by                 Action_Date

----------- ------ ------ ------------------------- -----------------------


1           A      I      PERTELLT3\John            2010-03-24 17:58:03.407





I make a simple UPDATE. Now the table shows the update and the audit shows the value before the update took place. The second update fails because Field2 doesn’t allow NULL values; the main record isn’t updated nor added to the audit table.



Msg 515, Level 16, State 2, Line 33

Cannot insert the value NULL into column 'Field2', table 'tempdb.dbo.TestOutput'; column does not allow nulls. UPDATE fails.


The statement has been terminated.



Field1      Field2

----------- ------


1           B



Field1      Field2 Action Action_by                 Action_Date

----------- ------ ------ ------------------------- -----------------------


1           A      I      PERTELLT3\John            2010-03-24 17:58:03.407


1           A      U      PERTELLT3\John            2010-03-24 17:58:03.413





Finally, when I delete the record I can see the record is gone from the main table but stored in the audit table.



Field1      Field2

----------- ------



Field1      Field2 Action Action_by                 Action_Date

----------- ------ ------ ------------------------- -----------------------


1           A      I      PERTELLT3\John            2010-03-24 17:58:03.407


1           A      U      PERTELLT3\John            2010-03-24 17:58:03.413


1           B      D      PERTELLT3\John            2010-03-24 17:58:03.413

Monday, March 22, 2010

Taking 70-448 SQL BI Exam

One of my goals has been to learn Microsoft’s SQL Business Intelligence stack. I’ve been reading different books, blogs and whitepapers to learn as much as I can, but I wasn’t very structured in my approach. While I picked up lots of new information and tips, I felt I was spinning my wheels. And I keep getting sidetracked with work projects and learning the basics of other new technologies.

I’ve been successful in the past with Microsoft certifications. Beginning way back in the SQL 7 days, I took and passed exams to achieve MCP, MCDBA, and MCSD status. I also took and passed SQL 2005 Database Administrator and Database Developer exams a few years back.

I’ve been pretty involved with Reporting Services since it first came out as an add on to SQL 2000. But I’ve only played around briefly with the old DTS and newer Integration Services. And I’ve never even opened an Analysis Services database. So overall I have a long way to go.

So I’ve settled on a method that I believe will help me. I’ve decided to take the 70-448 exam, Microsoft SQL Server 2008 – Business Intelligence Development and Maintenance. I feel by studying for this specific exam I’ll get a head start on learning BI. I know I’m not going to learn everything there is about BI with one exam, but it should give me enough background to go ahead and finish up on my own. And setting a goal of passing 70-448 will, hopefully, keep me on track.

Now when I took other exams I also struggled to keep to my studies. It was only when I actually scheduled the exam that I really sat down and concentrated. I’m not going to do that just yet. For the SQL 2005 exams I passed I knew enough that I could get by with using only the Microsoft Press Training Kits. I don’t feel anywhere near that comfortable with 70-448. But I’m still going to set a date. Today is Monday, March 22, 2010. I’m going to give myself just over 6 months to take and pass this exam. I’ll schedule the test on August 22nd, and I will pass the test by October 1st. That will also give me a little leeway if I fail the first time.

I plan on blogging on my progress for 3 reasons. First, if I write about it, it will help me understand the concept better. As someone once said, you don’t understand a concept until you can explain it to your mother. Second, it should keep me on schedule. I don’t want to get to October 1st and admit publically that I didn’t take the test. And third, it will act as a study group for others who also want to take 70-448. The more the merrier.

Resources

I’ve only got a few resources so far. I’ll add to this list as I go.

MCTS Self-Paced Training Kit (Exam 70-448): Microsoft SQL Server 2008-Business Intelligence Development and Maintenance: MCTS Exam 70-448 (Self-Paced Training Kits)

The first is the Microsoft Press 70-448 Training Kit, written by Eric Veerman, Teo Lachev, and Dejan Sarka of Solid Quality Mentors. I’ve always liked the MS Press TK’s. They come with practice exams, as well as a coupon for 15% your test.

Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services (Wrox Programmer to Programmer)

The second book I’ll be using is Knight’s 24-Hour Trainer, published by Wrox and written by Brian Knight, Devin Knight, and Mike Davis. I chose this book because it includes a CD that contains videos of the lessons.

There will be more resources listed later, as I progress towards my personal D-Day.

Tuesday, March 16, 2010

PowerShell and SQL

When PowerShell was first released a few years back I wasn’t too excited. I didn’t use scripting very much, mainly for cleaning out old backup files from our archived storage. I thought that using VBScript would suffice for my limited means. But I started reading up on PowerShell V1, even tinkering around with it a bit. However learning PowerShell soon dropped down on my “Must Learn” technologies. There was just too many other things being introduced.

My mind didn’t change much when PowerShell V2 was in beta. Then Microsoft told us how PowerShell was going to be integrated into SQL Server 2008 (among other products) and PowerShell began moving back up my list. I’d read articled on MSDN and blogs by SQL experts, but my thinking never really changed. I figured that PowerShell was just one more way of doing things I already did in other ways.

One of the items on my “To Do” list has always been to document the servers I’m responsible for. I toyed with different methods of gathering the information, from running SSIS packages to a central repository to executing T-SQL queries in a Reporting Service report to manually (and tediously) typing the data into Word docs.

Then I read an article by Edwin Sarmiento on MSSQL Tips. In his article, he gave an example of using SMO inside PowerShell to write the information to an Excel spreadsheet. Finally the light bulb went on; I could use PowerShell to create my Run Book!

Now I’m not going to go into an in-depth explanation on PowerShell, or SMO. There’s ton’s of information available, and they explain much clearer than I can. I’m still learning myself. Check out Allen White (blog), Buck Woody (blog | twitter), Aaron Nelson (blog | twitter) and many others that I’m not mentioning here. I just wanted to share the method I’m beginning to use. It’s still evolving as I learn new tricks.

The basis of my script was the one used by Edwin Sarmiento in this article. I’ve added a few things of my own to make it more like what I want. For instance I add server information as well as database information. And I put each server onto a different worksheet. 

The first thing my script does is to load the SMO namespace. After that I use the Get-Credential cmdlet to get an account to connect to the servers (I won’t always be connecting with Windows Authentication), create an Excel object and get a list of servers from a text file. After that I make sure that there’s enough worksheets in the workbook for all servers listed. Since a new Excel workbook has 3 worksheets by default I start my loop at 4.

#Get list of servers 
$srvlist = @(get-content ".\SQL_Servers.txt")

#Counter variable for rows
$c = $srvlist.Count 
$intRow = 1

#Verify there's a sheet in the workbook for each server 
for ($i = 4; $i -le $c; $i++) 
{
$Workbook.Sheets.Add() 
}


Then it’s just a matter of looping through the servers and databases, writing the values to the workbooks and applying formatting. My final step is to rename each sheet.



$name = $instance -replace("\\", "-")
$Sheet.Name = $name


Here’s an example of what one of my sheets looks like…



image



…and here’s the whole script. For this example I stopped at 7 columns so it would all show on the screen without scrolling. But my final script will show more information about both the servers and the databases.



#region LoadAssemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
#endregion

#Get SQL account information
$credential = Get-Credential
$loginName = $credential.UserName -replace("\\","")   $password = $credential.Password

$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $False
$Workbook = $Excel.Workbooks.Add()

#Get list of servers 
$srvlist = @(get-content ".\SQL_Servers.txt")

#Counter variable for rows
$c = $srvlist.Count 
$intRow = 1

#Verify there's a sheet in the workbook for each server 
for ($i = 4; $i -le $c; $i++) 
{
$Workbook.Sheets.Add() 
}

$a = 1
#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in $srvlist) 
{
$Sheet = $Workbook.Worksheets.Item($a) 
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.set_Login($loginName)
$srv.ConnectionContext.set_SecurePassword($password)    
#set headers
$Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2) = $instance
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$intRow += 1
$Sheet.Cells.Item($intRow,1) = "VERSION:"
$Sheet.Cells.Item($intRow,2) = "EDITION:"
$Sheet.Cells.Item($intRow,3) = "COLLATION:"
$Sheet.Cells.Item($intRow,4) = "OS VERSION:"
$Sheet.Cells.Item($intRow,5) = "PLATFORM:"
$Sheet.Cells.Item($intRow,6) = "PHYS MEM:"
$Sheet.Cells.Item($intRow,7) = "NUM CPU:"
for ($col = 1; $col –le 7; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}

#get values
$intRow += 1

$Sheet.Cells.Item($intRow,1) = $srv.Information.Version 
$Sheet.Cells.Item($intRow,2) = $srv.Information.Edition
$Sheet.Cells.Item($intRow,3) = $srv.Information.Collation
$Sheet.Cells.Item($intRow,4) = $srv.Information.OSVersion
$Sheet.Cells.Item($intRow,5) = $srv.Information.Platform
$Sheet.Cells.Item($intRow,6) = $srv.Information.PhysicalMemory
$Sheet.Cells.Item($intRow,7) = $srv.Information.Processors

$intRow += 2

$Sheet.Cells.Item($intRow,1) = "DATABASES"
$Sheet.Cells.Item($intRow,1).Font.Bold = $True

$intRow += 1

$Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
$Sheet.Cells.Item($intRow,2) = "COLLATION"
$Sheet.Cells.Item($intRow,3) = "COMPATIBILITY LEVEL"
$Sheet.Cells.Item($intRow,4) = "AUTOSHRINK"
$Sheet.Cells.Item($intRow,5) = "RECOVERY MODEL"
$Sheet.Cells.Item($intRow,6) = "SIZE (MB)"
$Sheet.Cells.Item($intRow,7) = "SPACE AVAILABLE (MB)"
for ($col = 1; $col –le 7; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}
$intRow += 1

$dbs = $srv.Databases
ForEach ($db in $dbs) 
{
#Divide the value of SpaceAvailable by 1KB 
$dbSpaceAvailable = $db.SpaceAvailable/1KB 
#Format the results to a number with three decimal places 
$dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable
$Sheet.Cells.Item($intRow, 1) = $db.Name
$Sheet.Cells.Item($intRow, 2) = $db.Collation
$Sheet.Cells.Item($intRow, 3) = $db.CompatibilityLevel
#Change the background color of the Cell depending on the AutoShrink property value 
if ($db.AutoShrink -eq "True")
{
$fgColor = 3
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow, 4) = $db.AutoShrink 
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor
$Sheet.Cells.Item($intRow, 5) = $db.RecoveryModel
$Sheet.Cells.Item($intRow, 6) = "{0:N3}" -f $db.Size
#Change the background color of the Cell depending on the SpaceAvailable property value 
if ($dbSpaceAvailable -lt 1.00)
{
$fgColor = 3
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow, 7) = $dbSpaceAvailable 
$Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor

$intRow += 1

}
$Sheet.UsedRange.EntireColumn.AutoFit()
$name = $instance -replace("\\", "-")
$Sheet.Name = $name
$intRow = 1
$a += 1
}
#Save file and close Excel. 
$xlExcel8 = 56
$timeStamp = Get-Date -Format "yyyyMMdd_HH_mm"
$fileName = ".\ServerInfo_" + $timeStamp + ".xls"
$Workbook.SaveAs($fileName, $xlExcel8)
$Excel.Quit
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)



I’ve run this script using PowerShell V2 and Excel 2007 and the Excel 2010 beta with no problems. I don’t believe there’s anything specific to V2 in the script. But I do have a warning, though. If, while you run the script, you get impatient and click somewhere inside Excel, you’ll get an error and the script will stop (NOTE TO SELF: Add error handling!). The error is HRESULT 800ac472 and it’s an Excel error, not PowerShell.

Monday, March 1, 2010

Issues installing SQL 2005 SP3

Before I get to the details, I’d like to ask a few questions. I’d be interested in hearing from anyone who’s had problems installing SQL service packs or hotfixes. Has anyone else had the errors I’m mentioning below, or anything similar? What procedures or best practices do you follow when installing patches? 

Last week I finally was able to install SQL 2005 SP3 on our production servers. I’ve installed countless service packs and hotfixes in the past on numerous servers both physical and virtual, development and production. And I’ve never run into the problems I had last week.

In this case, I’m upgrading four production sites from SQL 2005 SP2 with the CU7 hotfix installed, build 3239 (see this article from SQLServerCentral that matches build numbers to service packs). When I installed SP3 on our dev and test servers I received the usual warning about running software. So last week I tried to stop the SQL services on the first 2 servers. This brought up Error #1: The services were telling me that I lacked the proper permissions to stop the SQL service. Now my account is an administrative account on the servers so it has plenty of rights, certainly enough to stop the service. ISP3.

Now comes Error #2: The system databases on a SAN drive were read only, and again I didn’t have proper permissions to rewrite them! Again, I’m an administrator and should have been able to do this with no problems. Reading the install logs just gave me the same information. And again, this error only occurred on the first 2 servers, not the final 2.

Next I logged off the servers and logged back in using the same account that the SQL services run under. I continued to get the same 2 errors on Server 1, but Server 2 let me complete the installation. Still no errors at all on Servers 3 or Server 4. Now I’m getting concerned.

My next step was to restart Server 1. After the server came back up, I could finally install the service pack. When I finished I checked the Windows and SQL error logs for any clues that would explain why my permissions were being denied. I must admit my Windows admin skills aren’t the greatest so I probably missed the cause. I did notice that the anti-virus program we run had similar issues at the same time, but the anti-virus doesn’t hit the database files and I wasn’t trying to do anything with the anti-virus service. My guess is that somehow Server 1 and Server 2 somehow couldn’t validate my account after I logged in.

Anyway, I thought I was finished. Not quite. My final step was to verify the new version. So I ran SELECT SERVERPROPERTY('ProductLevel') and all four servers returned SP3. The build number now showed as 9.00.4035. All looks good, right? A few days later I ran EXEC sp_server_info.

Finally we get to Error #3. On Server 2, remember that I had problems at first with permissions but I thought the SP installed correctly on the 2nd try. But now looking at the value for DBMS_VER and SYS_SPROC_VERSION I’m seeing the build is still showing 9.00.3239! Why? I’m not sure. I think it’s due to 1 of 2 reasons.

First, even though the SP3 install looked to be successful the second time it somehow skipped a component. During the reinstall I thought I selected all components, but it’s possible that I only thought I did, skipping something. NOTE TO SELF: for future installs document what components you’re installing. At least take a screenshot of what I’ve checked.

The second possibility is because of Analysis Services. SSAS was installed on Server 2 but the service is disabled. That makes sense since we’re not using SSAS currently, but the reason it was installed on just one server is lost to the mists of time. In any case the SP3 install logs show that SSAS was disabled and it’s at build 3239. But by that logic it would only be as 3239 if it was running during other patches. Again, I can’t be sure.