Script to create a file on hard drive from server table

Question

Script to create a file on hard drive from server table

mbatchelor

SSC Veteran

Points: 235
More actions
February 26, 2009 at 7:28 am

#99507

I have a stored procedure from which I have created a sql table from which I need to create a data file that I can then send off to be processed. This data file needs to be created within the stored procedure.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Sriram-288748 SSCrazy Points: 2874 More actions · Answer 1

Hi,

Can you be more specific with what do you mean by data file, you want to create a text / excel file from within stored procedure, If I am correct you can do that, there are two ways, you can use a linked server/ use xp_cmdshell to create file, write data and then close it.

Sriram

mbatchelor SSC Veteran Points: 235 More actions · Answer 2

Yes, I guess. I have seen this command several times, but do not totally know how it works. I have created a table on the database and I need to get that information (data) written out to a file on my hard drive for transmitting on to a third party. I know that I have done this in a prior life (job) but I just can not get my hands on it.

rutgersdba SSC Enthusiast Points: 199 More actions · Answer 3

There are maybe 5 ways to do this. Search for BCP in BOL.

HTH

"Who then will explain the explanation? Who then will explain the explanation?" Lord Byron

Dinjaan SSC Enthusiast Points: 146 More actions · Answer 4

--This will ceate a folder .. this might just help you by doing some changes....--

DECLARE@wk_no VARCHAR(2)

DECLARE@Path VARCHAR(100)

DECLARE @md VARCHAR(100)

SET @wk_no = DATEPART(wk, GETDATE())

SET @Path = 'D:\FolderName\'

---------------------------------------------------

SET @md = ' mkdir ' + @Path + 'WK' + @wk_no

..... ----Replace ' mkdir ' with create file name script or syntax..----

EXEC xp_cmdshell @md, no_output

PRINT @md

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 5

mbatchelor (2/26/2009)
I have a stored procedure from which I have created a sql table from which I need to create a data file that I can then send off to be processed. This data file needs to be created within the stored procedure.

URL: http://vyaskn.tripod.com/code/generate_inserts.txt

navigate to the above URL, you will get the answer for your query.

nigel. SSChampion Points: 11636 More actions · Answer 6

Have a look at this article http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

Nigel
How to post questions