Red Gate forums :: View topic - Packager appends special character at the end of schema file
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager Previous Versions
SQL Packager Previous Versions forum

Packager appends special character at the end of schema file

Search in SQL Packager Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
chriscr



Joined: 22 Jan 2008
Posts: 4

PostPosted: Tue Jan 22, 2008 3:52 pm    Post subject: Packager appends special character at the end of schema file Reply with quote

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.
Back to top
View user's profile Send private message
chriscr



Joined: 22 Jan 2008
Posts: 4

PostPosted: Tue Jan 22, 2008 4:18 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
chriscr



Joined: 22 Jan 2008
Posts: 4

PostPosted: Thu Jan 24, 2008 3:28 pm    Post subject: Stumper? Reply with quote

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?
Back to top
View user's profile Send private message
chris.buckingham



Joined: 30 Jul 2007
Posts: 56

PostPosted: Thu Jan 24, 2008 8:00 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
chriscr



Joined: 22 Jan 2008
Posts: 4

PostPosted: Thu Jan 24, 2008 8:13 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
chris.buckingham



Joined: 30 Jul 2007
Posts: 56

PostPosted: Fri Jan 25, 2008 12:48 pm    Post subject: Reply with quote

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 Sad
_________________
Chris Buckingham
Red-Gate support
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group