Stored Procedure error - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure error

Author

Topic

anaylor01
Starting Member

28 Posts

Posted - 2008-12-12 : 18:23:27

When I run this stored procedure it returns invalid object errors. I want it to return only the data in the columns table. How can I prevent the invalid object errors from showing?

set nocount on
go
set Ansi_Warnings on
go
set ANSI_NULLS on
go
alter proc spp_FindText @text varchar(128)
as
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='servers')
drop table servers
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='databases')
drop table databases
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='columns')
drop table columns

CREATE TABLE servers(sname VARCHAR(255))
CREATE TABLE DATABASES(SERVERNAME VARCHAR(255), NAME VARCHAR(128))
CREATE TABLE columns(FoundIn varchar(100),servername varchar(100), databasename varchar(100),tableName VARCHAR(255),columnname varchar(245), LineNumber int,Procname varchar(200))
INSERT servers EXEC master..XP_CMDShell 'OSQL -L'
Insert into servers select @@servername
update servers set sname = ltrim(sname)
DELETE servers WHERE sname='Servers:' or sname like '%(local)%' or sname is null OR (SNAME NOT LIKE 'CL%' and SNAME not like 'SV%')
create index idx_ServName on servers (sname)
Declare search cursor for
Select sname FROM servers
DECLARE @Servname varchar(200)
DECLARE @TableName varchar(200)
DECLARE @Type char(1)
Declare @ColumnName varchar(128)
--DECLARE @LINKSERVER VARCHAR(128)
DECLARE @SQL VARCHAR(1000)
DECLARE @SQL1 VARCHAR(1000)
DECLARE @SQL2 VARCHAR(1000)
DECLARE @SQL3 VARCHAR(1000)
--set @text = 'dst'
--SET @LINKSERVER = 'EXEC SP_ADDLINKEDSERVER "' + @SERVNAME + '"'
Open Search
Fetch Next From Search into @Servname

While @@Fetch_Status = 0
Begin

SET @SQL = 'INSERT INTO DATABASES select ' + char(39) + @servname + char(39) + ' AS SERVERNAME, name from [' + @servname + ']. master.dbo.sysdatabases'
EXEC(@SQL)
delete from databases where name in ('model','master','tempdb','msdb')
Declare databases cursor for
Select name FROM databases
declare @database varchar(100)
Open databases
Fetch Next From databases into @database

While @@Fetch_Status = 0
Begin
set @sql1 = ('insert into columns (foundin,servername,databasename,tablename, columnname)SELECT ''Column'' as Foundin,' + char(39) + @servname + char(39) + ' as Servername, ' + char(39) + @database + char(39) + ' as databasename,Table_name AS TableName, Column_name as Columnname FROM '+ @database + '.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ''%' + @text + '%''')
exec(@sql1)
set @sql2 = ('insert into columns (foundin,servername,databasename,tablename) SELECT ''Table'' as Foundin,' + char(39) + @servname + char(39) + ' as Servername, ' + char(39) + @database + char(39) + ' as databasename,Table_name AS TableName FROM '+ @database + '.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE ''%' + @text + '%''')
exec (@sql2)
insert into columns (foundin, servername, databasename,linenumber,procname)SELECT DISTINCT 'Proc' as Foundin,'' + @servname + '' as Servername, '' + @database + '' as databasename, Len(SubString(text,1, PatIndex('%' + @text + '%', text)))-Len(Replace(SubString(text,1, PatIndex('%' + @text + '%', text)),char(13),''))+1 AS Line,
OBJECT_NAME(id) AS ProcName
FROM syscomments
WHERE text like '%' + @text + '%'
ORDER BY ProcName, Line
Fetch Next From databases into @database
end
close databases
deallocate databases
Fetch Next From Search into @Servname
end
close Search
deallocate search
go
select * from columns order by 1,2,3,4
go

gvphubli
Yak Posting Veteran

54 Posts

Posted - 2008-12-12 : 20:22:32

Your tables are created and dropped dynamically
OR
Make sure you prefix the table names with proper owern name like [owner_name].[table_name]

TechnologyYogi
http://gvphubli.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 01:08:32

which are objects that give invalid object error? first make sure they exist under proper owner name and also you do have access to them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources