Avatar of joos01
joos01
 asked on

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
SQLMicrosoft SQL Server* T-SQL

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
joos01

ASKER
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ão

joos01, a feedback will be appreciated.
Cheers
Vitor Montalvão

Recommendation to close this question by accepting the above comment as solution.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes