Management Studio 2005 registered servers

Unfortunaly it is not possible to share the registered servers in Management Studio 2005 while the file "RegSrvr.xml" can only be placed in the folder "%USERPROFILE%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell".
A solution could be to generate the registered servers file dynamic using MSXML and JScript.
This is a basic example on how to generate a registered servers file with one database instance:

var ProgId = "MSXML2.DOMDocument.3.0";
var XmlDoc = new ActiveXObject( ProgId );
XmlDoc.async = false;
XmlDoc.validateOnParse = false;
XmlDoc.resolveExternals = false;

var XmlNode= XmlDoc.createProcessingInstruction( "xml", "version='1.0' encoding='utf-8'" );
XmlDoc.appendChild( XmlNode );

XmlNode = XmlDoc.createElement( "RegisteredServers" );

var ServerType = XmlDoc.createElement( "ServerType" );
var ServerTypeId = XmlDoc.createAttribute( "id" );
ServerTypeId.value = "8c91a03d-f9b4-46c0-a305-b5dcc79ff907";
ServerType.setAttributeNode( ServerTypeId );
var ServerTypeName = XmlDoc.createAttribute( "name" );
ServerTypeName.value = "Database Engine";
ServerType.setAttributeNode( ServerTypeName );

var Server = XmlDoc.createElement( "Server" );
var ServerName = XmlDoc.createAttribute( "name" );
ServerName.value = "SANDBOX\\SSDB\_0";
Server.setAttributeNode( ServerName );
var ServerDescription = XmlDoc.createAttribute( "description" );
ServerDescription.value = "Primary sandbox database instance";
Server.setAttributeNode( ServerDescription );

var ConnectionInformation = XmlDoc.createElement( "ConnectionInformation" );

var SrvType = XmlDoc.createElement( "ServerType" );
SrvType.text = ServerTypeId.value;
ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( SrvType );

var SrvName = XmlDoc.createElement( "ServerName" );
SrvName.text = "tcp:SANDBOX.SQLAdmin.dk,1521";
ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( SrvName );

var AuthenticationType = XmlDoc.createElement( "AuthenticationType" );
AuthenticationType.text = "0";
ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( AuthenticationType );

ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( XmlDoc.createElement( "UserName" ) );

ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( XmlDoc.createElement( "Password" ) );

var AdvancedOptions = XmlDoc.createElement( "AdvancedOptions" );

var PacketSize = XmlDoc.createElement( "PACKET_SIZE" );
PacketSize.text = "4096";
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t\t" ) );
AdvancedOptions.appendChild( PacketSize );

var ConnectionTimeout = XmlDoc.createElement( "CONNECTION_TIMEOUT" );
ConnectionTimeout.text = "15";
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t\t" ) );
AdvancedOptions.appendChild( ConnectionTimeout );

var ExecTimeout = XmlDoc.createElement( "EXEC_TIMEOUT" );
ExecTimeout.text = "0";
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t\t" ) );
AdvancedOptions.appendChild( ExecTimeout );

var EncryptConnection = XmlDoc.createElement( "ENCRYPT_CONNECTION" );
EncryptConnection.text = "False";
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t\t" ) );
AdvancedOptions.appendChild( EncryptConnection );
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );

ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( AdvancedOptions );
ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t" ) );

Server.appendChild( XmlDoc.createTextNode( "\n\t\t\t" ) );
Server.appendChild( ConnectionInformation );
Server.appendChild( XmlDoc.createTextNode( "\n\t\t" ) );

ServerType.appendChild( XmlDoc.createTextNode( "\n\t\t" ) );
ServerType.appendChild( Server );
ServerType.appendChild( XmlDoc.createTextNode( "\n\t" ) );

XmlNode.appendChild( XmlDoc.createTextNode( "\n\t" ) );
XmlNode.appendChild( ServerType );
XmlNode.appendChild( XmlDoc.createTextNode( "\n" ) );
XmlDoc.appendChild( XmlNode );

XmlDoc.save( "RegSrvr.xml" );

Please notice that the server name to be displayed has extra "\" to get the right output. Also there are added some newline and tab code to get a readable result:

<?xml version="1.0" encoding="utf-8"?>
<registeredservers>
 <servertype id="8c91a03d-f9b4-46c0-a305-b5dcc79ff907" name="Database Engine">
  <server name="SANDBOX\SSDB_0" description="Primary sandbox database instance">
   <connectioninformation>
    <servertype>8c91a03d-f9b4-46c0-a305-b5dcc79ff907</servertype>
    <servername>tcp:SANDBOX.SQLAdmin.dk,1521</servername>
    <authenticationtype>0</authenticationtype>
    <username/>
    <password/>
    <advancedoptions>
     <PACKET_SIZE>4096</PACKET_SIZE>
     <CONNECTION_TIMEOUT>15</CONNECTION_TIMEOUT>
     <EXEC_TIMEOUT>0</EXEC_TIMEOUT>
     <ENCRYPT_CONNECTION>False</ENCRYPT_CONNECTION>
    </advancedoptions>
   </connectioninformation>
  </server>
 </servertype>
</registeredservers>

If you have a repository on you installed SQL Server services like Microsoft System Center Operations Manager (SCOM) you could use that as source in a extended version of the script above.
If you want to extend the script to generate registrations of instances of Integration Services, Analysis Services or Reporting Services, you can first register one instance of each manually to get the needed Server Types.

MSXML is documented by Microsoft at MSDN Library > Win32 and COM Development > Data Access and Storage > MSXML.

