locked
Export Data to new Excel File without using XP_CMDSHELL RRS feed

  • Question

  • Hello out there!

    I have to make it possible that a SSMS User would be able to export Data to a new (not existing) Excel File. Because of security issues xp_cmdshell isn't allowed. SQLCMD as I know, can only be used outside SSMS?! I'd like to Use CREATE or SELECT INTO to Create and fill the File.

    My Idea was to use Common Language Runtime (CLR) with VB.NET to start an EXCEL Object and create the needed Columns and save the file. The the User can easily use INSERT. Unfortunately that doesn't seem to work. The Code for starting Excel runs without an Error, but the Excel Object did not be created. When I try to save the File inside the clr_function I got an Error.

    Do you have other secure solution(s) for that Problem?

    Best Regards

    Vincent

    Common Language Runtime (CLR)
    Common Language Runtime (CLR)
    Common Language Runtime (CLR)
    Common Language Runtime (CLR)

    Thursday, November 6, 2014 6:49 AM

Answers

All replies

  • what is the error you are getting? Is it a possibility to use SSIS for data export 

    Satheesh
    My Blog | How to ask questions in technical forum


    Thursday, November 6, 2014 7:51 AM
  • Why not use export import wizard which is available from SSMS itself?

    http://blog.winhost.com/exporting-sql-server-data-with-import-and-export-wizard/


    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, November 6, 2014 8:14 AM
  • Hi Vincent,

    As other post, you can use SSIS and Import/Export wizard to export data from SQL Server to Excel. Besides the two methods, you can also export data to Excel by using OPENROWSET, as is shown below.

    sp_configure 'show advanced options', 1;
    RECONFIGURE;
    sp_configure 'Ad Hoc Distributed Queries', 1;
    RECONFIGURE;
    GO
    
    insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
        'Excel 12.0; Database=C:\test.xlsx; HDR=YES', 
        'SELECT name,test1 FROM [Sheet1$]') select name,test1 from ABCTest.dbo.ABC

    In addition, other than executing SQLCMD scripts from the Command Prompt, we can also edit and execute SQLCMD Scripts with Query Editor in SQL Server Management Studio(SSMS). To use the Database Engine Query Editor to write or edit SQLCMD scripts, we should enable the SQLCMD scripting mode. We can enable it by clicking the SQLCMD Mode icon in the toolbar or by selecting SQLCMD Mode from the Query menu. With the SQLCMD utility, we can export data to a CSV file , the CSV file opens natively with Microsoft Excel. Below is a simple example for you.

    :out C:\testoutput.csv
    select name,test1  from ABCTest.dbo.ABC
    !!DIR
    !!:GO
    GO


    For more details about using the SSMS Query Editor in SQLCMD Mode, please review the following links.
    http://msdn.microsoft.com/en-us/library/ms174187.aspx
    http://www.mssqltips.com/sqlservertip/2311/using-the-ssms-query-editor-in-sqlcmd-mode/


    Thanks,
    Lydia Zhang


    Friday, November 7, 2014 3:14 AM
  • Sorry for my late answer!!

    The Skill is to create Excelfiles dynamicly with TSQL. We have some Powerusers with own Database and owner rights who can do what they want in ther DB. Now they like to write there own Skripts that will create Files like 2012.xlsx, 2013.xlsx etc.. The Files first have to be created by the query. Like a SELECT ... INTO openrowset. That's what they want.

    No one of them knows SSIS, so i would have to do it myself. It would be better they can do it by semselfs. The Export Wizard would have to know a Filename so it's also no good for automation.

    I will try out SQLCMD. Maybe the QueryEditor is a way for us. It was new to me. I'll test it and let you know.

    Thanks for your Answers

    Vincent

    Thursday, November 20, 2014 6:22 AM