Problem querying SysObjects and SysIndexes

When executing a basic query against (see below) the system object
table in a SQL 2000 database it displays the following error.
SELECT 1 FROM SYSOBJECTS
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYSOBJECTS'.
The table does exist and the query is being run as an account with SQL
Admin privileges. This also occurs when trying to query the SysIndexes
table. Other databases on the server are able to query these tables.
What is causing this problem on this one database and how can it be
resolved?Some ideas.
1)Try SELECT 1 FROM sysobjects
2)Try SELECT 1 FROM dbo.sysobjects
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:1190368228.718871.96350@.57g2000hsv.googlegroups.com...
> When executing a basic query against (see below) the system object
> table in a SQL 2000 database it displays the following error.
> SELECT 1 FROM SYSOBJECTS
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'SYSOBJECTS'.
> The table does exist and the query is being run as an account with SQL
> Admin privileges. This also occurs when trying to query the SysIndexes
> table. Other databases on the server are able to query these tables.
> What is causing this problem on this one database and how can it be
> resolved?
>|||> What is causing this problem on this one database and how can it be
> resolved?
Object name case sensitivity is determined by the database collation. I
suspect the following query will return a case-sensitive collation:
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:1190368228.718871.96350@.57g2000hsv.googlegroups.com...
> When executing a basic query against (see below) the system object
> table in a SQL 2000 database it displays the following error.
> SELECT 1 FROM SYSOBJECTS
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'SYSOBJECTS'.
> The table does exist and the query is being run as an account with SQL
> Admin privileges. This also occurs when trying to query the SysIndexes
> table. Other databases on the server are able to query these tables.
> What is causing this problem on this one database and how can it be
> resolved?
>|||Since posting I had resolved it.
I already thought of the suggestions that Jack posted, it was what Dan
has suggested a particular 3rd party database had been setup as case
sensitive.
On 21 Sep, 12:22, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > What is causing this problem on this one database and how can it be
> > resolved?
> Object name case sensitivity is determined by the database collation. I
> suspect the following query will return a case-sensitive collation:
> SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robin9876" <robin9...@.hotmail.com> wrote in message
> news:1190368228.718871.96350@.57g2000hsv.googlegroups.com...
> > When executing a basic query against (see below) the system object
> > table in a SQL 2000 database it displays the following error.
> > SELECT 1 FROM SYSOBJECTS
> > Server: Msg 208, Level 16, State 1, Line 1
> > Invalid object name 'SYSOBJECTS'.
> > The table does exist and the query is being run as an account with SQL
> > Admin privileges. This also occurs when trying to query the SysIndexes
> > table. Other databases on the server are able to query these tables.
> > What is causing this problem on this one database and how can it be
> > resolved?

Problem on creating stored procedure

The code is as below:

--Drop procedures if they exsit
if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_PagedItems]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PagedItems]
GO

if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_PagedItemsByTime]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PagedItemsByTime]
GO

if exists (SELECT * FROM master.dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_selectedEventMessage]') AND OBJECTPROPERTY(id,N'IsProcedure')=1)
drop procedure [dbo].[sp_selectedEventMessage]
GO

--Definitions of procedures
USE LanDeskDB
GO

