--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.
