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<\/p>\n
To allow Your login must be allowed access to the file system.<\/p>\n 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….…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6813],"class_list":["post-2509","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2509","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2509"}],"version-history":[{"count":12,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2509\/revisions"}],"predecessor-version":[{"id":77261,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2509\/revisions\/77261"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2509"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2509"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2509"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2509"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}xp_cmdshell<\/strong> <\/code>to be used you might need to do this.<\/p>\n-- To allow advanced options to be changed.\r\nEXEC sp_configure 'show advanced options', 1\r\nGO\r\n-- To update the currently configured value for advanced options.\r\nRECONFIGURE WITH override\r\nGO\r\n-- To enable the feature.\r\nEXEC sp_configure 'xp_cmdshell', 1\r\nGO\r\n-- To update the currently configured value for this feature.\r\nRECONFIGURE WITH override\r\nGO<\/pre>\n
CREATE PROCEDURE spSaveTextToFile @TheString VARCHAR(MAX),\r\n @Filename VARCHAR(255)\r\n\/**\r\nSummary: >\r\n This is a simple way of saving SQL Server text to a file\r\n I've used it for years. Just pass it a string and all\r\n should be well, just as long as your database allows\r\n command-line access and you user has sufficient rights\r\nAuthor: PhilFactor\r\nDate: 13 July 2007\r\nDatabase: PhilFactor\r\nExamples: >\r\n\tspSaveTextToFile '\r\n\r\n\tIn Baltimore there lived a boy.\r\n\tHe wasn\u201dt anybody\u201ds joy.\r\n\tAlthough his name was Jabez Dawes,\r\n\tHis character was full of flaws.\r\n \r\n\tIn school he never led his classes,\r\n\tHe hid old ladies\u201d reading glasses,\r\n\tHis mouth was open when he chewed,\r\n\tAnd elbows to the table glued.\r\n\tHe stole the milk of hungry kittens,\r\n\tAnd walked through doors marked NO ADMITTANCE.\r\n\tHe said he acted thus because\r\n\tThere wasn\u201dt any Santa Claus.\r\n \r\n\tAnother trick that tickled Jabez\r\n\tWas crying \u201dBoo\u201d at little babies.\r\n\tHe brushed his teeth, they said in town,\r\n\tSideways instead of up and down.\r\n\tYet people pardoned every sin,\r\n\tAnd viewed his antics with a grin,\r\n\tTill they were told by Jabez Dawes,\r\n\t\u201dThere isn\u201dt any Santa Claus!\u201d\r\n \r\n\tDeploring how he did behave,\r\n\tHis parents swiftly sought their grave.\r\n\tThey hurried through the portals pearly,\r\n\tAnd Jabez left the funeral early.\r\n \r\n\tLike whooping cough, from child to child,\r\n\tHe sped to spread the rumor wild:\r\n\t\u201dSure as my name is Jabez Dawes\r\n\tThere isn\u201dt any Santa Claus!\u201d\r\n\tSlunk like a weasel of a marten\r\n\tThrough nursery and kindergarten,\r\n\tWhispering low to every tot,\r\n\t\u201dThere isn\u201dt any, no there\u201ds not!\u201d\r\n \r\n\tThe children wept all Christmas eve\r\n\tAnd Jabez chortled up his sleeve.\r\n\tNo infant dared hang up his stocking\r\n\tFor fear of Jabez\u201d ribald mocking.\r\n \r\n\tHe sprawled on his untidy bed,\r\n\tFresh malice dancing in his head,\r\n\tWhen presently with scalp-a-tingling,\r\n\tJabez heard a distant jingling;\r\n\tHe heard the crunch of sleigh and hoof\r\n\tCrisply alighting on the roof.\r\n\tWhat good to rise and bar the door?\r\n\tA shower of soot was on the floor.\r\n \r\n\tWhat was beheld by Jabez Dawes?\r\n\tThe fireplace full of Santa Claus!\r\n\tThen Jabez fell upon his knees\r\n\tWith cries of \u201dDon\u201dt,\u201d and \u201dPretty Please.\u201d\r\n\tHe howled, \u201dI don\u201dt know where you read it,\r\n\tBut anyhow, I never said it!\u201d\r\n\t\u201dJabez\u201d replied the angry saint,\r\n\t\u201dIt isn\u201dt I, it\u201ds you that ain\u201dt.\r\n\tAlthough there is a Santa Claus,\r\n\tThere isn\u201dt any Jabez Dawes!\u201d\r\n \r\n\tSaid Jabez then with impudent vim,\r\n\t\u201dOh, yes there is, and I am him!\r\n\tYour magic don\u201dt scare me, it doesn\u201dt\u201d\r\n\tAnd suddenly he found he wasn\u201dt!\r\n\tFrom grimy feet to grimy locks,\r\n\tJabez became a Jack-in-the-box,\r\n\tAn ugly toy with springs unsprung,\r\n\tForever sticking out his tongue.\r\n \r\n\tThe neighbors heard his mournful squeal;\r\n\tThey searched for him, but not with zeal.\r\n\tNo trace was found of Jabez Dawes,\r\n\tWhich led to thunderous applause,\r\n\tAnd people drank a loving cup\r\n\tAnd went and hung their stockings up.\r\n \r\n\tAll you who sneer at Santa Claus,\r\n\tBeware the fate of Jabez Dawes,\r\n\tThe saucy boy who mocked the saint.\r\n\tDonner and Blitzen licked off his paint.\r\n\tOdgen Nash\r\n\t','c:testing.txt'\r\nReturns: >\r\n nothing\r\n**\/\r\nAS\r\nSET NOCOUNT ON;\r\nDECLARE @MySpecialTempTable VARCHAR(255);\r\nDECLARE @Command NVARCHAR(4000);\r\nDECLARE @RESULT INT;\r\n--firstly we create a global temp table with a unique name\r\nSELECT @MySpecialTempTable =\r\n '##temp' + Convert(VARCHAR(12), Convert(INT, Rand() * 1000000));\r\n--then we create it using dynamic SQL, & insert dbo.A single row\r\n--in it with the MAX Varchar stocked with the string we want\r\nSELECT @Command =\r\n 'create table [' + @MySpecialTempTable\r\n + '] (MyID int identity(1,1), Bulkcol varchar(MAX))\r\ninsert into [' + @MySpecialTempTable + '](BulkCol) select @TheString';\r\nEXECUTE sys.sp_executesql @Command, N'@TheString varchar(MAX)', @TheString;\r\n--then we execute the BCP to save the file\r\nSELECT @Command = 'bcp \u201dselect BulkCol from [' + @MySpecialTempTable + ']'\r\n --+ @TheTable\r\n + '\u201d queryout ' + @Filename + ' -w -T -S' + @@ServerName;\r\nEXECUTE @RESULT = master..xp_cmdshell @Command, NO_OUTPUT;\r\n--we no longer need the file\r\nEXECUTE ('Drop table ' + @MySpecialTempTable);<\/pre>\n","protected":false},"excerpt":{"rendered":"