Need help creating textfiles from a recordset in SQL Server 2008 R2

Question

Post reply

Need help creating textfiles from a recordset in SQL Server 2008 R2

TheKasper

SSC-Addicted

Points: 433
More actions
April 30, 2012 at 5:27 am

#260633

I would like some help with my problem below:
OPEN C1
FETCH NEXT FROM C1 INTO
@var_1, @var_2, @var_3, @var_4, @var_5, @var_6, @var_7
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
----------------------------------------------------------------------------------------------------------------
-- Here I would like to create a tab-delimited text file with my variables and creating a new row when
-- moving to next line in the recordset.
-- I have tried the stored procedure exec master..xp_cmdshell
-- exec master..xp_cmdshell'echo '+ @var_1 + CHAR(9) + @var_2 + CHAR(9) +' (etc.) > c:\textfile_'+ var_7 +'.txt'
-- but it does not seem to work with variables...
----------------------------------------------------------------------------------------------------------------
END
FETCH NEXT FROM C1 INTO
@var_1, @var_2, @var_3, @var_4, @var_5, @var_6, @var_7
END
CLOSE C1
How would I best go around solving this..?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it's easy...create the entire string as one variable, and then execute it via xp_cmdshell.

the issue is you cannot replace a variable with a concatination for most procs, like xp_cmdshell

it's prety much just xp_cmdshell @somevar

DECLARE @cmd varchar(max)

SET @cmd = 'echo '+ @var_1 + CHAR(9) + @var_2 + CHAR(9) +' (etc.) > c:\textfile_'+ var_7 +'.txt'

exec master..xp_cmdshell @cmd

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TheKasper SSC-Addicted Points: 433 More actions · Answer 2

Tnx!

Sometimes the answer is right in front of you...

TheKasper SSC-Addicted Points: 433 More actions · Answer 3

It works fine allthough I couldn't use varchar(max)... Had to state a difinitive number (8000)...

Another question: How do I go about to start writing the text at line 7 of the textfile without using 6 statements of CHR(13)?

Lowell SSC Guru Points: 323518 More actions · Answer 4

jonas.kasper (4/30/2012)
It works fine allthough I couldn't use varchar(max)... Had to state a difinitive number (8000)...
Another question: How do I go about to start writing the text at line 7 of the textfile without using 6 statements of CHR(13)?

you'll have to include the CrLf in the string you are printing; the file's not a sheet of paper /chalkboard with a specific number of lines in it, the lines are interpreted from the text, so just find an easier way to include it in the file when you build it.

the REPLICATE function is kind of handy for that...that's one way:

SET @cmd =

--get 6 vbCrLf in there to start with:

REPLICATE(CHAR(13) + CHAR(10),6)

+ @otherVars

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!