Exporting query results to a file on the fly

Question

Hi all,

I need to export the results of a query to a csv file and put the file on a network shared folder.

1. Is it possible to achieve this within a stored procedure?
2. If yes, comes yet another constraint: can I achieve this without sysadmin privileges, aka without using xp_cmdshell + BCP utility?
3. If no to 2., does the caller have to have sysadmin privileges or would it suffice if the SP owner has sysadmin privileges?

Here are some more details to the problem: The SP must export and transfer the file on the fly and raise error if something went wrong. The caller must get a response immediately, i.e. in case of no error, he can assume that the results are successfully transferred to the folder. Therefore, a DTS/SSIS job that runs every N minutes is not an option. I know the problem smells like I will have to do this at application level, but I would be more than happy if all those stuff could be done from T-SQL.

Answer 1

You can build a SQL Agent job andkick it off via system SP's from a trigger or SP. The job may call SSIS or bulk dump scrits... returning instant error message may be an issue though

In general, it's quite unusual requirement - what are you trying to accomplish?

UPDATE: After some more thinking - this is a design issue and I have not been able to find a solution simply by using SQL Server SP's.

IN the past - this is what I did:

• on the app level - implement async process where user pushes a button, requesting a file download; the app accepts and let user go
• the user can check the status via status page or will get email when it's done or error occured
• in the mean time the application layer, kicks of either SSIS package or SQL Agent Job
• If parameters are needed - use design and implement special table: JOB_PARAMETERS - where you would put the parameters
• you would also need to create more tables to manage the jobs and store job status and communicate with application layer
• you may want o use SQL Server Broker on DB level
• You may want to use MSMQ on the app level

This is not easy, but this is the most efficient way to export data, where it goes from DB to a file, without traveling to app server and user PC via browser.

Answer 2

Can you use OLE Automation? It's ugly, and you could probably use some set based string building techniques instead of the cursor but here goes...

    Declare @Dir varchar(4000)
    Set @Dir = 'c:\some\path\accessible\to\SQLServer'

    If @Dir IS NULL
       Begin
        print 'dir is null.'
        Return 1
       End

    declare
        @FilePath as varchar(255),
        @DataToWrite as varchar(8000)

    If right(@DataDir,1) <> '\'
       Set @DataDir = @DataDir + '\'

    Set @FilePath = @DataDir + 'filename.csv' 

    DECLARE @RetCode int , @FileSystem int , @FileHandle int

    EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT
    IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0)
    begin
      RAISERROR ('could not create FileSystemObject',16,1)

    End

    declare @FileExists int

    Execute @RetCode = sp_OAMethod @FileSystem, 'FileExists', @FileExists OUTPUT, @FilePath
    --print '@FileExists = ' + cast(@FileExists as varchar)

    If @FileExists = 1
    Begin
        RAISERROR ('file does not exist',16,1)
        /*return 1*/
    End

    --1 = for reading, 2 = for writing (will overwrite contents), 8 = for appending
    EXECUTE @RetCode = sp_OAMethod @FileSystem , 'OpenTextFile' , @FileHandle OUTPUT , @FilePath, 8, 1
    IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
    begin
        RAISERROR ('could not create open text file',16,1)
    End


    DECLARE CSV CURSOR
    READ_ONLY
    FOR 

    Select Anything From MyDataTable
    order by whatever

    DECLARE @fld1 nvarchar(50)
        ,@fld2 nvarchar(50)

    OPEN CSV

    FETCH NEXT FROM CSV INTO @fld1, @fld2 

    WHILE (@@fetch_status <> -1)
    BEGIN
        IF (@@fetch_status <> -2)
        BEGIN

            Set @DataToWrite = @fld1 + ',' + @fld2 + char(13) + char(10) 
            EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Write' , NULL , @DataToWrite 

            IF (@@ERROR|@RetCode > 0)
               begin
                RAISERROR ('could not write to file',16,1)

               End
        END

        FETCH NEXT FROM OpenOrders INTO @fld1, @fld2 

    END


