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:
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