Audit trail - Sql Server 2000

Hi,
Greetings to all.
We are building up a new application using SQL Server 2000 & .NET.
Need to build basic “Audit trail” process, in which need to store {User_
Id/
DateTime/ Status/Process Name}.
What’s the best strategy for implementing “Audit trail” in SQL Server
2000
Applications?
Thanks for your time.I'm assuming that "Status" and "Process Name" are not columns in tables, but
rather logical names in your application. The simplest way is to use
something like .NET Enterprise Library or even build your own simple logic
in your application to keep trace of this.
Typically, in the database, if you want to track changes made to your
tables, you use triggers, but in your case, I think you want to track what
"functions" a user is using?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"SSUK" <SSUK@.newsgroups.nospam> wrote in message
news:7C422ADD-588A-4B34-BD0B-727C6F625BF9@.microsoft.com...
> Hi,
> Greetings to all.
> We are building up a new application using SQL Server 2000 & .NET.
> Need to build basic "Audit trail" process, in which need to store
> {User_Id/
> DateTime/ Status/Process Name}.
> What's the best strategy for implementing "Audit trail" in SQL Server 2000
> Applications?
> Thanks for your time.
>|||Thanks for your response. Let me explain my situation once again:-
e.g. I have a screen & different status of order on that screen i.e.
Created/Billed/Processed/Manufactured/Shipped.
What I want to do is, I want to record, which user had changed above order
status at what time?
Which means for each record, I want to build an Audit Trail?
I am aware that ,I can create my own Audit table & can create DB Triggers
for recording details ,or stored Proc can do same for me…
I am looking for what’s the best strategy to build an Audit trail.
Thank you very much for your precious time & Valuable inputs.
Regards.
"SriSamp" wrote:

