Packager appends special character at the end of schema file

Forum for users of SQL Packager database archive utility

Moderator: David Atkinson

Packager appends special character at the end of schema file

Postby chriscr » Tue Jan 22, 2008 3:52 pm

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
 
Posts: 4
Joined: Tue Jan 22, 2008 3:34 pm

Postby chriscr » Tue Jan 22, 2008 4:18 pm

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
 
Posts: 4
Joined: Tue Jan 22, 2008 3:34 pm

Stumper?

Postby chriscr » Thu Jan 24, 2008 3:28 pm

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?
chriscr
 
Posts: 4
Joined: Tue Jan 22, 2008 3:34 pm

Postby chris.buckingham » Thu Jan 24, 2008 8:00 pm

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.
Chris Buckingham
Red-Gate support
chris.buckingham
 
Posts: 56
Joined: Mon Jul 30, 2007 1:39 pm

Postby chriscr » Thu Jan 24, 2008 8:13 pm

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?
chriscr
 
Posts: 4
Joined: Tue Jan 22, 2008 3:34 pm

Postby chris.buckingham » Fri Jan 25, 2008 12:48 pm

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 :(
Chris Buckingham
Red-Gate support
chris.buckingham
 
Posts: 56
Joined: Mon Jul 30, 2007 1:39 pm


Return to SQL Packager Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests

cron