Display all instruction HTML data from an SQL database

Use the READTEXT function to retrieve all data in the Instruction HTML column for a specified record number. These steps represent a sample query and explain how to modify the query so that it will produce an HTML file.
:
  1. In Query Analyzer go to the
    Tools > Options > Results
     tab and clear the
    Print column headers (*)
     option. Select
    Apply
     and then
    OK
     to close the dialog box.
  2. From the
    Query
     menu, select
    Results to File.
  3. Enter this BHBATCHHIS query into a blank query window.
    /*
    -- Displays the contents of a text column in a 255-character wide query window
    -- @txtptrval is the text pointer value for the specified text column
    -- @offsetval is the offset value and represents the starting
    -- position within a text column
    -- @bufferval represents the amount of text to put in the row
    -- in this case, it is set to 255 because isql/w only displays
    -- 255 characters in a row
    -- @maxval is the full length of the entire text column
    */
    BEGIN
    SET NOCOUNT ON
    DECLARE
    @txtptrval VARBINARY(16),
    @offsetval INT,
    @bufferval INT,
    @maxval INT
    SELECT @txtptrval = TEXTPTR(
    bhbatchhis
    .InstructionHTML)
    FROM
    bhbatchhis
    WHERE recordno = '5817'
    SELECT @offsetval = 0
    SELECT @bufferval = 255
    SELECT @maxval = DATALENGTH(
    bhbatchhis
    .InstructionHTML) / 2-1
    FROM
    bhbatchhis
    WHERE recordno = '5817'
    --PRINT 'Total length of column: '
    --PRINT '------'
    --PRINT @maxval
    --PRINT ''
    -- Last chunk, reduce buffer size to the nChars remaining
    IF (@offsetval + @bufferval) > @maxval
    BEGIN
    SELECT @bufferval = @maxval - @offsetval
    --PRINT 'Last chuck... buffer size remaining is:'
    --PRINT '------'
    --PRINT @bufferval
    END
    WHILE @offsetval < @maxval
    BEGIN
    READTEXT bhbatchhis.InstructionHTML @txtptrval @offsetval
    @bufferval
    --PRINT 'Data started at character position'
    --PRINT @offsetval
    SELECT @offsetval = @offsetval + @bufferval
    --PRINT 'Data ended at character position'
    --PRINT @offsetval
    --PRINT ' '
    -- Last chunk, reduce buffer size to the get the last nChars
    remaining
    IF (@offsetval + @bufferval) > @maxval
    SELECT @bufferval = @maxval - @offsetval + 1
    END
    SET NOCOUNT OFF
    END
  4. In the query, edit the WHERE clause to specify the
    recordno
     to view the InstructionHTML.
  5. Run the query and save the results to a file name, such as
    Output.html
    .
    TIP:
    Be sure to specify the
    .html
     extension, change the
    Save as type
     to
    All Files (*.*)
    , and change the
    File Format
     to
    ANSI
    .
  6. In Windows Explorer, double-click the .html file to view the page in the browser.
Provide Feedback
Have questions or feedback about this documentation? Please submit your feedback here.
Normal