troubleshooting Question

SQL Server 2008 - output query to a file

Avatar of joos01
joos01 asked on
SQLMicrosoft SQL Server* T-SQL
4 Comments1 Solution71 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Vitor Montalvão
IT Engineer
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros