none
Export to a Text file in SQL Server Express

    Question

  • I have a table in a database has the same filed with a text file:
    RBW_AR_BOT_REC Size Comment Justification Padding
    record_id 1 'R' None None
    date 6 MMDDYY None None
    time 4 HHMM (24 hour time) None None
    cust_num 8 1 - 9999999 Right Zero
    odometer 6 0 - 999999 Right Zero
    receipt 8 1 - 99999999 Right Zero
    exception_customer 1 '0' = No
    '1' = Yes
    None None
    valid_void 1 '0' = valid
    'v' = void
    None None




    Justification Padding





    None None
    So that, after export data from the table, the file file must has that struct
    Tell me, how can i do???




    None None





    None None





    Right Zero





    Right Zero





    Right Zero





    None None





    None None




    Justification Padding





    None None





    None None





    None None





    Right Zero





    Right Zero





    Right Zero





    None None





    None None


    Monday, October 02, 2006 10:46 AM

Answers

  • hi,

    there's no direct way to "export" to file in a defined format like old style random access files...

    you can query a table object to return what you are looking for, but the export features are usually performed a client level (thus via an external application) and not at the server level..

    you can perhaps use SqlCMD.exe (or oSql.exe), the command line (not graphics tools) provided with SQLExpress, providing the "-oc:\myfile.txt" parameter (-o indicates to output the result to the specified file) (more info and synopsis about SQLCmd at http://msdn2.microsoft.com/en-us/library/ms162773.aspx)... and play with some Transact-SQL function to obtain something similar to the required result..

    say you are querying the AdventureWorks database for Person.Address table and you want to return the

    [AddressID] (int) formatted as right padded 6 chars and the [ModifiedDate] (datetime) formatted with the yyyy-mm-dd (without time dimension) 8 chars values...

    the typical query could be

    SET NOCOUNT ON;
    USE AdventureWorks;
    GO
    SELECT RIGHT(REPLICATE(' ' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID,
     CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate
     FROM Person.Address
     ORDER BY ModifiedDate;
    --<--------
    AddressID ModifiedDate
    --------- ------------
        61    1996-07-24
       234    1997-02-19
       224    1997-12-05
     11387    1997-12-29
    1997-12-29
    ( result abridged ) 

    and want the results to be output to c:\myRes.txt..

    create a file to store your query like

    <c:\myQuery>
    SELECT RIGHT(REPLICATE(' ' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID,
    CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate
    FROM Person.Address
    ORDER BY ModifiedDate;
    </c:\myQuery>

    and execute, from the command line, the SQLCmd utility like

    c:\..\>sqlcmd -E -S(Local)\SQLExpress -dAdventureWorks -iC:\myQuery -oC:\myRes.txt

    this will output the result as

    AddressID ModifiedDate
    --------- ------------
        61    1996-07-24  
       234    1997-02-19  
       224    1997-12-05  
     11387    1997-12-29  
         1    1998-01-04  
    ( result abridged ) 
    

    you can play with the other SQLCmd parameters as wel, like -h for repeted headers, -s for col separator and the like...

    regards

    Monday, October 02, 2006 4:56 PM
    Moderator

All replies

  •  

    I am going to move this to the sql express group at it is more a problem with using the software.

    On another note, you can use the management studio for the express systems (Free Download) and have the query saved or displayed as text, or you could format up the sql script and then use the sqlcmd command line tool to execute that command and save the output as text. But with the information you supplied we are going to need more information, for exable an definition of the table and if you could format the output sample better in the forums message so it is easily read.

    Monday, October 02, 2006 11:24 AM
  • hi,

    there's no direct way to "export" to file in a defined format like old style random access files...

    you can query a table object to return what you are looking for, but the export features are usually performed a client level (thus via an external application) and not at the server level..

    you can perhaps use SqlCMD.exe (or oSql.exe), the command line (not graphics tools) provided with SQLExpress, providing the "-oc:\myfile.txt" parameter (-o indicates to output the result to the specified file) (more info and synopsis about SQLCmd at http://msdn2.microsoft.com/en-us/library/ms162773.aspx)... and play with some Transact-SQL function to obtain something similar to the required result..

    say you are querying the AdventureWorks database for Person.Address table and you want to return the

    [AddressID] (int) formatted as right padded 6 chars and the [ModifiedDate] (datetime) formatted with the yyyy-mm-dd (without time dimension) 8 chars values...

    the typical query could be

    SET NOCOUNT ON;
    USE AdventureWorks;
    GO
    SELECT RIGHT(REPLICATE(' ' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID,
     CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate
     FROM Person.Address
     ORDER BY ModifiedDate;
    --<--------
    AddressID ModifiedDate
    --------- ------------
        61    1996-07-24
       234    1997-02-19
       224    1997-12-05
     11387    1997-12-29
    1997-12-29
    ( result abridged ) 

    and want the results to be output to c:\myRes.txt..

    create a file to store your query like

    <c:\myQuery>
    SELECT RIGHT(REPLICATE(' ' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID,
    CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate
    FROM Person.Address
    ORDER BY ModifiedDate;
    </c:\myQuery>

    and execute, from the command line, the SQLCmd utility like

    c:\..\>sqlcmd -E -S(Local)\SQLExpress -dAdventureWorks -iC:\myQuery -oC:\myRes.txt

    this will output the result as

    AddressID ModifiedDate
    --------- ------------
        61    1996-07-24  
       234    1997-02-19  
       224    1997-12-05  
     11387    1997-12-29  
         1    1998-01-04  
    ( result abridged ) 
    

    you can play with the other SQLCmd parameters as wel, like -h for repeted headers, -s for col separator and the like...

    regards

    Monday, October 02, 2006 4:56 PM
    Moderator
  • Hi Andrea Montanaru: 

    I am reading your post that you replied to the "Export to a text file in SQL Server Express". I need to do the same thing to export a result from a sql query to a text file. I need to execute this store procedure from within a vb.net Windows application ie.e call to a sql store procedure. I wonder if you have the complete sql script to include all the steps as mentioned in your original post like "create a file to store the query" and "write to the text file from the sql query result"

    Many thanks.

     

     

    Sunday, June 15, 2008 7:31 PM
  • hi,

     pmak wrote:

    Hi Andrea Montanaru: 

     

    Montanari

     

    I am reading your post that you replied to the "Export to a text file in SQL Server Express". I need to do the same thing to export a result from a sql query to a text file. I need to execute this store procedure from within a vb.net Windows application ie.e call to a sql store procedure. I wonder if you have the complete sql script to include all the steps as mentioned in your original post like "create a file to store the query" and "write to the text file from the sql query result"

    Many thanks.

     

    this is very "unfortunate".. you usually should perform this stuff on the client side of the app and not serverside as, again, SQL Server is data related and not file related..

    you "can" perform the required task using the xp_cmdshell system stored procedure.. please be warned that this feature is disabled by default and you have to manually enable it via the SAC or via sp_configure options... this to remember you this is not a recommended solution

    having said that, you can use both BCP.exe to bulk copy out to file or, again SQLCmd.exe as indicated, similar to

     

    SET NOCOUNT ON;
    GO
    DECLARE @sqlcmd varchar(1000);
    
    PRINT 'using SqlCMD.exe';
    SET @sqlcmd = 'sqlcmd -S' + @@SERVERNAME + ' -E -oc:\outfile.txt '
    DECLARE @cmd varchar(1000);
    SET @cmd = '-Q"SELECT RIGHT(REPLICATE('' '' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID, CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate FROM AdventureWorks.Person.Address ORDER BY ModifiedDate;"';
    SET @sqlcmd = @sqlcmd + @cmd;
    --SELECT @sqlcmd;
    EXEC xp_cmdshell @sqlcmd, no_output;
    
    PRINT 'using BCP.exe';
    SET @cmd = '"SELECT RIGHT(REPLICATE('' '' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID, CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate FROM AdventureWorks.Person.Address ORDER BY ModifiedDate;"';
    SET @sqlcmd = 'bcp ' + @cmd + ' queryout "c:\outfile2.txt" -c -T -S' + @@SERVERNAME 
    --SELECT @sqlcmd;
    EXEC xp_cmdshell @sqlcmd, no_output;
    

    regards

    Sunday, June 15, 2008 9:54 PM
    Moderator
  • Beating the horse dead.

    I'm in a situation where I need to export a handful of records so that the information in them is always available in the event that the database isn't available. I had hoped to come up with a solution that ensuring that anytime one of them is updated that their exported then and there to always have an up to date backup.


    David H
    Friday, July 16, 2010 12:17 PM
  • hi David,

    this is very unfortunate as well.. again, SQL Server is database centric and not ntfs file centric.. using the xp_cmdshell system sored procedure can be  a solution, but it's very uncommon to do that "syncronusly" at very each insert time.. that can "damage" performances as additional resources and cpu time is required to access the external file system.. and it's deprecated as it requires you to "open security holes" to allow SQL Server to access resources external to it's concern, so that potential external malicious attackers have a way out to your file system...

    usually, these kind of requirements are handled asyncronously, with a service broker scheduled task, or, even easier, via operating system SCHTASKS/AT tasks so that their related activiies do not directly penalize OLTP operation.. for my own similar requirement I wrote a .Net app, amInsert (you can find at the link following my sign.), that can be and in this case is scheduled every half an hour, to produce INSERT INTO statements files of a set of tables.. but again, that is asyncronous..

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de
    Saturday, July 17, 2010 12:24 PM
    Moderator
  • In our situation, there's going to be a PC driving a monitor that will display the results of an ASP.NET page. I've decided that when the page automatically reloads (via JavaScript) that the page will check to see if any of the records have changed. If they have, the page will generate an email and send it to the key players. To avoid cluttering their Inboxes, we're going to setup rules on their machines to move the alerts to a specific folder. Given that a message is downloaded locally, the information will be available even if the connection to Exchange is down - either due to network issues or the Exchange server going down. It  pretty much covers anything that might happen. If there's a power outage at the facility, the fact that the users don't have access to the information will be a moot point.
    David H
    Sunday, July 18, 2010 2:02 PM