> I'm assuming that "Status" and "Process Name" are not columns in tables, b
ut
> rather logical names in your application. The simplest way is to use
> something like .NET Enterprise Library or even build your own simple logic
> in your application to keep trace of this.
> Typically, in the database, if you want to track changes made to your
> tables, you use triggers, but in your case, I think you want to track what
> "functions" a user is using?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "SSUK" <SSUK@.newsgroups.nospam> wrote in message
> news:7C422ADD-588A-4B34-BD0B-727C6F625BF9@.microsoft.com...
>
>|||I would suggest triggers and audit tables. Try to keep the trigger as light
as possible. What kind of load (# of users, volume of data, etc.) does your
system have?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"SSUK" <SSUK@.newsgroups.nospam> wrote in message
news:ADC87BEF-D824-49DD-B840-304CD99C332D@.microsoft.com...
> Thanks for your response. Let me explain my situation once again:-
> e.g. I have a screen & different status of order on that screen i.e.
> Created/Billed/Processed/Manufactured/Shipped.
> What I want to do is, I want to record, which user had changed above order
> status at what time?
> Which means for each record, I want to build an Audit Trail?
> I am aware that ,I can create my own Audit table & can create DB Triggers
> for recording details ,or stored Proc can do same for me.
> I am looking for what's the best strategy to build an Audit trail.
> Thank you very much for your precious time & Valuable inputs.
> Regards.
>
> "SriSamp" wrote:
>|||Hi,
Apart from Trigger & Audit table ,does sql server comes with default
keywords like AUdit ? Or any other Standard/better approach to hadle it ?
Oracle allows us to audit data using the ‘AUDIT’ command. For example,
‘AUDIT DELETE ON my_table;’
Regards
"Alain Quesnel" wrote:

> I would suggest triggers and audit tables. Try to keep the trigger as ligh
t
> as possible. What kind of load (# of users, volume of data, etc.) does you
r
> system have?
> --
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "SSUK" <SSUK@.newsgroups.nospam> wrote in message
> news:ADC87BEF-D824-49DD-B840-304CD99C332D@.microsoft.com...
>
>|||Hi,
In terms of Users/Load ,it's 50 -75 users using it at one point.
"Alain Quesnel" wrote:

> I would suggest triggers and audit tables. Try to keep the trigger as ligh
t
> as possible. What kind of load (# of users, volume of data, etc.) does you
r
> system have?
> --
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "SSUK" <SSUK@.newsgroups.nospam> wrote in message
> news:ADC87BEF-D824-49DD-B840-304CD99C332D@.microsoft.com...
>
>|||I don't think there are any default tools, but I'd personally steer
clear of triggers. While they're great at doing what they do, and lend
themselves naturally to an audit trail, I've found the main drawback of
them is that you can't see them. When someone else comes to maintain
your application, they would have to know that the triggers are there.
I know that with good documentation and skilled staff this would appear
simple, but you can't generally count on either.
My prefered method (given this drawback of triggers) is to write a
sufficiently generic auditing stored procedure, then within your insert
/ update / delete stored procs call this method. This has the advantage
that your auditing behaviour is centralised, and therefore if you need
to change it you won't need to iterate through the triggers, but also
that the operation is visible to anyone else who needs to pick up your
app.
I suspect this is a sufficiently controversial suggestion to get
utterly thrashed on here now, so... off you go guys, above the belt
with the punches please.|||Thanks for your reply.I agree to your suggestion to quite some extent.But i
am wondering , do we have an better way / inbuilt way of handling this ? Or
any more standard way (Best practise) of handling this ?
"Will" wrote:

> I don't think there are any default tools, but I'd personally steer
> clear of triggers. While they're great at doing what they do, and lend
> themselves naturally to an audit trail, I've found the main drawback of
> them is that you can't see them. When someone else comes to maintain
> your application, they would have to know that the triggers are there.
> I know that with good documentation and skilled staff this would appear
> simple, but you can't generally count on either.
> My prefered method (given this drawback of triggers) is to write a
> sufficiently generic auditing stored procedure, then within your insert
> / update / delete stored procs call this method. This has the advantage
> that your auditing behaviour is centralised, and therefore if you need
> to change it you won't need to iterate through the triggers, but also
> that the operation is visible to anyone else who needs to pick up your
> app.
> I suspect this is a sufficiently controversial suggestion to get
> utterly thrashed on here now, so... off you go guys, above the belt
> with the punches please.
>|||I don't know of any in-built methods, I'm pretty sure there aren't any.
However the information you'll be recording is usually fairly specific
to your app. My advice would be either to maintain auditing in the
stored procs, or, depending on the structure of your web app you could
enhance your data access layer to add a custom audit module that
controls all the auditing.
Personally I prefer keeping out of SQL server based auditing for
several reasons:
1) you can more easily re-use your auditing code
2) you can more easily audit to a centralised database for several apps
3) by auditing to a separate database you are not having to back up
tracing data along with your actual app data (though you do have to
consider re-synchronisation when restoring)
4) you are able to capture web app info more easily (e.g. if you're
using forms based authentication you can capture usernames, or you can
capture the page that the update originated from).
5) the auditing is then done for your application, rather than on your
db. This has the advantage that if someone else makes a custom update
to your database, you can't accuse the application of it. On the other
hand, you don't have the traceability of what was done (but personally
I prefer the idea that your app only audits things that it does).
6) the auditing is more visible and more maintainable
I could probably come up with more reasons, but this should indicate my
preference.
Perhaps someone else could recommend some off the shelf plug-ins for
this?
Will|||Just to give you a couple of more options to evaluate:
Have a server-side profiler trace going in which you capture the relevant ev
ents.
Use some of the 3:rd party (transaction) log reader tools, where some has ex
plicit audit
capabilities. The transaction log contains information about all modificatio
ns and some of these
tools can also log SELECT using a profiler trace in conjunction with the tra
nsaction log. I've
listed some of these log reader tools on my links page:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SSUK" <SSUK@.newsgroups.nospam> wrote in message
news:7C422ADD-588A-4B34-BD0B-727C6F625BF9@.microsoft.com...
> Hi,
> Greetings to all.
> We are building up a new application using SQL Server 2000 & .NET.
> Need to build basic "Audit trail" process, in which need to store {User_Id
/
> DateTime/ Status/Process Name}.
> What's the best strategy for implementing "Audit trail" in SQL Server 2000
> Applications?
> Thanks for your time.
>

