Interacting with SQL Server

On this page we will go through how to interact with any version of SQL Server using native functionality. We will be using a logon audit table as an example. The table is called "Logons" in a database called "LogonAudit" on an SQL Server with the named pipes name of "AcmeServer\SQL". The table looks like this, where the LogonID has Identify Specification set, to automatically fill in an ID:

Logon table

SQL Server Scripting

The ExecSQL command

As part of the logon script, we would want to log in the audit table, who logs on. We can use the ExecSQL command for doing this by simply going:

ExecSQL "Insert Into Logons (UserName,LogonTime) Values ('[UserName]','[SqlDateTime]')",AcmeServer\SQL,LogonAudit

This will insert a new record into the "Logons" table. We are using the functions "UserName" and "SQLDateTime" to get the correct values to insert. Observe that according to the Transact-SQL syntax, values must be in apostrophes ('). And generally speaking, remember to always quote your SQL statements, as they will in many cases contain at least one comma, as is also the case in the above example.

On this page, we are assuming the script executing users have access to the database. All native SQL Server features can also take username and password parameters. If these are not set, Windows Authentications is used.

The important part to understand is that we can use any type of Transact-SQL statement with the ExecSQL command and we can also issue more than one statement by separating by semi-colon or new-line, just like we can do in an SQL window in the SQL Server Management Studio. This means that if we wanted to call a stored procedure instead, we would just use the Transact-SQL command "Exec". If we had a stored procedure to insert the record instead, it could look like this:

CREATE PROCEDURE [dbo].[InsertLogon] (

  @UserName nvarchar(50)

) AS

  INSERT Into Logons (UserName,LogonTime) Values (@UserName,GETDATE());

The point of wrapping this inside a stored procedure would of course be to add more logic, but we will use it to exemplify use of stored procedures. Our stored procedure can now be used by simply issuing a normal Transact-SQL Exec command:

ExecSQL "Exec InsertLogon '[UserName]'",AcmeServer\SQL,LogonAudit

Another example could be a script that would run daily to backup our database. Part of this script, could look like this:

ExecSQL "BACKUP DATABASE LogonAudit TO  DISK = N'C:\MyBackup.bak'",AcmeServer\SQL

Here we use the "Backup Database" command to issue a backup of our database. An expanded version can be found here that keeps a series of backups for a number of days.

If we needed a more advanced backup script, we could add more ExecSQL lines. But it could be a better idea to create a file with a list of commands and simply feed it to the ExecSQL command by using the "FileContent" function. We could have a file called "Backup.sql" that looks like this:
BACKUP DATABASE AcmeMusic TO DISK = N'W:\AcmeMusic.bak'
BACKUP DATABASE AcmeTravel TO DISK = N'W:\AcmeTravel.bak'
And to get this executed, we can simply use the "FileContent" function to retrieve the content to execute:

ExecSQL [FileContent Backups.sql],AcmeServer\SQL,LogonAudit

The SQLQuery function

The SQLQuery function is used to retrieve a single value from a single column. If we wanted to know when the last logon was for the current user, we could set a variable like this:

Set LastLogon = [SQLQuery "SELECT LogonTime FROM Logons WHERE UserName='[UserName]' ORDER BY LogonTime Desc",AcmeServer\SQL,LogonAudit]

The variable will be empty, if no records match our query. It would have been best practice to use "TOP 1" after the "Select" statement, but we have not done this here, to demonstrate that multiple values can get returned, but only the first one will be used.

Note how the column to retrieve the value of in the query is explicit by using "Select LogonTime ...". If we instead used "Select * ...", then the value of the first column of the table would be returned. In theory, you can't be sure that SQL Server will serve the first column first, so always explicitly select one column.

As with the ExecSQL command, this could have been wrapped in a stored procedure that could look like this:

CREATE PROCEDURE [dbo].[GetLastLogon](

  @UserName nvarchar(50)

) AS

  SELECT TOP 1 LogonTime FROM Logons WHERE UserName=@UserName ORDER BY LogonTime Desc

To extract the returned value from the stored procedure, we must use the "Exec" command again as part of our query:

Set LastLogon = [SQLQuery "Exec GetLastLogon '[UserName]'",AcmeServer\SQL,LogonAudit]

The SQLValues collection

The SQLValues collection is similar to the SQLQuery function except that it is a collection populated by retrieving all returned values from a single column. As SQLValues is a collection, we can use a ForEach or Loop construct to loop through each value. In this example we will just use the collection to feed to a list to show all logons for the current user:

List Logons for [UserName]:,[SQLValues "SELECT LogonTime FROM Logons WHERE UserName='[UserName]' ORDER BY LogonTime Desc",AcmeServer\SQL,LogonAudit]

Running this script will produce an ordered list of all logons that could look like this:

Logon table

As with the SQLQuery function, we could issue the "Exec" command instead to get the values from a stored procedure.

Retrieving multiple values from a multiple columns

In most cases, the features listed above will do for a systems administrator's typical tasks. If you need to get multiple values from multiple columns, or your data source is not SQL Server, then you need to revert to using COM, as known from most other scripting languages.

Our logon audit table is very simple, but suppose it had 50 columns (or it's on a different database server type, like an Oracle database), then it would not be optimal to issue a stack of SQLQuery functions to get the value of each column into for example variables. If we wanted to get the value of multiple columns on the latest logon record, we need to use ActiveX Data Objects:

SetObject SQL = CreateObject("ADODB.connection")

Exec SQL.Open("Provider=SQLNCLI10;Server=AcmeServer\SQL;Database=LogonAudit;Trusted_Connection=yes;")

SetObject LogonRecord = SQL.Execute("SELECT TOP 1 * FROM Logons WHERE UserName='[UserName]' ORDER BY LogonTime Desc")

Set LogonID = [ObjectValue LogonRecord.LogonID]

Set LogonTime = [ObjectValue LogonRecord.UserName]

Exec SQL.Close

In this example, we have created a COM object called "LogonRecord". Each column can be accessed as a property by using the "ObjectValue" function on the object. The columns can be referenced as properties on the object. Alternatively, the value can be extracted by using the collection methods on the recordset object, for example "LogonRecord.Fields.Item(UserName).Value" instead of "LogonRecord.UserName".

If we needed to loop through all the logon records, meaning multiple columns on multiple records, we can expand the example to look like this:

SetObject SQL = CreateObject("ADODB.connection")

Exec SQL.Open("Provider=SQLNCLI10;Server=AcmeServer\SQL;Database=LogonAudit;Trusted_Connection=yes;")

SetObject Logons = SQL.Execute("SELECT * FROM Logons WHERE UserName='[UserName]' ORDER BY LogonTime Desc")

While [ObjectValue Logons.EOF]=false

  Set LogonID = [ObjectValue Logons.LogonID]

  Set LogonTime = [ObjectValue Logons.UserName]

  ''.... do something with the variables

  Exec Logons.MoveNext

End While

Exec SQL.Close

Rating: 5 out of 5

"Use this as a replacement for VBScript and PowerShell"

"It's easy to include attractive GUI elements in FastTrack scripts, beyond the basic dialog boxes and text input that VBScript offers ... Another powerful feature is the ability to distribute scripts as Windows Installer (.msi) or standard .exe files. Although interesting in its own right, this ability results in a much more intriguing capability: to repackage -- or wrap -- software installers as .msi files without using snapshots. If you've ever created an .msi installer file from before-and-after system snapshots, for use with a software distribution system such as Group Policy or SCCM, then you know how hit-and-miss the results can be."

Read full review

Rating: 8 out of 10

"Faster than the rest"

"We found the FastTrack syntax to be more transparent and easier to learn than Microsoft's PowerShell – the editor in particular provided good support in this regard. the Script Editor offers a large number of options from the command set through to simple output of graphical elements, which cannot be achieved at all with PowerShell or other solutions or only with a significantly greater level of effort."

"Anyone wanting to tackle the many hurdles in everyday admin and especially anyone for whom logon scripts and client automation is a priority will benefit from the variety of functions offered by FastTrack."

Review in English      Review in German