How do other people do logging in large systems on ASE ?
There are a couple of mechanisms that are commonly used and another less common
1) print messages
eg print "%1! %2! Rows affects %3!", @datetime, "LOGLEVEL-DEBUG", 100000
i) At the simple level print are simple and very effective. Best feature is being able to cope with different datatypes.
ii) When you run the proc manually, then you see the output immediately without any changes.
i) Looking through these logs is very awkward. If you're producing a few hundred log files each day each with a few gig of information
2) table logging
Simply adding message to a table
insert into LogTable select convert(varchar, @datetime) + "LOGLEVEL-DEBUG" + convert(varchar, 10000)
i) Awkward to develop against - datatypes must be converted to char.
ii) Contention on the log table.
iii) Performance hit
Sends a message out of the db to a listener for that to log.
i) No contention
ii) No performance hit
1) Awkward to develop against. Datatypes must be converted to char
We wanted to make our logging configurable so that all logging would go through a single proc which could then log in different ways.
However, its really awkward to handle the different datatypes. Either the calling proc must spend ages converting everything to a char or
the proc must access lots of different types of datatypes and then try and form a string based on the inputs.
What we really want is the equivalent of sprintf (select @s = sprint "%1! %2!", @datetime, "STRING" ?
So how do people develop a simple and configurable logging mechanism ?
Does anyone have any good idea on implementing this ?