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