Post reply

read text file contents in a stored procedure

  • September 21, 2004 at 5:14 pm

    #361172

    Hi all,

    I am trying to integrate our courier label printing system with the order database.

    I execute a batch file which does all sorts of crazy dos things to get the output from the sql server to the remote port of the lable printer. The consignment number is then written back to a text file by the label printing software.

    What is the easiest and quickest way to open/read this text file so I can insert the consignment number into the order.

    The text file has only one short string in it consisting of three characters and 5 numerics as;

    AAA00076

    Any help greatly appreciated.

  • September 21, 2004 at 11:28 pm

    #523904

    I believe the quickest / easiest way would be to load the file into a table, then read the number from there.

    You can use isql/osql/bcp/dts etc in order to get the file into a table.

    Naturally, you also need to design some workflow rules around this whole thing - output -> printing -> number -> read and process the number from file...

    When something breaks, you need some way to pick up where you left, or the "transaction" may not be consistent, if you know what I mean..

    /Kenneth

  • September 22, 2004 at 2:06 am

    #523922

    you may want to take a look at "Read a text file from SQL Server query analyzer" http://qa.sqlservercentral.com/scripts/contributions/417.asp

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • September 22, 2004 at 2:36 am

    #523924

    Thanks for the replies. I had looked at bulk insert and also at using openrowset. Didnt know if there were any consequenses of using a bulk insert command to only grab one value.

    Cheers.

    Rolf

  • September 22, 2004 at 6:48 am

    #523964

    I can't really imagine a downside to bulk insert for this situation. I would suggest you use it in this case. However, in case someone else is reviewing this for a solution to a little different problem, you can make this as complex as you want on the DOS side to get it done using custom SQL or a DOS environment variable. Here is basically how.

    PreTemplate.txt (do not include a carriage return at the end of this file)

    @ECHO OFF

    SET MYVALUE=

    PostTemplate.txt

    ECHO %MYVALUE%

    Driver.cmd

    @ECHO OFF

    REM Do something here to generate data.txt

    COPY PreTemplate.txt NewCmd.cmd

    TYPE data.txt >> NewCmd.cmd

    TYPE PostTemplate.txt >> NewCmd.cmd

    call=NewCmd.cmd

    The above pulls the value (such as your AAA00076 value) into a DOS environment variable and then ECHOs it. The templates could easily be converted to generate SQL statements that could then be run via a call to osql. Although highly flexible it is non-intuitive and not the best option in your case.

     

  • September 22, 2004 at 8:48 am

    #523998

    Here's a procedure that may solve the problem.  I suppose it all depends on where you need to actually execute the "Read File" from.  This proc takes in the path and returns the file contents.  It does a "ReadAll", so you may need to adjust the sizes of the *_contents varChars.

    CREATE PROCEDURE dbo.p_fsoReadAll (
    @in_filePath VarChar(260),   -- UNC paths seem to work fine here.
    @out_contents VarChar(1000)
)
AS
BEGIN
    DECLARE @hr Int
    DECLARE @o_fso Int, @o_file Int, @sz_contents VarChar(1000)
    DECLARE @noErr Bit, @errMethod VarChar(255)
    DECLARE @src VarChar(255), @desc VarChar(255)
    
    SET @noErr = 1
    
    EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @o_fso OUT
    IF @hr = 0
    BEGIN
        EXEC @hr = sp_OAMethod @o_fso, 'OpenTextFile', @o_file OUT, @in_filePath, 1
        IF @hr <> 0
        BEGIN
            EXEC sp_OAGetErrorInfo @o_fso, @src OUT, @desc OUT
            SET @errMethod = 'sp_OAMethod: OpenTextFile'
            SET @noErr = 0
        END
    END
    
    IF @hr = 0
    BEGIN
        EXEC @hr = sp_OAMethod @o_file, 'ReadAll', @sz_contents OUT
        IF @hr <> 0
        BEGIN
            EXEC sp_OAGetErrorInfo @o_fso, @src OUT, @desc OUT
            SET @errMethod = 'sp_OAMethod: ReadAll'
            SET @noErr = 0
        END
    END
    
    IF @hr = 0
    BEGIN
        EXEC @hr = sp_OADestroy @o_file
        IF @hr <> 0
        BEGIN
            EXEC sp_OAGetErrorInfo @o_fso, @src OUT, @desc OUT
            SET @errMethod = 'sp_OADestroy: File Object'
            SET @noErr = 0
        END
    END
    
    IF @hr = 0
    BEGIN
        EXEC @hr = sp_OADestroy @o_fso
        IF @hr <> 0
        BEGIN
            EXEC sp_OAGetErrorInfo @o_fso, @src OUT, @desc OUT
            SET @errMethod = 'sp_OADestroy: FSO Object'
            SET @noErr = 0
        END
    END
    
    SET @out_contents = @sz_contents
END
GO

  • September 22, 2004 at 8:53 am

    #523999

    Forgot one thing...

    If the @noErr variable is 0, you may want to set @out_contents as

    'ERROR - Handle: ' + @hr + '; Method: ' + @errMethod + '; Source: ' + @src + '; Desc: ' + @desc

     

    Hope this helps...

     

  • September 22, 2004 at 9:06 am

    #524004

    Thanks for all the responses. Its a fairly simple procedure..most of the details being taken care of by the dos batch file.

    I am assumming that the stored procedure will not continue to run till the batch file which is launched with xp_cmd has finished and hands control back.

    The bcp method will be fine as I can read either the consignment number of the error code from the imported text file then I can do error checking and processing within the stored procedure.

    Thanks again

    Rolf

  • September 22, 2004 at 9:15 pm

    #524099

    Just a thought...

    You could output the text file in xml format and use sp_xml_preparedocument to get the file and and SELECT....OPENXML to retrieve the value. It might be simpler than some other options.....

     

    Allan

  • September 23, 2004 at 3:44 am

    #524123

    that is simpler but unfortunately I have no control over the lable printing app. It decides how to export the data etc.

    Rolf

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

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