The script can only generate RegSrvr.xml for Management Studio 2005 while the format for Management Studio 2008 is rather different - but more on that another day...

Executing a remote HTA file

By default it is not possible to execute a HTML Application (HTA) file placed remote like in a fileserver share.
When a colleague has created a silver bullet HTA file it is tempting to copy the file to a local disk. But then I miss when great new stuff is added :-(

This can be solved by two extra files: A Windows Shell (cmd) file placed together with the HTA file and a local shortcut for the cmd file.

The cmd file only needs three lines:
COPY /Y \\SANDBOX\SQLAdmin\SilverBullet.hta %TEMP%\SilverBullet.hta

START /WAIT mshta.exe %TEMP%\SilverBullet.hta

DEL %TEMP%\SilverBullet.hta

<2008-10-27>Dennis is absolutely right about the DEL statement, which is now corrected.</2008-10-27>

The usage of the environmental variable %TEMP% makes the cmd file usefull also for users that are not local administrator on the workstation.

The shortcut can also be placed together with the HTA file, so changes from default settings can be shared. For example the execution can be set to a minimized window so that the cmd.exe window does not show on the users desktop.

WhoAmI database view

Sometimes it can be useful to be able to know the result of a database connection.
This can be done by creating the view [dbo].[WhoAmI] in most or each database. The view can be defined as
CREATE VIEW [dbo].[WhoAmI] AS
SELECT
@@SERVERNAME AS 'ServerName'
,@@SERVICENAME AS 'InstanceName'
,@@VERSION AS 'SqlVersion'
,DB_NAME() AS 'DatabaseName'
,SUSER_SNAME() AS 'UserLogin'
,HOST_NAME() AS 'ClientName'
,APP_NAME() AS 'ApplicationName'
,ORIGINAL_LOGIN() AS 'OriginalLogin'
,USER_NAME() AS 'DatabaseUserName'

And then grant SELECT to the database role [public] by
GRANT SELECT ON [dbo].[WhoAmI] TO [public];
Normally I would ban the use of the database role [public], but this is an usage I could accept.

Such a view was very useful debugging a system using Oracle on LINUX, SQL Server and Oracle Transparant Gateway (TG). We had several connectivity problems, and were glad to be able to find out which database and in which context we actually got the connection.

If you use extended properties on your databases, you could include these in the view by using the function fn_listextendedproperty().

SQL Server Agent PowerShell job step

I have had some problems executing a PowerShell script in a SQL Server Agent job step.
The errormessage I got – several times – was
Executed as user: SYS\ServiceDefaultSQL. The process could not be created for step 1 of job 0x5D25506B0FA13F45A770C7CD8B3BA051 (reason: The system cannot find the file specified). The step failed.
After some experiments I thought there was a NTFS security problem, so that the domain account the SQL Server Agent service was running in didn't have access rights to the PowerShell filestructure. At my test environment PowerShell is installed after the SQL Server services.
While I was browsing the NTFS rights, I asked a friend (Jakob Bindslet). He pointed me right to the solution.

The solution is somewhat awkward, while it's based on calling cmd.exe inside a CmdExec job step with the command
cmd.exe /c %SystemRoot%\system32\windowspowershell\v1.0\powershell.exe -NoLogo -NoProfile -Noninteractive "&'Z:\SQLAdmin\Jobs\HelloWorld.ps1'"
After beeing pointed in the right direction, I found out this is also working
C:\WINDOWS\system32\windowspowershell\v1.0\powershell.exe -NoLogo -NoProfile -Noninteractive "&'T:\SQLAdmin\Jobs\HelloWorld.ps1'"

Well – actually the documentation in Books Online clearly states that I "must provide the full path to the executable if the executable is not located in a directory specified in the system path or the path for the user that the job step runs as." (Creating Job Steps).
I think a downside could be the static path, that might not be alike for other Windows hosts.
But at my job we are using a single SQL Server Agent service for the entire production environment. This makes the last statement with the full PowerShell path usefull.

It does not work when using %ComSpec% or %SystemRoot%\system32\ because the environmental variables is not known to the SQL Server Agent job step.
BTW - the variables known to a job step is called tokens and macros. You can read more in Books Online > Using Tokens in Job Steps.

HTA dynamic document

From time to time I still use HTA (HTML Application) to create a quick (and dirty) user interface for minor automation tasks. This is a quick example on how a dynamic document can be created and used.

The start document looks like this:

After clicking "New Document" the document looks like this:

The code is written in JScript and looks like this:

<?xml version=1.0" encoding=utf-8"?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Strict//EN">
<html>
<head>
 <title>Dynamic Document</title>
 <hta:application id="htaDynamicDocument" applicationname="DynamicDocument">
 <script type="text/javascript" language="javascript">
function writeNewDoc() {
 writeHeader();
 document.writeln("<p>This is a new document.</p>");
 writeFooter();
}
function writeHeader() {
 document.clear();
 document.writeln("<h1>Dynamic Document</h1>");
}
function writeFooter() {
 document.writeln("<p>© 2008, Niels Grove-Rasmussen</p>");
}
function startDocument() {
 writeHeader();
 var MarkUp = "<p>";
 MarkUp += '<input type="button" value="New Document" ';
 MarkUp += 'onclick="writeNewDoc();" ';
 MarkUp += 'title = "Open new document." />';
 MarkUp += "</p>";
 document.writeln(MarkUp);
 writeFooter();
}
 </script>
</head>

<body>
 <script type="text/jscript" language="jscript">
  startDocument();
 </script>
</body>
</html>

HTA is described by Microsoft at HTA Developers Center.