Solved

using local tables in sql server 2005

Posted on 2011-09-14
19
297 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query Grouping Question Record Inserts 9 64
SQL- GROUP BY 4 53
Finding Where Clause Value in SQL Views and SP 21 62
SQL Server View  - Timeout Expired 5 21
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

710 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