You may run into the 15023 error if you restore a MS SQL database from backup.  You expect a restored database to be in exactly the same state as the backup, but the login fails for a user that had permissions in the backed up database.  When you use the "User Mapping" SQL Management Studio functionality to allow the user permissions to the new database, you receive the 15023 error.  This is caused by Security identification numbers (SID) that are mismatched or 'orphaned' in the sysusers table. 

The SQL Server stored proc sp_change_users_login locates and fixes these records.  Run it with a single parameter 'Report' to get a listing of abandoned user names and corresponding SIDs:

exec sp_change_users_login Report

The 'Update_One' parameter will reconnect a single login:

exec sp_change_users_login Update_One, 'MyLogin', 'MyLogin'

You can find more info about this issue at:

http://support.microsoft.com/kb/246133

http://support.microsoft.com/kb/240872

This next blog expands on the available parameters for sp_change_users_login:

http://blog.sqlauthority.com/2007/02/15/sql-server-fix-error-15023-user-already-exists-in-current-database/

Also, try checking out the source for sp_change_users_login found in the Sql Server Management Studio under Databases | System Databases | Master | Programmability | Stored Procedures | sp_change_users_login.

SQL Management Objects (SMO) is something of a Swiss army knife that lets you traverse meta data, automate backup and restore, and otherwise manage SQL Server 2005 through .NET code.  For instance, to perform the classic database hierarchy walk use the SmoApplication object and enumerate the servers.  First you need to reference the Smo assemblies:

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll

Then call the SmoApplication EnumAvailableSqlServers() method, passing 'true' to list only local servers:

1 2 3 4 5 6 7

if (server.PingSqlServerVersion(server.Name).Major > 8) { foreach (UserDefinedDataType udf in database.UserDefinedDataTypes) { Console.WriteLine("Type: {0}", udf.Name); } }

A nifty SMO bonus is that objects Database and downwards in the hiearchy have a Script StringCollection property.  Script is pre-populated with T-SQL and can be used to recreate objects.

SMO provides DBA automation functionality like backup/restore and create/drop databases. These operations depend on the SMO Server object.  The constructor for Server can have a) no parameters for your local server, b) a server name only, or c) a ServerConnection object in case you need to supply user name and password.

1 2	ServerConnection serverConnection = new ServerConnection("MyMachine", "sa", "MyM@ch1n3"); Server raServer = new Server(serverConnection);

Here is an example of creating a new database to be used as a backup location:

1 2 3 4 5 6 7 8

static Database CreateBackupDatabase(Server server, string databaseName) { string backupDatabaseName = databaseName + "_" + DateTime.Now.ToString("yyMMddHHmmffffff"); Database backupDatabase = new Database(server, backupDatabaseName); Console.WriteLine("Creating new database {0}", backupDatabase.Name); backupDatabase.Create(); return backupDatabase; }

The backup operation requires a backup "device", in this case a file that will be used to store the backup.  Assign an SMO Database object to be backed up, add the backup device to its list of devices and call the SqlBackup() method.  You may also want to assign event handlers for PercentComplete and Complete events.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

static void DoBackup(Server server, string databaseName, Database backupDatabase) { Console.WriteLine("Backing up {0}", databaseName); BackupDeviceItem backupDevice = new BackupDeviceItem(databaseName + ".bak", DeviceType.File); Backup backup = new Backup(); backup.Database = databaseName; backup.Devices.Add(backupDevice); backup.PercentComplete += new PercentCompleteEventHandler(InProgress); backup.Complete += new ServerMessageEventHandler(Complete); backup.SqlBackup(server); } static void InProgress(object sender, PercentCompleteEventArgs e) { Console.WriteLine("Percent complete: {0}", e.Percent); }

Restoring can sometimes be trickier due to the original database having a strangle-hold on the physical data and log files.  To work around this use the Restore RelocateFiles property to map new file names and locations. Also notice that to get the physical file path we use the Database object's FileGroups property, drill down into the Files and use the path of the first file in the list.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

