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().
2008-10-07
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.
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>
<?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.
2008-09-23
List Windows Shares using PowerShell
A list of shares on a given Windows host can be created by:
Get-WmiObject Win32_Share
But the two shares "IPC$" og "ADMIN$" is not really interesting.
A more usefull list can be created by:
Get-WmiObject -Query "SELECT * FROM Win32_Share WHERE Name != 'ADMIN$' AND Name != 'IPC$'"
I know it's not correct or nice to use "SELECT *...", but this is an quick and dirty example.
The WMI class definition can be browsed in WMI CIM Studio, that can be downloaded from Microsoft.
Please use your preferred internet search site to get the current URI.
Get-WmiObject Win32_Share
But the two shares "IPC$" og "ADMIN$" is not really interesting.
A more usefull list can be created by:
Get-WmiObject -Query "SELECT * FROM Win32_Share WHERE Name != 'ADMIN$' AND Name != 'IPC$'"
I know it's not correct or nice to use "SELECT *...", but this is an quick and dirty example.
The WMI class definition can be browsed in WMI CIM Studio, that can be downloaded from Microsoft.
Please use your preferred internet search site to get the current URI.
2008-09-15
Restore Workshop draft
Some day - no, week(-end) - I would like to gather some comrades in arms - SQL Server naturally! - and do a workshop on SQL Server database restore.
The initial list of subjects is like this:
Each group should have one workstation and three servers - virtual servers could do, but there should be enough disk capacity to create several partitions for each server.
The workshop should be placed away from a major city, so that the participants are not tempted to drop in and out... Of course the food and the housing should support the process and the good spirit.
The seats should be distributed to persons that did not participate before those did the subjects last year.
I think it could be fun and most enlightening!
Paul Randal has made list of good questions to ask yourself - „SQLskills SQL101: Practicing disaster recovery“
2017-07-18 Reference to Paul Randal added.
The initial list of subjects is like this:
- Restore on full backup
- Restore on log backup
- Restore on differential backup
- Restore point-in-time
- Restore online
- Restore Full-Text index
- Restore Filestream
- All above on both single and multiple filegroups
- Restore msdb
- Restore master
- Rebuild database instance
- Rebuild Windows Server
- Discuss how to get, store and access the actual version of operating system, applications, .NET, MDAC etc.
- Switch center using clustering, database mirroring and log shipping
- Validate backup - discuss how to do this automatic
- Discuss device file versus backup files
- Recover using snapshot
- Discuss documentation like how, where, what, tools, access and maintenance
- Discuss security about backup sets and snapshots and access to these
- A general discussion about SQL Server infrastructure from the viewpoint of recovery and restore
Each group should have one workstation and three servers - virtual servers could do, but there should be enough disk capacity to create several partitions for each server.
The workshop should be placed away from a major city, so that the participants are not tempted to drop in and out... Of course the food and the housing should support the process and the good spirit.
The seats should be distributed to persons that did not participate before those did the subjects last year.
I think it could be fun and most enlightening!
Paul Randal has made list of good questions to ask yourself - „SQLskills SQL101: Practicing disaster recovery“
History
2008-09-15 Post created on old notes.2017-07-18 Reference to Paul Randal added.
2008-08-14
ISO 8601 date formatting using PowerShell
My favorite timestamp is the ISO 8601 format [YYYY]-[MM]-[DD]T[hh]:[mm]:[ss.sss]Z.
The ISO 8601 standard is very well described in a Wikipedia article (ISO 8601).
I do miss a ISO formatting option in .NET/PowerShell, but using DateTime formatting, an acceptable result is found:
$theDate = Get-Date
"{0:yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fffffff'Z'}" -f $theDate.ToUniversalTime()
$theDate.ToUniversalTime().ToString( "yyyy-MM-ddTHH:mm:ss.fffffffZ" )
The formatting is shown twice using different syntax.
I've chosen seven digits on the second because I have logging in mind while working with time stamp. I have (once) seen that three digits wasn't enough - it was in a technical database log on a z/Server "mainframe".
A execution gives this result:
2008-01-13T10:02:36.8992896Z
I would like to get the week of the year, but again this is a problem. Is it because a week number is more used in Europe than on the other side of the Atlantic?
Thomas Lee has tried (eggheadcafe conversation), and I tried his examples. I regret to say he's right – again.
Using the formatting specifier 'o' (oscar) from the DateTimeFormatInfo Class to the CmdLet Get-Date will generate a output with seven digits, but also a a part with a reference to the timezone. Mark Puckett has made a comment to this post about this possibility. And he is right that this specifier wil give a output that is very precise. But I choose not to use this as I go for UTC time in a log as I think it makes the log entries more readable on senior management level.
2017-02-05 Section on -Format o added inspired by comment by Mark Puckett.
The ISO 8601 standard is very well described in a Wikipedia article (ISO 8601).
I do miss a ISO formatting option in .NET/PowerShell, but using DateTime formatting, an acceptable result is found:
$theDate = Get-Date
"{0:yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fffffff'Z'}" -f $theDate.ToUniversalTime()
$theDate.ToUniversalTime().ToString( "yyyy-MM-ddTHH:mm:ss.fffffffZ" )
The formatting is shown twice using different syntax.
I've chosen seven digits on the second because I have logging in mind while working with time stamp. I have (once) seen that three digits wasn't enough - it was in a technical database log on a z/Server "mainframe".
A execution gives this result:
2008-01-13T10:02:36.8992896Z
I would like to get the week of the year, but again this is a problem. Is it because a week number is more used in Europe than on the other side of the Atlantic?
Thomas Lee has tried (eggheadcafe conversation), and I tried his examples. I regret to say he's right – again.
Using the formatting specifier 'o' (oscar) from the DateTimeFormatInfo Class to the CmdLet Get-Date will generate a output with seven digits, but also a a part with a reference to the timezone. Mark Puckett has made a comment to this post about this possibility. And he is right that this specifier wil give a output that is very precise. But I choose not to use this as I go for UTC time in a log as I think it makes the log entries more readable on senior management level.
History
2008-01-14 Blog post created2017-02-05 Section on -Format o added inspired by comment by Mark Puckett.
SQL Server Agent PowerShell job step
This seems like a valid and simple way to execute a PowerShell script in a SQL Server Agent job step.
The script I've been testing with looks like this (ExecuteablePS.ps1):
param( [string]$script:myParam = $( throw "Error: Command statement is missing." ) )
Write-Output "Hello to output."
Write-Host "Hello to host."
Write-Output "The parameter 'myParam' was given the value '$myParam'."
It is on purpose that the script requires a parameter, while most - if not all - my automation scripts is parametrized on the physical elements like server (host).
The script also demonstrates that both the Output stream and the Host stream is caught by SQL Server Agent.
The SQL Server Agent job step is defined as a CmdExec step.
The step only has one line:
powershell.exe -NoLogo -NoProfile -Command "&'C:\SQLAdmin\Script\ExecuteablePS.ps1' 'CoolValue'"
I spend some time getting the " and ' right...
When the job is executed, the history is:
Executed as user: Sandbox\Frodo. Hello to output. Hello to host. The parameter 'myParam' was given the value 'CoolValue'. Process Exit Code 0. The step succeeded.
I use CmdExec in stead of PowerShell as job step type, so that the solution can be used below SQL Server 2008.
Also I like to use powershell.exe instead of sqlps.exe. This makes the solution valid on PowerShell v2.
The script I've been testing with looks like this (ExecuteablePS.ps1):
param( [string]$script:myParam = $( throw "Error: Command statement is missing." ) )
Write-Output "Hello to output."
Write-Host "Hello to host."
Write-Output "The parameter 'myParam' was given the value '$myParam'."
It is on purpose that the script requires a parameter, while most - if not all - my automation scripts is parametrized on the physical elements like server (host).
The script also demonstrates that both the Output stream and the Host stream is caught by SQL Server Agent.
The SQL Server Agent job step is defined as a CmdExec step.
The step only has one line:
powershell.exe -NoLogo -NoProfile -Command "&'C:\SQLAdmin\Script\ExecuteablePS.ps1' 'CoolValue'"
I spend some time getting the " and ' right...
When the job is executed, the history is:
Executed as user: Sandbox\Frodo. Hello to output. Hello to host. The parameter 'myParam' was given the value 'CoolValue'. Process Exit Code 0. The step succeeded.
I use CmdExec in stead of PowerShell as job step type, so that the solution can be used below SQL Server 2008.
Also I like to use powershell.exe instead of sqlps.exe. This makes the solution valid on PowerShell v2.
2008-08-13
Write to user defined file with PowerShell
I'm working on a general solution for PowerShell job execution on SQL Server Agent. This generates a lot of small tasks, which mostly will be described here with a solution.
Right now it's about logging, and for this I'm thinking in several directions.
This task is for a solution using files, that are defined in the executing script.
The script writes to the same file in each execution, but the file will only contain the last execution:
$FileName = $MyInvocation.MyCommand.Name.split(".")[0] + ".Log"
Write-Host $FileName
"Hello to file..." > $FileName
"Another hello to file" >> $FileName
The execution generates this content (Get-Content Write2File.Log):
Hello to file...
Another hello to file
Another execution gives the same content - two lines, not four.
Right now it's about logging, and for this I'm thinking in several directions.
This task is for a solution using files, that are defined in the executing script.
The script writes to the same file in each execution, but the file will only contain the last execution:
$FileName = $MyInvocation.MyCommand.Name.split(".")[0] + ".Log"
Write-Host $FileName
"Hello to file..." > $FileName
"Another hello to file" >> $FileName
The execution generates this content (Get-Content Write2File.Log):
Hello to file...
Another hello to file
Another execution gives the same content - two lines, not four.
PowerShell include
Inclusion of a script file in a PowerShell script is done with dot-include.
Given the file "Include.ps1":
Write-Output "Hello from Include.ps1"
and the file "Test-Include.ps1":
Write-Output "Hello from Test-Include.ps1"
.'D:\My Documents\SQLAdmin\Script\Include.ps1'
An execution from a PowerShell prompt in the path "D:\My Documents\SQLAdmin\Script":
PS D:\My Documents\SQLAdmin\Script> .\Test-Include.ps1
Hello from Test-Include.ps1
Hello from Include.ps1
PS D:\My Documents\SQLAdmin\Script>
An execution from another path:
PS D:\> .'D:\My Documents\SQLAdmin\Script\Test-Include.ps1'
Hello from Test-Include.ps1
Hello from Include.ps1
PS D:\>
An execution from Windows Shell (cmd.exe):
H:\>powershell.exe .'H:\My Documents\SQLAdmin\Script\Test-Include.ps1'
Hello from Test-Include.ps1
Hello from Include.ps1
H:\>
These examples are delibrately using a path with a space in a folder name.
Given the file "Include.ps1":
Write-Output "Hello from Include.ps1"
and the file "Test-Include.ps1":
Write-Output "Hello from Test-Include.ps1"
.'D:\My Documents\SQLAdmin\Script\Include.ps1'
An execution from a PowerShell prompt in the path "D:\My Documents\SQLAdmin\Script":
PS D:\My Documents\SQLAdmin\Script> .\Test-Include.ps1
Hello from Test-Include.ps1
Hello from Include.ps1
PS D:\My Documents\SQLAdmin\Script>
An execution from another path:
PS D:\> .'D:\My Documents\SQLAdmin\Script\Test-Include.ps1'
Hello from Test-Include.ps1
Hello from Include.ps1
PS D:\>
An execution from Windows Shell (cmd.exe):
H:\>powershell.exe .'H:\My Documents\SQLAdmin\Script\Test-Include.ps1'
Hello from Test-Include.ps1
Hello from Include.ps1
H:\>
These examples are delibrately using a path with a space in a folder name.
Subscribe to:
Posts (Atom)
Posts
