SQL Server 2008 - output query to a file

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
joos01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joos01Author Commented:
Thanks - I am going to work with the DBA to find the best solution give the restrictions that he has placed on me....
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
joos01, a feedback will be appreciated.
Cheers
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comment as solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.