Hi, I use Visual Studio 2008 database project for managing my database scripts and version control. by default visual studio script templates are encoded in UTF-8 format. when I try to concatenate the scripts for database release (using the DOS command "type *.sql > dbscripts.sql") and run the concatenated script from SQL server management studio, I get invalid character error. however if I convert each script to ANSI format (using notepad), and concatenate them then I don't get any errors when I run the concatenated script. but converting each script into a different encoding format is tedious. what is the best way to execute multiple SQL scripts regardless of the encoding format of the scripts? do I need to create a batch file and use sqlcmd to run it and redirect the output to a text file? are there any alternatives? Thanks in advance.
That's because of the BOM in the file. The TYPE command is too primitive to detect it and will just copy the 3 bytes to the output.
You can change the encoding used for the .sql file. Tedious too btw. File + Save As, click the arrow on the Save button, Save With Encoding and select your current code page. Like 1252 for Western Europe and Americas, it is probably the first one in the list. This is the same thing you did with Notepad.
Beware of the trouble you can get into if the server runs with a different code page and your scripts contain accented characters. I would myself just write a little utility that uses Directory.GetFiles and StreamReader/Writer to fix the problem.
found this link which generates "SQLCMD" commands using Windows Powershell. for concatenation, it looks like there is no solution except saving each file in ANSI format. I can at least generate a file with all sqlcmd commands using this without having to concatenate them
http://sqladm.blogspot.com/2010/02/generate-sqlcmd-statements.html