Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Generate HTML output for given SQL Query Expand / Collapse
Author
Message
Posted Tuesday, November 20, 2007 9:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, November 21, 2007 4:20 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, November 21, 2007 9:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, November 21, 2007 9:35 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, November 22, 2007 3:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, November 26, 2007 4:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, March 27, 2008 5:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, November 11, 2009 8:59 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 13, 2014 11:23 AM
Points: 245, Visits: 781
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
Posted Wednesday, November 18, 2009 12:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 03, 2014 4:05 AM
Points: 9, Visits: 172
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.
Post #820595
Posted Tuesday, October 25, 2011 8:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:13 AM
Points: 257, Visits: 901
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 »

Add to briefcase 12»»

Permissions Expand / Collapse