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

Question

Post reply

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

sayan ghosh

SSC Veteran

Points: 205
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: 19243 More actions · Answer 1

BrainDonor

SSCoach

Points: 19243

August 13, 2009 at 3:37 am

#1038565

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://qa.sqlservercentral.com/scripts/BCP/65710/

BrainDonor

Steve Hall
Linkedin
Blog Site

BrainDonor SSCoach Points: 19243 More actions · Answer 2

BrainDonor

SSCoach

Points: 19243

August 13, 2009 at 4:42 am

#1038597

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

matt6288

SSCertifiable

Points: 5058

August 13, 2009 at 7:30 am

#1038672

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: 53353 More actions · Answer 4

The Dixie Flatline

SSC Guru

Points: 53353

August 13, 2009 at 12:47 pm

#1038929

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: 1000539 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)
Intro to Tally Tables and Functions

nigel. SSChampion Points: 11636 More actions · Answer 7

nigel.

SSChampion

Points: 11636

August 14, 2009 at 9:57 am

#1039490

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: 1000539 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)
Intro to Tally Tables and Functions

sayan ghosh SSC Veteran Points: 205 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: 1000539 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)
Intro to Tally Tables and Functions

sayan ghosh SSC Veteran Points: 205 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