Generate HTML output for given SQL Query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Raja Mohamed » Generate HTML output for given SQL Query

13 posts, Page 1 of 2

1 2 »»

Generate HTML output for given SQL Query

Rate Topic

Display Mode

Topic Options

Author

Message

Raja-467879

Posted Tuesday, November 20, 2007 9:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 09, 2007 12:31 AM
Points: 0, Visits: 6

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

Post #424417

Anukul

Posted Wednesday, November 21, 2007 4:20 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 6:46 AM
Points: 12, Visits: 35

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

-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles

Post #424517

dosth

Posted Wednesday, November 21, 2007 9:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 13, 2009 3:28 AM
Points: 3, Visits: 10

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

Post #424662

Anukul

Posted Wednesday, November 21, 2007 9:35 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 6:46 AM
Points: 12, Visits: 35

Hi Dosth,

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

Tks,
Anukul

-------------------------------------
Anukul
My Blog
My Twitter Profile
My Stumbles

Post #424665

dosth

Posted Thursday, November 22, 2007 3:22 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 13, 2009 3:28 AM
Points: 3, Visits: 10

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

Post #424906

tejaswini.godbole

Posted Monday, November 26, 2007 4:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, May 27, 2012 10:47 PM
Points: 1, Visits: 17

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

Post #425633

carllgrant

Posted Thursday, March 27, 2008 5:19 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 15, 2008 10:37 AM
Points: 30, Visits: 19

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 '

Post #475829

Sailor

Posted Wednesday, November 11, 2009 8:59 AM

SSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 7:08 AM
Points: 236, Visits: 752

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.

Post #817243

sharad-1144828

Posted Wednesday, November 18, 2009 12:05 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 17, 2013 12:29 AM
Points: 9, Visits: 163

Hi Can someone please helpout me for the following error:
Smile

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.

Post #820595

Mike Dougherty-384281

Posted Tuesday, October 25, 2011 8:31 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:11 PM
Points: 205, Visits: 709

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

Post #1195763

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions