Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generate HTML output for given SQL Query


Generate HTML output for given SQL Query

Author
Message
Raja-467879
Raja-467879
Forum Newbie
Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)

Group: General Forum Members
Points: 0 Visits: 6
Comments posted to this topic are about the item Generate HTML output for given SQL Query
Anukul
Anukul
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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

dosth
dosth
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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
Anukul
Anukul
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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

dosth
dosth
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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
tejaswini.godbole
tejaswini.godbole
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
carllgrant
carllgrant
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
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 '



Sailor
Sailor
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 848
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
sharad-1144828
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 180
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
Mike Dougherty-384281
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 944
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search