Generate HTML output for given SQL Query

  • Raja-467879

    Grasshopper

    Points: 22

    Comments posted to this topic are about the item Generate HTML output for given SQL Query

  • Anukul

    SSChasing Mays

    Points: 624

    Hi,

    After creating the SP in server, under a database, when the sp is executed it is giving an error msg:

    Invalid object name 'tempdb.sys.objects'.

    I removed tempdb and made sys.objects as sysobjects, then when executed, it gives error:

    (9 row(s) affected)

    Server: Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near ' '.

    Server: Msg 208, Level 16, State 1, Procedure SP_Get_Query_HTML, Line 37

    Invalid object name '##tempHTML2'.

    I am executing this statement:

    exec SP_GET_QUERY_HTML 'select top 10 * from Sample '

    What is wrong in this? Why is it not working?

    Also, where is the output file generated?

    -Anukul

    [font="Verdana"]
    -------------------------------------
    Anukul
    My Blog
    My Twitter Profile
    My Stumbles[/url]
    [/font]

  • dosth

    SSC Journeyman

    Points: 85

    Hi..,

    Thanks for the code., I got this working. but i have a trouble if the records gets more. bcoz in your code you are using varchar to hold the HTML string , The varchar has max of 8000 char. If the HTML grows more than that how to show the content.

    Pls help me to solve this ?

    Thanks,

    Dosth

  • Anukul

    SSChasing Mays

    Points: 624

    Hi Dosth,

    Can u post the complete code on this thread after you corrected it and made it workable?

    Tks,

    Anukul

    [font="Verdana"]
    -------------------------------------
    Anukul
    My Blog
    My Twitter Profile
    My Stumbles[/url]
    [/font]

  • dosth

    SSC Journeyman

    Points: 85

    Hi Anukul,

    This is the code i got worked.

    /*

    EXECUTE [SPGET_QUERY_HTML] 'select * from yourtablename'

    SELECT * FROM ##TEMPhtml1

    SELECT * FROM ##TEMPhtml2

    */

    Create procedure [dbo].[SPGET_QUERY_HTML]

    ( @p_sqlstmt varchar(8000))

    as

    declare @columns varchar(8000)

    declare @finalhtmlout varchar(8000)

    declare @colHeader varchar(8000)

    declare @Final varchar(8000)

    Declare @sqlstmt varchar(8000)

    -- drop temporary tables used.

    IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = '##TEMPhtml1')

    DROP TABLE ##TEMPhtml1

    IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = '##TEMPhtml2')

    DROP TABLE ##TEMPhtml2

    -- prepare query

    set @sqlstmt = 'select * into ##tempHTML1 from (' + @p_sqlstmt + ') as T1'

    execute (@sqlstmt)

    --Prepare columns details

    SELECT @columns = COALESCE(@columns + ' + '' '' + ', '') + 'convert(varchar(100),isnull(' + column_name +','' ''))'

    FROM tempdb.information_schema.columns where table_name='##tempHTML1'

    --Prepare column Header

    set @colHeader = ' '

    SELECT @colHeader = @colHeader + ' '

    FROM tempdb.information_schema.columns where table_name='##tempHTML1'

    set @colHeader=@colHeader + ' '

    --prepare final output

    set @Final= 'Select '' '' into ##tempHTML2 from ##tempHTML1 '

    execute( @Final)

    set @finalhtmlout= ' ' + @colHeader

    select @finalhtmlout= @finalhtmlout + [ ] from ##tempHTML2

    set @finalhtmlout= @finalhtmlout + ' '

    -- drop temporary tables used.

    IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = '##TEMPhtml1')

    DROP TABLE ##TEMPhtml1

    IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = '##TEMPhtml2')

    DROP TABLE ##TEMPhtml2

    --return final output

    select @finalhtmlout as HTMLoutput

    Thanks,

    Dosth

  • tejaswini.godbole

    SSC Rookie

    Points: 49

    Hello Dosth,

    This Code also doesn't work . What changes ankul has suggested needs to be done to work on SQL 2000 or SQL 2005. Even after modification i get error message as

    Server: Msg 2714, Level 16, State 6, Line 1

    There is already an object named '##tempHTML1' in the database.

    Do we need particular setting to execute/run this code. If yes please let me know.

    The Code i execueted is as below

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    --exec [SPGET_QUERY_HTML] 'select top 11 * from EMP_MASTER '

    CREATE procedure [SPGET_QUERY_HTML]( @p_sqlstmt varchar(8000))

    as

    declare @columns varchar(8000)

    declare @finalhtmlout varchar(8000)

    declare @colHeader varchar(8000)

    declare @Final varchar(8000)

    Declare @sqlstmt varchar(8000)

    begin

    -- drop temporary tables used.

    IF EXISTS (SELECT * FROM TEST.dbo.sysobjects WHERE name = '##TEMPhtml1')

    DROP TABLE ##TEMPhtml1

    IF EXISTS (SELECT * FROM TEST.dbo.sysobjects WHERE name = '##TEMPhtml2')

    DROP TABLE ##TEMPhtml2

    -- prepare query

    set @sqlstmt = 'select * into ##tempHTML1 from (' + @p_sqlstmt + ') as T1'

    execute (@sqlstmt)

    --Prepare columns details

    SELECT @columns = COALESCE(@columns + ' + '' '' + ', '') + 'convert(varchar(100),isnull(' + column_name +','' ''))'

    FROM TEST.information_schema.columns where table_name='##tempHTML1'

    --Prepare column Header

    set @colHeader = ' '

    SELECT @colHeader = @colHeader + ' '

    FROM TEST.information_schema.columns where table_name='##tempHTML1'

    set @colHeader=@colHeader + ' '

    --prepare final output

    set @Final= 'Select '' '' into ##tempHTML2 from ##tempHTML1 '

    execute( @Final)

    set @finalhtmlout= ' ' + @colHeader

    select @finalhtmlout= @finalhtmlout + [ ] from ##tempHTML2

    set @finalhtmlout= @finalhtmlout + ' '

    -- drop temporary tables used.

    IF EXISTS (SELECT * FROM TEST.dbo.sysobjects WHERE name = '##TEMPhtml1')

    DROP TABLE ##TEMPhtml1

    IF EXISTS (SELECT * FROM TEST.dbo.sysobjects WHERE name = '##TEMPhtml2')

    DROP TABLE ##TEMPhtml2

    --return final output

    select @finalhtmlout as HTMLoutput

    END

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    If I could run this code then this will help us on our project a lot.

    Regards,

    Tej

  • carllgrant

    Old Hand

    Points: 320

    Thanks, Raja - this works great!

    A few mods:

    1. Changed the VARCHAR(8000) variables to VARCHAR(MAX) to avoid problem noted by another respondent.

    2. To handle ORDER BY clauses, I added code to split out that clause,

    then appended the variable containing it to the following statment:

    set @Final= 'Select '' '' into ##tempHTML2 from ##tempHTML1 '

  • Sailor

    SSCertifiable

    Points: 5221

    I am getting this error:

    (3 row(s) affected)

    Msg 1038, Level 15, State 5, Line 1

    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

    Msg 208, Level 16, State 1, Procedure SPGET_QUERY_HTML, Line 43

    Invalid object name '##tempHTML2'.

    Line 43 is from part on this select:

    --Prepare column Header

    set @colHeader = ' '

    SELECT @colHeader = @colHeader + ' '

    FROM tempdb.information_schema.columns where table_name='##tempHTML1'

    set @colHeader=@colHeader + ' '

    Table I am selecting from:

    CREATE TABLE [dbo].[Jobs](

    [ServerName] [varchar](50) NULL,

    [JobName] [varchar](50) NULL,

    [StartTime] [datetime] NULL,

    [EndTime] [datetime] NULL,

    [Status] [varchar](50) NULL,

    [Log] [nvarchar](max) NULL,

    [PreviousStart] [datetime] NULL,

    [PreviousEnd] [datetime] NULL

    Lastly, when I just do a simple select * from jobs as HTMLOutput, I don't get the html, just the raw text. I am SQL 2005.

  • sharad-1144828

    SSC Enthusiast

    Points: 175

    Hi Can someone please helpout me for the following error:

    🙂

    Msg 1038, Level 15, State 5, Line 1

    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

    Msg 208, Level 16, State 1, Procedure SPGET_QUERY_HTML1, Line 38

    Invalid object name '##tempHTML2'.

    Please let me know where exactely it is going wrong & what action shall i take on this.

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

    I'd like to suggest that this solution strikes me as a bad idea. Why are you asking your database to do so much string manipulation? If you're using ADODB, recordset.getString() is a much better way to transform data into the basic table this example produces. If you're using PHP, the data connector is probably returning your results as an array. With implode() you could get a similarly basic markup.

    If you have more complex html to generate you'll be spending even more time with procedural code (probably cursors) and doing odd things to work around varchar limitations, etc. You might also be tempted to use SQL Server's for XML...

    I would still suggest that the webserver should be be requesting data, transforming it, and (probably) caching it. Also a webservice could be acting as an additional tier between client-side scripts and the database. The programming environment is a better place to transform data for presentation.

    The .GetString() method has been around since 1998!

    http://www.4guysfromrolla.com/webtech/121598-1.shtml

  • dav0id

    SSC Enthusiast

    Points: 136

    I would tend to agree with Mike Dougherty-384281. This approach seems like a bad idea for a number of reasons.

    Why not use a "FOR XML" query and transform the XML on a web page?

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

    dav0id (10/25/2011)


    I would tend to agree with Mike Dougherty-384281. This approach seems like a bad idea for a number of reasons.

    Why not use a "FOR XML" query and transform the XML on a web page?

    I couldn't tell if your question is rhetorical, a suggestion, or an inquiry. 🙂

    I would avoid using FOR XML on the general principle that it's still a large volume of string manipulation even if in the guise of a native feature. Mostly we're still using SQL Server 2000 instances and what I read of its newfangled XML capability made me think it should be avoided. Perhaps '2008 has evolved sufficiently that it's a viable solution.

    If your proposed XML transform on the web page is using DOM methods on the XML document, the performance win you might be getting from requesting XML directly from the database (at the cost of exposing the server directly to clients?) you then pay in DOM method performance. A web service in front of the SQL Server minimizes server exposure, gives a web-side cache opportunity, and can probably respond to client-side/browser xmlhttp requests with JSON.

    The author's original post is an example of a possible solution. As the opening move in the discussion that follows, I want to thank Raja Mohamed for taking the time to write it up. I felt it was a worthwhile contribution to this community to point out the potential scalability problem. Thanks dav0id for agreeing my concern is valid.

  • Paul Paiva

    SSCrazy

    Points: 2237

    Raja,

    Excellent job, thanks for sharing the code! Worked perfectly for me.

    Regarding the last two persons to post comments - you make good points that there are better and more efficient ways, but you completely missed the spirit of the article. That is, there may be times when you don't have that perfect situation. For example, I needed a way to properly format my query for sending in the body of an email. This did the trick.

    Paul

    - Paul

    http://paulpaivasql.blogspot.com/

  • rjoseph

    Ten Centuries

    Points: 1087

    So what is the final code for this tool. I would love to use this but I cannot see how. Where do you obtain the output after running this script via SSMS or do you obtain it from a job. Please help, I trying to get similar outputs for hard disk, logs, space reporting.

    Thanks and regards,

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 15 posts - 1 through 15 (of 15 total)

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