none
SQL query output to a text file

    Question

  • Hi,

    I just installed SQL Express 2008.

    How can I divert the output of a SQL query to a text file?

    If I run a query SELECT * FROM SYS.DATABASES from any application, I want to store the output to a text file on the local disk.

    *** Without using any exe / sql tool. ***

    Wednesday, July 21, 2010 10:45 AM

Answers

  • Hi marked9,

     

    Based on my understanding, you want to export data from SQL Server to a flat file and then transmit to client based on ASP.NET, right?

    There is no direct way to achieve this work. We have to write some code to transmit to the client.

    Meanwhile this issue is more related to ASP.NET, I recommend that you ask it in ASP.NET forum.

     

    In order to export data to a file via ASP.NET, we have to retrieve data from SQL Server primarily. Once we retrieve data from SQL Server to memory, we then could create a stream which is from server to client and write the data to this stream with predefined format. For more details, please ask it in ASP.NET forum.

     

    If anything is unclear, please let me know.

    Monday, July 26, 2010 6:56 AM
    Moderator

All replies

  • See following http://www.mssqltips.com/tip.asp?tip=1633

    Enable XP command Shell

    EXEC master.dbo.sp_configure 'show advanced options', 1
    RECONFIGURE
    EXEC master.dbo.sp_configure 'xp_cmdshell', 1
    RECONFIGURE 
    
    

    Then execute below will export to text file

    EXEC xp_cmdshell 'bcp "SELECT * FROM SYS.DATABASES" queryout "C:\bcptest.txt" -T -c -t,' 
    
    Wednesday, July 21, 2010 10:58 AM
  • Thanks a LOT..

    Query ran successfully, but no bcptest.txt on c drive..!?!

    Remote connections are allowed.

    SQLState = 08001, NativeError = -1
    Error = [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
    SQLState = 08001, NativeError = -1
    Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
    SQLState = S1T00, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
    NULL

    Wednesday, July 21, 2010 11:06 AM
  • Are you runnning this query remotely? Check to see that you have remote connections enabled by following below steps.

    To configure the Database Engine to accept remote connections
    1.In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click Protocols for <instancename>.
    
    2.In the details pane, right-click one of the available protocols, and then click Properties.
    
    Note 
    The shared memory protocol cannot be enabled for remote connections.
     
    
    3.To enable a protocol for remote connections, set the Enabled box to Yes.
    

    SQL Service account will need access to drive or folder in order to create file on C Drive. If SQL service account is not local admin then you have to pass path where account have permission to create file.

    Instead of creating file on C Drive can you run query to export file to "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Export.ext" - hoping that you have installed sql server on C drive with default settings.

    Wednesday, July 21, 2010 11:27 AM
  • Hi,

    Try following command, as enabling xp_cmdshell might not be good security practice devaiation for exporting data to text file..

    sqlcmd -S<<ServerName>> -dMaster -E -Q "select * from sys.databases" -o "c:\serverList.txt"

    You can make .BAT File and this will create file in client's location. <<ServerName>> is a placeholder, you need to replace this with your server.

    Hope this helps.

    -Chintak.

     

    Wednesday, July 21, 2010 11:53 AM
  • Didn't work, I checked all the connection permissions.
    Wednesday, July 21, 2010 1:50 PM
  • Hi,

    What error we are getting back, when we run follwoing query on cmmand prompt on server?

    sqlcmd -S<<ServerName>> -dMaster -E -Q "select * from sys.databases" -o "c:\serverList.txt"

    You need to change <<SereverName>>.

    If this run on server what error message you get on remote server.

    To work this command you need to have your windows login as login in sql. If you are using sql authentication the you need to remove -E and use -U<<userName>> and -P<<passowrd>> in sqlcmd command

    -Chintak.

    Wednesday, July 21, 2010 5:13 PM
  • Hi,

    What error we are getting back, when we run follwoing query on cmmand prompt on server?

    sqlcmd -S<<ServerName>> -dMaster -E -Q "select * from sys.databases" -o "c:\serverList.txt"

    You need to change <<SereverName>>.

    If this run on server what error message you get on remote server.

    To work this command you need to have your windows login as login in sql. If you are using sql authentication the you need to remove -E and use -U<<userName>> and -P<<passowrd>> in sqlcmd command

    -Chintak.

     

    Hello Chintak,

    I am looking for a actual query that will export output to a text file, I dont want to use any external tool or exe to run a sql query remotely or locally.

    My purpose is to prepare a sql query to be used in ASP.NET code to remotely/locally run and export output to a local file on the sql server.

    I checked all the connection parameters, not sure what is blocking it.  

     

     

    H:\>sqlcmd
    HResult 0x7E, Level 16, State 1
    VIA Provider: The specified module could not be found.

    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible.
     Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

    Thursday, July 22, 2010 7:45 AM
  • Hi,

    You can put this as a .BAT file on your web server. Then you can execute this. You can also utilise BAT file agrguments if you want to pass different query.

    As per below post you are getting this error because you have not specified the server name in SQLCMD.

    http://geekswithblogs.net/TimH/archive/2008/03/12/sql-server-2005-error-via-provider-the-specified-module-could.aspx

    But, can you just run folloing cmmands by replcing SERVERNAME to your server, to check what is problem.

    If you are using windows authentication:  sqlcmd -S SERVERNAME -E -d master

    If you are using SQL authentication:  sqlcmd -S SERVERNAME -Uusername -Ppassword -d master (here need to replace username and password with your credentials)

    -Chintak.

     

    Thursday, July 22, 2010 12:44 PM
  • Remeber that any .sql command fired to the database doing an export of daa (like bcp does) wil always refer to the local context of the database server unless you force the client tool to output the data to a file like in

    sqlcmd -S<<ServerName>> -dMaster -E -Q "select * from sys.databases" -o "c:\serverList.txt"

    from above.

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Saturday, July 24, 2010 10:25 PM
    Moderator
  • Hi marked9,

     

    Based on my understanding, you want to export data from SQL Server to a flat file and then transmit to client based on ASP.NET, right?

    There is no direct way to achieve this work. We have to write some code to transmit to the client.

    Meanwhile this issue is more related to ASP.NET, I recommend that you ask it in ASP.NET forum.

     

    In order to export data to a file via ASP.NET, we have to retrieve data from SQL Server primarily. Once we retrieve data from SQL Server to memory, we then could create a stream which is from server to client and write the data to this stream with predefined format. For more details, please ask it in ASP.NET forum.

     

    If anything is unclear, please let me know.

    Monday, July 26, 2010 6:56 AM
    Moderator