CODEWITHSUNDEEP
sql-servert-sqlbatch-file
SNR
SNR

Reputation: 43

How to automate command prompt command in SQL Server?

I am using the below command to load performance counters into SQL Server database.

C:\Users\w8>TYPEPERF -f SQL -s admin -cf "C:\CounterCollect\Counters.txt" -si 15 -o SQL:SQLServerDS!log1 -sc 4

I have a requirement to schedule it for every 15 mins to collect the counters. I am guessing if we can make it as batch file then I can schedule it using task scheduler but i am new to batch files concept.

Can anyone help me with this?

enter image description here

Upvotes: 1

Views: 694

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19184

Since this is for SQL Server I suggest you use SQL Agent instead of windows scheduler.

In SQL Server Management Studio, open out SQL Server Agent, right click on jobs and create a new job.

Now create a new step of type Operating system (cmdexec) and simply paste that in:

TYPEPERF -f SQL -s admin -cf "C:\CounterCollect\Counters.txt" -si 15 -o SQL:SQLServerDS!log1 -sc 4

You do need to make sure that TYPEPERF is installed on the SQL Server and you might need to run it in a certain folder. You also need to ensure the SQL Agent account has access. None of these are different in windows scheduler though.

Use of a batch file is optional. If the command was any more complex it might be worth it.

Your job (since it can run unattended) needs to run as somebody so it knows if it's allowed to do things, and can tell various logs who it is etc. At the moment it is running as the default 'somebody', (NT Service\SQLSERVERAGENT), which it turns out doesn't have rights to create a file. So you need to run it as somebody who can.

Any of the things listed below can be done to give it required rights. Some of these are plain silly and some may or may not be appropriate for your situation.

  • Create a special service account and use that
  • Add NT Service\SQLSERVERAGENT to local admin
  • Find the folder and give NT Service\SQLSERVERAGENT create rights in there
  • Create a SQL Server credential of yourself and use that in the job
  • Change SQL Agent to run as yourself

The first option is the most 'Entperprise' but probably over the top for your needs.

The last option is easier but suffers if you have to change your password.

I'll explain the last option:

  1. Open Services manager by running services.msc (or typing services into search)
  2. Find SQL Server Agent in the list
  3. Double click it, go to the Log On tab and enter your login/password there
  4. Press OK (you might get a message about log on as a service right)

Now your SQL Agent will run as you and you shouldn't get that message anymore.

Upvotes: 1

Magoo
Magoo

Reputation: 80023

A batch file is effectively whatever commands you'd type into cmd.

There are a few differences, but mainly control issues - looping and so on.

If that typeperf command is the only one you need, then just exactly that line should suffice.

You can decorate it a little. Depends on taste

@echo off
setlocal
typeperf...
cls
exit

where @ at the start of a command disables command-echoing for that command.
echo off turns command echoing off until the batch ends
setlocal ensures any environment changes made are temporary
cls clears the cmd window
`exit forcibly exits batch.

Just need to place it in a file named whateveryoulikebutdontuseacommandkeyword.bat

use a text-editor but not a word-processor. Notepad is barely adequate as it tends to try to format the text entered. Ensure the file is saved as ASCII.

Upvotes: 0

Related Questions