Transfering content of a string variable to a file in a SP

Question

Transfering content of a string variable to a file in a SP

sayan ghosh

SSC Veteran

Points: 255
More actions
August 12, 2009 at 9:46 pm

#133819

Hi,
How do we transfer the content of a string variable to a file on a SP? I need to do these:
1. Create an empty text file from an SP
2. Push the content of a variable to the newly created file
The variable size is Varchar(Max), and this is the code I am trying to make work (but it ain't 🙁 ) ---
Declare @cmd sysname
Declare @ReqContent Varchar(max)
SET @cmd = 'echo ' + @ReqContent + ' > C:\DD4TRD.txt'
EXEC master..xp_cmdshell @cmd ,NO_OUTPUT
Thanks,
Sayan

Viewing 12 posts - 1 through 11 (of 11 total)

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

BrainDonor SSCoach Points: 19254 More actions · Answer 1

Have you tried using bcp? This is the most common way of exporting data to a text file, pick the bones out of this article:

http://www.sqlservercentral.com/scripts/BCP/65710/

BrainDonor

Steve Hall
Linkedin
Blog Site

BrainDonor SSCoach Points: 19254 More actions · Answer 2

Ans while looking for something else I stumbled upon this article, explaining bcp quite well:

http://sqlfool.com/2008/12/bcp-basics/

Steve Hall
Linkedin
Blog Site

matt6288 SSCertifiable Points: 5058 More actions · Answer 3

If you are stuck with not using bcp then you probably need to change the datatype of the @cmd variable to varchar(max) also. sysname is basically a nvarchar(128) datatype.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

This page intentionally left blank.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

nigel. SSChampion Points: 11636 More actions · Answer 5

Question:

Why is your @cmd variable of type sysname? This has a max length of 256, which has the potential to get truncated if @ReqContent is too long. Try changing to varchar(max), since your @ReqContent is of this type.

Your method works fine for me with short strings in @ReqContent.

You may have already done this, put:

EXEC master..xp_cmdshell 'type C:\DD4TRD.txt'

at the end of your script to output the file contents.

Regards,

Nigel

Nigel
How to post questions

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

Command lines are limited by the system to only 255 characters and I don't know if they can actually be of the NVARCHAR data type (never tried it). Obviously, VARCHAR(MAX) can greatly exceed 255 characters so that won't work.

What kind of data is in the VARCHAR(MAX) variable and how are you building 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)

nigel. SSChampion Points: 11636 More actions · Answer 7

Jeff Moden (8/14/2009)
Command lines are limited by the system to only 255 characters

Jeff,

Are you sure? Take a look at this.

Also the argument to xp_cmdshell is varchar(8000)!?

Nigel

Nigel
How to post questions

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 8

Ah.... my bad, Nigel. More coffee please. :blush: I was thinking of the returns from some commands. Thank you for the catch.

Still, 8191 can be a far cry from VARCHAR(MAX) and (correct me if I'm wrong, Nigel), you will probably also have to set the width using still another DOS command. Even OSQL won't support the output of a VARCHAR(MAX)... I've not had to do such a thing in quite a while but, IIRC, it truncates just like max column width in the SMSS GUI does.

I believe the only way someone will be able to pull this off (export of a VARCHA(MAX) variable in excess of ~8k) is to use either BCP or SSIS.

--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)

sayan ghosh SSC Veteran Points: 255 More actions · Answer 9

Thanks to everyone for their inputs. I came to know that Varchar(8000) is the max I could pass from an SP to SSIS, also that's the max Xp_CmdShell could work with. So I am now calling the SP from a Script task, which executes the SP, sets a string variable equal to the OUT parameter of the SP containing the big string (whose size > 8000), and finally pushing the big string stored in the variable to a newly created file using System.IO.

Thanks again for this discussion.

Sayan

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

sayan ghosh (8/15/2009)
Thanks to everyone for their inputs. I came to know that Varchar(8000) is the max I could pass from an SP to SSIS, also that's the max Xp_CmdShell could work with. So I am now calling the SP from a Script task, which executes the SP, sets a string variable equal to the OUT parameter of the SP containing the big string (whose size > 8000), and finally pushing the big string stored in the variable to a newly created file using System.IO.
Thanks again for this discussion.
Sayan

Thanks for the feedback, Sayan. Glad you got it worked out. As a sidebar, I'm still interested as to what is in the long string and how you assembled 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)

sayan ghosh SSC Veteran Points: 255 More actions · Answer 11

Hi Jeff,

Well actually I needed to construct a file, which has certain keys with specific headers and footers. Consider the example below:

{X987gt}

[Financial data]

Extraction 1

Time: 1130

1234R

4567R

3765R

Extraction 2

Time: 1130

3786R

9856R

2345R

Extraction 3

Time: 1130

1234R

4673R

7129R

...

I am building the list of keys (the keys appended by R) by querying from database, and formatting it according to the above requirement. And this is the content that I was referring to as the 'big string', whose datalength(bigstring__) ~ 30000.

🙂

Sayan