Forum Discussion

KurtMayer's avatar
KurtMayer
Contributor
2 years ago

I need to delete a single Error Log record from the System table "ErrorLog"

I need to delete a single record in the Error Log but my Data Adapter won't let me, complaining about "unsafe SQL statements".

In a rather bone-headed move, while developing a BR, I accidentally wrote an insanely large block of text to the Error Log -- like somewhere between 1.3 and 1.5 million characters -- in a single record via BRApi.ErrorLog.LogMessage.

It's made the Error Log workspace page pretty much unusable because it's trying to read that record to display it. It takes 5 or 6 minutes to display the Error Log.

After finding the UniqueID for the offending record in the ErrorLog table, I tried the following SQL in a Data Adapter to remove the record:

DELETE TOP (1) from myOneStream_Framework.dbo.ErrorLog where UniqueID = 'bb5158a6-aae9-471c-b883-edddc8640a5e'

Apparently, OneStream thinks I am a cyber-criminal and blocked my code execution with the following message: 

Database query error. The specified SQL statement is not safe.

I also tried:

UPDATE myOneStream_Framework.dbo.ErrorLog

SET Description = 'log entry trimmed'

where UniqueID = 'bb5158a6-aae9-471c-b883-edddc8640a5e'

And was again rewarded with a resounding slap on the hand:

Database query error. The specified SQL statement is not safe.

Any suggestions on how I can get rid of this monstrous record?

Data Integration
Data Management
  • MarkBird's avatar
    MarkBird
    2 years ago

    Hey Kurt

    You can't execute an insert/delete statement from a SQL adapter. You'll need to do this from a Business Rule.

    I normally just create an Extender Business Rule. Something like this should work:

     

    Namespace OneStream.BusinessRule.Extender.ExecuteSQL
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
			Try
				'Create connection to Framework database
				Using dbConn As DbConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)
					'Execute SQL
					BRApi.Database.ExecuteActionQuery(dbconn, "UPDATE ErrorLog Set Description = 'log entry trimmed' Where UniqueID = 'bb5158a6-aae9-471c-b883-edddc8640a5e'", False, True)
				End Using

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace

     

    Then Execute it using the play button:

     

    Regards,

    Mark

  • MarkBird's avatar
    MarkBird
    Contributor III
    2 years ago

    Hey Kurt

    You can't execute an insert/delete statement from a SQL adapter. You'll need to do this from a Business Rule.

    I normally just create an Extender Business Rule. Something like this should work:

     

    Namespace OneStream.BusinessRule.Extender.ExecuteSQL
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
			Try
				'Create connection to Framework database
				Using dbConn As DbConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)
					'Execute SQL
					BRApi.Database.ExecuteActionQuery(dbconn, "UPDATE ErrorLog Set Description = 'log entry trimmed' Where UniqueID = 'bb5158a6-aae9-471c-b883-edddc8640a5e'", False, True)
				End Using

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace

     

    Then Execute it using the play button:

     

    Regards,

    Mark

  • Henning's avatar
    Henning
    Valued Contributor II
    2 years ago

    Hi, you are probably aware of this option, but posting this for interested folks: There is an option to write error logs into a file rather than the actual error log to avoid exactly what happened here ( ğŸ˜‰ ). Not only for single entries into the error log, but sometimes users write potentially hundreds of thousands of error logs into the error log, flooding it that way.
    [How to] Log into a file instead of the Error Log - OneStream Community (onestreamsoftware.com)

  • KurtMayer's avatar
    KurtMayer
    Contributor
    2 years ago

    Mark -- worked like a charm!!!  Thanks!

    Unrelated, how do you insert a 'code block' like you did with your code example?  I'm used to seeing an icon that essentially inserts the <code> tag.

    • MarkBird's avatar
      MarkBird
      Contributor III
      2 years ago

      No problem.

      Step1

      Step2

      Then just make sure you choose the correct language:

       

       

      • KurtMayer's avatar
        KurtMayer
        Contributor
        2 years ago

        That's what I was looking for and what I've used in other forum sites!  Guess what's not available when you create a post?  Not sure why inserting a code block is only available in a Reply, but thanks for pointing me in the right direction.