Audit table

Hi All
I'm looking for some code which create audit table and build triggers which
register all changes on table.
Maybe someone from you know where can I find it?
Regards
MichalChapter 6: Audit Logging in the book "Transact-SQL Cookbook" discuses audit
logging using triggers in detail:
http://vyaskn.tripod.com/transact-sql_cookbook.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Michal" <michalb77@.gazeta.pl> wrote in message
news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
Hi All
I'm looking for some code which create audit table and build triggers which
register all changes on table.
Maybe someone from you know where can I find it?
Regards
Michal|||Hi,
Copy the Order table structure to AuditOrder table.
Select * into AuditOrders from Orders where 1= 2
Auditing trigger:-
--
CREATE TRIGGER Audittriiger_order ON Order
FOR INSERT, UPDATE, DELETE
AS
if @.@.rowcount = 0 return
DECLARE @.action char (7), @.inserted bit, @.deleted bit
set @.deleted = case when exists (select * from deleted) then 1 else 0 end
set @.inserted = case when exists (select * from inserted) then 1 else 0 end
set @.action = case when @.deleted = 1 and @.inserted = 1
then 'Before'
else 'DELETE'
end
if @.deleted = 1
begin
INSERT INTO AuditOrders ( operation, orderid, Order_name,
Order_qty,Order_date)
SELECT @.action AS F1,
RTRIM(SYSTEM_USER) AS F2,
[Order].Orderid,
[Order].Order_ name
FROM Deleted INNER JOIN Order ON Deleted.Orderid = Order.OrderID end
set @.action = case when @.deleted = 1 and @.inserted = 1
then 'After'
else 'INSERT'
end
if @.inserted = 1
begin
INSERT INTO AuditOrders( operation, orderid, Order_name,
Order_qty,Order_date)
SELECT @.action AS F1,
RTRIM(SYSTEM_USER) AS F2,
[Order].Orderid,
[Order].Order_ name
FROM Inserted INNER JOIN Order ON Inserted.Orderid = Order.OrderID
end
"Michal" <michalb77@.gazeta.pl> wrote in message
news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
> Hi All
> I'm looking for some code which create audit table and build triggers
> which register all changes on table.
> Maybe someone from you know where can I find it?
> Regards
> Michal
>|||Michael may also want to have an additional date/time column in AuditOrder
that stores when the change took place (getdate) and perhaps another column
to store the system username (system_user) of who initiated the change.
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:e5ZpZ5tsFHA.1168@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Copy the Order table structure to AuditOrder table.
> Select * into AuditOrders from Orders where 1= 2
> Auditing trigger:-
> --
> CREATE TRIGGER Audittriiger_order ON Order
> FOR INSERT, UPDATE, DELETE
> AS
> if @.@.rowcount = 0 return
> DECLARE @.action char (7), @.inserted bit, @.deleted bit
> set @.deleted = case when exists (select * from deleted) then 1 else 0 end
> set @.inserted = case when exists (select * from inserted) then 1 else 0
> end
> set @.action = case when @.deleted = 1 and @.inserted = 1
> then 'Before'
> else 'DELETE'
> end
> if @.deleted = 1
> begin
> INSERT INTO AuditOrders ( operation, orderid, Order_name,
> Order_qty,Order_date)
> SELECT @.action AS F1,
> RTRIM(SYSTEM_USER) AS F2,
> [Order].Orderid,
> [Order].Order_ name
> FROM Deleted INNER JOIN Order ON Deleted.Orderid = Order.OrderID end
>
> set @.action = case when @.deleted = 1 and @.inserted = 1
> then 'After'
> else 'INSERT'
> end
> if @.inserted = 1
> begin
> INSERT INTO AuditOrders( operation, orderid, Order_name,
> Order_qty,Order_date)
> SELECT @.action AS F1,
> RTRIM(SYSTEM_USER) AS F2,
> [Order].Orderid,
> [Order].Order_ name
> FROM Inserted INNER JOIN Order ON Inserted.Orderid = Order.OrderID
> end
>
> "Michal" <michalb77@.gazeta.pl> wrote in message
> news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
>

Attribute Limit of 5000 When Mining a Cube

I'm trying to build a association model in the Standard Edition based on an existing cube. I keep getting the error:

Error (Data mining): The 'Product Recommendations' mining model has 60385 attributes. This number of attributes exceeds the attribute limit of 5000 allowed by the current version of the algorithm associated with the mining model.

I created Cube Slice filters and those limit the Customer and Product dimensions (Product is Nested) to well under 5000. The error message also does not change. The number of attributes is equal to the number of rows in the Product dimension, but I expected the cube slice to reduce the number. I tested all the SQL used while it processes and with the MDXFilters the number of rows returned is well under 5000.

So, in short, the final questions is, is it possible to create a mining model in standard edition based on an existing cube where the nested dimension in the model has more than 5000 rows? Is there some other way to filter the query?

I guess my only choice on this if there isn't a way is to extract the data into relational table with only the rows I want to analyze....that's a huge pain and doesn't really make sense when the filters should limit the model size.

What is annoying on this is I can't find one reference anywhere on the microsoft site that this limit even exists within the product...

Thanks for any assistance on this!

This sounds like a bug (and knowing the code, I can see where it comes from).

Standard edition is limited to 5000 attributes for Standard Edition. What you are doing is perfectly legal, assuming there are actually less than 5000 attributes. You may need to contact support so we can get this fixed, or start by writing up a bug report at connect.microsoft.com. If you cannot get this resolved through support and this is blocking you, please let us know and we will see if we can make something happen.

Thanks

-Jamie

|||

Thanks...I'll get a support case open and see what happens.

- L

|||

Jamie,

I opened a support case with Microsoft and have been working with them for a few days. They did agree that this is a defect in the product. The current response is that, "they may consider a design change to account for filtered cube slices". This is a pretty big deal for our development. I'd also question the limit in general since its not even documented, does the limit really drive more sales to the Enterprise Version of the product? I think if there is a limit, it should really be much larger since in practice, 5000 rows in a nested table is so small. It is very common for small companies to have product catalogs of 60k products.

THANKS!

|||

I have been working with the support person you are in contact with. The issue isn't if the problem with be fixed, it is simply how the problem will be fixed. I believe we will have a resolution today - in any case, it will resolve the issue you are seeing.

Regarding the 5000 attribute limit, that's a completely seperate debate that I don't want to get deeply into in the forums. I would only state that the differentiation between the various editions isn't limited to data volumes, functional differences arise as well (e.g. data mining transforms in Integration Services, etc.). Your cost structure in general may not support a wholesale changeover to Enterprise edition due to data volumes, but the ROI of some of the features that are made available in the larger edition may warrant an upgrade on a single machine. There are different licensing options for this as well, i.e. server CAL vs Processor licences, which for a small-medium size business, a 50-CAL Enterprise license may do the trick and still be in the affordable price range.

If you want to contact me directly, it's Jamie <dot> MacLennan <at> microsoft <dot> com

Thanks

Attribute Limit of 5000 When Mining a Cube

I'm trying to build a association model in the Standard Edition based on an existing cube. I keep getting the error:

Error (Data mining): The 'Product Recommendations' mining model has 60385 attributes. This number of attributes exceeds the attribute limit of 5000 allowed by the current version of the algorithm associated with the mining model.

