Skip to main content
Database Administrators

Return to Question

Tweeted twitter.com/StackDBAs/status/753731834794635265
Improved title, formatting, and tags
Source Link
Solomon Rutzky
Solomon Rutzky
  • 69.5k
  • 8
  • 155
  • 300

Alternative to xp_cmdshell for emailing a report as a CSV file

I've got an issue that I could do with some ideas as to how to achieve what's needed without using (or enabling) xp_cmdshellxp_cmdshell if possible.

I know that xp_cmdshellxp_cmdshell itself poses risks, even with a proxy account, however - in our environment it's disabled and convincing the IT Manager to enable it is going to be difficult at best.

The issue I'm having is that I have a stored procedure, it looks up data from various tables and puts it all into a table variable.

I want to do the following:

  • Export the content of the table variable to a CSV file.
  • Attach the created CSV file to an e-mail and send this to a specific address.

This needs to happen automatically without user input of any kind, the e-mail side of it will be provided by variables in the same stored procedure.

I've thought of 2 ways to potentially do this, both use bcp and xp_cmdshellxp_cmdshell.

The first method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to fire off an SSIS Package or SQL Agent Job which will query the temp table, export to CSV and e-mail for me.

The second method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to select the temporary table into a CSV file, then use sp_send_dbmailsp_send_dbmail to send it.

A third alternative might be to fire another SP/function and have it do either of the above methods instead, keeping the new functionality separate - but this is simply a thought to keep things simple.

So, what I'm asking for is any ideas as to how I can achieve this safely, preferably without using xp_cmdshellxp_cmdshell. I can't use powershellPowerShell or a similar technology as no-one here knows powershellPowerShell, although I'm familiar with VB  .NetNET, however I'm not sure how that helps (if at all).

If you need more information to be able to help, please let me know.

Alternative to xp_cmdshell

I've got an issue that I could do with some ideas as to how to achieve what's needed without using (or enabling) xp_cmdshell if possible.

I know that xp_cmdshell itself poses risks, even with a proxy account, however - in our environment it's disabled and convincing the IT Manager to enable it is going to be difficult at best.

The issue I'm having is that I have a stored procedure, it looks up data from various tables and puts it all into a table variable.

I want to do the following:

  • Export the content of the table variable to a CSV file.
  • Attach the created CSV file to an e-mail and send this to a specific address.

This needs to happen automatically without user input of any kind, the e-mail side of it will be provided by variables in the same stored procedure.

I've thought of 2 ways to potentially do this, both use bcp and xp_cmdshell.

The first method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to fire off an SSIS Package or SQL Agent Job which will query the temp table, export to CSV and e-mail for me.

The second method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to select the temporary table into a CSV file, then use sp_send_dbmail to send it.

A third alternative might be to fire another SP/function and have it do either of the above methods instead, keeping the new functionality separate - but this is simply a thought to keep things simple.

So, what I'm asking for is any ideas as to how I can achieve this safely, preferably without using xp_cmdshell. I can't use powershell or a similar technology as no-one here knows powershell, although I'm familiar with VB  .Net, however I'm not sure how that helps (if at all).

If you need more information to be able to help, please let me know.

Alternative to xp_cmdshell for emailing a report as a CSV file

I've got an issue that I could do with some ideas as to how to achieve what's needed without using (or enabling) xp_cmdshell if possible.

I know that xp_cmdshell itself poses risks, even with a proxy account, however - in our environment it's disabled and convincing the IT Manager to enable it is going to be difficult at best.

The issue I'm having is that I have a stored procedure, it looks up data from various tables and puts it all into a table variable.

I want to do the following:

  • Export the content of the table variable to a CSV file.
  • Attach the created CSV file to an e-mail and send this to a specific address.

This needs to happen automatically without user input of any kind, the e-mail side of it will be provided by variables in the same stored procedure.

I've thought of 2 ways to potentially do this, both use bcp and xp_cmdshell.

The first method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to fire off an SSIS Package or SQL Agent Job which will query the temp table, export to CSV and e-mail for me.

The second method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to select the temporary table into a CSV file, then use sp_send_dbmail to send it.

A third alternative might be to fire another SP/function and have it do either of the above methods instead, keeping the new functionality separate - but this is simply a thought to keep things simple.

So, what I'm asking for is any ideas as to how I can achieve this safely, preferably without using xp_cmdshell. I can't use PowerShell or a similar technology as no-one here knows PowerShell, although I'm familiar with VB.NET, however I'm not sure how that helps (if at all).

If you need more information to be able to help, please let me know.

Source Link
Arkane
Arkane
  • 51
  • 1
  • 2

Alternative to xp_cmdshell

I've got an issue that I could do with some ideas as to how to achieve what's needed without using (or enabling) xp_cmdshell if possible.

I know that xp_cmdshell itself poses risks, even with a proxy account, however - in our environment it's disabled and convincing the IT Manager to enable it is going to be difficult at best.

The issue I'm having is that I have a stored procedure, it looks up data from various tables and puts it all into a table variable.

I want to do the following:

  • Export the content of the table variable to a CSV file.
  • Attach the created CSV file to an e-mail and send this to a specific address.

This needs to happen automatically without user input of any kind, the e-mail side of it will be provided by variables in the same stored procedure.

I've thought of 2 ways to potentially do this, both use bcp and xp_cmdshell.

The first method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to fire off an SSIS Package or SQL Agent Job which will query the temp table, export to CSV and e-mail for me.

The second method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to select the temporary table into a CSV file, then use sp_send_dbmail to send it.

A third alternative might be to fire another SP/function and have it do either of the above methods instead, keeping the new functionality separate - but this is simply a thought to keep things simple.

So, what I'm asking for is any ideas as to how I can achieve this safely, preferably without using xp_cmdshell. I can't use powershell or a similar technology as no-one here knows powershell, although I'm familiar with VB .Net, however I'm not sure how that helps (if at all).

If you need more information to be able to help, please let me know.