Export to CSV and email

Question

Export to CSV and email

Frank Cazabon

Hall of Fame

Points: 3467
More actions
September 17, 2014 at 11:51 am

#293326

Hi,
I need to have a process extract some data, export it to CSV and then send it as a plain (non-MIME) message, or as a single-part MIME message. Multi-part MIME messages can't be used.
This process will be called about 20 times daily with different subsets of data.
What is the best way to approach this?
September 17, 2014 at 11:11 pm

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

Frank Cazabon (9/17/2014)
Hi,
I need to have a process extract some data, export it to CSV and then send it as a plain (non-MIME) message, or as a single-part MIME message. Multi-part MIME messages can't be used.
This process will be called about 20 times daily with different subsets of data.
What is the best way to approach this?

If you don't mind using SSIS, here's one way... just "skip the zip"...

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64028/

--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)

Frank Cazabon Hall of Fame Points: 3467 More actions · Answer 2

Jeff Moden (9/17/2014)
If you don't mind using SSIS, here's one way... just "skip the zip"...
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64028/

Thanks Jeff,

maybe I should have mentioned the client only has SQL Express 2012. I have a feeling that SSIS is not available in the Express versions, is that right?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

jacksonandrew321 (9/17/2014)
Hi Jeff,
What should I do if we have to deal with Multi-part MIME messages?

To be honest, I have no idea on that. I've never tried such a thing.

--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)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

Frank Cazabon (9/18/2014)
Jeff Moden (9/17/2014)
If you don't mind using SSIS, here's one way... just "skip the zip"...
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64028/
Thanks Jeff,
maybe I should have mentioned the client only has SQL Express 2012. I have a feeling that SSIS is not available in the Express versions, is that right?

That's correct. SQL Express 2012 is the data engine only and then with some limitations. If you want to do this from there, you'll either need to do it from the DOS prompt or bring xp_CmdShell into play.

As a bit of a sidebar, I've always been a bit angry that MS never put better file handling features into T-SQL and says to "Use SSIS instead".

--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)

Frank Cazabon Hall of Fame Points: 3467 More actions · Answer 5

Thanks,

I'll probably end up doing this in my application rather than in the database.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

Frank Cazabon (9/24/2014)
Thanks,
I'll probably end up doing this in my application rather than in the database.

That's where most people end up and that's not a bad idea at all. I'm sure I'm preaching to the choir but mind the privs. The application shouldn't have privs to do anything at the operating system level except in the directories it needs to work with. Also remember that there is such a thing as "DOS INJECTION" which also covers things like PowerShell, etc, etc. If possible, the commands created to do the necessary things should be parameterized in the app. If that's not possible and concatenation is used to build the commands, they must absolutely go through a "command line delouser" to make sure no one comes in through the back door.

--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)

Nadrek SSC-Insane Points: 20039 More actions · Answer 7

Personally, I'd recommend doing this from a DOS prompt/Windows scheduled task/Enterprise scheduler scheduled task from the get-go; the business requirement will only get bigger from here!!! The next thing you know, you're going to have to deal with gigabytes of data, compress it, encrypt it, and send it by email, FTP, SFTP, FTPS, and verify an acknowledgement file that counts how many rows/segments/pieces were received.

I also find it horrifying when you

A) Deliberately have a SQL Server do communications to the Internet

A1) possibly excepting Microsoft patches

A2) possibly excepting anti-virus updates

B) Deliberately have a SQL Server in a position you're likely to use up lots of CPU and/or RAM outside of SQL

B1) like (some) encryption

B2) like (some) compression

So, the basic idea:

Write a batch file (or an application in any language you like, if you wish).

With a batch file:

1) Write one or more .sql files containing the T-SQL to execute your code or stored procedure

2) Update the batch file with a "sqlcmd" statement that executes your .sql file(s)

2a) Ideally use Windows authentication, and make sure the AD account that will run it has (at least) the minimum access required to both database and file location

3) Update the batch file with a statement to do compression/encryption/etc.

4) Update the batch file with a statement to transmit the data

4a) For email, try something like blat[/url], which is in the public domain, and while I haven't ever tried it, appears to be able to do multipart MIME as well if you can figure it out.

4b) For SFTP, try Putty[/url], which is MIT licensed.

5) Update the batch file to eliminate the temporary file you juts sent

5a) Compress and archive it if you need to; just don't forget to clean up old ones so you don't run out of space.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

You're a man after my own heart, Nadrek. I had a fairly major project that I solved using copies of SQL Express setup as "bricks" to do nothing but do the FTP/SFTP work and move the files (thousands per day) in a common area for processing by a system (I called it "Steps", which stands for Simplified Text Extraction and Parsing Sytem... hence the need for "bricks" 🙂 ) on SQL Server that I built. Most people balk at such methodology because it's so "old school" and so I deferred to what folks normally do with such things... build an app or use SSIS. I don't do either. I also didn't use things like Putty or Blat (although their enhancements to the command line would be useful) ... it was all via batch files (long live the Command Prompt :-D) and T-SQL.

--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)