Solved

using local tables in sql server 2005

Posted on 2011-09-14
19
302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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
 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 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