A word on the decimal data type

Was inspired to write up a few notes on the SQL Server data type.  This is pretty basic stuff, but everyone should know it.

If you declare a column as DECIMAL, it will default to the precision and scale 18, 0.  Not very useful, is it?  You might as well have used a bigint column to store integer values like that.  So first off, when you declare a decimal column, always give it precision and scale.

Script TSQL Server Level security

Here's a handy script that's part of my toolbox everywhere I go.  Scripts out and identifies basic server level security objects, and generates a tsql statement to recreate the objects.

Note that this script only works on SQL 2005 or above.  This is far from an official script, so caveat emptor.

I welcome to any and all feedback on these scripts.  I also have a database-level security script here.

SELECT @@SERVERNAME

--create windows logins
select 'CREATE LOGIN ['+ name +'] FROM WINDOWS WITH DEFAULT_DATABASE=['+default_database_name+'], DEFAULT_LANGUAGE=['+default_language_name+']' 
from sys.server_principals
where type in ('U','G')


--script out SQL logins
--http://support.microsoft.com/kb/918992


--Server level roles
SELECT DISTINCT
 QUOTENAME(r.name) as server_role_name, r.type_desc, QUOTENAME(m.name) as principal_name, m.type_desc 
, TSQL = 'EXEC master..sp_addsrvrolemember @loginame = N''' + m.name + ''', @rolename = N''' + r.name + ''''
FROM sys.server_role_members AS rm
inner join sys.server_principals r on rm.role_principal_id = r.principal_id
inner join sys.server_principals m on rm.member_principal_id = m.principal_id
where r.is_disabled = 0 and m.is_disabled = 0
and m.name not in ('dbo', 'sa', 'public')
and m.name <> 'NT AUTHORITY\SYSTEM'


--Server Level Security
SELECT rm.state_desc, rm.permission_name, principal_name = QUOTENAME(u.name),  u.type_desc
,  TSQL = rm.state_desc + N' ' + rm.permission_name + N' TO ' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))
FROM sys.server_permissions rm
inner join sys.server_principals u 
on rm.grantee_principal_id = u.principal_id
where u.name not like '##%' 
and u.name not in ('dbo', 'sa', 'public')
order by rm.permission_name, u.name

update 11/4/2013: Changed formatting to new style, include link to script out hashes for sql logins, other updates since 2010.

Script TSQL Database-level security

Here's a handy script that's part of my toolbox everywhere I go.  Scripts out and identifies basic database level security objects, and generates a tsql statement to recreate the objects. 

Note that this script only works on SQL 2005 or above.  This is far from an official script, so caveat emptor.

I welcome to any and all feedback on these scripts.  I also have a server-level security script here.

--Run the below on each database for database-level security.

SELECT DB_NAME() as Database_Name

--Database Level Roles
SELECT DISTINCT
     QUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc
,    TSQL = 'EXEC sp_addrolemember @membername = N''' + d.name COLLATE DATABASE_DEFAULT + ''', @rolename = N''' + r.name + ''''
FROM sys.database_role_members AS rm
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals d on rm.member_principal_id = d.principal_id
where d.name not in ('dbo', 'sa', 'public')

--Database Level Security
SELECT     rm.state_desc
     ,   rm.permission_name
    ,   QUOTENAME(u.name) COLLATE database_default
    ,   u.TYPE_DESC
     ,   TSQL = rm.state_desc + N' ' + rm.permission_name + N' TO ' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))   
FROM sys.database_permissions AS rm
     INNER JOIN
     sys.database_principals AS u
     ON rm.grantee_principal_id = u.principal_id
WHERE rm.major_id = 0
and u.name not like '##%'
and u.name not in ('dbo', 'sa', 'public')
ORDER BY rm.permission_name ASC, rm.state_desc ASC

--Database Level Explicit Permissions
SELECT     perm.state_desc
    , perm.permission_name
    ,   QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
       + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name COLLATE DATABASE_DEFAULT) + ')' END AS [Object]
     , QUOTENAME(u.name COLLATE database_default) as Usr_Name
    ,   u.type_Desc
    , obj.type_desc
    ,  TSQL = perm.state_desc + N' ' + perm.permission_name
           + N' ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
           + N' TO ' + QUOTENAME(u.name COLLATE database_default)
FROM sys.database_permissions AS perm
     INNER JOIN
     sys.objects AS obj
     ON perm.major_id = obj.[object_id]
     INNER JOIN
     sys.database_principals AS u
     ON perm.grantee_principal_id = u.principal_id
     LEFT JOIN
     sys.columns AS cl
     ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
where
     obj.name not like 'dt%'
and obj.is_ms_shipped = 0
and u.name not in ('dbo', 'sa', 'public')
ORDER BY perm.permission_name ASC, perm.state_desc ASC

Or, wrap the whole thing in a msforeachdb:

exec sp_msforeachdb 'use [?]; 
SELECT DB_NAME() as Database_Name

--Database Level Roles
SELECT DISTINCT
 QUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc
, TSQL = ''EXEC sp_addrolemember @membername = N'''''' + d.name COLLATE DATABASE_DEFAULT + '''''', @rolename = N'''''' + r.name + ''''''''
FROM sys.database_role_members AS rm
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals d on rm.member_principal_id = d.principal_id
where d.name not in (''dbo'', ''sa'', ''public'')

--Database Level Security
SELECT  rm.state_desc 
 ,   rm.permission_name 
    ,   QUOTENAME(u.name) COLLATE database_default
    ,   u.TYPE_DESC
 ,   TSQL = rm.state_desc + N'' '' + rm.permission_name + N'' TO '' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))    
FROM sys.database_permissions AS rm
 INNER JOIN
 sys.database_principals AS u
 ON rm.grantee_principal_id = u.principal_id
WHERE rm.major_id = 0
and u.name not like ''##%'' 
and u.name not in (''dbo'', ''sa'', ''public'')
ORDER BY rm.permission_name ASC, rm.state_desc ASC

--Database Level Explicit Permissions
SELECT perm.state_desc
    , perm.permission_name 
    ,   QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name) 
       + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ''('' + QUOTENAME(cl.name COLLATE DATABASE_DEFAULT) + '')'' END AS [Object]
 , QUOTENAME(u.name COLLATE database_default) as Usr_Name
    ,   u.type_Desc
    , obj.type_desc
    ,  TSQL = perm.state_desc + N'' '' + perm.permission_name 
  + N'' ON '' + QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name) 
  + N'' TO '' + QUOTENAME(u.name COLLATE database_default)

FROM sys.database_permissions AS perm
 INNER JOIN
 sys.objects AS obj
 ON perm.major_id = obj.[object_id]
 INNER JOIN
 sys.database_principals AS u
 ON perm.grantee_principal_id = u.principal_id
 LEFT JOIN
 sys.columns AS cl
 ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
where 
 obj.name not like ''dt%''
and obj.is_ms_shipped = 0
and u.name not in (''dbo'', ''sa'', ''public'')
ORDER BY perm.permission_name ASC, perm.state_desc ASC
';

Update 11/4/2013: Changed formatting to new style, included a foreachdb version to hit all databases.

October User Groups meeting and Powershell Intro for DBAs session materials

Here's a link to my slide deck and examples (including the sample scripts we didn't get to talk about) from the Baton Rouge SQL Server User Group meeting on October 13, 2010. 

The BRSSUG has been holding joint meetings with the BR .net User Group recently and its always worked out well. The two groups can share a sponsor - in last night's case it was Sparkhound - share a lightning round speaker (a short 15-minute topic session) and share door prize giveaways.  The SQL Server group moves to a different conference room after the lightning round.

In the end, a user group is all about community.  Since in reality DBAs and developers work together in the wild, why not share site/time with each other's user groups?

And yes, there was orange Fanta. 

Check out @BRSSUG's twitter page for a few pictures from last night.

DMV Overview Presentation from Houston TechFest 2010 on October 9

Here's the .Zip file link to slide deck and scripts (including the scripts I didn't get to) for my talk on DMV's from Houston TechFest 2010.

What a great event!  Good job to the event coordinators. 


Could have used more signage to the building, and more signage within the building. 


This is probably my best experience giving this talk, and I've given it at three SQL Saturdays and a SQL Server user group meeting too. Excellent questions, excellent discussion, and I don't mind at all that we went long.  It was my second straight event speaking in the last session slot, starting at 5pm.  First off, starting at 5pm sucks.  At a previous SQL Saturday, I had to turn off the lights because of a weak projector and I lost half my audience to the dark and late time. 

But despite all that lined up against me, I had more than a dozen folks in attendance and they were very engaged in the conversation.  Thanks and congrats to all of you in attendance for making it a great session.

Presentation on SQL DR Overview

Here's the link to the Lightning Round presentation I made to the Baton Rouge IT Professionals User Group on August 31.  The end was a demo on how to set up basic maintenance plans in SQL Server Management Studio 2008 R2.  I opened for a great presentation by Chris Eveler from Dell on Equalogic servers!

CDC: dbo.fn_all_changes_... frustrating

I've come to find that the generated dbo.fn_all_changes_... function for Change Data Capture (CDC) returns some errors and if you skip to the end of this blog entry, I have a quick fix to fn_all_changes_dbo_... that solves this problem.

They are created for you with the sproc sys.sp_cdc_generate_wrapper_function with the idea of giving you a way to easily select changed data rows between two dates.  The first two parameters of the fn_all_changes_... (and its sister, the fn_net_changes...) are dates, start and end date.

This works for my testing table called dbo.cdctest:

select * from dbo.fn_all_changes_dbo_cdctest (null,null, 'all')

This doesn't:

select * from dbo.fn_all_changes_dbo_cdctest ('2010-01-01',null, 'all')

It returns:

Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ... .
The statement has been terminated.

Why?  Because when looking for LSN's at or after 2010-01-01, fn_all_changes_.. chokes.

(Yeah, there are other reasons the error can happen.  Its a dummy response to an inherent problem that TVF's can't return errors.  There are other problems that could create the above error message.  This blog entry is specifically dealing with the problem of dates-before-CDC-was-enabled.)

On my server, the date 2010-01-01 occurs before any CDC was activated.  Before any LSN's had been created for this table.  The error occurs if you provide a date before CDC was started. 

Which means you have to give it a date/time that is between the time you set up CDC and the time of the first changed data events.  A very specific date/time, too.

Even though I didn't set up CDC until this afternoon, this still returns a valid LSN:

SELECT @lsn = sys.fn_cdc_map_time_to_lsn ('smallest greater than or equal','01/01/2010')

But if I run this, I can find out exactly when I set up CDC on this test table, dbo.cdctest.  Note the increments of time in the first parameter.

select * from dbo.fn_all_changes_dbo_cdctest ('2010-08-17 16:46:24.6',null, 'all')
select * from dbo.fn_all_changes_dbo_cdctest ('2010-08-17 16:46:24.7',null, 'all')
select * from dbo.fn_all_changes_dbo_cdctest ('2010-08-17 16:46:24.8',null, 'all')
select * from dbo.fn_all_changes_dbo_cdctest ('2010-08-17 16:46:24.9',null, 'all')


Here's what I get

Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ... .
The statement has been terminated.
Msg 313, Level 16, State 3, Line 2
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ... .
The statement has been terminated.

(69 row(s) affected)

(69 row(s) affected)

Research that "An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_" error and you find that it is a dummy response for when a valid LSN can't be found. 

LSN's are binary expressions that compare with logical operators, right? What is the reason it couldn't have been done with simple > < signs?  (funny, because >.< is exactly how I feel...)

This CDC function works with the date 01/01/2010, even though I started CDC long after 01/01/2010:

SELECT sys.fn_cdc_map_time_to_lsn ('smallest greater than or equal','01/01/2010')

Returns:

0x0000001B000002690034

And this still works too:

select * from cdc.dbo_cdctest_CT where __$Start_lsn > 0x0000001B000002690034 order by __$start_lsn


So why doesn't this work? 


select * from dbo.fn_all_changes_dbo_cdctest ('2010-01-01',null, 'all')


This is because "the LSN range is invalid". This can be fixed it by adding two lines to fn_all_changes_dbo_...:

Before the line:

if @from_lsn is not null and @to_lsn is not null and     (@from_lsn = [sys].[fn_cdc_increment_lsn](@to_lsn)) 
    return 

Add:

    if @from_lsn is null
        select @from_lsn = [sys].[fn_cdc_get_min_lsn]('dbo_cdctest')   

(Obviously, I'm disclaiming myself from any liability if you muck with a MS-provided stored proc on your production box.  I don't recommend making the above fix, I'm only pointing out its maddening simplicity.)

And before you ask about my insistence about using 01/01/2010, its just for an example.  Nothing special.  And sure, this problem is not a big one because CDC is typically used on a rolling or ongoing basis, not from a beginning-to-now basis. 

I welcome any and all responses on this.

UPDATE: small typo fixes.

DMV Introduction Files from SQL Saturday 28

Files from my DMV Presentation at SQL Saturday 28 at 10:15AM.  Glad to see you there!

Visual Studio Schema Compare frustration

In Visual Studio Schema Compare, if you have a problem where the Export to Editor is grayed out, the Write Updates is unavailable or the Schema Update Script window is blank, it is in my experience one of two things.

1. One or both of your connections is to a SQL Server, and you don't have adequate permissions.  See here.
2. One or both of your connections is to a Database Solution, and you need to rebuild your database project(s).  If you can't rebuild your database because of errors... fix them, and rebuild the database project.  Make sure you have the sqlcmdvariables chosen for the compare and setup correctly between your database projects, if you have more than one. 
   
   

Check Sharepoint version with SQL

Ever needed to check your sharepoint installation version, but don't have access to check it in central admin?

Run this query in the _Config database.

SELECT Version, TimeStamp FROM Versions      
WHERE VersionId = '00000000-0000-0000-0000-000000000000'    
ORDER BY Id DESC

Backup SQL with Powershell (long)

Had to set up back up a SQL Express 2005 database.  Why some vendors insist on creating software dependent on SQL Express, and then not giving their clients any way to back up said data, is mind-bogglingly stupid.

Here's a PowerShell script I wrote to accept a server name, database, and target backup folder.  It performs a single full backup when called. 

This script, which includes error-handling, sub-folder creation and more robust error reporting, is a lot more involved than any examples I found online, had to amalgamate the knowledge of dozens of google bing searches. 

Some key syntax features of Powershell that I learned about and implemented here:

1. The InnerException loop is the only way to get the actual SQL Error out.  Otherwise, you're just stuck with Error 3041, "BACKUP failed to complete the command", which is not informative at all.  This is probably the most critical part of this, the ability to get meaningful errors into a text file log in the event of a backup failure.
2. The Test-Path verifies if the container exists, so that you can create a directory if it doesn't.
3. The $SMOBackupObject.Database wants a Database name (string) not a Database Object.  That really threw me off for an hour or so.  My fault, ofcourse.
4. I used the SilentlyContinue ErrorActionPreference so that the user would see a clean error message (in my if $error.count section) instead of a messy red error from PowerShell, which would not be informative anyway (see #1).
5. I used both the Trap syntax (to access the Exception object) and the if $error.count, because I wanted to have it return something to the end user regardless. 
6. The out-null parts also hide messy returns to the end-user.
7. The $error.clear() is also pretty important, as it clears out the errors that had run beforehand, if the user wants to try running the script again.  Not sure why this is necessary, its not intuitive that errors persist like that.  Open to any advice.

function SQLFullBackup ([string]$SQLServerName, [string]$SQLDBname, [string]$BackupFolder) 
{  
 
    # SQL Server Backup
    # Executes a single FULL backup of a given database 
 
    $error.clear()
    $ErrorActionPreference = 'SilentlyContinue'
    $BackupDate = Get-Date -format yyyyMMddHHmmss
 
    # SPECIFY SQLServer\Instance name with this variable
    $SQLServer =  New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLServerName
 
 
    Trap [Exception] {
        $err = $_.Exception
        while ( $err.InnerException )
            {
            $err = $err.InnerException
            $errorfullmessage += $err.Message + " "
            };
 
        $errorfullmessage | out-File ($BackupFolder + "\BackupHistory_Error_" + $SQLDBName + "_" `
            + $BackupDate + ".txt");
    }
 
    #Create the subfolder if it does not already exist.
    if ((Test-Path -path ($BackupFolder + "\" + $SQLDBName) -pathtype container) -ne $True)
    {
        New-Item -path $BackupFolder -name $SQLDBName -type directory | out-null
    }
 
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
 
    $SMOBackupObject = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
    $SMOBackupObject.Database = $SQLDBName
    $SMOBackupObject.Action = "Database" # File, Log
    $SMOBackupObject.Incremental = $false
    $SMOBackupObject.Devices.AddDevice($BackupFolder + "\" + $SQLDBName + "\" + $SQLDBName + "_" `
            + $BackupDate + ".bak", "File")
    $SMOBackupObject.SqlBackup($SQLServer) 
 
    if($error.count -gt 0)
    {   
        "Backup of database " + $SQLDBName+ " failed.  See log file in " + $BackupFolder + "\BackupHistory_Error_" `
        + $SQLDBName + "_" + $BackupDate + ".txt"
    }
            Else
    {
        "Backup of database " + $SQLDBName + " successful."
    }
 
};
 

Here are the function calls, per database, for the above function:

SQLFullBackup ".\SQLEXPRESS" "express" "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backup"
SQLFullBackup ".\SQLEXPRESS" "express2" "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backup"

Here's sample output of a successful call or a failed call.

PS C:\Windows\system32>C:\powershell_scripts\sqlfullbackup calls.ps1
Backup of database express successful.
Backup of database express2 failed.  See log file in 

c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backup\BackupHistory_Error_wexpress2_20100810094357.txt 

Here's the files created:
BackupHistory_Error_express2_20100810094357.txt
\express\express_20100810094357.bak

And the error log actually contains an informative SQL Error.  In this case:

An exception occurred while executing a Transact-SQL statement or batch. 

Could not locate entry in sysdatabases for database 'express2'. No entry found with that name. 

Make sure that the name is entered correctly.
BACKUP DATABASE is terminating abnormally. 

How did this DBA first get into Powershell?  Here's a blog post about it.  

How I Learned to Stop Worrying and Love the Powershell

So, as a precursor to my next blog post where I actually do something SQL related with Powershell, I'll post my first two powershell scripts here.  They are completely unrelated to SQL, but was my first exposure to Powershell 2.0 and all its majesty.

I am a consultant, and my Cisco VPN client app has half a dozen different VPN connections.  I spent a lot of time on client sites, or, on a VPN to the client side from the office or the home office.  Needless to say, there's lots of stuff I do (such as non-billable with like this exercise, and other efforts that are billable) while not on a VPN.

And since I don't want certain apps running on my machine while I'm on VPN, that I usually run while I am not on VPN, I wrote a pair of Powershell scripts, one called onvpn and another called offvpn.

Here's what they do:

offvpn.ps1 starts up my google calendar sync (which syncs my google calendar to exchange to my iphone, very handy), tweetdeck, firefox, and MSN Messenger.

Clear-Host
if (-not (Get-Process googlecalendarsync -ea 0) ) { Start-Process "C:\Program Files\Google\Google Calendar Sync\GoogleCalendarSync.exe" }
if (-not (Get-Process firefox -ea 0) ) { start-process "C:\Program Files\Mozilla Firefox\firefox.exe" }
if (-not (Get-Process msnmsgr -ea 0) ) { start-process  "C:\Program Files\Windows Live\Messenger\msnmsgr.exe" }

if ( Get-Process vpngui -ea 0 ) { Stop-Process -name vpngui }
if ( Get-Process vpnui -ea 0 ) { Stop-Process -name vpnui }

what does onvpn.ps1 do?  Just the opposite, plus a few more things like the amazonmp3downloader app that only starts up on demand.  It also starts up my vpngui client and my Remote Desktop Client manager app, which I'll inevitably be using over the VPN client.

Clear-Host

Get-Process firefox -ea 0 | % { $_.CloseMainWindow() } 
if ( Get-Process googlecalendarsync -ea 0 ) { Stop-Process -name googlecalendarsync }
if ( Get-Process msnmsgr -ea 0 ) { Stop-Process -name msnmsgr }
if ( Get-Process amazonmp3downloader -ea 0 ) { Stop-Process -name amazonmp3downloader }
if (-not ( Get-Process vpngui -ea 0 ) ) { Start-Process "C:\Program Files\Cisco Systems\VPN Client\vpngui.exe" }
if (-not ( Get-Process RDCMan -ea 0 ) ) { Start-Process "C:\Program Files\Remote Desktop Connection Manager\RDCMan.exe" }

The Get-Process call determines if the processes are running before starting or stopping them.

One small annoyance is that the Stop-Process command isn't that graceful. Firefox always starts up with the "well, this is embarrassing" screen asking me if I want to try and restore the previous session. If there was a way to more gracefully shut down these apps, I'd be open to it.

(Also, as a corollary, I figured out how to post nice code.  The Visual Studio 2010 PowerTools has a copy-to-HTML feature.  Much nicer, eh?)

Questions and comments are welcome.

UPDATE: Updated the onvpn.ps1 script to use a kinder, gentler way of shutting down some apps.  See the comments.

SSRS: does not have write access to 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files

In case anyone gets this error:

Server Error in '/' Application.
The current identity ({whatever domain\username}) does not have write access to 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files'.

This blog entry helped me out.  The result was odd but it worked.

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regiis -ga "{whatever domain\username}"
 

Start granting {whatever domain\username} access to the IIS metabase and other
 directories used by ASP.NET.
An error has occurred: 0x800703f0 An attempt was made to reference a token that
does not exist.

Fun with SQL 2005 Service Pack 3

At a remote client site last night, I ran into a series of problems with SQL 2005 RTM and updating it to SP3.

Error Number : 1612

Error Description : Unable to install Windows Installer MSP file

like that, on the SQL, AS, NS, RS, DTS and Tools components. Furthermore, the SP3 install was asking me for the original install media locations of the .msi files before failing each component during the Service Pack installation.

I tried a few of things unsuccessfully: setting explicit admin permissions on numerous folders (has worked in the past), using a different copy of SQL 2005 CD1 media, trying SP2 instead (same problems), uninstalling the antivirus software, googling like a mad man. (Er... I mean Binging.)

Eventually, my mad searching led me to this MSDN Blog Entry, which initially seemed very daunting. And I was correct, it was very time-consuming. But it worked.

(Note that if you've found this blog entry because you're experiencing some of the errors within, the above Blog Entry link Parts 1 and 2 are what you are looking for.)

This error in the service pack log files was the most telling:

Warning: Local cached package 'C:\WINDOWS\Installer\a1b67256.msi' is missing.

45 minutes of searching through logs for eight-digit random strings, copying files from the unpacked SP3 exe and the original media into c:\windows\installer, SQL, AS and RS all were installed by the service pack correctly.

But, the NS, DTS and Tools components gave me an entirely different error.

Error 2902
Operation ixfAssemblyCopy called out of sequence.

 I re-copied the msi and msp files, thinking that perhaps I had done something wrong. That didn't work. I reinstalled, noticing that NS and DTS were installed to be available on first run. I fully installed all features this time around for NS, DTS and Tools, but again, SP3 reported the above ixfAssemblyCopy error.

To get SP3 to take Tools, I had to uninstall and reinstall that component, using the CD2:\Setup\SqlRun_Tools.msi. That allowed Service Pack 3 to finally successfully update. 

What a night!

Some talking points:

• Interesting how I was told five years ago that when Microsoft made SSIS, they didn't re-use a single line of code for DTS. That is hard to believe, since the installers for SSIS are still called SqlRun_DTS.msi.
  
  
• There was some interesting stuff in the logs that threw me off. Stuff like
  
  MSI (s) (74:74) [20:12:05:278]: Note: 1: 2203 2: i:\35a1934bc4ebd525729353c57bb8\HotFixSQL\Files\sqlrun_sql.msp 3: -2147287037
  MSI (s) (74:74) [20:12:05:278]: SOURCEMGMT: Source is invalid due to missing/inaccessible package.
  
  That's really odd, since there actually is an I:\ drive mapped on this server, and it doesn't contain any folders like that. I assume that long-string-named folder is temporary and that its use of I: would be virtual. 
  
  Could the Service Pack actually have been thrown off by the fact that an i: drive existed? I couldn't unmap it, wasn't my server, but I can't help but thinking that was fishy.  Anyone else encountered service pack issues when an I: drive exists?
  
  
• I still don't know the problem with why SP3 couldn't copy the .msi and .msp files on its own. Seems simple enough, what's the explanation? Couldn't be permissions, I gave explicit admin rights to the installing user to every folder I could. Talk about aggravating.

SQL Saturday #28 Twitter Referral Contest

Here's the official rules for the twitter referral registration contest for SQL Saturday #28 in Baton Rouge, LA on August 14, 2010 on the campus of Louisiana State University.
 
In case you haven't heard, SQL Saturday Baton Rouge has over 400 registrants as of early July.  We're in Taylor Hall (formerly known as CEBA) on LSU campus.  This isn't no community college, all of the rooms for SQL Saturday #28 feature stadium seating and mounted projectors, and seating for no fewer than 60+ in each room.  We have nine tracks, including SQL admin and development, BI, .net programming, infrastructure and more.  And its all free: free breakfast, free lunch, free goodies and giveaways, free training from local and national experts in their fields, including many Microsoft MVP's.

1. Refer people to register for SQL Saturday #28.  This doesn't have to be via Twitter, how you encourage them to attend SQL Saturday is up to you!
2. After they register, tell them to tweet a message in this format: @(Your_twitter_name) referred me to #SQLSat28!  We will be monitoring that hash tag for referrals, so its important they use "#SQLSat28".
3. When they sign-in at the event we will mark that person under your name. Yes, they must attend (and so must you!) for you to get credit.  No, you can't refer yourself. This is only for registrants that sign up beginning 7/12/2010, the day we announced these rules.
4. The person with the most referrals who shows up will win a one-year MSDN subscription, to be awarded in the closing event of SQL Saturday.  You must be present to win!  In the case of a tie, we'll flip a coin or something.

FYI- the official twitter account of SQL Saturday 28 is @SQLSatBR and the hashtag for the event is #SQLSat28. 

Questions? Tweet them to @SQLSatBR, comment on this blog post or email SQL Saturday 28's organizers at sqlsaturday28@sqlsaturday.com.

Spare the switch, fix the WinMerge

Are you like me and you prefer the open-source WinMerge as your text comparison/merge tool?

And are you like me, annoyed when you compare and get the "Cannot open file C:\Documents and Settings\....\TFSTemp\......  The system cannot find the file specified." error?

The easy workaround is that you have to close WinMerge before calling it again from Visual Studio.

This is because the really popular string of switches out there (just google "winmerge visual studio") includes the /s switch.

The /s "limits WinMerge windows to a single instance," apparently by bringing up that annoying error.  Not the most graceful way to do that.

Anyway, drop that /s switch.  Visual Studio will be able to open multiple compare windows up for you.  This might have other implications, but at least educate yourself as to what it does, and why that error above isn't really an error.

Here's the switch list I use: 

/e /x /dl %6 /dr %7 %1 %2

 Reference this online manual doc for explanations on the switches.


"There is no off position on the genius switch. " -David Letterman

Notes from Teched 2010

Here's my notes from TechEd North America 2010

http://docs.google.com/View?id=df658hrc_90xx688r48

They are extensive, yes.  I tried to make them somewhat interesting to read with humor and such.  My boss, who dropped big bucks on the conference and expects me to be able to bill in kind to cover the investment by a factor of ten, really likes the fact that I produced notes of this length.  It will be a factor in the decision to send me to more conferences in the future.  I'll be sharing the notes with the department as well.

If you get sent to a conference, I'd recommend doing something like this as well, in addition to giving a knowledge share with your colleagues afterwards.  Its best to type up your notes ASAP after the class, so that you can decipher what your wrote/typed and make sure it is explained well to someone who wasn't at the class.  Include the slide downloads and videos, if provided by the conference.

If you're a manager, I'd set requirements like this in place beforehand and make sure the expectation is that this isn't a vacation, this isn't a free training for the betterment of your career, this is a focused training aimed at improving your ability to work/bill in your intended job role.  You're wasting your company's money if you come to costly conferences like TechEd and hang out at the expo all day, or take certification practice exams, or just drink and party on Bourbon Street.

DMV Introduction Files from SQL Saturday 22

Here's the link to my presentation and files from Pensacola SQL Saturday on June 5 2010.

Baton Rouge SQL Server User Group meeting 5/26

Here's the powerpoint and files I used at the Baton Rouge SQL Server user group meeting on May 26. 

Thanks to the other DBA's who attended!  It was a great user group we held together with Baton Rouge .net User Group.

 Twitters:
Baton Rouge .net User Group: @BRDNUG
Baton Rouge SQL Server User Group: @BRSSUG

I'll be giving the same talk (with some fixes to the jokes in the slides) at the SQL Saturday in Pensacola in less than two weeks and at Baton Rouge SQL Saturday.

LINQ doesn't like TINYINT PK IDENTITY fields

So, to save space (and it is a bit archaic, but why not) I sometimes use tinyint and smallint for the PK IDENTITY columns in small lookup tables.

For example, a list of counties or congressional districts for a state government project.  Will never be more than 255 or negative.  Tinyint.  1 byte instead of 4.

Yeah yeah, I'm aware that in the long run, that adds up to very little saved disk space.  Very little.  Still, I can't help but be tight with data type constraints.  Its not like I will be allowing every column to be declared varchar(8000), much less varchar(max), even when "disk space is cheap".  Its not like I'll be using bigint instead of int when I know I'll never get more than 2 billion records. 

Except that when your developers want to use LINQ, you can't use a TINYINT IDENTITY column. Stupid error.

Changed it, begrudgingly, to smallint.  2 bytes instead of 4.  Grrr...

Read more:
http://linqinaction.net/blogs/roller/archive/2007/11/28/linq-to-sql-doesn-t-support-identity-tinyint.aspx

SSRS Web Site vs Web Service

Had an issue yesterday where installing Microsoft CRM couldn't find the reporting server.

The error was a 404, essentially, the CRM install couldn't see the Reporting server.

The issue was that we had copy-pasted the Reporting Services web site

http://domain.com/Reports

instead of the Reporting Services web service

http://domain.com/ReportServer


Duh.


In conclusion: don't do that.


"The best way to find yourself is to lose yourself in the [web] service of others." -Mahatma Gandhi [and this blogger]

TFS Sidekicks

I've do a lot of DB Pro solutions and DBA code reviews at my office and on client sites. This free sidekick add-on for Visual Studio has been invaluable.

http://www.attrice.info/downloads/index.htm

Whenever I want to look at files included in a TFS changeset, or all files checked in to TFS for one work item, or comparison across changesets, I find this Visual Studio plugin extremely handy.

For example, I use it for Code Reviews, to compare changes across all Changesets for a given Work Item. It has a quick function to compare the latest version of a file to the latest version of a file before this work item. Ridiculously efficient, I've forgotten how I'd do this before.

Very quick install, low-impact. I imagine it could be very handy for lots of different Source Control-related sources and diffs in Visual Studio that you just can’t do with Visual Studio alone. Anyone else use it?

Twitterpated

Follow this blog's auto-twits- @SQLTact
Follow the Baton Rouge SQL Server User Group- @BRSSUG
Follow Baton Rouge SQL Saturday (August 14, 2010!)- @SQLSatBR

"Whoever said that things have to be useful?" – Evan Williams, co-founder of Twitter

A message on default constraints

One of my assignments as a consultant DBA for a local Fortune 500 company is to support development of a large project. I was in a meeting on naming conventions and best practices from a DBA standpoint with the development leads this past week when the practice of naming default contraints came up.

The benefit of providing user-defined names on your default constraints is that SQL won't create a standardized name with a random string at the end. That random string can be a real pain when it comes to database object comparison, plus, it looks ugly and isn't nearly as informative as a user-defined name could be.

Here's the email sent to the developers, who were unsure of the syntax and the deprecated behavior of default objects. Names have been changed to protect the innocent tables and columns.

You are correct, creating a CONSTRAINT object is deprecated behavior and will not be supported. Creating the constraints in the CREATE TABLE script is the preferred method. Constraints can still be named, however.

CREATE TABLE [dbo].[TABLECD](

[CD] [char](1) NOT NULL DEFAULT ' ',

[id] [varchar](15) NOT NULL DEFAULT ' ',

[ADDUSER] [int] NOT NULL DEFAULT ((0)),

[ADDDATE] [datetime] NOT NULL DEFAULT GETDATE(),

[EDITUSER] [int] NOT NULL DEFAULT ((0)),

[EDITDATE] [datetime] NOT NULL DEFAULT CONVERT(datetime, '1899/12/31'),

[DELETEUSER] [int] NOT NULL DEFAULT ((0)),

[DELETEDATE] [datetime] NOT NULL DEFAULT CONVERT(datetime, '1899/12/31'),

[DELETEFLAG] [char](1) NOT NULL DEFAULT ' ',

When the above script is run, below is what actually gets created. Notice the names generated.

DEFAULT on column ADDDATE DF__TABLECD__ADDDA__59FA5E80 (getdate())

DEFAULT on column ADDUSER DF__TABLECD__ADDUS__59063A47 ((0))

DEFAULT on column CD DF__TABLECD__CD__571DF1D5 (' ')

DEFAULT on column DELETEUSER DF__TABLECD__DELET__5CD6CB2B ((0))

DEFAULT on column DELETEDATE DF__TABLECD__DELET__5DCAEF64 (CONVERT([datetime],'1899/12/31',0))

DEFAULT on column DELETEFLAG DF__TABLECD__DELET__5EBF139D (' ')

DEFAULT on column EDITDATE DF__TABLECD__EDITD__5BE2A6F2 (CONVERT([datetime],'1899/12/31',0))

DEFAULT on column EDITUSER DF__TABLECD__EDITU__5AEE82B9 ((0))

DEFAULT on column id DF__TABLECD__id__5812160E (' ')

Here’s a version of the script that sets user-provides constraint names:

CREATE TABLE [dbo].[TABLECD](

[CD] [char](1) NOT NULL CONSTRAINT [DF_TABLECD_CD] DEFAULT ' ',

[id] [varchar](15) NOT NULL CONSTRAINT [DF_TABLECD_id] DEFAULT ' ',

[ADDUSER] [int] NOT NULL CONSTRAINT [DF_TABLECD_ADDUSER] DEFAULT ((0)),

[ADDDATE] [datetime] NOT NULL CONSTRAINT [DF_TABLECD_ADDDATE] DEFAULT GETDATE(),

[EDITUSER] [int] NOT NULL CONSTRAINT [DF_TABLECD_EDITUSER] DEFAULT ((0)),

[EDITDATE] [datetime] NOT NULL CONSTRAINT [DF_TABLECD_EDITDATE] DEFAULT CONVERT(datetime, '1899/12/31'),

[DELETEUSER] [int] NOT NULL CONSTRAINT [DF_TABLECD_DELETEUSER] DEFAULT ((0)),

[DELETEDATE] [datetime] NOT NULL CONSTRAINT [DF_TABLECD_DELETEDATE] DEFAULT CONVERT(datetime, '1899/12/31'),

[DELETEFLAG] [char](1) NOT NULL CONSTRAINT [DF_TABLECD_DELETEFLAG] DEFAULT ' '

)

And here’s the output. Notice the constraint names are not truncated or appended with random strings.

DEFAULT on column ADDDATE DF_TABLECD_ADDDATE (getdate())

DEFAULT on column ADDUSER DF_TABLECD_ADDUSER ((0))

DEFAULT on column CD DF_TABLECD_CD (' ')

DEFAULT on column DELETEDATE DF_TABLECD_DELETEDATE (CONVERT([datetime],'1899/12/31',0))

DEFAULT on column DELETEFLAG DF_TABLECD_DELETEFLAG (' ')

DEFAULT on column DELETEUSER DF_TABLECD_DELETEUSER ((0))

DEFAULT on column EDITDATE DF_TABLECD_EDITDATE (CONVERT([datetime],'1899/12/31',0))

DEFAULT on column EDITUSER DF_TABLECD_EDITUSER ((0))

DEFAULT on column id DF_TABLECD_id (' ')

“There are no constraints on the human mind, no walls around the human spirit, no barriers to our progress except those we ourselves erect.” -Ronald Reagan

SQL + Vark

In honor of Google's acquisition of Aardvark, thought I'd share a SQL-related Vark from a while back.

Here's how I spent part of my morning in an empty house.

http://vark.com/t/83c38c

Question

Dec 05, 2009

Sent to Aardvark via IM

Friend 1

22 / M / Omaha, NE

In database design, are relational schemas created with ER diagrams usually in 3NF? Is there a case where ERD schemas wouldn't be in 3NF?

Answer 1

+ 11 minutes

Aardvark found someone who knows about database

William A. (<--- that's me)

I am a database architect. For a relational system, yes, diagrams are created with at least third normal form. It is a part of the design process to consider business rules, usage and business entities... strictly designing a system to a normalization spec is not realistic. When designing a data warehouse however, converting higher-normalization relational data into a less normalized form may lead to better performance in a system that will be primarily for reads, not writes.

Friend 1

Is there a something about the ERD approach to designing relational schemas that makes it generally create schemas that satisfy third normal form, or is it just something you keep in mind during the design process?

William A.

So, I wasn't aware ERD was an "approach". Typically a CERD is followed by a PERD. That is, a Conceptual ERD, where relationships are specified as M-M, and objects are in business terms that a client would be able to easily recognize and comprehend. A Physical ERD is not easily consumable by a client or business user because it translates the CERD into database tables and foreign key relationships. The PERD contains join tables that break many to many relationships into a pair of 1-M relationships, for example. So building an ERD is to me separate from proper Normalization methodology.

Friend 1

Yeah, ERD schema design and 3NF normalization are usually not thought of simultaneously, which is why I'm having a hard time finding any resources that relate the two. Much harder is figuring out how well ERD creates schemas which satisfy 3NF, whether that was the intention of the design or not.

William A.

To me, ERD is not a design process or architecture methodology. To me, it is a document. Yes, ERD can display a schema that has been created in 3NF well, though typically only a PERD would be capable to displaying proper normalization. I'd suggest papers or books by CJ Date or EF Codd

Friend 1

I'll look into it, thanks for your help.

William A.

good luck

He'll need it.

Aardvark is a really neat social tool that is sort of like the oft-advertised KGB, except the answers come from other users who have signed up for knowledge in general categories. I signed up for Aardvark after hearing a story about it on NPR, and had a delightful conversation about Xbox360's and the weather with a fellow named Mert in Turkey. As you can see, the above conversation was brought to me because I signed up as knowledgeable in the area of database.

I've also Vark'd (is that a word?) about Conan O'Brien's TV ratings and martial arts schools for my kid. But since this is a SQL blog, I figured I'd share this one.

UPDATE: Sadly, Google killed Aardvark in September 2011.  Oh well.

Upgrade SQL 2005 to 2008 checklist

Pre-upgrade checklist

• Make sure you don't have an sa accounts with blank passwords. This is a really good idea regardless.
  
• On the sql server, get your .net installation up to .NET Framework 3.5 SP1 or later. Best way to do this is to install Visual Studio 2008 SP1.
  
• For Server 2003, install Windows Installer v4.5 from the SQL 2008 media. Not needed for Server 2008.
• Make sure that all DBAs, sysadmins, and dev team leads know that they will need to install the SSMS 2008. (Still a good idea if you have SQL 2005 servers in your environment to keep SSMS 2005 and BIDS 2005 installed at your workstation, especially for replication and SSIS packages.)
  

General tips

• Easiest way I can think to do this is to install a SQL 2008 instance, detach/attach the 2005 databases to the new edition. Obviously, this creates a server with a different instance name. This may or may not be a big deal, if you can change your connections strings easily enough.
  
  In all likelihood, you'll instance want to upgrade in-place your SQL 2005 instance. This can be done through the wizard, using the option "Upgrade from SQL Server 2000 or SQL Server 2005."
  
  
• Remember that upgraded, attached or copied databases from SQL 2005 may still be in SQL 2005 compatibility mode. Be sure to do your post-implementation testing against databases in SQL 2008 compatibility mode!
  

If you have anything to add, please do so in the comments below so that others who find this blog post can benefit.

Links for more reading:
http://msdn.microsoft.com/en-us/library/ms144267.aspx
http://msdn.microsoft.com/en-us/library/bb677619.aspx
Clustered environment: http://msdn.microsoft.com/en-us/library/ms191295.aspx
http://www.sqlservercentral.com/articles/SQL+Server+2008/67066/

How many work days?

Here's a fun script I got asked to write to calculate the number of work days between a given start and end date, including those dates.

The query does not exclude holidays, only Saturdays and Sundays are removed from the count. It would be easy enough to exclude holidays by hooking a CASE up to a table that contains date records for the holidays your business has declared non-work days. SQL does NOT know that Mardi Gras is a holiday for your south Louisiana company, folks. :)

It also determines the value of your DATEFIRST setting and sets it if it is not default. This is only a session-wide declaration.

I welcome any feedback.

Again, I know, blogspot doesn't have a way to format this better.

IF @@DATEFIRST <> 7
SET DATEFIRST 7

declare @startdate smalldatetime
, @enddate smalldatetime
, @workdays int


select @startdate = '1/1/2010'
, @enddate = '1/17/2010'

select @workdays =

--Raw number of days including both the start and end dates.
datediff(d, @startdate, @enddate) + 1

--Is the start date a weekend?
- CASE WHEN
datepart(dw, @startdate) in (1, 7)
THEN
1
ELSE 0
END

--Is End Date a weekend?
- CASE WHEN
datepart(dw, @enddate) in (1, 7)
THEN
1
ELSE 0
END

--Remove whole week weekends.
-
CASE WHEN
datediff(d, @startdate, @enddate)>7
THEN
CASE WHEN
datediff(d, @startdate, @enddate)/7 > 0
THEN (datediff(d, @startdate, @enddate)/7) * 2
ELSE 0
END
ELSE 0
END

-- Remove a weekend from an incomplete week
-
CASE WHEN
datediff(d, @startdate, @enddate)%7 > 0
THEN
CASE WHEN
datepart(dw, @startdate) + (datediff(d, @startdate, @enddate)%7) = 8
THEN 1
WHEN
datepart(dw, @startdate) + (datediff(d, @startdate, @enddate)%7) > 8
THEN 2
ELSE 0
END
ELSE 0
END


SELECT @workdays

"Measure not the work until the day's out and the labor done."-Elizabeth Barrett Browning

Roll call - when all members are present

Had to write a neat query that was a bit of fun-

A protocol is a set of services. A clinic offers services, independently of protocols. Should a clinic offer all those services, it satisfies a protocol. Return a list of all satisfied protocols given a single clinic.

(Pardon the spacing and syntax, but blogspot nukes my tabs and spaces. I must figure out a better way to post code.)

SELECT
p.ID
FROM
dbo.Protocol p
WHERE
p.IsActive = 1
AND p.ID NOT IN (
SELECT
ps.ProtocolID
FROM
dbo.ProtocolServices ps
LEFT JOIN
dbo.ClinicServices cs
ON
ps.ServiceID = cs.ServiceID
and cs.IsActive = 1
and cs.clinicid = 987
WHERE
ps.IsActive = 1
and cs.ID is null
)

What the query does is to say - give me all Protocols, except the ones where the service list is not satisfied. The subquery looks at the join tables (they are named just as they behave, per naming conventions) and determines any Protocols where the Clinic is not satisfying all Services - that is, where there is a null in the LEFT JOIN on ClinicServices.

Now, here's the fun part.

The developer I was working with, despite all his talents, is a groupie for lambda expressions, which I understand are some sort of dark magic concocted in obsidian towers for purposes dire and contradictory to the good will of databases.

Despite all that hyperbole, here's what he came up with to convert the above query to the below nonsense, err, λ expression. From what he explained to me, Lambda expressions are dynamic and use the declaratives seen below while LINQ is more hard coded. I welcome further input with the caveat that LINQ, EF and other systems for generating bad TSQL are systema non grata around here. :)

var protocols = Protocols.Where(p => p.IsActive);
var activeProtocolServices = ProtocolServices.Where(ps => ps.IsActive);
var clinicServices = ClinicServices
.Where(cs => cs.IsActive)
.Where(cs => cs.ClinicID == 987);
var allProtocolServicesForClinics = activeProtocolServices.Join(clinicServices, ps => ps.ServiceID, cs => cs.ServiceID, (ps, cs) => ps);
var protocolServicesNotApplicableForClinic = activeProtocolServices.Except(allProtocolServicesForClinics);
var protocolsForClinic = protocols.Except(protocolServicesNotApplicableForClinic.Select(ps => ps.Protocol));

January SQL Server & .NET Joint User Group Meeting

http://batonrouge.sqlpass.org/

January SQL Server & .NET Joint User Group Meeting

Note: This is meeting will be held at the same time as the .NET User Group. However we will break into two sessions after the Lightning Round, which will be held in the Kitchen Area.

There will be great giveaways including Windows 7 Ultimate and Office 2007.

Location: Lamar Advertising

Address: 5551 Corporate Blvd, Baton Rouge, LA 70808

Date and Time: 1/13/2010 5:45:00 PM

Sponsored by: WillStaff Worldwide and Rose Huguet, Realtor

Agenda

5:45 pm - 6-15 pm: Networking and Refreshments

6:15 pm – 6:30 pm: Lightning Round

6:30 pm – 6:40 pm: Introductions

6:40 pm – 8:10 pm: Break into two Sessions (SQL Server and .NET)

8:10 pm – until: Open Forum for questions and Raffle

Lightning Round (In Lamar Kitchen Area)

Presenter: Stephanie Thomas
Stephanie is a Sales Manager for WillStaff Worldwide in Metairie and has extensive experience as an IT recruiter.

Topic: Branding Yourself in a Tough Economy
In a tough economy it is important to prepare yourself for the worst. Stephanie will provide some tips for making yourself more marketable.

Level: Introductory

SQL Server Presentation (In Lamar Kitchen Area)

Topic: Database Normalization/Denormalization

Speaker: Thomas LeBlanc

BIO:

Thomas is a Database Administrator for Amedisys, Inc in Baton Rouge, LA. He has been in the IT field for 20 years starting as a COBOL programmer, graduating to dBase, FoxPro, Visual FoxPro, upgrading to Visual Basic versions 3-6 and even some .NET(C#). Designing and developing normalized database has become his passion. Full-time DBA work started about 9 years ago for Thomas while working at a Paper Mill in St. Francisville, LA continuing with IEM, then Amedisys. Current activity at Amedisys involves 4 other DBAs and a manager supporting 400+ GB databases with replication to a Data Warehouse and DB Mirroring to a disaster recovery site. Performance tuning and reviewing database design and code are an everyday occurrence for DBAs at Amedisys. Thomas’ free time is spent helping those less fortunate and improving his relationship with his family and God.

Overview:

How did the development world conclude that an integer is the best primary key? What is 4th and 5th normal form? What has been added to SQL Server in the 2005 and 2008 to help change the way database design has evolved over the years? Can we still use a VarChar(xx) for a primary key? What is the difference between a lookup and Parent/Child relationship? What is an example of Many-To-Many? This session will go through the history of 20 year s of experience with various database designs – normalize and denormalized. The discussion will include the benefits and forward looking that should be required for using various design techniques.

.NET Presentation (Lamar Media Room)

Topic: The Busy .NET Developer’s Guide to Extending .NET Apps with Scripting.

Speaker: Ted Neward

BIO:

Ted is an independent consultant specializing in high-scale enterprise systems, working with clients ranging in size from Fortune 500 corporations to small 10-person shops. He is an authority in Java and .NET technologies, particularly in the areas of Java/.NET integration (both in-process and via integration tools like Web services), back-end enterprise software systems, and virtual machine/execution engine plumbing.

Overview:

Ever wished you could just put parts of your program in end-users' hands and let them build the infinite little changes they want? Ever thought about how you might make your application more robust by writing less code, not more? Embed a scripting engine into your application--complete with the safeguards necessary to ensure that users can't do anything they shouldn't be able to--and release yourself from the Principle of Perpetual Enslavement. This presentation will describe how to embed a scripting engine, discuss the pros and cons of the various ones available, and how to put enough safeguards around the scripts to make sure that your application can't be hijacked by bad users' scripts.