SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 help me immediately
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

rishikantr
Starting Member

India
1 Posts

Posted - 05/25/2007 :  01:57:16  Show Profile
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
22403 Posts

Posted - 05/25/2007 :  03:14:13  Show Profile
"help me immediately"

Why? You going to pay my consultancy fees? Say "please" even?

I'm outta this thread ...
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 05/25/2007 :  03:45:10  Show Profile  Visit spirit1's Homepage
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 05/25/2007 :  05:13:17  Show Profile  Send madhivanan a Yahoo! Message
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
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 05/25/2007 :  11:22:22  Show Profile
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/25/2007 :  12:49:10  Show Profile
"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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 05/25/2007 :  19:16:25  Show Profile
Since the forum is CLOSED why does it still allow posting ?


KH

Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 05/26/2007 :  09:06:00  Show Profile  Visit spirit1's Homepage
good question?

probably graz had too much SQL Crack and forgot about it..

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000