Click here to Skip to main content
13,454,124 members (55,007 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


9 bookmarked
Posted 22 Dec 2010

Tip: Run a Transact-SQL Script (.SQL File) From the Command Line

, 22 Dec 2010
Rate this:
Please Sign up or sign in to vote.
Provides the quick-and-dirty syntax to connect to a given Microsoft SQL Server instance so you can then run a .sql query script against it, e.g., if you are calling this from code as part of a larger pipeline
This tip isn't really anything much, just a tidbit to stuff away in the file folder of useful snippets. Therefore I am putting this in the Scrapbook section.

Running a Transact-SQL Query in a Quick-And-Dirty Fashion

Say I have a .sql file and I want to run it very quickly against a certain database on my computer. I happen to know I can connect to a database using plain-old-fashioned Windows Authentication and integrated security, so we aren't doing anything fancy.

Tutorial: Northwind Database

This small tutorial assumes you've downloaded and installed the Northwind database on your instance of SQL Server Express. If not, see my article at HowTo: Install the Northwind and Pubs Sample Databases[^] if you're using SQL Server 2005 Express, or HowTo: Install the Northwind and Pubs Sample Databases in SQL Server 2008 Express[^] if you're using SQL Server 2008 Express.

Let's create a .sql query file for use against the Northwind database, which I will say is on the SQLEXPRESS instance on my computer, called COMPUTER as its computer name.

The following code is in the MyQuery.sql file:

SELECT TOP 10 * FROM Customers

I typed the above into Notepad and then saved it as myQuery.sql on the Desktop. Now let's whip it off. Click the Start button, and then click Command Prompt.

Next, at the C:\> prompt, type:

C:\Users\bchart\Desktop\> sqlcmd -S COMPUTER\SQLEXPRESS -d Northwind -i myQuery.sql

That's all you do if you want to exec a quickie query from a .sql script file against a specific server instance and database. Type:

C:\Users\bchart\Desktop\> sqlcmd -?

For more juicy command-line goodness :)

Now Let's Put this To Work

This is a great tip, because what if you have a command-line console application you've written in C# to do, e.g., automated database reporting, and you have it living as, e.g., a Scheduled Task. Now what if you have some .sql Transact-SQL script you need that process to run and it's some ungodly long stored-procedure creation script or whatever.

Well, with the tip above, just make a C# call to run the command line above from your code, as in:

using System.Diagnostics;

Process process = new Process();
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardOutput = true;
process.StartInfo.RedirectStandardError = true;
process.StartInfo.CreateNoWindow = true;
process.StartInfo.FileName = "sqlcmd.exe";
process.StartInfo.Arguments = "-S COMPUTER\\SQLEXPRESS -d Northwind -i myQuery.sql";
process.StartInfo.WorkingDirectory = @"C:\Users\bchart\Desktop";

This C# code does identically the same thing as when I ran the command line above by hand from the Command Prompt. Clickety to this nice article[^] to learn more about spawning child processes from code!



This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Brian C Hart
Software Developer (Senior) xyLOGIX, LLC
United States United States
From Fridley, Minnesota and I like computer programming! When I got started, I was working mostly with Windows GUI programming in C/C++. Then later on I worked with COM/DCOM for a school internship. I used COM/DCOM to write an ad hoc cluster server and job-running environment for a cluster of 24 Windows-based high-end visualization workstations. I moved on to C# and have been working in C# and Windows Forms ever since. I have yet to embrace Silverlight Smile | :)

You may also be interested in...


Comments and Discussions

Questionthanks for great tip Pin
Member 1047466311-Nov-14 5:51
memberMember 1047466311-Nov-14 5:51 
AnswerRe: thanks for great tip Pin
Brian C Hart11-Nov-14 5:56
memberBrian C Hart11-Nov-14 5:56 
QuestionSQLCMD process is not getting stopped after execution is completed. Pin
Gowthu B23-Jul-14 2:42
memberGowthu B23-Jul-14 2:42 
AnswerRe: SQLCMD process is not getting stopped after execution is completed. Pin
Brian C Hart11-Nov-14 6:07
memberBrian C Hart11-Nov-14 6:07 
Questionsqlcmd output or status Pin
kkeyur21-Nov-12 0:18
memberkkeyur21-Nov-12 0:18 
AnswerRe: sqlcmd output or status Pin
Brian C Hart11-Nov-14 6:04
memberBrian C Hart11-Nov-14 6:04 
GeneralReason for my vote of 4 great tip Pin
daltonrs@hotmail.com11-Jan-11 0:15
memberdaltonrs@hotmail.com11-Jan-11 0:15 
GeneralRe: Reason for my vote of 4great tip Pin
Brian C Hart11-Nov-14 6:08
memberBrian C Hart11-Nov-14 6:08 
GeneralReason for my vote of 3 good Pin
Sumit Kumar Pranav27-Dec-10 7:02
memberSumit Kumar Pranav27-Dec-10 7:02 
GeneralRe: Reason for my vote of 3good Pin
Brian C Hart11-Nov-14 6:08
memberBrian C Hart11-Nov-14 6:08 
GeneralReason for my vote of 5 Thanks for sharing this tip. Pin
linuxjr23-Dec-10 2:23
memberlinuxjr23-Dec-10 2:23 
GeneralRe: Reason for my vote of 5Thanks for sharing this tip. Pin
Brian C Hart11-Nov-14 6:22
memberBrian C Hart11-Nov-14 6:22 
GeneralReason for my vote of 5 Good one............:-) Pin
Vivek Johari22-Dec-10 21:52
memberVivek Johari22-Dec-10 21:52 
GeneralReason for my vote of 5 Thanks, I will be able to use this. Pin
RockingDownTheHighway22-Dec-10 11:21
memberRockingDownTheHighway22-Dec-10 11:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01-2016 | 2.8.180321.1 | Last Updated 22 Dec 2010
Article Copyright 2010 by Brian C Hart
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid