Category: Database Development

In our setup program, we have recently changed to use vsdbcmd to deploy our dbschema file. I followed the steps in this Microsoft MSDN article on how to deploy vsdbcmd.exe on a machine that doesn’t have Visual Studio installed. However, I kept getting the follow error when I tested the program on a non-developer machine: An unexpected failure occurred: An attempt was made to load a program with an in correct format. (Exception from HRESULT: 0x8007000B). After doing some research, I got it working by following the instructions in this blog article. Basically, you do not need to copy any of the SQL CE libraries or the BatchParser library. Simply copy all the Visual Studio files listed in the MSDN article, then install SQL SMO and SQL Compact on the machine where you run vsdbcmd. That should work.


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?


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!