vsdbcmd.exe gets blocked by its calling process

In my setup program, I was calling vsdbcmd.exe, a very wonderful database tool from Microsoft, to deploy my dbschema file in C#. I used a System.Diagnostics.Process object to invoke the tool, as I’ve always done with calling command-line utilities. And as always, I redirected the process’s output and error so I could display them in my setup log file. Unexpectedly this did not work. My program was hanging, still alive, but hanging. The database was created half way, and vsdbcmd.exe was still live in task manager. However there was no CPU activities from vsdbcmd.exe. After a bunch of experiments, I found out that redirecting the process’ output was the cause of the blockage. Redirecting error was okay. Gah why can’t vsdbcmd.exe work like all the other programs?

SQLCMD Mode

Okay, so there is a SQLCMD Mode for the query window in Management Studio for SQL Server. However, what I didn’t know was that there is also a SQLCMD Mode for the Transact-SQL Editor in Visual Studio 2010. Apparently, in order to compile any SQLCMD syntax inside of a script, such as the post deployment script for a database project, you must have SQLCMD turned on to avoid any parser errors. Cheers!