help me immediately - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

help me immediately

Author

Topic

rishikantr
Starting Member

1 Post

Posted - 2007-05-25 : 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

22859 Posts

Posted - 2007-05-25 : 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

11752 Posts

Posted - 2007-05-25 : 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

22864 Posts

Posted - 2007-05-25 : 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
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-25 : 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

22859 Posts

Posted - 2007-05-25 : 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" ...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-25 : 19:16:25

Since the forum is CLOSED why does it still allow posting ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-26 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources