|
Joined: 10/7/2006
Posts: 7
|
|
|
I was wondering, why the sitelog table does not take the datetime stamp from the database and why there is the smalldatetime format used, without seconds?
If you like to foloow the user clicks in date history, without seconds this is quite hard...
Here is one solution to change the format and to use the stampt from the database:
BEGIN TRANSACTIONNUMERIC_ROUNDABORT OFFCONCAT_NULL_YIELDS_NULL ONANSI_NULLS ONANSI_PADDING ONANSI_WARNINGS ON
GO
ALTER TABLE
DROP CONSTRAINT FK_SiteLog_Portals
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE
(
SiteLogId int NOT NULL IDENTITY (1, 1),
DateTime datetime NOT NULL,
PortalId int NOT NULL,
UserId int NULL,
Referrer nvarchar(255) NULL,
Url nvarchar(255) NULL,
UserAgent nvarchar(255) NULL,
UserHostAddress nvarchar(255) NULL,
UserHostName nvarchar(255) NULL,
TabId int NULL,
AffiliateId int NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT
dbo.Tmp_SiteLog ON
GO
IF EXISTS
EXEC('INSERT INTO dbo.Tmp_SiteLog (SiteLogId, DateTime, PortalId, UserId, Referrer, Url, UserAgent, UserHostAddress, UserHostName, TabId, AffiliateId)
SELECT SiteLogId, CONVERT(datetime, DateTime), PortalId, UserId, Referrer, Url, UserAgent, UserHostAddress, UserHostName, TabId, AffiliateId FROM dbo.SiteLog WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT
dbo.Tmp_SiteLog OFF
GO
DROP TABLE
GO
EXECUTE
sp_rename N'dbo.Tmp_SiteLog', N'SiteLog', 'OBJECT'
GO
ALTER TABLE
dbo.SiteLog ADD CONSTRAINT
PK_SiteLog PRIMARY KEY CLUSTERED
(
SiteLogId
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX
(
PortalId
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE
dbo.SiteLog WITH NOCHECK ADD CONSTRAINT
FK_SiteLog_Portals FOREIGN KEY
(
PortalId
) REFERENCES dbo.Portals
(
PortalID
) ON UPDATE NO ACTION
ON DELETE CASCADE
NOT FOR REPLICATION
GO
COMMIT
/* Change stored procedure */
set
set QUOTED_IDENTIFIER ON
ANSI_NULLS ON
GO
ALTER procedure
@DateTime datetime,
@PortalId int,
@UserId int = null,
@Referrer nvarchar(255) = null,
@Url nvarchar(255) = null,
@UserAgent nvarchar(255) = null,
@UserHostAddress nvarchar(255) = null,
@UserHostName nvarchar(255) = null,
@TabId int = null,
@AffiliateId int = null
declare
insert into
DateTime,
PortalId,
UserId,
Referrer,
Url,
UserAgent,
UserHostAddress,
UserHostName,
TabId,
AffiliateId
)
values
getdate(),
@PortalId,
@UserId,
@Referrer,
@Url,
@UserAgent,
@UserHostAddress,
@UserHostName,
@TabId,
@AffiliateId
)
GO
(@SiteLogHistory intSiteLog (
as
[dbo].[AddSiteLog]
IX_SiteLog ON dbo.SiteLog
dbo.SiteLog
(SELECT * FROM dbo.SiteLog)
dbo.Tmp_SiteLogdbo.SiteLog
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET
SET
SET
SET
SET
COMMIT
BEGIN TRANSACTION
|
| |
|
|