static void DoRestore(Server server, string databaseName, Database backupDatabase) { Console.WriteLine("Restoring {0}", backupDatabase.Name); Restore restore = new Restore(); restore.Database = backupDatabase.Name; restore.ReplaceDatabase = true; string backupPath = Path.GetDirectoryName(backupDatabase.FileGroups[0].Files[0].FileName); string dataPath = string.Format("{0}\\{1}.mdf", backupPath, backupDatabase.Name); restore.RelocateFiles.Add(new RelocateFile("Falafel", dataPath)); restore.RelocateFiles.Add(new RelocateFile("Falafel_log", Path.ChangeExtension(dataPath, ".ldf"))); BackupDeviceItem backupDevice = new BackupDeviceItem(databaseName + ".bak", DeviceType.File); restore.Devices.Add(backupDevice); restore.PercentComplete += new PercentCompleteEventHandler(InProgress); restore.Complete += new ServerMessageEventHandler(Complete); restore.SqlRestore(server); }

Thats a few of the things SMO can do for you. It's not a replacement for T-SQL, but if you need access to meta-data, automation or other DBA tasks from managed code, then the SMO namespace may be worth exploring. BTW, you can also use SMO in PowerShell directly, wrapped in commands or as the infrastructure for PowerShell providers.

You can write your own cmdlet ("command-let") to extend PowerShell in the .NET language of your choice.  You need to write both the cmdlet and a PowerShell snap-in to help install and register the command.  Here's an example snap-in for a "get-food" command  that lists tasty Mediterranean foods (like Falafels):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

using System; using System.Collections.ObjectModel; // supports Collection using System.Management.Automation; // supports PSSnapIn using System.Management.Automation.Runspaces; // supports *ConfigurationEntry using System.ComponentModel; // supports RunInstaller using Falafel; // Project also references System.Configuration.Install public class FalafelSnapIn : CustomPSSnapIn { public FalafelSnapIn() : base() { } public override string Name { get { return "FalafelSnapIn"; } } public override string Vendor { get { return "Falafel Software "; } } public override string Description { get { return "Runs custom Falafel commands."; } } /// <summary> /// Specify the cmdlets that belong to this custom PowerShell snap-in. /// </summary> private Collection<CmdletConfigurationEntry> _cmdlets; public override Collection<CmdletConfigurationEntry> Cmdlets { get { if (_cmdlets == null) { _cmdlets = new Collection<CmdletConfigurationEntry>(); _cmdlets.Add( new CmdletConfigurationEntry("get-food", typeof(FalafelCmdlet), null)); } return _cmdlets; } } }

CustomPSSnapIn knows how to be installed via installutil.exe, contains information about name, vendor, description etc., and has collections of types that can be registered with PowerShell such as cmdlets, Types, Formats and Providers.  FalafelSnapIn descends from CustomPSSnapIn, overrides the Cmdlets collection and adds the "get-food" cmdlet to the collection. Notice that much of the PowerShell specific functionality is supported in the System.Management.Automation namespace.

 Next we'll look at FalafelCmdlet, the implementing class for the "get-food" cmdlet.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

using System; using System.Management.Automation; // supports PSSnapIn, CmdLet, Parameter namespace Falafel { [Cmdlet(VerbsCommon.Get, "Food")] public class FalafelCmdlet : Cmdlet { private string _contains; [Parameter(Mandatory = false, Position = 0, HelpMessage = "List item descriptions containing this string")] public string Contains { get { return _contains; } set { _contains = value; } } protected override void ProcessRecord() { MediterraneanFoods foods = new MediterraneanFoods(); foreach (MediterraneanFood food in foods.FindFoods(_contains)) { WriteObject(food); } } } }

First the Cmdlet attribute marks this class as a cmdlet and helps formalize the naming convention for cmdlets as being verb-noun combinations.  VerbsCommon lists the verbs that may be used:

FalafelCmdlet descends from Cmdlet but you can also use PSCmdlet.  Cmdlet is lighter-weight but PSCmdlet has more access to the PowerShell runtime. For this example the functionality would be the same so I will go with the lighter-weight Cmdlet.  The Contains property in this example holds a string used in searching food descriptions.  The Parameter attribute marks the Contains property as a parameter for the cmdlet, provides a help string and identifies Contains as not being mandatory.

Finally the ProcessRecord() method of Cmdlet is overridden to perform the actually work of the command.  In ProcessRecord() a class called MediterraneanFoods returns a generic list of MediterraneanFood objects based on description.  We won't get into the workings of MediterraneanFoods here because its purpose is to simply provide sample functionality for the command.  Note: Watch this space for a tasty blog by Lino on Anonymous Delegates that gets into how the generic list is searched.

The really cool part of ProcessRecord() is the WriteObject() method of Cmdlet.  Instead of Console.Writeline() text-only output, WriteObject() actually ouputs MediteraneanFood objects into the PowerShell pipeline.  This means that your objects are automatically usable by other commands.  You'll see this in a minute when we register and run the command. 

