SQL Server 2008 - output query to a file

joos01
joos01 used Ask the Experts™
on
Hi all - I am hoping that someone can help me with some SQL Server scripting.

I have a SQL server stored procedure that retrieves a bunch of data via a cursor.  I want to be able to loop through the cursor and write the results to an output file.  I initially coded the stored procedure to use SQL Server’s OLE automation functions (sp_OACreate, sp_OAMethod and sp_OADestory), but I do not have execute privileges for this procedures.  I spoke with the DBA about this issue and he was not keen on turning the OLE Automation feature on for the server (security threat -
 multiple databases exist on this server).  

I then changed the logic to use the xp_cmdshell commands.  Again, I do not have execute privileges for this SQL Server functionality (again the DBA is hesitant).

Does anyone have a way (in SQL Server) to send the query data to an output file without using the above mentioned logic?  Any ideas, thoughts or suggestions are much apreciated.

TIA
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
IT Engineer
Distinguished Expert 2017
Commented:
Since it looks that you have limited permissions you should start to ask the DBA if you are allowed to output to a file. If the answer is no then there's no solution for you.

PS: You can always use sqlcmd to run a remote query on the server and have the output redirect to a local text file.
joos01

Author

Commented:
Thanks - I am going to work with the DBA to find the best solution give the restrictions that he has placed on me....
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
joos01, a feedback will be appreciated.
Cheers
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Recommendation to close this question by accepting the above comment as solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial