I have a msbuild exec task that calls a cmd file to setup my database.
I am using the msbuild exec task like this:
<Exec Command="$(SqlFolder)\Setup\SetupDatabase.cmd $(DBServer) MyDB $(SqlCmdRunner)" IgnoreExitCode="False">
<Output TaskParameter="ExitCode" ItemName="DBSetupExitCode"/>
</Exec>
In the setup cmd, it uses osql to run my sql scripts.
Everything is fine, except that when I create a sql file (table/stored proc, etc..) in VS 2008, the encoding (UTF-8?) causes a problem when I run from MSBuild. I receive an incorrect syntax error (near a character that looks like an U), but the syntax is correct. If I execute the cmd outside of MSBuild it works as expected.
If I "Save As" the sql file and "Save with Encoding" US-ASCII, msbuild will NOT error out which is why I think it is an Encoding issue.
While the "Save As" works for now, it seems like there should be a better way. I would imagine others using MSBuild to setup their db have run into this problem, so looking for advice/tips.
Best Answer
This is indeed a bizarre problem. The way around it is to update the template format that Visual Studio 2008 uses to create SQL files. Simply open them in Notepad and save as ANSI (or unicode, whichever you prefer). From then on when you create a new SQL file in Visual Studio it will automatically get the proper encoding.
You'll find five .SQL files that need to be updated under this directory:
C:\Program Files\Microsoft Visual Studio 9.0\Common7\Tools\Templates\Database Project Items