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