Post reply

Create a text file from SQL data

  • July 6, 2006 at 8:42 am

    #173306

    I need to create a text file from the data I have extracted with a stored procedure.  I have tried thru Reporting Services and creating a file out of Query Analyzer.  My problem is the data is 600 characters long and Query Analyzer only outputs 256 characters.  Reporting services does not create a text file.  Can anyone let me know the easiest way to do this?

  • July 6, 2006 at 9:19 am

    #647777

    > Query Analyzer only outputs 256 characters

    Not necessarily. Try changing the setting in 'Tools -> Options -> Results -> Maximum characters per column'. Maximum setting is 8192.

    Maybe that will give you what you need?

     

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • July 6, 2006 at 12:56 pm

    #647815

    you could also try using bcp utility as well.

  • July 7, 2006 at 1:33 am

    #647889

    From command line use BCP.  For help, just type bcp /?

    It is by far the fastest and easiest way to get data in and out of SQL.

  • July 7, 2006 at 7:43 am

    #647974

    There's also DTS to export to a text file.

  • July 7, 2006 at 7:57 am

    #647986

    And OpenRowSet

    N 56°04'39.16"
    E 12°55'05.25"

  • July 7, 2006 at 8:05 am

    #647994

    Thanks all.  I am going to use the bcp utility with the xp_cmdshell procedure to create the text file.  I found a good article on SQLTeam.com that explains everything I wanted to do.  That article can be found at http://www.sqlteam.com/item.asp?ItemID=4722.

     

    Again thanks for your help.

  • July 7, 2006 at 8:15 am

    #647996

    "Drop Database" is the fastest way to get data out of SQL server.

  • March 10, 2008 at 8:52 pm

    #787731

    Hi, My name is Juan F. and I am new with this tool but in reference to bcp utility, I have a problem. I am able to execute the bcp utility to export data to an output text file. The problem I am having is that the output file is replaced with a new one everytime I execute the bcp. What I want to do is to append the data to the existing output file. Here is my format:

    --If error found then extract process data to an output file using bcp.

    IF @n_NumErrors = 1

    BEGIN

    DECLARE @FileName varchar(50),

    @bcpCommand varchar(2000)

    SET @FileName = 'c:\itops.txt'

    SET @bcpCommand = 'bcp "SELECT ML.ErrorLogDesc FROM SWAProdSupport..V_SWA_MONITOR_LOG ML (Nolock),'

    SET @bcpCommand = @bcpCommand + '(SELECT MAX(UpdatedDate) as "MaxDate", LogMntrID FROM SWAProdSupport..V_SWA_MONITOR_LOG ML (Nolock) '

    SET @bcpCommand = @bcpCommand + 'WHERE LogMntrID = ' + CONVERT(VARCHAR(6),@i_MntrID) + ' AND UpdatedDate >= DATEADD(day,-25,getdate()) GROUP BY LogMntrID) MaxResults '

    SET @bcpCommand = @bcpCommand + 'WHERE ML.LogMntrID = MaxResults.LogMntrID AND ML.UpdatedDate = MaxResults.MaxDate" queryout "'

    SET @bcpCommand = @bcpCommand + @FileName + '" -U sysuser -P frogbull -c'

    --Export data out to a text file executing the call with xp_cmdshell

    EXEC Master..xp_Cmdshell @bcpCommand

    END

    Can you please help?

  • March 10, 2008 at 9:08 pm

    #787739

    Hello Juan.

    Once the file is generated(bcp step is complete), you can run this dos command as next step in the process. This will rename the file, append date to the filename. Hope this helps.

    rename c:\itops.txt itops-%date:~10,4%%date:~4,2%%date:~7,2%

    --------------------------------------------------------------------------------------
    Save our world, its all we have! A must watch video Pale Blue Dot[/url]

  • March 11, 2008 at 10:12 am

    #788104

    Be careful about using DOS commands.

    This defeats the concept of access security.

Viewing 11 posts - 1 through 10 (of 10 total)

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