How to terminate sqlcmd immediately after execution completed?

Question

I create a process in C# to execute sqlcmd /S <servername> /d <dbname> /E /i to run sql script that create tables, views, stored procedures.

The problem is the process does not terminate after the execution is completed. I want to know how can I terminate sqlcmd immediately after the operation is completed.

Is there any input argument to sqlcmd that I can specify to terminate the process right away or can I do it directly in C#?

Update

This is the C# code that I use to execute the process.

foreach (var file in files)
{
    ////var begin = DateTime.Now;
    ////context.TrackBuildWarning(string.Format("Start exec sql file at {0}.", DateTime.Now));

    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 = string.Format("-S {0} -d {1} -i \"{2}\" -U {3} -P {4}", sqlServerName, databaseName, file, sqlUserName, sqlPassword);
    process.StartInfo.WorkingDirectory = @"C:\";
    process.Start();
    process.WaitForExit(); // if I comment out this code it will execute much faster

    ////context.TrackBuildWarning(string.Format("Finished exec sql file at {0} total time {1} milliseconds.", DateTime.Now, DateTime.Now.Subtract(begin).TotalMilliseconds));
}

As you can see, I have comment that if I remove (or comment) out process.WaitForExit() it will execute A LOT faster.

The solution would be to find out why it isn't closing after its finished? — Jeremy Thompson, Oct 05 '12 at 03:09
@JeremyThompson I tried to run `sqlcmd /S ...` directly and it does terminate after its operation completed. I just updated my question to include my C# code, maybe there is something wrong in the code. — Anonymous, Oct 05 '12 at 03:32
@Dr.Wily'sApprentice 3.2 minutes vs 32.5 minutes. It does not make any sense for me, but it does faster. — Anonymous, Oct 05 '12 at 04:22
Does your input file contain a large number of batches? For example, is it a script that creates hundreds of stored procedures in your database? In past experience, osql was terribly slow in that regard, taking upwards of 45 minutes to run such a script. I have not experienced that with sqlcmd, though. Also, just for the sake of comparison, when you execute this command directly at a command prompt, how long does it take? — Dr. Wily's Apprentice, Oct 05 '12 at 04:32
I loop through all files in the folder and execute them one by one. This means that I create one thing (table, view, or procedure) at a time. I tried to run the command directly on cmd and it take less than 1 second. It has similar result if I comment out that line but it is really slow if I leave it there. — Anonymous, Oct 05 '12 at 04:36

score 57 · Answer 1 · answered Feb 05 '15 at 15:32

57

Just use -Q (uppercase).

This command selects 'asd' and quits immediately: sqlcmd -S (local) -Q "select('asd')"

answered Feb 05 '15 at 15:32

Filipe Borges

2,712
20
32

2

sqlcmd.exe's -i argument executes SQL from a file which is what the OP wanted. Your suggestion of -q or -Q are not the same- those arguments tell sqlcmd to take the query from the command line. – Micah Epps Jul 31 '18 at 13:32
True, you got me. But -i argument always quits after executing the files. – Filipe Borges Jul 31 '18 at 15:22

score 3 · Answer 2 · answered Jun 06 '14 at 09:40

3

Simple use this syntax:

sqlcmd -q "exit(command)"

answered Jun 06 '14 at 09:40

user2280510

39
3

10

Just use -Q (uppercase). You do not need the exit(command) stuff – Filipe Borges Feb 12 '15 at 13:19
sqlcmd.exe's -i argument executes SQL from a file which is what the OP wanted. Your suggestion of -q or -Q are not the same- those arguments tell sqlcmd to take the query from the command line. – Micah Epps Jul 31 '18 at 13:32

score 1 · Answer 3 · answered Oct 05 '12 at 03:41

1

Do you have quit or exit at the end of your SQL file?

See here for the sqlcmd commands.

answered Oct 05 '12 at 03:41

Pete McKinney

1,211
1
11
21

How can I know that the command is executed before the process is terminated if I specify `quit` or `exit` to the argument list? Is it 100% that the command is always executed before it quit? – Anonymous Oct 05 '12 at 03:46
sqlcmd will run all the commands in the file and put their output to stdout. Your c# is capturing stdout and stderr. You will have to read them once the process finishes and determine the results of your queries from that. – Pete McKinney Oct 05 '12 at 10:36

How to terminate sqlcmd immediately after execution completed?

3 Answers3