Here are the PowerShell commands I use to install and register the cmdlet:

cd C:\Clients\Falafel\Projects\FalafelCmdletLibrary\bin\Debug
set-alias iu $Env:windir\Microsoft.NET\Framework\v2.0.50727\installutil.exe
iu FalafelCmdletLibrary.dll
Add-PSSnapin FalafelSnapIn

The first line changes the directory to where the assembly for the FalafelCmdlet is stored.  Then, as a convenience you can use set-alias to make access to InstallUtil.exe easier.  The "IU" alias for InstallUtil installs the assembly.  This step produces a certain amount of logging I won't include here.  Finally the Add-PSSnapin makes the snap-in available to the current PowerShell console session.  You can call Get-PSSnapin to see the description and verify it's there:

Now we can run the "get-food" command, passing the "contains" parameter.  Notice the output by default is in table format.

Remember the call to WriteObject() that makes all our output actual objects instead of text?  Here's an example of piping the output of the one command that will easily work with an existing command without any adapting or parsing necessary to make it all work.  The "|" character is used to pipe the output from get-food to a format-list:

...and we have the output in list, not table, format.  Or we could pipe the output to the get-member command that performs reflection on objects passed to it.  You can see the MediterraneanFood object has Description and Name properties:

This all opens up possibilities for you to wrap existing .NET functionality in command-line form and to use the output in other existing commands.

At Tech Ed in Orlando last week John Waters picked up "Windows PowerShell Unleashed by Tyson Kopczynski" and as John has a eye for cool technology I did likewise and got hooked on PowerShell.  I'm not usually a live-on-the-command-line-love-batch-files kind of guy but PowerShell covers enough ground in a powerful and consistent way that I'm considering adding this to my technology toolbox.  Although PowerShell is intended for system administrator use, there may be a place for PowerShell in development to do limited testing against .NET objects, task automatation and general exploration.  In any case it's a great toy and a hoot to play with.

PowerShell has several important differences from cmd.exe. 

• PowerShell lets you use NET FCL objects, COM objects and even your own .NET classes.
• PowerShell scripts can be code signed (see Windows PowerShell Unleashed by Tyson Kopczynski for detailed steps).  Previous scripting environments like Windows Scripting Host (WSH) opened large security holes.  This feature allows scripts from trusted sources to be run.
• PowerShell is object based, not text based.  This eliminates parsing and reformatting to use the output from a command.
• PowerShell provides a consistent interface. 
  • You can navigate through files, the certificate store, environmental variables and the registry, all using commands you already know. 
  • Commands confirm to the pattern verb-name, i.e. "get-service" to cut down on memorization.  There are aliases for historic DOS and UNIX commands so you can list a directory with the PowerShell native "get-childitem", UNIX style "ls" or DOS "dir". 
  • PowerShell lets you locate and interrogate available commands and objects.
• PowerShell is extensible.  You can create your own commands in a .NET assembly and register them for use in PowerShell.  There are several other points of extensibility including providers for navigation, types and formatting.

Here's a sample session of PowerShell to give you a very brief notion of how it works.  Be aware that this is only scratching the surface of the possibilities for PowerShell.  Let's say we want to work with Windows services, so we need to know what commands are available:

PS C:\Clients\Falafel\Projects\FalafelCmdletLibrary\bin\Debug> get-command *service

CommandType     Name                                                Definition
-----------     ----                                                ----------
Cmdlet          Get-Service                                         Get-Service [[-Name] <String[]>] [-Include <Stri...
Cmdlet          New-Service                                         New-Service [-Name] <String> [-BinaryPathName] <...
Cmdlet          Restart-Service                                     Restart-Service [-Name] <String[]> [-Force] [-Pa...
Cmdlet          Resume-Service                                      Resume-Service [-Name] <String[]> [-PassThru] [-...
Cmdlet          Set-Service                                         Set-Service [-Name] <String> [-DisplayName <Stri...
Cmdlet          Start-Service                                       Start-Service [-Name] <String[]> [-PassThru] [-I...
Cmdlet          Stop-Service                                        Stop-Service [-Name] <String[]> [-Force] [-PassT...
Cmdlet          Suspend-Service                                     Suspend-Service [-Name] <String[]> [-PassThru] [...

From here we can see what services are available for SQL Server:

PS C:\Clients\Falafel\Projects\FalafelCmdletLibrary\bin\Debug> get-service MSSQL*

Status   Name               DisplayName
------   ----               -----------
Running  MSSQL$NRLAPTOP2    MSSQL$NRLAPTOP2
Running  MSSQL$SQLEXPRESS   SQL Server (SQLEXPRESS)
Running  MSSQL$TELERIK      MSSQL$TELERIK
Running  MSSQLSERVER        SQL Server (MSSQLSERVER)
Stopped  MSSQLServerADHe... SQL Server Active Directory Helper
Running  MSSQLServerOLAP... SQL Server Analysis Services (MSSQL...

Now we want to stop the MSSQLSERVER service and any dependant services:

PS C:\Clients\Falafel\Projects\FalafelCmdletLibrary\bin\Debug> stop-service "MSSQLSERVER" -force

If we re-run get-service we can see that the service is stopped:

PS C:\Clients\Falafel\Projects\FalafelCmdletLibrary\bin\Debug> get-service mssql*

Status   Name               DisplayName
------   ----               -----------
Running  MSSQL$NRLAPTOP2    MSSQL$NRLAPTOP2
Running  MSSQL$SQLEXPRESS   SQL Server (SQLEXPRESS)
Running  MSSQL$TELERIK      MSSQL$TELERIK
Stopped  MSSQLSERVER        SQL Server (MSSQLSERVER)
Stopped  MSSQLServerADHe... SQL Server Active Directory Helper
Running  MSSQLServerOLAP... SQL Server Analysis Services (MSSQL...

You can also interrogate the service objects using the get-member command. For example you could take the service objects returned by get-service and direct them to the get-member command using the "|" pipe symbol. The following is only a partial listing.

PS C:\Clients\Falafel\Projects\FalafelCmdletLibrary\bin\Debug> get-service | get-member

   TypeName: System.ServiceProcess.ServiceController

Name                      MemberType    Definition
----                      ----------    ----------
Name                      AliasProperty Name = ServiceName
Close                     Method        System.Void Close()
Continue                  Method        System.Void Continue()
CreateObjRef              Method        System.Runtime.Remoting.ObjRef CreateObjRef(Type requestedType)
Dispose                   Method        System.Void Dispose()
Equals                    Method        System.Boolean Equals(Object obj)
ExecuteCommand            Method        System.Void ExecuteCommand(Int32 command)
get_DependentServices     Method        System.ServiceProcess.ServiceController[] get_DependentServices()
get_DisplayName           Method        System.String get_DisplayName()
get_MachineName           Method        System.String get_MachineName()
get_ServiceHandle         Method        System.Runtime.InteropServices.SafeHandle get_ServiceHandle()

What about navigation?  If I want to change locations in the file system of course there's "CD" or the PS native "set-location". What's unique here is that you can navigate the registry, environmental variables, certificate stores or any other system that PS has a provider for (yes, you can write your own providers).  For example the following is perfectly legal:

PS C:\Clients\Falafel\Projects\FalafelCmdletLibrary\bin\Debug> cd env:
PS Env:\> dir

Name                           Value
----                           -----
Path                           C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\ATI Technolo...
TEMP                           C:\DOCUME~1\NOELRI~1\LOCALS~1\Temp
SESSIONNAME                    RDP-Tcp#1
PATHEXT                        .COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.PSC1
USERDOMAIN                     NRLAPTOP
PROCESSOR_ARCHITECTURE         x86

You could image using CD to navigate hiearchical database information in this way (to what practical end I do not know, but it is amusing). Find out what PowerShell drives are available on your machine by using get-psdrive:

PS C:\Clients\Falafel\Projects\FalafelCmdletLibrary\bin\Debug> get-psdrive

Name       Provider      Root                                                                           CurrentLocation
----       --------      ----                                                                           ---------------
Alias      Alias
C          FileSystem    C:\                                    Clients\Falafel\Projects\FalafelCmdletLibrary\bin\Debug
cert       Certificate   \
D          FileSystem    D:\
Env        Environment
Function   Function
HKCU       Registry      HKEY_CURRENT_USER
HKLM       Registry      HKEY_LOCAL_MACHINE
Variable   Variable

Use get-psprovider to find the providers on your system.  See the MSDN for examples of writing your own provider. 

PS C:\Clients\Falafel\Projects\FalafelCmdletLibrary\bin\Debug> Get-PSProvider

Name                 Capabilities                                      Drives
----                 ------------                                      ------
Alias                ShouldProcess                                     {Alias}
Environment          ShouldProcess                                     {Env}
FileSystem           Filter, ShouldProcess                             {C, D}
Function             ShouldProcess                                     {Function}
Registry             ShouldProcess                                     {HKLM, HKCU}
Variable             ShouldProcess                                     {Variable}
Certificate          ShouldProcess                                     {cert}

You can download PowerShell at http://www.microsoft.com/technet/scriptcenter/topics/msh/download.mspx.  It comes with the install and docs for "Getting Started", "Quick Reference" and "Users Guide".

There's a fascinating little article "Create Advanced Web Applications With Object-Oriented Techniques" by Ray Djajadinata that delves into how JavaScript implements objects, inheritance, anonymous methods, and even a bit about simulating private properties and namespaces.  This may be relevant if you want greater depth in AJAX, particularly the Microsoft flavor ASP.NET AJAX.  There's a sidebar (by Bertrand Le Roy) about the ASP.NET AJAX OOPS implementation and use of JavaScript to add reflection and other .NET familar constructs including properties, events, enumerations and interfaces.

We recently completed a self-paced tutorial for Telerik RadControls.

You can download the self paced tutorial from the Telerik site at:

http://www.telerik.com/support/self-paced-tutorial.aspx

The tutorial addresses the entire suite of RadControls, AJAX, client-side scripting, and custom data-binding techniques.

You can support docking in your web applications using Telerik's new control suite "Prometheus".  Prometheus is completely redesigned to use Microsoft's ASP.NET Ajax.  The Prometheus docking controls make it easy to define objects that may be dragged and areas where objects may be dragged to.  With docking support you can create web portal sites, "PageFlake" style web pages (where the user can dynamically add controls and drag them around on the page), or even "post-it notes" can be added to the page.  The current state-of-play is that the controls are in beta and have a few quirks, but the performance is very responsive and the look and feel is also quite good.

To use Prometheus "RadDock" controls first download and install the Microsoft ASP.NET Ajax extensions at http://ajax.asp.net/, then get the free Prometheus beta download at: http://www.telerik.com/products/aspnet-prometheus/download.aspx.  Once installed, create a new project type "ASP.NET AJAX-Enabled Web Application".  BTW, if you try to use the standard ASP.NET web application you will get really interesting results.

Your toolbox will have the new AJAX extension controls, including "ScriptManager".  ScriptManager is the workhorse of Microsoft's ASP.NET Ajax that registers client side scripts that enable AJAX functionality. The ScriptManager is automatically placed on the default web page so you don't need to do anything further there.

Also in the toolbox are the new Prometheus controls.  The three you need for drag and drop support are:

• RadDock is the container for text or other controls that need to be dragged on the web page.
• RadDockZone defines an area on the screen where a RadDock can dropped onto.
• RadDockLayout can contain a number of RadDockZone controls so you can set the skin for everything at once.  RadDockLayout also has a property StoreLayoutInViewState that can be used to persist the RadDock locations across multiple postbacks.

To test these controls drop a RadDockLayout, two RadDockZones within the RadDockLayout and RadDock controls in each of the RadDockZones. 

How about adding content to the RadDock? 

• Use the Radock Text property for simple text only that doesn't involve any other controls.
• Create your own ITemplate implementation class and set the RadDock ContentTemplate property to that class in the code behind.
• Add to the ContentTemplate property markup.  At this stage of development I don't see a smart tag or other UI assistance so instead add a ContentTemplate tag and add controls within the tag.

For example we could add another new Prometheus control "RadColorPicker":

<ContentTemplate>
  <telerik:RadColorPicker ID="RadColorPicker1" runat="server" Preset="Standard">
  </telerik:RadColorPicker>
</ContentTemplate>

To finish up we set the Skin property of RadDockLayout to "Longhorn".  If you've used the current version of RadDock you're used to adding various skin files to the project.  Not with the Prometheus version where the skins are built-in and you can choose the Skin property value from a drop down list.  Finally you can set the title bar text for each RadDock using the Title property.

When you run the Prometheus version of the docking controls you should experience snappy responsiveness and a very respectiable UI.  Try downloading Prometheus and retrofit some part of your current web application to support docking.  Enjoy!

Note: if you want to learn Telerik RadControls from the ground up, check out the Falafel-authored, self-paced tutorial at http://www.telerik.com/support/self-paced-tutorial.aspx and download the sample projects from our community download site at http://www.falafel.com/community/files/Default.aspx.