I created Cube Slice filters and those limit the Customer and Product dimensions (Product is Nested) to well under 5000. The error message also does not change. The number of attributes is equal to the number of rows in the Product dimension, but I expected the cube slice to reduce the number. I tested all the SQL used while it processes and with the MDXFilters the number of rows returned is well under 5000.

So, in short, the final questions is, is it possible to create a mining model in standard edition based on an existing cube where the nested dimension in the model has more than 5000 rows? Is there some other way to filter the query?

I guess my only choice on this if there isn't a way is to extract the data into relational table with only the rows I want to analyze....that's a huge pain and doesn't really make sense when the filters should limit the model size.

What is annoying on this is I can't find one reference anywhere on the microsoft site that this limit even exists within the product...

Thanks for any assistance on this!

This sounds like a bug (and knowing the code, I can see where it comes from).

Standard edition is limited to 5000 attributes for Standard Edition. What you are doing is perfectly legal, assuming there are actually less than 5000 attributes. You may need to contact support so we can get this fixed, or start by writing up a bug report at connect.microsoft.com. If you cannot get this resolved through support and this is blocking you, please let us know and we will see if we can make something happen.

Thanks

-Jamie

|||

Thanks...I'll get a support case open and see what happens.

- L

|||

Jamie,

I opened a support case with Microsoft and have been working with them for a few days. They did agree that this is a defect in the product. The current response is that, "they may consider a design change to account for filtered cube slices". This is a pretty big deal for our development. I'd also question the limit in general since its not even documented, does the limit really drive more sales to the Enterprise Version of the product? I think if there is a limit, it should really be much larger since in practice, 5000 rows in a nested table is so small. It is very common for small companies to have product catalogs of 60k products.

THANKS!

|||

I have been working with the support person you are in contact with. The issue isn't if the problem with be fixed, it is simply how the problem will be fixed. I believe we will have a resolution today - in any case, it will resolve the issue you are seeing.

Regarding the 5000 attribute limit, that's a completely seperate debate that I don't want to get deeply into in the forums. I would only state that the differentiation between the various editions isn't limited to data volumes, functional differences arise as well (e.g. data mining transforms in Integration Services, etc.). Your cost structure in general may not support a wholesale changeover to Enterprise edition due to data volumes, but the ROI of some of the features that are made available in the larger edition may warrant an upgrade on a single machine. There are different licensing options for this as well, i.e. server CAL vs Processor licences, which for a small-medium size business, a 50-CAL Enterprise license may do the trick and still be in the affordable price range.

If you want to contact me directly, it's Jamie <dot> MacLennan <at> microsoft <dot> com

Thanks

Attaching a database with MSDE2000A

Hello,
I want to get an already build database attached to the MSDE2000 SQL Server. I do not have the SQL Server Enterprise Explorer. Any help appreciated !
Hartmut
You can use OSQL.EXE and execute sp_attach_db.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hMelchin" <hMelchin@.discussions.microsoft.com> wrote in message
news:A78B08E2-4072-4E4A-94B4-9951472D486E@.microsoft.com...
> Hello,
> I want to get an already build database attached to the MSDE2000 SQL Server. I do not have the SQL
Server Enterprise Explorer. Any help appreciated !
> Hartmut

Attaching a database with MSDE2000A

Hello,
I want to get an already build database attached to the MSDE2000 SQL Server.
I do not have the SQL Server Enterprise Explorer. Any help appreciated !
HartmutYou can use OSQL.EXE and execute sp_attach_db.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hMelchin" <hMelchin@.discussions.microsoft.com> wrote in message
news:A78B08E2-4072-4E4A-94B4-9951472D486E@.microsoft.com...
> Hello,
> I want to get an already build database attached to the MSDE2000 SQL Server. I do
not have the SQL
Server Enterprise Explorer. Any help appreciated !
> Hartmut