, Work in progress. You are welcome to add comments.
Task:
Using sqlcmd.exe together with Windows Task Scheduler to run an SQL file or query against an SQL database and output the results to a file.
Files needed:
On the SQL server, SQLCMD.EXE – included in the installation folder of your SQL server
On the client machine, SQLCMD.EXE – client to be used from a remote location such as the Orion server
Test Setup:
Windows 2008 R2 SP1 (Orion)
Windows 2008 R2 SP1 (SQL 2008 Ent) X64
Windows 2008 R2 SP1 (SQL 2005 Exp) X64
- The Basics
1. Log on to you SQL server using SQL Management Studio
2. Select the SQL server instance then click on New Query
3. In the query view type:
select @@version
go
4. Execute
5. The results will display the version information of the currently selected SQL server
- Executing the query using SQLCMD.EXE
1. Go to the folder where sqlcmd.exe is located.
For SQL 2005: X:\Program Files\Microsoft SQL Server\90\Tools\Binn
For SQL 2008: X:\Program Files\Microsoft SQL Server\100\Tools\Binn
You may want to look in the Program File(x86) folders if not running native 64 bit SQL server.
2. Create a file called myfirstsqlcmd.bat
3. Create a file called myfirstsqlcmdquery.sql
What you want to do now is create a batch file (.bat) which will execute a SQL query found in the SQL query file (.sql).
- Contents of the batch file
Since we are running this locally, the contents of the batch file will be minimal. Here’s the contents of the batch file I created that will run the sql query found in the sql query file.
sqlcmd -S SQLSERVER -U sa -P Password -i myfirstsqlcmdquery.sql -o sql_version.txt
Syntaxes explained:
-a packet_size
-A (dedicated administrator connection)
-b (terminate batch job if there is an error)
-c batch_terminator
-C (trust the server certificate)
-d db_name
-e (echo input)
-E (use trusted connection)
-f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]
-h rows_per_header
-H workstation_name
-i input_file
-I (enable quoted identifiers)
-k[1 | 2] (remove or replace control characters)
-K application_intent
-l login_timeout
-L[c] (list servers, optional clean output)
-m error_level
-N (encrypt connection)
-o output_file
-p[1] (print statistics, optional colon format)
-P password
-q "cmdline query"
-Q "cmdline query" (and exit)
-r[0 | 1] (msgs to stderr)
-R (use client regional settings)
-s col_separator
-S [protocol:]server[\instance_name][,port]
-t query_timeout
-u (unicode output file)
-U login_id
-v var = "value"
-V error_severity_level
-w column_width
-W (remove trailing spaces)
-x (disable variable substitution)
-X[1] (disable commands, startup script, environment variables and optional exit)
-y variable_length_type_display_width
-Y fixed_length_type_display_width
-z new_password
-Z new_password (and exit)
- Contents of the SQL query file
The contents of this file should be the exact query you would execute in order to get what you need from the SQL server/instance/table. Here’s the contents of the sql file I created. This file contains the SQL query used in The Basics step.
select @@VERSION
go
- Running the batch file
You can now test the batch file to see if it works as it should. If it does, you’ll find that a new file named sql_version.txt has been created in your working folder.
You can now create a Windows Task that will run the batch file when you see fit.
- Creating the same steps from a remote client machine (Orion)
Before you proceed you will need the SQLCMD.EXE file installed on the client machine. The file can be downloaded at the following locations.
SQL 2005: http://www.microsoft.com/en-us/download/details.aspx?id=15748
SQL 2008 R2: http://www.microsoft.com/en-us/download/details.aspx?id=26728
If the above links are expired, search online for "SQL 20XX Feature Pack"
Once the SQL client software has been installed, you can proceed to carry out the exact same steps as previously mentioned. If you wish to run a query against the Orion database, make sure to add the –d syntax which specifies the name of the database.
Here’s an example in which I am running a query against an Orion database to gather node change details.
Batch File: LAST_CHANGE.BAT
Contents:
SQLCMD -S SQLSRV\INSTANCE,PORT –d SolarWindsOrion -U <domain\sqluser_or_sqluser> -P <sqluserpassword> –i LAST_CHANGE.SQL -o C:\NPM_REPORTS\LAST_CHANGE.TXT
SQL Query File: LAST_CHANGE.SQL
Contents:
Select NodeID, Event_Type_Name, Event_Type, EventTypeIcon, Cast(Message As nvarchar(250)) as Message, Event_Time From ( SELECT TOP 10
Nodes.NodeID AS NodeID,
Events_EventTypes.Name AS Event_Type_Name,
Events.EventType AS Event_Type,
( STR(Events.EventType) + '.gif') AS EventTypeIcon,
Events.Message AS Message,
Events.EventTime AS Event_Time
FROM
Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)
WHERE
( EventTime BETWEEN 40989 AND 40996.625 )
AND
(
(Events.Message LIKE '%changed%')
)
ORDER BY 6 DESC
) As r
- Sending the file by mail
It should be possible to link Windows Tasks together where one task would send an email after another task completes. You are able to attach files in the email and you could attach the output file. I haven’t tried this myself as I have a mail client installed on the client servers that does that for me –SendEmail