CREATE PROCEDURE sp_PagedItems
(
@.QueryVARCHAR(1000),
@.Pageint,
@.RecsPerPageint,
@.startDateVARCHAR(100),
@.endDateVARCHAR(100),
@.allTimeint,
@.flagint
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(2000)
DECLARE @.Order VARCHAR(200)
DECLARE @.TotalBIGINT

CREATE TABLE #TempTable
(
TempTableID BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
EventLogID BIGINT ,
EventDateTime datetime,
MachineID INT ,
TypeID INT ,
SessionID INT ,
SourceID INT ,
MessageID BIGINT ,
UserID INT,
CategoryNumber INT,
EventID INT
)
IF (@.flag = 1)
BEGIN
SET @.Order = 'ORDER BY EventDateTime'
END
IF (@.flag = 2)
BEGIN
SET @.Order = 'ORDER BY SessionID,EventDateTime'
END
IF (@.flag = 3)
BEGIN
SET @.Order = 'ORDER BY TypeID,EventDateTime'
END
IF (@.flag = 4)
BEGIN
SET @.Order = 'ORDER BY CategoryNumber,EventDateTime'
END
IF(@.allTime <> 1)
BEGIN
IF(LEN(@.Query)>1)
BEGIN
SET @.Query = @.Query+'AND EventDateTime>='''+@.startDate+''' AND EventDateTime <= '''+@.endDate+''''
END
ELSE
BEGIN
SET @.Query = 'WHERE EventDateTime>='''+@.startDate+''' AND EventDateTime <= '''+@.endDate+''''
END
END
SET @.SQL=
'INSERT INTO #TempTable (EventLogID,EventDateTime,MachineID,TypeID,SessionID,SourceID,MessageID,UserID,CategoryNumber,EventID)'+
'SELECT EventlogID,EventDateTime,MachineID,TypeID,SessionID,SourceID,MessageID,UserID,CategoryNumber,EventID FROM EventLog '+
@.Query+' '+@.Order
EXEC (@.SQL)

CREATE TABLE #TempTableTwo
(
TempTableTwoID BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
TempTableID BIGINT,
EventLogID BIGINT ,
EventDateTime datetime,
MachineID INT ,
TypeID INT ,
SessionID INT ,
SourceID INT ,
MessageID BIGINT ,
UserID INT,
CategoryNumber INT,
EventID INT,
)

DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = @.Page * @.RecsPerPage+1

INSERT #TempTableTwo

SELECT * FROM #TempTable T
WHERE T.TempTableID >@.FirstRec AND T.TempTableID < @.LastRec

SELECT TempT.EventLogID AS EventLogID,TempT.EventDateTime AS EventDateTime,
Ma.MachineName AS MachineName,Se.SessionName AS SessionName,Ty.TypeName AS TypeName,
TempT.CategoryNumber AS CategoryNumber,Us.UserName AS UserName,So.SourceName AS SourceName,
TempT.EventID AS EventID
FROM #TempTableTwo TempT,Machines Ma,Types Ty,Sessions Se,Sources So,Users Us
WHERE TempT.MachineID = Ma.MachineID AND TempT.TypeID = Ty.TypeID AND TempT.SessionID = Se.SessionID
AND TempT.SourceID = So.SourceID AND TempT.UserID = Us.UserID

SELECT COUNT(*) FROM #TempTable

DROP TABLE #TempTable
DROP TABLE #TempTableTwo
SET NOCOUNT OFF
END
GO

CREATE PROCEDURE sp_PagedItemsByTime
(
@.QueryVARCHAR(1000),
@.Page int,
@.RecsPerPage int,
@.startDateVARCHAR(100),
@.endDateVARCHAR(100),
@.allTime int
)
AS
BEGIN
EXEC sp_PagedItems @.Query,@.Page,@.RecsPerPage,@.startDate,@.endDate,@.allTime,1
END
GO

CREATE PROCEDURE sp_selectedEventMessage
(
@.EventLogID int
)
AS

BEGIN

SELECT Ma.MachineName,Ev.EventDateTime,Se.SessionName,Ty.TypeName,So.SourceName,Me.MessageDescription
FROM EventLog Ev,Sessions Se,Types Ty,Sources So,Messages Me,Machines Ma
WHERE Ev.EventLogID = @.EventLogID AND Ev.SessionID = Se.SessionID AND Ma.MachineID = Ev.MachineID
AND Ev.TypeID = Ty.TypeID AND Ev.SourceID = So.SourceID AND Ev.MessageID = Me.MessageID

END

GO

I got the error messge as
Server: Msg 2714, Level 16, State 5, Procedure sp_PagedItems, Line 107
There is already an object named 'sp_PagedItems' in the database.
Server: Msg 2714, Level 16, State 5, Procedure sp_PagedItemsByTime, Line 13
There is already an object named 'sp_PagedItemsByTime' in the database.
Server: Msg 2714, Level 16, State 5, Procedure sp_selectedEventMessage, Line 12
There is already an object named 'sp_selectedEventMessage' in the database.

But I already delete those procedures before I create them. Could anyone give some suggestion?Change this master.dbo.sysobjects to LanDeskDB.dbo.sysobjects in your 3 EXISTS statements in the top of your code and it will work fine for you.

Terri|||Thank you very much. It works now.

problem on alter table

I would like to disable triggers of all tables. How can I do that? I can get
the names of table and trigger from sysobjects. But how can I
programmatically to replace the table name and trigger name from the ALTER
TABLE command?
ALTER TABLE table1 DISABLE TRIGGER trigger1
Thanks.Hi
-- to diable all
EXEC sp_MSForEachTable N'ALTER TABLE ? DISABLE TRIGGER ALL'
GO
-- to enable all
EXEC sp_MSForEachTable N'ALTER TABLE ? ENABLE TRIGGER ALL'
GO
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:7A084773-C0CC-4543-99A8-B563C44EDF7F@.microsoft.com...
> I would like to disable triggers of all tables. How can I do that? I can
get
> the names of table and trigger from sysobjects. But how can I
> programmatically to replace the table name and trigger name from the ALTER
> TABLE command?
> ALTER TABLE table1 DISABLE TRIGGER trigger1
> Thanks.|||Thanks Dimant.
It works OK but I cannot find this store procedures on SQL Book Online. Why?
Thank you.
"Uri Dimant" wrote:

> Hi
> -- to diable all
> EXEC sp_MSForEachTable N'ALTER TABLE ? DISABLE TRIGGER ALL'
> GO
> -- to enable all
> EXEC sp_MSForEachTable N'ALTER TABLE ? ENABLE TRIGGER ALL'
> GO
>
> "joeau" <joeau@.discussions.microsoft.com> wrote in message
> news:7A084773-C0CC-4543-99A8-B563C44EDF7F@.microsoft.com...
> get
>
>|||Hi
Its not documented from MS. Actually it may have some changes in the future
version of the SQL Server , so I'd not recommend you to use it in
production.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eK94OOrMFHA.3076@.TK2MSFTNGP14.phx.gbl...
> Hi
> -- to diable all
> EXEC sp_MSForEachTable N'ALTER TABLE ? DISABLE TRIGGER ALL'
> GO
> -- to enable all
> EXEC sp_MSForEachTable N'ALTER TABLE ? ENABLE TRIGGER ALL'
> GO
>
> "joeau" <joeau@.discussions.microsoft.com> wrote in message
> news:7A084773-C0CC-4543-99A8-B563C44EDF7F@.microsoft.com...
> get
ALTER
>