Exporting Data to a Flat File

[grayt26]

Hi All,

I have a lengthy procedure that i run that usually takes a few hours but at the end of that procedure I would like it to be able to export a table out to a File File format preferable comma delimited. Is there a way of putting code into the procedure that will do this for me so I don't have to hang around and wait for procedure to finish then use the export/import package?

Thanks for reading.

 

[AlexCuse]

If you have an existing DTS package (which it sounds like you might) you can call this from your t-sql code.

http://www.sqldts.com/210.aspx

If it's not a DTS package you are using to export, then I would have a look at the bcp utility in BOL.

Hope this helps,

ALex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[nice95gle]

You have a couple of ways:

You can create a DTS/SSIS package to export your file then execute your package from your SP, or you can use a BCP command in your SP to copy the data over.


Good luck

Well Done is better than well said
- Ben Franklin

 

[nice95gle]

Sorry for repeating what Alex said, he posted as I was typing.

Well Done is better than well said
- Ben Franklin

 

[AlexCuse]

Nice95 - Haven't seen you around for a while. Hope you've been well. I forgot about SSIS, thx for pointing it out

grayT26 - If SSIS is what you've got set up already, I've found the easiest way to start it from t-sql is to create a job to run it, and use sp_start_job to start the job. There is probably a better way to do this though.

Hope it helps,

Alex



[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[nice95gle]

Hey Alex, I've been around just really getting my hands dirty with 2005. So time spent posting has be limited. But I've been great thanks for asking.


Well Done is better than well said
- Ben Franklin

 

[grayt26]

Hi thanks for this, I will probably use the spss route. I can create and save the SPSS file but how do i execute it from the SP?

Thanks for the help.

 

[nice95gle]

I don't think you would be able to execute the SPSS file directly from a SP you can try using xp_cmdshell. Use a .bat file to execute your SPSS file, then call your .bat file from the SP using xp_cmdshell. I can assume you know dos commands, if you need help let me know.

Good Luck


Well Done is better than well said
- Ben Franklin