| Author |
 Topic  |
|
|
rishikantr
Starting Member
India
1 Posts |
 Posted - 05/25/2007 : 01:57:16
|
hi all ,
i have returned a stored procedure
which goes like this
-------------
-------------
create procedure imp_flr
@lg_dt datetime,
@table_name varchar(50)
AS
DECLARE
@lg_dt1 datetime,
@sqlquery nvarchar(1024)
BEGIN
SET @lg_dt1=@lg_dt-1
set @sqlquery='SELECT logdate, substring(entry,24,8)as tid,substring(entry,39,9) AS status INTO #tmpimp_st2 FROM '+@table_name+'(nolock) WHERE logdate between '+char(39)+convert(varchar(50),@lg_dt1,21)+char(39)+' and '+char(39)+convert(varchar(50),@lg_dt,21)+char(39)+' and entry like'+char(39)+'%import started%'+char(39)+''
print @sqlquery
exec sp_executesql @sqlquery
set @sqlquery='SELECT logdate, substring(entry,24,8)as tid,substring(entry,39,9) AS status INTO #tmpimp_fi2 FROM '+@table_name+'(nolock) WHERE logdate between '+char(39)+convert(varchar(50),@lg_dt1,21)+char(39)+' and '+char(39)+convert(varchar(50),@lg_dt,21)+char(39)+' and entry like'+char(39)+'%import finished%'+char(39)+''
PRINT @sqlquery
exec sp_executesql @sqlquery
SELECT tid,count(tid)AS no_of_threads INTO #tmpimp_st3 FROM #tmpimp_st2 GROUP BY tid
select * from #tmpimp_fi2
SELECT tid,count(tid)AS no_of_threads INTO #tmpimp_fi3 FROM #tmpimp_fi2 GROUP BY tid
SELECT A.tid,A.no_of_threads INTO #tmpimp_st5 FROM #tmpimp_st3 A WHERE A.tid not in (SELECT tid FROM #tmpimp_fi3)
SELECT A.tid,A.no_of_threads-B.no_of_threads as no_of_threads INTO #tmpimp_fi5 FROM #tmpimp_st3 A join #tmpimp_fi3 B ON A.tid = B.tid WHERE A.no_of_threads-B.no_of_threads>0
SELECT tid as tid, logdate as logdate INTO #tmpimp_st7 from #tmpimp_st2 where tid in (select tid from #tmpimp_st5)
SELECT tid as tid,max(logdate) as logdate INTO #tmpimp_fi7 from #tmpimp_st2 where tid in (select tid from #tmpimp_fi5) group by tid
set @sqlquery='SELECT logdate, substring(entry,charindex('+char(39)+'tid:'+char(39)+',entry,1)+4,charindex('+char(39)+'import started'+char(39)+',entry,1)-charindex('+char(39)+'tid:'+char(39)+',entry,1)-5)as tid,
substring(entry,charindex('+char(39)+'Import Started'+char(39)+',entry,1)+17,charindex('+char(39)+') from'+char(39)+',entry,1)-charindex('+char(39)+'Import Started'+char(39)+',entry,1)-17)as type_of_import,
substring(substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0,charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0)) as clientname,
substring(substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0)+1,charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0)+1)-charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0)-1) as sitename,
substring(substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0)+1)+1,datalength(substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9))-charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0))) as listname,
no_of_rows_to_be_imported =
case
when substring(entry,charindex('+char(39)+'Import Started'+char(39)+',entry,1)+17,charindex('+char(39)+') from'+char(39)+',entry,1)-charindex('+char(39)+'Import Started'+char(39)+',entry,1)-17) like '+char(39)+'ODBC Import'+char(39)+' then substring(entry ,charindex('+char(39)+'imported:'+char(39)+',entry,1)+9,charindex('+char(39)+'.'+char(39)+',entry,charindex('+char(39)+'imported:'+char(39)+',entry,1))-charindex('+char(39)+'imported:'+char(39)+',entry,1)-9)
else substring(entry,charindex('+char(39)+'imported:'+char(39)+',entry,1)+9,datalength(entry)-charindex('+char(39)+'imported:'+char(39)+',entry,1)-8)
end
from '+@table_name+'(nolock) where logdate in(select logdate from #tmpimp_st7 ) and logdate between '+convert(varchar(20),@lg_dt1,20)+' and '+char(39)+convert(varchar(20),@lg_dt,20)+char(39)+' and entry like '+char(39)+'%import started%'+char(39)+''
exec sp_executesql @sqlquery
set @sqlquery='SELECT logdate, substring(entry,charindex('+char(39)+'tid:'+char(39)+',entry,1)+4,charindex('+char(39)+'import started'+char(39)+',entry,1)-charindex('+char(39)+'tid:'+char(39)+',entry,1)-5)as tid,
substring(entry,charindex('+char(39)+'Import Started'+char(39)+',entry,1)+17,charindex('+char(39)+') from'+char(39)+',entry,1)-charindex('+char(39)+'Import Started'+char(39)+',entry,1)-17)as type_of_import,
substring(substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0,charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0)) as clientname,
substring(substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0)+1,charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0)+1)-charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0)-1) as sitename,
substring(substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0)+1)+1,datalength(substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9))-charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),charindex('+char(39)+','+char(39)+',substring(entry,charindex('+char(39)+'\FLODB'+char(39)+',entry,1)+9,charindex('+char(39)+') initiated'+char(39)+',entry,1)-charindex('+char(39)+'\FLODB'+char(39)+',entry,1)-9),0))) as listname,
no_of_rows_to_be_imported =
case
when substring(entry,charindex('+char(39)+'Import Started'+char(39)+',entry,1)+17,charindex('+char(39)+') from'+char(39)+',entry,1)-charindex('+char(39)+'Import Started'+char(39)+',entry,1)-17) like '+char(39)+'ODBC Import'+char(39)+' then substring(entry ,charindex('+char(39)+'imported:'+char(39)+',entry,1)+9,charindex('+char(39)+'.'+char(39)+',entry,charindex('+char(39)+'imported:'+char(39)+',entry,1))-charindex('+char(39)+'imported:'+char(39)+',entry,1)-9)
else substring(entry,charindex('+char(39)+'imported:'+char(39)+',entry,1)+9,datalength(entry)-charindex('+char(39)+'imported:'+char(39)+',entry,1)-8)
end
from '+@table_name+'(nolock) where logdate in(select logdate from #tmpimp_fi7 ) and logdate between '+convert(varchar(20),@lg_dt1,20)+' and '+char(39)+convert(varchar(20),@lg_dt1,20)+char(39)+' and entry like '+char(39)+'%import started%'+char(39)+''
exec sp_executesql @sqlquery
END
GO
-----------------
-----------
it compiles successsfully but when i run it i get a error message like this
Server: Msg 208, Level 16, State 1, Procedure imp_flr, Line 28
Invalid object name '#tmpimp_st2'.
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 05/25/2007 : 03:14:13
|
"help me immediately"
Why? You going to pay my consultancy fees? Say "please" even?
I'm outta this thread ... |
 |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 05/25/2007 : 03:45:10
|
with a tone like this i doubt you'll get any help.
the proper way to do it is:
"Please help me as soon as possible. Thank you."
Being nice gets you a long way. Being rude gets you ignored.
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 05/25/2007 : 05:13:17
|
The scope of that table is not available outside dynamic sql
<<
Help me immediately
>>
Nowbies need proper naming conventions for Subject 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 05/25/2007 : 11:22:22
|
Guys, I think rishikantr's rather terse request may be the result of language issues rather than attitude...
rishikantr, instead of using SELECT INTO, declare your temporary tables explicitly in the body of your procedure, and then use INSERT INTO in your dynamic SQL.
e4 d5 xd5 Nf6 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 05/25/2007 : 12:49:10
|
"Guys, I think rishikantr's rather terse request may be the result of language issues rather than attitude..."
Fair enough, but either way a subject indicating what the nature of the issue is would be more helpful, and would attract people with knowledge of the matter.
And that's leaving aside posting in a forum called "CLOSED" ... |
Edited by - Kristen on 05/25/2007 12:49:44 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 05/25/2007 : 19:16:25
|
Since the forum is CLOSED why does it still allow posting ?
KH
|
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 05/26/2007 : 09:06:00
|
good question?
probably graz had too much SQL Crack and forgot about it.. 
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp |
|
|
| |
Topic |
|
|
|
help me immediately
Forum Locked
