Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...truncating eventlog in dnn 3.4.1truncating eventlog in dnn 3.4.1
Previous
 
Next
New Post
10/13/2015 1:46 AM
 

Hello I am struggling over this truncate script.  I have sql server 2012 and I have tried everything to get this to work.  According to my web.config file the database owner is dbo and the object qualifier is blank as it just has the two quotation marks close together with no space and no words.  Having read the instructions for executing this script I replaced all the databaseowner placeholders with dbo and all the objectqualifiers with nothing.

Suffice it to say I couldn't get it to work and half of the errors were syntax.  One was some error where it was looking for a boolean expression but didn't get what it expected.  I tried to substitute the place holder with the dbo and the nothingness with braces and than without the braces but in both cases I didn't get any good result as the script was littered with syntax errors and I just didn't know if you had an updated script or you could look at the one I posted to see if you could see any reason as to why it would fail to work in sql server 2012.

The script is pasted below for your viewing given this html editor doesn't strip the code.

 

Thanks,

 

Bo

 

 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/* Truncate EventLog - Version 0.9.8 (2015-07-25)

   ================================================================================
   (c) Sebastian Leupold, dnnWerk/gamma concept mbH 2014-2015

   Run this Script to remove all entries of EventLog in DNN Platform V.5.0.0 - 7.4.0
      
   == Please make sure to use latest version from http://dnnscript.codeplex.com ===
   
   Instructions:
   =============
   - Install by running as script from SQL item in Host menu inside DNN or run in 
     SQL Server Management Studio, after replacing placeholders {databaseOwner} and
{objectQualifier} by its proper values from web.config file.

   - Make sure that the currently used account is member of dbOwner database role.
   
   License and Disclaimer:
   =======================
   Published under Microsoft Open Source Reciprocal License (Ms-RL). For details, 
   please read http://dnnscript.codeplex.com/license.
   Feel free to use this script as you need, but there is no warranty or liability 
   for any damage or effort, eventually been caused.

   Please report issues at https://dnnscript.codeplex.com/WorkItem/Create
   ================================================================================
*/

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}sys_currentDNNVersion]') AND Type = N'FN')
DROP FUNCTION {databaseOwner}[{objectQualifier}sys_currentDNNVersion]
GO
-- --------- create tooling: --------- 

CREATE FUNCTION {databaseOwner}[{objectQualifier}sys_currentDNNVersion]()
RETURNS Int
AS
BEGIN
DECLARE @Vers Int;
SELECT Top(1) @Vers = Major * 10000 + Minor * 100 + Build FROM {databaseOwner}[{objectQualifier}Version] ORDER BY CreatedDate DESC;
RETURN @Vers;
END
GO

IF {databaseOwner}[{objectQualifier}sys_currentDNNVersion]() >= 70400 BEGIN
-- Drop Foreign Key Constraints:
DECLARE @fkName nVarChar(100) = Null;
SELECT @fkName = name FROM sys.foreign_keys 
WHERE parent_object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ExceptionEvents]')
  AND Object_id IN (SELECT constraint_object_id  
                     FROM  sys.foreign_key_columns F 
 JOIN  sys.columns C ON F.parent_object_id = C.object_id AND F.parent_column_id = C.column_ID 
 WHERE C.Name = N'LogEventID');
IF Not @fkName Is Null
Exec(N'ALTER TABLE {databaseOwner}[{objectQualifier}ExceptionEvents] DROP CONSTRAINT [' + @fkName +'];');

SET @fkName = Null;
SELECT @fkName = name FROM sys.foreign_keys 
WHERE parent_object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}EventLog]')
  AND Object_id IN (SELECT constraint_object_id  
                     FROM  sys.foreign_key_columns F 
 JOIN  sys.columns C ON F.parent_object_id = C.object_id AND F.parent_column_id = C.column_ID 
 WHERE C.Name = N'ExceptionHash');
IF Not @fkName Is Null
Exec(N'ALTER TABLE {databaseOwner}[{objectQualifier}EventLog] DROP CONSTRAINT [' + @fkName +']')
END
GO

