This is a simple way of saving SQL Server text to a file. I’ve used it for years. Just pass it a string and all should be well, just as long as your database allows command-line access and you user has sufficient rights
To allow xp_cmdshell to be used you might need to do this.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE WITH override GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE WITH override GO |
Your login must be allowed access to the file system.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 |
CREATE PROCEDURE spSaveTextToFile @TheString VARCHAR(MAX), @Filename VARCHAR(255) /** Summary: > This is a simple way of saving SQL Server text to a file I've used it for years. Just pass it a string and all should be well, just as long as your database allows command-line access and you user has sufficient rights Author: PhilFactor Date: 13 July 2007 Database: PhilFactor Examples: > 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' Returns: > nothing **/ 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 dbo.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 sys.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); |
Load comments