procedure failed because 'LoginList' is table obje

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

SQL Server 2000 Forums

Transact-SQL (2000)

procedure failed because 'LoginList' is table obje

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-10 : 17:05:08

When I execute the script on my machine it works but when we send it to the client it gives the error:

procedure 'LoginListInput' failed because 'LoginListInput' is a table object
Here is my script:

use mosaikDB737
exec sp_MSforeachDB
'
use

select sk.loginname COLLATE SQL_Latin1_General_CP1_CI_AS as searchedTxt ,
object_name(id) AS ProcName ,Len(SubString(o.text,1, PatIndex(''%'' + ltrim(rtrim(sk.loginname COLLATE SQL_Latin1_General_CP1_CI_AS))
+ ''%'', o.text))) AS Line
from syscomments as o inner join mosaikDB737.dbo.LoginListInput as sk on o.text like ''%'' +
ltrim(rtrim(sk.loginname COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%''
'
Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-10 : 17:14:54

Could you explain to us in words what you are trying to do with this query?

Tara Kizer

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-10 : 18:35:25

Hi Tkizer,

it just looks up all the SPs (syscomments of the current database ?) in all the databases of the instance, then it searches the occurences of the keywords located in mosaikDB737.dbo.LoginListInput as sk in each of those databases (referred by ? in my script) then I insert the results in the table mosaikDB737.dbo.SearchOutput1.

For the part:

Len(SubString(o.text,1, PatIndex(''%'' + ltrim(rtrim(sk.loginname COLLATE SQL_Latin1_General_CP1_CI_AS))+ ''%'', o.text)))-Len(Replace(SubString(o.text,1, PatIndex
(''%'' + ltrim(rtrim(sk.loginname COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%'', o.text COLLATE SQL_Latin1_General_CP1_CI_AS)),char(13),''''))+1 AS Line

That s just to return the line number of the occurence of the keyword sk.loginname in the string o.text which is the text of the current SP in the current DB

Thanks a lot

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-11 : 10:46:49

my mistake it was a case sensitivity pb sk.loginname should be spelled sk.LoginName
Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources