How to get output from a select to a file ?

Question

Post reply

How to get output from a select to a file ?

ben.brugman

SSChampion

Points: 13369
More actions
May 13, 2016 at 2:34 am

#316458

From within the SQL environment (say a stored procedure) I would like to output the result from a select to a file on disk.
I know this is possible using xp_CmdShell, but the target system does not allow this.
How can I achieve this from within a stored procedure ?
Thanks,
Ben

Viewing 13 posts - 1 through 12 (of 12 total)

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

sestell1 SSChampion Points: 10230 More actions · Answer 1

Interesting question.

I've always used a combination of xp_cmdshell and bcp to move data to/from files within T-SQL.

You could also use OLE automation, but that would require that you enable 'Ole Automation Procedures' on the server, which I'm guessing you won't be able to do either.

http://stackoverflow.com/questions/8132663/creating-a-text-file-on-local-machine-using-sql-server-2008

If you just need a file extract, you could use database mail to execute your query and attach the results as a file. You'd then have to manually save the attachment to your file location though if you need it in a particular location.

If you don't necessarily need to do it from T-SQL, I'd recommend looking into SSIS. You could build an SSIS package to export the data you need to a file located where you need it, then schedule the package as a job in SQL Server. Actually, you could even then use T-SQL to trigger the job using a call to the sp_start_job system stored procedure. 😉

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

For a one time or manual process, you could choose the option "Results to File".

You might be able to go around the xp_cmdshell process by creating a job with a cmdexec step. What you really need is bcp, so no matter how you call it. The issue with this is that calling a job is an asynchronous process.

SSIS is another option if you want to stay away from the OS commands.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ben.brugman SSChampion Points: 13369 More actions · Answer 3

sestell1 (5/13/2016)
Interesting question.
I've always used a combination of xp_cmdshell and bcp to move data to/from files within T-SQL.

As said this poses a security 'breach'.

You could also use OLE automation, but that would require that you enable 'Ole Automation Procedures' on the server, which I'm guessing you won't be able to do either.
http://stackoverflow.com/questions/8132663/creating-a-text-file-on-local-machine-using-sql-server-2008

I do have to look into this, but I think you are right that I won't be able to do this either.

If you just need a file extract, you could use database mail to execute your query and attach the results as a file. You'd then have to manually save the attachment to your file location though if you need it in a particular location.

Yes, have to check with the 'customer' if this is an option. (He/She want the file, maybe a mail wil be sufficient).

If you don't necessarily need to do it from T-SQL, I'd recommend looking into SSIS. You could build an SSIS package to export the data you need to a file located where you need it, then schedule the package as a job in SQL Server. Actually, you could even then use T-SQL to trigger the job using a call to the sp_start_job system stored procedure. 😉

Luis Cazares (5/13/2016)
For a one time or manual process, you could choose the option "Results to File".
You might be able to go around the xp_cmdshell process by creating a job with a cmdexec step. What you really need is bcp, so no matter how you call it. The issue with this is that calling a job is an asynchronous process.
SSIS is another option if you want to stay away from the OS commands.

SSIS, I am not a huge fan of this either, there always seems to be a problem with deployment and credential issues.

As for the OS commands, an option (and I think this is the best option) is to start the process from a batch, then using BCP with output to get the results. Using the windows schedular to start this process. I would have prevered to start the process from within SQLServer (from a stored procedure), but a batch would be acceptable. And I think a batch is easier to deploy (and maintain) than an SSIS solution.

I haven't tried the suggested solutions or my solution at this moment.

Thanks for your time and attention.

Ben

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

ben.brugman (5/13/2016)
sestell1 (5/13/2016)
Interesting question.
I've always used a combination of xp_cmdshell and bcp to move data to/from files within T-SQL.
As said this poses a security 'breach'.

Nah. No security breach. Not even a bad practice if it's limited to well written stored procedures and DBAs. If you give privs to non-DBAs to run xp_CmdShell directly, that THAT would be a very poor security practice. Even with xp_CmdShell enabled, only those with sysadmin privs (presumably only DBAs) can run it directly.

If anyone else can run it directly, then you do need to fix that because that means that you've either given them or an app the wrong privs. If you gave a user privs to run it directly through the proxy, then that's also a problem that needs to be fixed.

But DBAs, well written (SQL and DOS injection-proofed and limited to only a given task) stored procedures, and jobs using it are not a bad practice.

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

sestell1 SSChampion Points: 10230 More actions · Answer 5

ben.brugman (5/13/2016)
SSIS, I am not a huge fan of this either, there always seems to be a problem with deployment and credential issues.

If you did decide to go this route, I'd recommend setting up all of your SSIS package connections using trusted connections so that they use the credentials of whomever runs the package.

In SQL Server, set up a CREDENTIAL with an account that has the required access. Use the CREDENTIAL to create a PROXY account for SSIS execution.

When you schedule the job and create the step to run your SSIS package, change the Execute As dropdown to the PROXY account you created. This should avoid any credential issues.

I don't deploy SSIS packages into the Integration Services service on the server. I put then in a folder on the server, and execute them from there in the file system.

Jeff Moden (5/13/2016)
Nah. No security breach. Not even a bad practice if it's limited to well written stored procedures and DBAs. If you give privs to non-DBAs to run xp_CmdShell directly, that THAT would be a very poor security practice. Even with xp_CmdShell enabled, only those with sysadmin privs (presumably only DBAs) can run it directly.

Thank you Jeff! I was starting to feel like I was the only one that felt this way about enabling and using xp_cmdshell.

ben.brugman SSChampion Points: 13369 More actions · Answer 6

Jeff Moden (5/13/2016)Nah. No security breach. Not even a bad practice if it's limited to ...

In general most practises do not form a security breach, provided that the other levels of security are implemented perfectly. So when you have a good firewall and trustworthy coworkers, this would make all other security measures obsolete. *)

In real life when security is breached, it is very often because a number of 'levels' have been compromised. So upping the security although it seems 'obsolete' often does improve the overal security.

And allthough I have NO say in the configuration of xp_cmdshell, I do agree not allowing xp_cmdshell makes security tighter.

But thanks for the observation that this does not have to lead to a security breach if usage is 'limited' and correctly used.

This might persuade the 'controlers'.

Ben

*)

I hope the gist of this is clear, although this might be a bit 'too short-sighted'.

ManicStar SSCoach Points: 17992 More actions · Answer 7

ManicStar

SSCoach

Points: 17992

May 20, 2016 at 8:03 am

#1880495

You could do it with PowerShell pretty easily.

sestell1 SSChampion Points: 10230 More actions · Answer 8

Manic Star (5/20/2016)
You could do it with PowerShell pretty easily.

Is it possible to run Powershell scripts from within SQL Server though without enabling xp_CMDShell?

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 9

ben.brugman (5/19/2016)
And allthough I have NO say in the configuration of xp_cmdshell, I do agree not allowing xp_cmdshell makes security tighter.

Not allowing it does two things...

1. It disallows some incredible and very secure functionality.

2. It gives everyone a nice warm fuzzy that takes an attacker less than 3 milliseconds to override.

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

Sergiy SSC Guru Points: 110206 More actions · Answer 10

ben.brugman (5/19/2016)
I do agree not allowing xp_cmdshell makes security tighter.

There is simply no way to actually disallow xp_cmdshell.

If you know one - please share it with us.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 11

Sergiy (5/23/2016)
ben.brugman (5/19/2016)
I do agree not allowing xp_cmdshell makes security tighter.
There is simply no way to actually disallow xp_cmdshell.
If you know one - please share it with us.

Heh... why bother? Someone getting in with SA privs can use PowerShell, an EXEC task, etc, etc. Besides, if the attacker gets in, why would they even bother with xp_CmdShell? They've already got the payload they were looking for.

The key is to give sysadmin and control server privs only to highly trusted individuals that actually need such privs to do their jobs and then keep the bad guys out with effective password policies, dynamic ports, bullet proofing code for SQL Injection (#1 method of databreaches even to this day except maybe for dummies that share passwords) etc, etc.

--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: 1003865 More actions · Answer 12

ben.brugman (5/13/2016)
From within the SQL environment (say a stored procedure) I would like to output the result from a select to a file on disk.
I know this is possible using xp_CmdShell, but the target system does not allow this.
How can I achieve this from within a stored procedure ?
Thanks,
Ben

Maybe EXEC task in a job?

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