Text file creation dropping extra space 1

[Malchik]

Hi

I use a SP to create a text file using FSO. I need to add extra spaces at the end of each line. When I look in the file, the extra spaces are not there.

For example:
SET @Data = 'ABC'
SET @Data = @Data + SPACE(5)
EXECUTE @RetCode = sp_OAMethod @FileHandle,'WRITELINE', NULL, @Data

it will be ABC with no extra spaces, but if I do that:
SET @Data = 'ABC'
SET @Data = @Data + SPACE(5) + '.'
EXECUTE @RetCode = sp_OAMethod @FileHandle,'WRITELINE', NULL, @Data

i will be ABC . So if the last character is not a space, it is fine.

Does anyone knows how to force the WRITELINE command to not drop the extra spaces?

Thanks!

Mal'chik

 

[gmmastros]

Somewhere in your procedure, you must be declaring @Data. I suspect you are declaring it as type varchar (or nvarchar). Instead, try using char (or nchar).

By design, varchar and nvarchar remove trailing spaces, but char and nchar do not.

To see what I mean...

Declare @TestA VarChar(20)
Declare @TestB char(20)

Set @TestA = 'ABC'
Set @TestB = 'ABC'

Select @TestA + '.', @TestB + '.'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Malchik]

It was my first guess, but my variable is already a CHAR.

Mal'chik

 

[gmmastros]

I just tested this. It appears to be working ok (keeping the trailing spaces). In fact, it appeared to keep the spaces even for varchar variables.

DECLARE @fso int
DECLARE @hr int
DECLARE @ts int
DECLARE @FileName VarChar(255)

DECLARE @VarCharData varchar(20)
DECLARE @CharData char(20)

Set @FileName = 'C:\TestSpaces.txt'

-- Create a filesystem object
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT
EXEC @hr = sp_OAMethod @fso, 'OpenTextFile', @ts OUT,@FileName, 2, true

EXEC @hr = sp_OAMethod @ts, 'WriteLine', NULL, 'Text With Spaces     '
EXEC @hr = sp_OAMethod @ts, 'WriteLine', NULL, 'TextNoSpaces'

Set @VarCharData = 'ABC' + Space(5)
Set @CharData    = 'ABC' + Space(5)
EXEC @hr = sp_OAMethod @ts, 'WriteLine', NULL, @VarCharData
EXEC @hr = sp_OAMethod @ts, 'WriteLine', NULL, @CharData

EXEC @hr = sp_OAMethod @ts, 'Close', NULL

Notice how I hard code a couple strings and then also use char and varchar for a couple strings too.

The varchar one had the extra spaces on the end (5 extra). the char one had 17 spaces.

When I run this code, a file is created in the root folder of the C drive. To verify the spaces, I opened the file in notepad and saw that there were spaces at the end of the lines.

There must be something else going on.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Malchik]

I just tried your code and the spaces are still being dropped, but that helps me. I now suspect that there is a settings in the SQL itself, i will take a closer look at the server configuration.

thanks!

Mal'chik

 

[Malchik]

I found the issue. I am working on a Development server that was installed with FRENCH_CI_AS instead of SQL_LATIN_GENERAL_CP1_CI_AS. I tested my code on another SQL server and it is working... I should have though of that before

thanks George... here is a star for you!

Mal'chik

 

[gmmastros]

Are the spaces being dropped from the 'Text With Spaces ' line? How are you verifying that the spaces are being dropped?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I'm glad you got it sorted.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom