byte order mark (BOM) issue

Last Post 22 May 2012 11:22 PM by danyal. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Resolved
danyal
New Member
New Member

--
22 May 2012 06:11 AM
hi all ,

I am having a sp which generates a dat file , as follows

\n

BEGIN

SET NOCOUNT ON;

DECLARE @PATH VARCHAR(500)
DECLARE @TIME VARCHAR(50)
DECLARE @DIR VARCHAR(100)
DECLARE @FILENAME VARCHAR(50)
DECLARE @OUTPUTCATCH VARCHAR(MAX)
DECLARE @SERVERNAME VARCHAR(50)

SET @SERVERNAME = (select @@servername)

SELECT @DIR=VALUE FROM SPADE_EXTRACT_SETUP WHERE NAME = 'UTL_FILE_DIR'

SET @DIR = @DIR + '\'

SET @FILENAME = 'DD_SPADE_Extract_' + @p_TIME +'.dat'

SET @PATH = 'bcp "SELECT ORIGIN_HOST + TRANS_DATE+TRANS_SEQUENCE+TRANS_CODE+TRANS_VERSION+TRANS_SEGMENT+TRANS_ACTION+ISNULL(TRANS_DATA,'''') FROM KATESPADEDEV_NEW.DBO.SPADE_EXTRACT with(nolock) ORDER BY TRANS_SEQUENCE" queryout "' + @DIR + @FILENAME + '" -C -T -S "' + @SERVERNAME +'" -w'

EXEC @OUTPUTCATCH= MASTER..XP_CMDSHELL @PATH


END

now the files creates perfectly but when i send this file to Unix and open it in VI editor i have these unwanted byte order mark , ÿþ , characters in start of the file :-( which are creating trouble in file reading by a program

.

can someone plz tell me how to remove / eliminate them in teh first place while creating the file from sql server xp_cmdshell ?? I mean by setting some parameters etc

Please help
danyal
New Member
New Member

--
22 May 2012 06:12 AM
here is what i did with .net C# to get it done and now those 2 characters "ÿþ" are not in the generated file , but I want to get it done in SP level in sql (desperately)

Encoding outputEnc = new UTF8Encoding(false);

string text = System.IO.File.ReadAllText(@"D:\winvi302\DD_SPADE_Extract_20120521171737.dat");

File.WriteAllText(@"D:\winvi302\Test.dat", text, outputEnc);


so basically Encoding outputEnc = new UTF8Encoding(false); did the trick , so how can i achieve the same functionality from sql server

in my SP pasted above ?? :-/ ANY idea anyone ??
gunneyk
New Member
New Member

--
22 May 2012 06:41 AM
I would first suggest using SSIS to generate the file which can give you a lot more options. But in this case it might be possible that you need to specify a code page or a specific collation. I don't know what those characters represent in the file but have a look in BOL under copying Data between different collations and see if that helps:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/c705468f-265b-450f-acc9-56ece5866238.htm
danyal
New Member
New Member

--
22 May 2012 07:23 AM
ok i will take a look at it Gunneyk , thanks for yr reply

but can U suggest how can i achieve the same effect as i did with .net code ??
danyal
New Member
New Member

--
22 May 2012 11:11 AM
sorry bro but this link doesn't work :-( not opening
rm
New Member
New Member

--
22 May 2012 11:33 AM
You should open it in sql books online, topic of the link is 'Copying Data Between Different Collations'.
danyal
New Member
New Member

--
22 May 2012 11:22 PM
hi all

I managed to resolve the issue (Thanks to Almighty Allah)

I just changed this line

SET @PATH = 'bcp "SELECT ORIGIN_HOST + TRANS_DATE+TRANS_SEQUENCE+TRANS_CODE+TRANS_VERSION+TRANS_SEGMENT+TRANS_ACTION+ISNULL(TRANS_DATA,'''') FROM KATESPADEDEV_NEW.DBO.SPADE_EXTRACT with(nolock) ORDER BY TRANS_SEQUENCE" queryout "' + @DIR + @FILENAME + '" -C -T -S "' + @SERVERNAME +'" -w'

to this

SET @PATH = 'bcp "SELECT ORIGIN_HOST + TRANS_DATE+TRANS_SEQUENCE+TRANS_CODE+TRANS_VERSION+TRANS_SEGMENT+TRANS_ACTION+ISNULL(TRANS_DATA,'''') FROM KATESPADEDEV_NEW.DBO.SPADE_EXTRACT with(nolock) ORDER BY TRANS_SEQUENCE" queryout "' + @DIR + @FILENAME + '" -c -C1252 -T -S "' + @SERVERNAME

and it did the trick :-)
 


Acceptable Use Policy
---