Generic SPROC to write data to text file.

Question

Generic SPROC to write data to text file.

npatel0130

Valued Member

Points: 71
More actions
October 7, 2009 at 2:46 pm

#216138

I have multiple SPROC which generates data into tables. I need to write a one Generic SPROC which can take any sproc as input and write data out to text file. I am using SQL 2000.

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

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

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

What format shall the text file be in? CSV? TSV? Fixed Length Fields? Space Delimited? True CSV? Text Qualified CSV? Other Delimited? Do the files need column headers? What should the row terminator be? Can you use xp_CmdShell? If not, can you create a Linked Text Server? If not, has "Ad-hoc queries" been enabled to use OPENDATASOURCE or OPENROWSET? What will the "wrapper" proc be allowed as privs? And finally, is SQL Server 32 bit or 64 bit a lot of that won't work on 64 bit if we try to use the text drivers from the MS-JET engine?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

npatel0130 Valued Member Points: 71 More actions · Answer 2

Jeff Moden (10/7/2009)
What format shall the text file be in? CSV? TSV? Fixed Length Fields? Space Delimited? True CSV? Text Qualified CSV? Other Delimited? Do the files need column headers? What should the row terminator be? Can you use xp_CmdShell? If not, can you create a Linked Text Server? If not, has "Ad-hoc queries" been enabled to use OPENDATASOURCE or OPENROWSET? What will the "wrapper" proc be allowed as privs? And finally, is SQL Server 32 bit or 64 bit a lot of that won't work on 64 bit if we try to use the text drivers from the MS-JET engine?

Jeff,

The output file needs to be in CSV with variable length fields with the column headers. No restriction on the delimited. I can use the xp_CmdShell. I am using 64bit SQL server.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

What's the reasoning for pushing the data from the SQL server, rather than using an external component to pull it out?

You tend to get a LOT more options, better securability, etc.... with an external component to extract and format the data, that by having a single generic component doing it from inside the SQL server.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

npatel0130 Valued Member Points: 71 More actions · Answer 4

We are implementing ESB using BIZTalk and rewriting ATL code which populates the SQL DB for the customer. Customer wants to use BAM PORTAL. We have solution to populate the BAM DB from SQL DB once the rewrite is completed (6 months). For 6 months I want to populate the BAM data via orchstration by reading the csv file produce by the SPROC.

There is no problem with using external compinent to pull it out as long as i can schedule the job and does not cost.

Thanks for you help.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 5

npatel0130 (10/8/2009)
Jeff Moden (10/7/2009)
What format shall the text file be in? CSV? TSV? Fixed Length Fields? Space Delimited? True CSV? Text Qualified CSV? Other Delimited? Do the files need column headers? What should the row terminator be? Can you use xp_CmdShell? If not, can you create a Linked Text Server? If not, has "Ad-hoc queries" been enabled to use OPENDATASOURCE or OPENROWSET? What will the "wrapper" proc be allowed as privs? And finally, is SQL Server 32 bit or 64 bit a lot of that won't work on 64 bit if we try to use the text drivers from the MS-JET engine?
Jeff,
The output file needs to be in CSV with variable length fields with the column headers. No restriction on the delimited. I can use the xp_CmdShell. I am using 64bit SQL server.

Perfect. In that case, (although I agree with Matt Miller), take a look at BCP in Books OnLine. You'll probably have to "stage" the data in a table consisting of all varchar columns and include the "first" row as a "header", but it will all run very fast. I strongly recommend the use of the -T (trusted connection) option so as to not reveal any passwords in open code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

npatel0130 (10/8/2009)
We are implementing ESB using BIZTalk and rewriting ATL code which populates the SQL DB for the customer. Customer wants to use BAM PORTAL. We have solution to populate the BAM DB from SQL DB once the rewrite is completed (6 months). For 6 months I want to populate the BAM data via orchstration by reading the csv file produce by the SPROC.
There is no problem with using external compinent to pull it out as long as i can schedule the job and does not cost.
Thanks for you help.

It seems a strang choice to put a fair amount of throw-away work into writing an external file, only to read it again in Biztalk. Why not start by pulling the data directly from SQL Server using BizTalk? That seems to be by far the cleanest method (ond from the sounds of it - no throw-away work?) You can simply have biztalk poll the server on a regular basis for data.

Not that Jeff's solution isn't fine (it's actually very fast if you can work with the formatting options you get from bcp), but you might also consider SSIS (since it can be run externally and scheduled that way, with whatever credentials you wish). SSIS will have the advantage to giving you somewhat easier access to the various output options. inlcuding XML, etc...).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?