November 20, 2007 at 9:49 pm
Comments posted to this topic are about the item Generate HTML output for given SQL Query
November 21, 2007 at 4:20 am
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]
November 21, 2007 at 9:30 am
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
November 21, 2007 at 9:35 am
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]
November 22, 2007 at 3:22 am
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
November 26, 2007 at 4:01 am
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
March 27, 2008 at 5:19 pm
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 '
November 11, 2009 at 8:59 am
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.
November 18, 2009 at 12:05 am
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.
October 25, 2011 at 8:31 am
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!
October 25, 2011 at 6:54 pm
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?
October 27, 2011 at 2:00 pm
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.
December 13, 2011 at 1:10 pm
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/
July 16, 2013 at 7:58 am
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,
May 12, 2016 at 6:59 am
Thanks for the script.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply