Solved

using local tables in sql server 2005

Posted on 2011-09-14
19
287 Views
Last Modified: 2012-05-12
Hi,
part of a  stored procedure  i have in sql server 2005 is;
set @strSQLsw = '
select * into #swpivot from (
select server_name, ' + @colssw + '  from (
select server_name,
right(convert(varchar, dateadd(m, datediff(m, 0, [resolved_date]), 0), 106), 8)
 as eom, PROD_CAT1
            from #extranetSoftware
            where [resolved_date] >= ''' +
convert(varchar, @start_date_range,
 112) + ''' and [resolved_date] < ''' +convert(varchar,
 @end_date_range+1,112) + ''') o
            pivot (count(PROD_CAT1) for eom in (' + @cols +' )) as p
)a
select * from #swpivot
 '

print (@strSQLsw)
execute (@strSQLsw)

that typically returns the following;
 
 
Server_name      Dec 2010 Software      Dec 2011 Software         
Mail001                                            1                                           0         
Sms005                                            0                                           2       

I want to process the contents of #swpivot but it not visible outside of  @strSQLsw

i get the following error when i add  "select * from #swpivot"
after the line "execute (@strSQLsw)"


Msg 208, Level 16, State 0, Procedure CO_RPT54_ComponentFailuresTrend_Extranet_testV2, Line 180
Invalid object name '#swpivot'.

any help appreciated, thanks
0
Comment
Question by:blossompark
  • 10
  • 5
  • 2
  • +1
19 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 112 total points
ID: 36535328
After the SET s@strSQLw = ....
 You need to execute that string before selecting from #swpivot
0
 
LVL 3

Assisted Solution

by:Srm74
Srm74 earned 278 total points
ID: 36535335
You have to execute the @strSQLsw, before you can select on the temporary table,

otherwise its not there...

Hope it helps..
0
 

Author Comment

by:blossompark
ID: 36535504
Hi kelvinsparks and  and srm 74, thanks for your responses...I am  a little confused
execute (@strSQLsw) is already in  the code....it is executing?
0
 
LVL 3

Assisted Solution

by:Srm74
Srm74 earned 278 total points
ID: 36535532
The sql commands are exdecuted sequentially..

And the execute is located AFTER the "select * from #swpivot"..

You need to move the line "select * from #swpivot" below the execute!!..

i.e.




print (@strSQLsw)
execute (@strSQLsw)

select * from #swpivot
0
 

Author Comment

by:blossompark
ID: 36535548
yes i understand that..
but that is what gives me the error
0
 

Author Comment

by:blossompark
ID: 36535553
set @strSQLsw = '
select * into #swpivot from (
select server_name, ' + @colssw + '  from (
select server_name,
right(convert(varchar, dateadd(m, datediff(m, 0, [resolved_date]), 0), 106), 8)
 as eom, PROD_CAT1
            from #extranetSoftware
            where [resolved_date] >= ''' +
convert(varchar, @start_date_range,
 112) + ''' and [resolved_date] < ''' +convert(varchar,
 @end_date_range+1,112) + ''') o
            pivot (count(PROD_CAT1) for eom in (' + @cols +' )) as p
)a
select * from #swpivot
 '

print (@strSQLsw)
execute (@strSQLsw)
select * from #swpivot


0
 

Author Comment

by:blossompark
ID: 36535557
the second select * from #swpivot

 gives the error

commenting out  this, produces no error
0
 
LVL 3

Assisted Solution

by:Srm74
Srm74 earned 278 total points
ID: 36535619
Have you tried adding go..

i.e.

set @strSQLsw = '
select * into #swpivot from (
select server_name, ' + @colssw + '  from (
select server_name,
right(convert(varchar, dateadd(m, datediff(m, 0, [resolved_date]), 0), 106), 8)
 as eom, PROD_CAT1
            from #extranetSoftware
            where [resolved_date] >= ''' +
convert(varchar, @start_date_range,
 112) + ''' and [resolved_date] < ''' +convert(varchar,
 @end_date_range+1,112) + ''') o
            pivot (count(PROD_CAT1) for eom in (' + @cols +' )) as p
)a


print (@strSQLsw)
execute (@strSQLsw)

GO

select * from #swpivot
0
 

Author Comment

by:blossompark
ID: 36535677
when i add go
i get a parse  error

Msg 208, Level 16, State 0, Line 1
Invalid object name '#swpivot'.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 3

Assisted Solution

by:Srm74
Srm74 earned 278 total points
ID: 36535706
Why not just drop the @strSQLsw...

Creating a temptable inside an execute seems to fail..


select * into #swpivot from (
select server_name, ' + @colssw + '  from (
select server_name,
right(convert(varchar, dateadd(m, datediff(m, 0, [resolved_date]), 0), 106), 8)
 as eom, PROD_CAT1
            from #extranetSoftware
            where [resolved_date] >= ''' +
convert(varchar, @start_date_range,
 112) + ''' and [resolved_date] < ''' +convert(varchar,
 @end_date_range+1,112) + ''') o
            pivot (count(PROD_CAT1) for eom in (' + @cols +' )) as p
)a

select * from #swpivot



0
 
LVL 3

Assisted Solution

by:Srm74
Srm74 earned 278 total points
ID: 36535707
And remember to drop the temp table again
0
 

Author Comment

by:blossompark
ID: 36536007
hi srm74
removing  @strSQLsw variable
causes compile error

Msg 102, Level 15, State 1,, Line 173
Incorrect syntax near ' + @cols +'.
0
 
LVL 18

Assisted Solution

by:lludden
lludden earned 110 total points
ID: 36536444
Local Temp tables created inside dynamic SQL are local to that context.
DECLARE @sSQL varchar(MAX)	= 'SELECT 1 as Col1 INTO #tmp1'

EXECUTE (@sSQL)
SELECT * FROM #tmp1  --<Error - object not found>

Open in new window


CREATE TABLE #tmp1 (col1 int)
DECLARE @sSQL varchar(MAX)	= 'INSERT INTO #tmp1 SELECT 1 as Col1'

EXECUTE (@sSQL)
SELECT * FROM #tmp1--<This works

Open in new window


So you have to either use a global temp table ##, or create a table in tempdb.  You can use a global temp table and index it off of the SPID of the calling process.
0
 

Author Comment

by:blossompark
ID: 36536923
Hi lludden, thanks for your input... will try your suggestions and update
0
 

Author Comment

by:blossompark
ID: 36537255
Hi lludden,
unfortunately, the number of columns in the local table may vary.....it depends on  the time frame of the date range inputted
Below is complete code for the stored procedure if that helps clarify
@start_date_range datetime ,  
	@end_date_range datetime
      
AS

SET @end_date_range = reporting.dbo.fn_RoundTimeToMidnight(@end_date_range)


create table #LT
(  
    RESOLVED_DATE   datetime,   
	DESCRIPTION    varchar(1024),
PROD_CAT1       varchar(256)
)

DECLARE @TSQL varchar(8000)
    
	SET  @TSQL = 
"INSERT INTO #LT 
SELECT * FROM OPENQUERY
(REMEDY_PROD,
' select  

ARADMIN.fn_adjusted_date(LAST_RESOLVED_DATE) as Resolved_Date,
SUBSTR(REPLACE(REPLACE(DESCRIPTION ,CHR(10),'' ''),CHR(13),'' ''), 1,1024) AS DESCRIPTION, 

PRODUCT_CATEGORIZATION_TIER_1 as Prod_Cat1
 FROM ARADMIN.HPD_HELP_DESK 
 WHERE 
--HPD_HELP_DESK.Status =  5 AND 
(HPD_HELP_DESK.INCIDENT_ASSOCIATION_TYPE  Is Null
 OR   HPD_HELP_DESK.INCIDENT_ASSOCIATION_TYPE  <>  1    ) 
AND HPD_HELP_DESK.Owner_Support_Company   =  ''ABC''  
 
AND 
(ARADMIN.fn_adjusted_date(HPD_HELP_DESK.CLOSED_DATE)
 BETWEEN timestamp ''" + CONVERT(varchar(19) , @start_date_range , 120)
 + "'' AND timestamp ''" + CONVERT(varchar(19) , @end_date_range , 120) + "'')
 ')  

"             
	
    EXEC (@TSQL)

SELECT * into #extranet FROM
(SELECT 
ServerList.ServerName as Server_Name,
RESOLVED_DATE,
 
PROD_CAT1

from #LT
inner join ServerList on #LT.Description LIKE
'%'+ServerList.ServerName+'%')A

--select * from #extranet

select * into #extranetHardware from
(select * from #extranet
where prod_cat1 ='Hardware')a

select * into #extranetSoftware from
(
select * from #extranet
where prod_cat1 ='Software')a
 --order by Server_Name;

--select * from #extranetHardware
--select * from #extranetSoftware


/*pivot for hardware by lowfatspread */
/* pivot for hardware */
--select * into #hwpivot from
--(
declare @strSQLhw varchar(max)
, @colshw varchar(max)
, @cols varchar(max);

;with CTE as (
	select 	dateadd(m, datediff(m, 0, @start_date_range), 0) as st, 
		dateadd(m, datediff(m, 0, @end_date_range), 0) as et
	union all
	select dateadd(m, 1, st),
			et
	from CTE 
	where dateadd(m, 1, st) <= et
)
select @colshw = stuff((select '],[' + right(convert(varchar, st, 106), 8)+'] as [' + right(convert(varchar, st, 106), 8)+' Hardware'
 from CTE for xml path('')), 1, 2, '') + ']'

;with CTE as (
	select 	dateadd(m, datediff(m, 0, @start_date_range), 0) as st, 
		dateadd(m, datediff(m, 0, @end_date_range), 0) as et
	union all
	select dateadd(m, 1, st),
			et
	from CTE 
	where dateadd(m, 1, st) <= et
)
select @cols = stuff((select '],[' + right(convert(varchar, st, 106), 8)
 from CTE for xml path('')), 1, 2, '') + ']'



set @strSQLhw = '
select * into #hwpivot from
(
select server_name, ' + @colshw + '  from (
select server_name, 
right(convert(varchar, dateadd(m, datediff(m, 0, [resolved_date]), 0), 106), 8)
 as eom, PROD_CAT1
		from #extranetHardware
		where [resolved_date] >= ''' + 
convert(varchar, @start_date_range,
 112) + ''' and [resolved_date] < ''' +convert(varchar, @end_date_range+1,112) + ''') o
		pivot (count(PROD_CAT1) for eom in (' + @cols +' )) as p 
)a
select * from #hwpivot
'
print (@strSQLhw)
execute (@strSQLhw)


/*pivot for software by lowfatspread */
/* pivot for software */
declare @strSQLsw varchar(max)
, @colssw varchar(max)
, @cols1 varchar(max);

;with CTE as (
	select 	dateadd(m, datediff(m, 0, @start_date_range), 0) as st, 
		dateadd(m, datediff(m, 0, @end_date_range), 0) as et
	union all
	select dateadd(m, 1, st),
			et
	from CTE 
	where dateadd(m, 1, st) <= et
)
select @colssw = stuff((select '],[' + right(convert(varchar, st, 106), 8)+'] as [' + right(convert(varchar, st, 106), 8)+' Software'
 from CTE for xml path('')), 1, 2, '') + ']'

;with CTE as (
	select 	dateadd(m, datediff(m, 0, @start_date_range), 0) as st, 
		dateadd(m, datediff(m, 0, @end_date_range), 0) as et
	union all
	select dateadd(m, 1, st),
			et
	from CTE 
	where dateadd(m, 1, st) <= et
)
select @cols1 = stuff((select '],[' + right(convert(varchar, st, 106), 8)
 from CTE for xml path('')), 1, 2, '') + ']'



set @strSQLsw = '
select * into #swpivot from (
select server_name, ' + @colssw + '  from (
select server_name, 
right(convert(varchar, dateadd(m, datediff(m, 0, [resolved_date]), 0), 106), 8)
 as eom, PROD_CAT1
		from #extranetSoftware
		where [resolved_date] >= ''' + 
convert(varchar, @start_date_range,
 112) + ''' and [resolved_date] < ''' +convert(varchar,
 @end_date_range+1,112) + ''') o
		pivot (count(PROD_CAT1) for eom in (' + @cols +' )) as p
)a
select * from #swpivot
 '

print (@strSQLsw)
execute (@strSQLsw)
select * from #swpivot

Open in new window

0
 
LVL 18

Assisted Solution

by:lludden
lludden earned 110 total points
ID: 36537492
Can't you just get rid of that last SELECT * after the execute (@strSQLsw)?

You are doing the select from within the Dymanic SQL.

if you need to have that data available for further processing, then you can do.
DECLARE @tmpTbl varchar(20) = '##swpivot' + CAST(@@SPID as varchar(10))
set @strSQLsw = '
IF  EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = ' + char(39) + @tmpTbl + char(39) + ' AND type = ' + char(39) + 'U' + char(39) + ') DROP TABLE ' + @tmpTbl
EXECUTE (@strSQLsw)

set @strSQLsw = '
select * into ' + @tmpTbl + ' from (
select server_name, ' + @colssw + '  from (
select server_name,
right(convert(varchar, dateadd(m, datediff(m, 0, [resolved_date]), 0), 106), 8)
 as eom, PROD_CAT1
            from #extranetSoftware
            where [resolved_date] >= ''' +
convert(varchar, @start_date_range,
 112) + ''' and [resolved_date] < ''' +convert(varchar,
 @end_date_range+1,112) + ''') o
            pivot (count(PROD_CAT1) for eom in (' + @cols +' )) as p
)a '
print (@strSQLsw)
execute (@strSQLsw)


0
 

Author Comment

by:blossompark
ID: 36541623
Hi lludden,
thanks for that...will try this and update with results
0
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 112 total points
ID: 36541798
Try this

set @strSQLsw = '
select * into #swpivot from (
select server_name, ' + @colssw + '  from (
select server_name,
right(convert(varchar, dateadd(m, datediff(m, 0, [resolved_date]), 0), 106), 8)
 as eom, PROD_CAT1
            from #extranetSoftware
            where [resolved_date] >= ''' +
convert(varchar, @start_date_range,
 112) + ''' and [resolved_date] < ''' +convert(varchar,
 @end_date_range+1,112) + ''') o
            pivot (count(PROD_CAT1) for eom in (' + @cols +' )) as p
)a

sp_executesql @strsqlsw
select * from #swpivot
0
 

Author Closing Comment

by:blossompark
ID: 36544100
Hi kelvinsparks,srm74 and lludden,,,
thank you for your inputs...
i have used some of the code and compromised the output i originally hoped for,
(essentially output to 2 tables instead of 1)

Thanks again for all your help...
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now