Packager appends special character at the end of schema file

chriscr

I am running SQL Packager 5.4.0.89. What I am doing is using Packager to generate schema and data SQL scripts that are deltas from our deveploment and testing databases for change management purposes and troubleshooting. During the script creation process we are appending the delta scripts to a master script file that contains all database changes for each build. This is so that when we need to create the environment to a specific build level, we can highlight all the code that we want (leaving out what we don't want) and executing that in one shot to create the DB environment.

The problem we are running into is that Packager seems to be appending a special character at the end of the schema.sql file and when it gets appended to our master delta script file, it foils the execution of the first line of our data file. In this case: SET NUMERIC_ROUNDABORT OFF. We encounter a syntax error. If you set your cursor to the beginning of the line and hit backspace once, the SET command colors blue and everything works fine.

I have tried to determine what the character code number is, and it comes back as character code 63, which is really odd, because that is a question mark, and I obviously don't see it in the script before execution. I'm sure it has something to do with the Unicode encoding, but I haven't figured it yet. Regardles, the below code snippet is my copy command:

COPY /B /Y "$(REDGATE_COMPARE_OUT)\Delta_Schema.sql" + "$(REDGATE_COMPARE_OUT)\Delta_data.sql" "$(REDGATE_COMPARE_OUT)\AllDeltas.sql"

and this snippet is the XML that I am using when I execute Packager via command line (I have removed the username/password info):

<?xml version="1.0"?>
<commandline>
<server1>xdc-data3\picard</server1>
<database1>XPIM_DEV_MAIN_4_SCM</database1>
<username1></username1>
<password1></password1>
<server2>xdc-data3\picard</server2>
<database2>XPIM_Dev_Main_Baseline</database2>
<username2></username2>
<password2></password2>

<includeschema>Table</includeschema>
<includeschema>View</includeschema>
<includeschema>StoredProcedure</includeschema>
<includeschema>Function</includeschema>
<includeschema>Trigger</includeschema>
<includedata>Table</includedata>

<scriptencoding>Unicode</scriptencoding>
<options>d</options>
<options>ic</options>
<location>Scripts</location>
<makeexe />
<name>XPIMDelta</name>
<schemascript>Scripts\Delta_Schema.sql</schemascript>
<datascript>Scripts\Delta_data.sql</datascript>
<Force/>
</commandline>

Has anyone else run into this? I really need this character to not be there and any help would be appreciated. Thanks.

chriscr

A little more... I have figured out the encoding. The application that uses the database is for international use. Therefore, all the data columns are stored in Unicode. Since SQL 2005 unicode is UCS-2, the closest compatible unicode type that Packager supports is UTF-16. That is the reason for the unicode setting in the XML.

So the special character is character code 63 in UTF-16 format.

chriscr

It's coming up on 48 hours and 75 views since my original post and there hasn't been a single response. Is there a lot of head stratching going on? Or is everyone busy this week? Or perhaps I didn't explain the problem well enough?

chris.buckingham

The FF FE are standard prefixes for unicode, and that is why this is placed at the start of the file. You could eliminate these characters by using COPY with /A to limit the copy to ASCII.

chriscr

First, thank you very much for your response.

The problem, I don't think I can afford to lose the encoding. The _data.sql file may contain records to be inserted that have string data that will not process correctly in the copied database unless the encoding is preserved. If I force it to copy as ASCII then wouldn't I lose that encoding?

chris.buckingham

If you have to retain the binary file held in the data SQL and aggregate the files with the COPY /B then you will have to strip the FFFE bytes from the beginning of every file to be aggregated (except the first file as SQL Server Management Studio will strip these). There are a number of ways you can do this - the easiest would be a bespoke VB Script utility simply parsing through the file, but that could be modified to suck in all the files in a directory, strip the leading FFFE's and write them files to an output sql file.

BTW I have found a site http://www.dostips.com/ that will give you various DOS batch libraries, in particular :substitute function that might be useful if you want to stick to DOS batch file mode