I am not able to create a text file using SQL server 2005

Question

I am not able to create a text file using SQL server 2005

Sarath Vellampalli

SSCertifiable

Points: 5822
More actions
July 18, 2011 at 10:43 am

#100397

Hi,
I am using the below code to create a text file from SQL server 2005.
DECLARE @vCmd VARCHAR(MAX)
DECLARE @vCLUEDATE VARCHAR(MAX)
DECLARE @vWorkfilename VARCHAR(MAX)
SET @vCLUEDATE = '000000001EFHRCF01FF 20110718205227VERI2011071820110718 2'
SET @vWorkfilename = 'F:\ClueUpload\CVGVER.20110718.0101.txt'
Select@vCmd = 'echo ' + @vCLUEDATE + '>' + @vWorkfilename
EXEC master..xp_cmdshell @vCmd
But it is creating the file without data, but it should create with one line of data.
Then i changed the @vCmd as below
Select@vCmd = 'echo ' + @vCLUEDATE + ' > ' + @vWorkfilename
then it is working. but it is adding one space to the end of line that is after 2 it is adding. but i don't want that space, so how can I do this.
Please help me on this...
Regards,
Sarath Babu Vellampalli
Kindest Regards,
Sarath Vellampalli

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Interesting problem. I you remove the space before the 2 or change it to another character, the space after the string in the file goes away with your first CMD. If you use the second CMD, it returns the string to the screen (sans the 2). If you add 1 mor character after the 2, you also don't get a space on the first CMD.

Not sure what to do to fix it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

I'm not sure you can use an alternate answer but if you replace the spaces with underscores, all the problems vanish.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

This will do it provided you understand that it will end the line with a ^C_R^L_F

DECLARE @vCmd VARCHAR(8000)

DECLARE @vCLUEDATE VARCHAR(MAX)

DECLARE @vWorkfilename VARCHAR(MAX)

SET @vCLUEDATE = '000000001EFHRCF01FF 20110718205227VERI2011071820110718 2'

SET @vWorkfilename = 'c:\test.txt'

Select @vCmd = 'SQLCMD -E -dtempdb -h-1 -Q"SET NOCOUNT ON; SELECT ' + QUOTENAME(@vCLUEDATE,'''') + ';" -o' + @vWorkfilename

EXEC master..xp_cmdshell @vCmd

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sarath Vellampalli SSCertifiable Points: 5822 More actions · Answer 4

Thanks Jeff, it is working for me.

Kindest Regards,

Sarath Vellampalli