One of the feature differences between SQL Server Express Edition and the other paid versions is the ability to schedule jobs or tasks using the SQL Server Agent.
Even if you are running SQL Server Express Edition, there is a need to run scheduled jobs. Things like:
- Backups
- Index and Statistics Maintenance
- Shrink DB (Just Kidding, don’t do that)
- A variety of other jobs.
Windows has the Task Scheduler, which makes it really easy to schedule programs to run on your Windows desktop or Windows Server.
The problem is that there is no direct way to run a SQL query from the Windows Scheduler. It is a really great way to run programs, batch files, or PowerShell, just no direct way to run TSQL queries.
Some people get around this by running their SQL queries from inside power shell, and that works great if you have everything set up to run PowerShell talking to your database. But sometimes we just need quick, simple solution.
Here is where we can run TSQL using a couple of programs installed with SQL Server. OSQL.EXE and SQLCMD.exe.
To try it out, just run a command prompt, and type in OSQL /?
If it is installed, you will get the list of command line options.
You can then try running something like this
osql -S servername\sqlexpress -E -Q "EXEC someProcedureName"
The -S tells OSQL what server to connect to. -E is the trusted connection, which means it is using your windows credentials. The -E can be replaced with a -U and -P to specify a username and password. Next the -Q is the query specified. Keep in mind that these parameters are case sensitive.
Next you can do a similar thing with sqlcmd.exe. Here I type sqlcmd /? To see a list of parameters.
sqlcmd -S servername\sqlexpress -U sa -P myPassword -Q "EXECUTE someProcedure"
The parameters are very similar to osql, but in this case I have used the -U and -P to specify a SQL Login instead of -E for the trusted connection.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!