CLOSE CSV
DEALLOCATE CSV

EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Close' , NULL
IF (@@ERROR|@RetCode > 0)
RAISERROR ('Could not close file',16,1)

EXEC sp_OADestroy @FileSystem

return 0

End

Answer 3

Generally, no, this kind of work can't be done without a lot of fuss and effort and sysadmin rights.

SQL is a database engine, and is focused on database problems, and so and quite rightly has very poor file manipulation tools. Work-arounds include:

• xp_cmdshell is the tool of choice for file manipulations.
• I like the sp_OA* solution myself, 'cause it gives me flashbacks to SQL 7.0. But using those functions always made me nervous.
• You might be able to do something with OPENROWSET, where the target of an insert is a file defined with this function. Sounds unlikely, might be worth a look.
• Similarly, a linked server definition might be used as a target for inserts or select...into... statements.

Security seems to be your showstopper. By and large, when SQL shells out to the OS, it has all the rights of the NT account under which the SQL service started up on; if you'd want to limit network access, configure that account carefully (and never make it domain admin!)

It is possible to call xp_cmdshell as a user without sysadmin rights, and to configure these calls to not have the same access rights as the SQL Service NT account. As per BOL (SQL 2005 and up):

---

xp_cmdshell Proxy Account
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.

---

So your user logs in with whatever user rights (not sysadmin!) and executes the stored procedure, which calls xp_cmdshell, which will "pick up" whatever proxy rights have been configured. Again, awkward, but it sounds like it'd do what you'd want it to do. (A possible limiting factor is that you only get the one proxy account, so it has to fit all possible needs.)

Honestly, it sounds to me like the best solution would be to:

• Identify the source of the call to the stored procedure,
• Have the procedure return the data to be written to the file (you can do all your formatting and layout in the procedure if need be), and
• Have the calling routine manage all the file preparation steps (which could be as simple as redirecting data returned from SQL into an opened file)

So, what does launch the call to the stored procedure?

Answer 4

It seems to me, that you are not waiting for an SQL code in the answer on your question. The main aspect of you question is the security aspect. What should you do to implement your requirement without sysadmin privileges and without a new security hole? This is your real question I think.

I see at least 3 ways to solve your problem. But first of all a short explanation why sysadmin privileges exists in all solutions based on Extended Stored Procedures. Extended Stored Procedures like xp_cmdshell are very old. They existed at least before SQL Server 4.2, the first Microsoft SQL Server running under the first Windows NT (NT 3.1). In the old version of SQL Server I was not security restriction to execute such procedures, but later one made such restrictions. It is important to understand, that all general purpose procedures which allow starting any process under SQL Server account like xp_cmdshell and sp_OACreate must have sysadmin privileges restriction. Only a task oriented procedures with a clear area of usage and role based permissions can solve the problem without a security hole. So this is the 3 solution ways which I promised before:

• You create a new SQL account on you SQL server with sysadmin privileges. Then you create a stored procedure which use some from Extended Stored Procedures like xp_cmdshell or sp_OACreate and technically implement you requirements (export some information into a CSV file). With respect of EXECUTE AS Clause (see http://msdn.microsoft.com/en-us/library/ms188354.aspx) you configure the created stored procedure so, that it runs under the account with sysadmin privileges. You delegate the execution of this procedure to users with a some SQL role, to be more flexible from the side of delegation of permission.
• You can use CLR Stored Procedures instead of xp_cmdshell and sp_OACreate. You should also use role based permissions on the procedure created.
• The end-user doesn’t call directly any SQL stored procedure what you create. There is exists a piece of software (like WCF service or a Web Site) which call your SQL stored procedure. You can implement the export to CSV file inside of this software and not inside of any SQL stored procedure.

In all implementation ways you should exactly define where you will hold the password of the account with which you access to the file system. There are different options which you have, all with corresponding advantages and disadvantages. It's possible to use impersonation to allow access to the file system with the end-user‘s account. The best way depends on the situation which you have in your environment.