Having worked on a (new) backup solution for my employer, I have spend several hours fighting with examples not working.
Until I found the explanation and a solution at Allen Whites blog: http://sqlblog.com/blogs/allen_white/archive/2008/12/07/loading-smo-assemblies-into-powershell.aspx
I still wonder why the the classes listed in another blog entry by Allen (http://sqlblog.com/blogs/allen_white/archive/2007/10/19/sql-2008-smo-doing-a-little-rearranging.aspx) were moved and then destroying the compability between SQL 2005 and SQL 2008...
2008-12-18
2008-11-03
HTA dynamic document II
The solution I sketched out 2008-10-07 I found out had one major fault - the entire DOM document was cleared, which made the solution a little too dynamic. For example also script tags was cleared...
Today I found an article about making a GUI using HTA: "Extreme Makeover: Wrap Your Scripts Up in a GUI Interface". This inspired me to play a little with the innerHTML attribute.
The script 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" />
<style>
h1, h2, p { font-family:sans-serif }
</style>
</head>
<script type="text/jscript" language="jscript">
function newContent() {
var markup = "<p>Navigare necesse est!<p>"
return markup;
}
function jsContent() {
myArea.innerHTML = "<h2>jsContent</h2>" + newContent();
}
</script>
<script type="text/vbscript" language="vbscript">
Sub vbContent_onclick
markup = "<p>Vivere non est necesse,</p>"
myArea.innerHTML = "<h2>vbContent</h2>" + markup + newContent()
End Sub
</script>
<body>
<h1>Dynamic Document</h1>
<input type="button" value="JScript content" onclick="jsContent()">
 
<input type="button" value="VBScript content" name="vbContent">
<span id="myArea"></span>
<p style="font-size:smaller">© 2008, Niels Grove-Rasmussen — SQLAdmin.dk</p>
</body>
</html>
An execution of the script starts like this:
Clicking "JScript content" alter the document to this:
Clicking "VBScript content" alter the document to this:
If "JScript content" is clicked again, the document looks like the second image.
The trick is in two parts:
Today I found an article about making a GUI using HTA: "Extreme Makeover: Wrap Your Scripts Up in a GUI Interface". This inspired me to play a little with the innerHTML attribute.
The script 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" />
<style>
h1, h2, p { font-family:sans-serif }
</style>
</head>
<script type="text/jscript" language="jscript">
function newContent() {
var markup = "<p>Navigare necesse est!<p>"
return markup;
}
function jsContent() {
myArea.innerHTML = "<h2>jsContent</h2>" + newContent();
}
</script>
<script type="text/vbscript" language="vbscript">
Sub vbContent_onclick
markup = "<p>Vivere non est necesse,</p>"
myArea.innerHTML = "<h2>vbContent</h2>" + markup + newContent()
End Sub
</script>
<body>
<h1>Dynamic Document</h1>
<input type="button" value="JScript content" onclick="jsContent()">
 
<input type="button" value="VBScript content" name="vbContent">
<span id="myArea"></span>
<p style="font-size:smaller">© 2008, Niels Grove-Rasmussen — SQLAdmin.dk</p>
</body>
</html>
An execution of the script starts like this:
Clicking "JScript content" alter the document to this:
Clicking "VBScript content" alter the document to this:
If "JScript content" is clicked again, the document looks like the second image.
The trick is in two parts:
- The span-section with the identifier "myArea".
- Adding a string to the innerHTML attribute of the span-section.
2008-10-27
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...
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...
2008-10-21
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.
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.
2008-10-15
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().
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().
Subscribe to:
Posts (Atom)
Posts