-- Truncate tables:
IF {databaseOwner}[{objectQualifier}sys_currentDNNVersion]() >= 70400 BEGIN
TRUNCATE TABLE {databaseOwner}[{objectQualifier}Exceptions]
TRUNCATE TABLE {databaseOwner}[{objectQualifier}ExceptionEvents]
TRUNCATE TABLE {databaseOwner}[{objectQualifier}EventLog]
END ELSE
TRUNCATE TABLE {databaseOwner}[{objectQualifier}EventLog]
GO

IF {databaseOwner}[{objectQualifier}sys_currentDNNVersion]() >= 70400 BEGIN
-- Recreate Foreign Key Constraints (using common naming):
ALTER TABLE {databaseOwner}[{objectQualifier}ExceptionEvents] 
 WITH CHECK ADD CONSTRAINT [FK_{objectQualifier}ExceptionEvents_EventLog] 
FOREIGN KEY([LogEventID])
REFERENCES {databaseOwner}[{objectQualifier}EventLog] ([LogEventID])
 ON DELETE CASCADE;
 
ALTER TABLE {databaseOwner}[{objectQualifier}EventLog] 
 WITH CHECK ADD CONSTRAINT [FK_{objectQualifier}EventLog_Exceptions] 
FOREIGN KEY([ExceptionHash])
REFERENCES {databaseOwner}[{objectQualifier}Exceptions] ([ExceptionHash])
 ON DELETE NO ACTION;
END
GO

DROP FUNCTION {databaseOwner}[{objectQualifier}sys_currentDNNVersion]

GO

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 
New Post
10/13/2015 3:46 AM
 
you need to replace "{databaseOwner}" by "dbo." (please note the trailing "." and "objectQualifier" by "" - or run the script as is from Host > SQL.

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
10/13/2015 11:05 AM
 

Hello Sebastian thanks for you quick response.  I did what you mentioned and the errors were basically reduced to two and none of them syntax errors here is 

the new sql based on your recommendations

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/* Truncate EventLog - Version 0.9.8 (2015-07-25)

   ================================================================================

   (c) Sebastian Leupold, dnnWerk/gamma concept mbH 2014-2015

 

   Run this Script to remove all entries of EventLog in DNN Platform V.5.0.0 - 7.4.0

      

   == Please make sure to use latest version from http://dnnscript.codeplex.com ===

   

   Instructions:

   =============

   - Install by running as script from SQL item in Host menu inside DNN or run in 

     SQL Server Management Studio, after replacing placeholders dbo. and

""- by its proper values from web.config file.

 

   - Make sure that the currently used account is member of dbOwner database role.

   

   License and Disclaimer:

   =======================

   Published under Microsoft Open Source Reciprocal License (Ms-RL). For details, 

   please read http://dnnscript.codeplex.com/license.

   Feel free to use this script as you need, but there is no warranty or liability 

   for any damage or effort, eventually been caused.

 

   Please report issues at https://dnnscript.codeplex.com/WorkItem/Create

   ================================================================================

*/

 

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'dbo.[""- sys_currentDNNVersion]') AND Type = N'FN')

DROP FUNCTION dbo.[""-sys_currentDNNVersion]

GO

-- --------- create tooling: --------- 

 

CREATE FUNCTION dbo.[""-sys_currentDNNVersion]()

RETURNS Int

AS

BEGIN

DECLARE @Vers Int;

SELECT Top(1) @Vers = Major * 10000 + Minor * 100 + Build FROM dbo.[""-Version] ORDER BY CreatedDate DESC;

RETURN @Vers;

END

GO

 

IF dbo.[""-sys_currentDNNVersion]() >= 70400 BEGIN

-- Drop Foreign Key Constraints:

DECLARE @fkName nVarChar(100) = Null;

SELECT @fkName = name FROM sys.foreign_keys 

WHERE parent_object_id = OBJECT_ID(N'dbo.[""-ExceptionEvents]')

  AND Object_id IN (SELECT constraint_object_id  

                     FROM  sys.foreign_key_columns F 

 JOIN  sys.columns C ON F.parent_object_id = C.object_id AND F.parent_column_id = C.column_ID 

 WHERE C.Name = N'LogEventID');

IF Not @fkName Is Null

Exec(N'ALTER TABLE dbo.[""-ExceptionEvents] DROP CONSTRAINT [' + @fkName +'];');

 

SET @fkName = Null;

SELECT @fkName = name FROM sys.foreign_keys 

WHERE parent_object_id = OBJECT_ID(N'dbo.[""-EventLog]')

  AND Object_id IN (SELECT constraint_object_id  

                     FROM  sys.foreign_key_columns F 

 JOIN  sys.columns C ON F.parent_object_id = C.object_id AND F.parent_column_id = C.column_ID 

 WHERE C.Name = N'ExceptionHash');

IF Not @fkName Is Null

Exec(N'ALTER TABLE dbo.[""-EventLog] DROP CONSTRAINT [' + @fkName +']')

END

GO

 

-- Truncate tables:

IF dbo.[""-sys_currentDNNVersion]() >= 70400 BEGIN

TRUNCATE TABLE dbo.[""-Exceptions]

TRUNCATE TABLE dbo.[""-ExceptionEvents]

TRUNCATE TABLE dbo.[""-EventLog]

END ELSE

TRUNCATE TABLE dbo.[""-EventLog]

GO

 

IF dbo.[""-sys_currentDNNVersion]() >= 70400 BEGIN

-- Recreate Foreign Key Constraints (using common naming):

ALTER TABLE dbo.[""-ExceptionEvents] 

 WITH CHECK ADD CONSTRAINT [FK_""-ExceptionEvents_EventLog] 

FOREIGN KEY([LogEventID])

REFERENCES dbo.[""-EventLog] ([LogEventID])

 ON DELETE CASCADE;

 

ALTER TABLE dbo.[""-EventLog] 

 WITH CHECK ADD CONSTRAINT [FK_""-EventLog_Exceptions] 

FOREIGN KEY([ExceptionHash])

REFERENCES dbo.[""-Exceptions] ([ExceptionHash])

 ON DELETE NO ACTION;

END

GO

 

DROP FUNCTION dbo.[""-sys_currentDNNVersion]

GO

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Below are the errors thrown from sql server 2012

---------------------------------------------------------------------------------------------------------------------------------------------------------------

Msg 208, Level 16, State 1, Line 2

Invalid object name 'dbo.""-Version'.

Msg 208, Level 16, State 1, Line 3

Invalid object name 'dbo.""-Version'.

Msg 4701, Level 16, State 1, Line 8

Cannot find the object """-EventLog" because it does not exist or you do not have permissions.

Msg 208, Level 16, State 1, Line 2

Invalid object name 'dbo.""-Version'.

-----------------------------------------------------------------------------------------------------------------------------------------------------------

I know I have permissions to my tables as I am signed in as admin and I tested truncating the sitelog and the schedulehistory table and that worked fine under my account.  Any ideas for this error.  I double checked my table prefixes just to verify it matched what the web.config file said and indeed it did.

Thanks for your help.

 
New Post
10/13/2015 11:44 AM
 
I apologize, in your case, you need to replace {objectQualifier} by an empty string, without the quotes, e.g.
IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'dbo.[sys_currentDNNVersion]') AND Type = N'FN')

DROP FUNCTION dbo.[sys_currentDNNVersion]

GO

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
10/13/2015 1:59 PM
 

Just did what you said but I get those same errors that I got when I was using ""- here is the sql statement as revised

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/* Truncate EventLog - Version 0.9.8 (2015-07-25)

================================================================================

(c) Sebastian Leupold, dnnWerk/gamma concept mbH 2014-2015

Run this Script to remove all entries of EventLog in DNN Platform V.5.0.0 - 7.4.0

== Please make sure to use latest version from http://dnnscript.codeplex.com ===

Instructions:

=============

- Install by running as script from SQL item in Host menu inside DNN or run in

SQL Server Management Studio, after replacing placeholders dbo. and

by its proper values from web.config file.

- Make sure that the currently used account is member of dbOwner database role.

License and Disclaimer:

=======================

Published under Microsoft Open Source Reciprocal License (Ms-RL). For details,

please read http://dnnscript.codeplex.com/license.

Feel free to use this script as you need, but there is no warranty or liability

for any damage or effort, eventually been caused.

Please report issues at https://dnnscript.codeplex.com/WorkItem/Create

================================================================================

*/

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'dbo.[sys_currentDNNVersion]') AND Type = N'FN')

DROP FUNCTION dbo.[sys_currentDNNVersion]

GO

-- --------- create tooling: ---------

CREATE FUNCTION dbo.[sys_currentDNNVersion]()

RETURNS Int

AS

BEGIN

DECLARE @Vers Int;

SELECT Top(1) @Vers = Major * 10000 + Minor * 100 + Build FROM dbo.[Version] ORDER BY CreatedDate DESC;

RETURN @Vers;

END

GO

IF dbo.[sys_currentDNNVersion]() >= 70400 BEGIN

-- Drop Foreign Key Constraints:

DECLARE @fkName nVarChar(100) = Null;

SELECT @fkName = name FROM sys.foreign_keys

WHERE parent_object_id = OBJECT_ID(N'dbo.[ExceptionEvents]')

AND Object_id IN (SELECT constraint_object_id

FROM sys.foreign_key_columns F

JOIN sys.columns C ON F.parent_object_id = C.object_id AND F.parent_column_id = C.column_ID

WHERE C.Name = N'LogEventID');

IF Not @fkName Is Null

Exec(N'ALTER TABLE dbo.[ExceptionEvents] DROP CONSTRAINT [' + @fkName +'];');

SET @fkName = Null;

SELECT @fkName = name FROM sys.foreign_keys

WHERE parent_object_id = OBJECT_ID(N'dbo.[EventLog]')

AND Object_id IN (SELECT constraint_object_id

FROM sys.foreign_key_columns F

JOIN sys.columns C ON F.parent_object_id = C.object_id AND F.parent_column_id = C.column_ID

WHERE C.Name = N'ExceptionHash');

IF Not @fkName Is Null

Exec(N'ALTER TABLE dbo.[EventLog] DROP CONSTRAINT [' + @fkName +']')

END

GO

-- Truncate tables:

IF dbo.[sys_currentDNNVersion]() >= 70400 BEGIN

TRUNCATE TABLE dbo.[Exceptions]

TRUNCATE TABLE dbo.[ExceptionEvents]

TRUNCATE TABLE dbo.[EventLog]

END ELSE

TRUNCATE TABLE dbo.[EventLog]

GO

IF dbo.[sys_currentDNNVersion]() >= 70400 BEGIN

-- Recreate Foreign Key Constraints (using common naming):

ALTER TABLE dbo.[ExceptionEvents]

WITH CHECK ADD CONSTRAINT [FK_ExceptionEvents_EventLog]

FOREIGN KEY([LogEventID])

REFERENCES dbo.[EventLog] ([LogEventID])

ON DELETE CASCADE;

ALTER TABLE dbo.[EventLog]

WITH CHECK ADD CONSTRAINT [FK_EventLog_Exceptions]

FOREIGN KEY([ExceptionHash])

REFERENCES dbo.[Exceptions] ([ExceptionHash])

ON DELETE NO ACTION;

END

GO

DROP FUNCTION dbo.[sys_currentDNNVersion]

GO

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Keep in mind that I am trying to run this via ssms 2012.  I prefer this over using sql via host as it saves the third party step and directly queries the database.  Any other ideas as to why it is not working in my case.  What would cause my setup to be any different the others except that I am using sql 2012 and I wonder if that causes a problem with your code.  Weird but anyway let me know if you have any more suggestions.

In a sense I wish dotnetnuke would not have done this dividing eventlog into 3 tables as the headache wouldn't be there otherwise but thank you for putting this code together because I certainly would not have known how to cause foreign keys to drop out in order to truncate a table and than to put them back together again.

 
Previous
 
Next
HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...truncating eventlog in dnn 3.4.1truncating eventlog in dnn 3.4.1


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.

Content Layout

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out