/*
It should be easy to save a VARCHAR or XML variable to a file. When we use the Scripting.FilesystemObject, it is reasonably easy, but there is the bugbear of the fact that the COM interface was never updated to understand Varchar(MAX) or XML variables. Nevertheless, we already have code on the site to do Varchars this way.
BCP is a wonderful tool as long as you respect its limitations. It is an external tool that communicates with the database using OLE DB. It is fast and efficient.
One of its limitations is that it doesn't support the use of local temporary tables in the QUERYOUT option. This means that only base tables, global temporary tables, procedures, table functions and views can be used in the SQL Statements that you supply to BCP.
This can prove awkward.
Most of us get around this problem by having a special table to do the import or export jobs. This will not work if you are likely to be calling the routine twice or more simultaneously. Also it is messy and difficult for people to understand.
Here is a stored procedure for storing any VARCHAR(MAX) to file. It can be used just as easily for XML, or Varbinary(MAX) simply by changing the VARCHAR(MAX). It works by creating a global temporary file with a unique name in order to do the work and then deleting it after the job is complete.
You willl want to put in error checking and logging for your own particular application. Be warned too that it uses XP_CMDshell, which is far too sharp a knife to be given to a user login. However, I don't know of any way around that if one is going to use BCP.
*/
CREATE PROCEDURE spSaveTextToFile
@TheString VARCHAR(MAX),
@Filename VARCHAR(255)
/*
spSaveTextToFile '
In Baltimore there lived a boy.
He wasn''t anybody''s joy.
Although his name was Jabez Dawes,
His character was full of flaws.
In school he never led his classes,
He hid old ladies'' reading glasses,
His mouth was open when he chewed,
And elbows to the table glued.
He stole the milk of hungry kittens,
And walked through doors marked NO ADMITTANCE.
He said he acted thus because
There wasn''t any Santa Claus.
Another trick that tickled Jabez
Was crying ''Boo'' at little babies.
He brushed his teeth, they said in town,
Sideways instead of up and down.
Yet people pardoned every sin,
And viewed his antics with a grin,
Till they were told by Jabez Dawes,
''There isn''t any Santa Claus!''
Deploring how he did behave,
His parents swiftly sought their grave.
They hurried through the portals pearly,
And Jabez left the funeral early.
Like whooping cough, from child to child,
He sped to spread the rumor wild:
''Sure as my name is Jabez Dawes
There isn''t any Santa Claus!''
Slunk like a weasel of a marten
Through nursery and kindergarten,
Whispering low to every tot,
''There isn''t any, no there''s not!''
The children wept all Christmas eve
And Jabez chortled up his sleeve.
No infant dared hang up his stocking
For fear of Jabez'' ribald mocking.
He sprawled on his untidy bed,
Fresh malice dancing in his head,
When presently with scalp-a-tingling,
Jabez heard a distant jingling;
He heard the crunch of sleigh and hoof
Crisply alighting on the roof.
What good to rise and bar the door?
A shower of soot was on the floor.
What was beheld by Jabez Dawes?
The fireplace full of Santa Claus!
Then Jabez fell upon his knees
With cries of ''Don''t,'' and ''Pretty Please.''
He howled, ''I don''t know where you read it,
But anyhow, I never said it!''
''Jabez'' replied the angry saint,
''It isn''t I, it''s you that ain''t.
Although there is a Santa Claus,
There isn''t any Jabez Dawes!''
Said Jabez then with impudent vim,
''Oh, yes there is, and I am him!
Your magic don''t scare me, it doesn''t''
And suddenly he found he wasn''t!
From grimy feet to grimy locks,
Jabez became a Jack-in-the-box,
An ugly toy with springs unsprung,
Forever sticking out his tongue.
The neighbors heard his mournful squeal;
They searched for him, but not with zeal.
No trace was found of Jabez Dawes,
Which led to thunderous applause,
And people drank a loving cup
And went and hung their stockings up.
All you who sneer at Santa Claus,
Beware the fate of Jabez Dawes,
The saucy boy who mocked the saint.
Donner and Blitzen licked off his paint.
Odgen Nash
','c:\testing.txt'
*/
AS
SET NOCOUNT ON
DECLARE @MySpecialTempTable VARCHAR(255)
DECLARE @Command NVARCHAR(4000)
DECLARE @RESULT INT
--firstly we create a global temp table with a unique name
SELECT @MySpecialTempTable = '##temp'
+ CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))
--then we create it using dynamic SQL, & insert a single row
--in it with the MAX Varchar stocked with the string we want
SELECT @Command = 'create table ['
+ @MySpecialTempTable
+ '] (MyID int identity(1,1), Bulkcol varchar(MAX))
insert into ['
+ @MySpecialTempTable
+ '](BulkCol) select @TheString'
EXECUTE sp_ExecuteSQL @command, N'@TheString varchar(MAX)',
@TheString
--then we execute the BCP to save the file
SELECT @Command = 'bcp "select BulkCol from ['
+ @MySpecialTempTable + ']'
--+ @TheTable
+ '" queryout '
+ @Filename + ' -w -T -S' + @@servername
EXECUTE @RESULT= master..xp_cmdshell @command, NO_OUTPUT
--we no longer need the file
EXECUTE ( 'Drop table ' + @MySpecialTempTable )