Post reply

How to export query result set into text/excel file?

  • November 9, 2009 at 3:21 am

    #216530

    How to export query result set into Text/Excel file using query or stored procedure?

    Say query is:

    Select * from sys.objects

    i want to save this result in text/excel file but through query.

  • November 9, 2009 at 5:16 am

    #1076598

    You may try using the openrowset() function .

    http://technet.microsoft.com/en-us/library/ms190312.aspx

    An example is provided in the blog below:

    http://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/

  • November 10, 2009 at 6:15 am

    #1077173

    While running query in above link i got following error.

    As i do not have administrator permission, can i still import data to excel sheet.

    Msg 15281, Level 16, State 1, Line 1

    SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

  • November 10, 2009 at 3:30 pm

    #1077565

    Are you using SSMS? In one of the menu options you should be able to direct your query output to a text file instead of a table.

    Joie Andrew
    "Since 1982"

  • November 10, 2009 at 4:01 pm

    #1077583

    alpeshgediya

    A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

    There exists a reason for this ... it protects the Database against unwanted intrusion. Speak to your administrator to change your permissions. It is not wise to try to hack your way around this security provision.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • November 10, 2009 at 4:31 pm

    #1077589

    You could also run the query through SQLCMD and save it to an output text file.

    Joie Andrew
    "Since 1982"

  • November 12, 2009 at 6:51 am

    #1078320

    Yes i am Using SSMS....

    Thanks for you support.

  • November 12, 2009 at 11:05 am

    #1078494

    So were you able to export your result set to a text file?

    Joie Andrew
    "Since 1982"

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply