Logging: Can't disable Informational Events

I enabled Windows Events for OnError, OnTaskFailed, OnWarning for my package and subelements. However, even if no errors occur, I still get Informational Events generated even though those are disabled for all modules and packages.

Any way to disable Information Events? Is this an SSIS defect, or just an oddity of how it implements Windows Events?

Windows Event:
Source: SQLISPackage
Type: Information
Event ID: 12288
Description: Package "XXXXXX" started.

Event ID: 122289
Description: Package "XXXXXX" finished successfully.

faype02 wrote:

I enabled Windows Events for OnError, OnTaskFailed, OnWarning for my package and subelements. However, even if no errors occur, I still get Informational Events generated even though those are disabled for all modules and packages.

Any way to disable Information Events? Is this an SSIS defect, or just an oddity of how it implements Windows Events?

Windows Event:
Source: SQLISPackage
Type: Information
Event ID: 12288
Description: Package "XXXXXX" started.

Event ID: 122289
Description: Package "XXXXXX" finished successfully.

Oh yeah, I can reproduce that. Weird - I don't think those should be there.

[Microsoft follow-up] Is this a bug or by design?

-Jamie

|||

It appears that this is by design see:

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx

In section on "Custom Log Provider" it says:

"To facilitate operations management, SSIS packages always write some basic logging information to the Windows event log, even for packages that do not incorporate logging. SSIS packages write events for package initiation and package completion and they can be identified using either the SQLISPackage or SQLISService event sources."

It would be a lovely option to be able to disable this.

Regards

|||So if I have a package that executes every 30 seconds, I have no control over thousands of "package start" and "package finished" log events per day?

There must be a way to intercept these with an event handler in the package - how would this be done?

logging who did what

I have a web application accessing a SQL Server database (the ususal stuff).

I want to be able to log who did what on which table. I need to display this information on the web application. Is there an easy way of doing this, rather that making duplicates of a lot of data?

The best way I have thought of so far is making a new table with the following fields:
Table_Changed
Table_Primary_Key
Old_Field_Value
New_Field_Value
User
Date_Changed

Every time someone changes something, it is logged in this table, so that, at any time, I can display who changed what.
I have one more question. If I do do it this way, is there a way of getting the primary key value of any table? E.G. could I do something like this_table.primary_key.value ?

JagsYou may try to use "Trigger" to do this.|||Thank you for the help, but I am not actually worried about how I am going to do it (I was thinking of using triggers anyway).
I am more worried about whether the method I am using will work nicely, or will the table I create become so large that it will slow the server down too much.|||I guess how large the table gets will depend on how many updates your site will do each day. One option to get around this is to archive the data at set intervals. For instance, setup a sql server job once a month to copy all the data from the production server's logging table onto a 2nd servers logging table. This table would reside on a server that doesn't matter as much how fast it is running as the backup would happen in the middle of the night or some other time of inactivity.

Logging using OnError Event

Hi

We are generating log file in our SSIS package by enabling the built-in feature of SSIS tool. We are generating log for the "OnError" event. This also recorded the error/failed task messages in the text file "log.txt". That error information is too complex with more unwanted information like below

-OnError,,,pkgExtract,,,8/30/2006 11:50:04 AM,8/30/2006 11:50:04 AM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,,,pkgExtract,,,8/30/2006 11:50:04 AM,8/30/2006 11:50:04 AM,-1071607780,0x,There was an error with input column "create_user_id" (116) on input "OLE DB Destination Input" (103). The column status returned was: "The value violated the integrity constraints for the column.".

OnError,,,pkgExtract,,,8/30/2006 11:50:04 AM,8/30/2006 11:50:04 AM,-1071607767,0x,The "input "OLE DB Destination Input" (103)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (103)" specifies failure on error. An error occurred on the specified object of the specified component.

This is infact not in a better readable format. We also don't want to do our error logging in database.

Is there any way of defining our error log and create error error log with customization of our messages . Can we do it using OnError event handler.

Please help us with some good solution to avoid giving this confused error log messages.

Thanks

Kumaran

Sure, you can do that. Read this: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

-Jamie

Logging uses of SELECT ?

Scenario:

-I'm brand new to SQL Server 2000

-I have a vendor supplied application running on SQLServer 2k.

-I need to log which of my users has seen which data (from a
particular table). It's a healthcare privacy thing.

-The application does not do this and the vendor has no interest in
implementing it, though it seems pretty easy if one has the app code.

So I don't control the application or its code but I need to catch
who's seen what. Note that I don't have to catch it all. In other
words, I don't need to catch every instance, only whether user X has
seen patient Y's data at all. So I'm talking about a fairly small data
set. The end result I'm heading toward is a case in which a patient
demands to know who's seen his health records. I go to a db table and
say "select username from <viewlogtable> where patient_id = XXX".

How do I do that? It'd be easy if I could it were possible to write a
select trigger, or if I had some hooks into the app, but as it is I am
stumped. This is trickier than logging updates and inserts...

Thanks,
JohnThere is no such thing as a SELECT trigger. You may be able to satisfy
the requirement by analyzing the SQL log. There are some third party
products that can be of use. Do a google on "Lumigent" for literature
on their product.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"John" <jgoodlea@.fhcrc.org> wrote in message
news:80ff7fc9.0312151444.41c57adc@.posting.google.c om...
> Scenario:
> -I'm brand new to SQL Server 2000
> -I have a vendor supplied application running on SQLServer 2k.
> -I need to log which of my users has seen which data (from a
> particular table). It's a healthcare privacy thing.
> -The application does not do this and the vendor has no interest in
> implementing it, though it seems pretty easy if one has the app code.
> So I don't control the application or its code but I need to catch
> who's seen what. Note that I don't have to catch it all. In other
> words, I don't need to catch every instance, only whether user X has
> seen patient Y's data at all. So I'm talking about a fairly small data
> set. The end result I'm heading toward is a case in which a patient
> demands to know who's seen his health records. I go to a db table and
> say "select username from <viewlogtable> where patient_id = XXX".

Unfortunately I can't see any way of doing that. There's no such thing as a
select trigger.

As another poster said, Lumigent may do this, but I doubt it since I don't
believe Selects are logged at all. (wouldn't make much sense.)

Another option is to run the SQL Profiler and setup a profile to look for
JUST selects, etc. This solution actually might work fairly well
short-term. And even long-term if you really tighten the profile down to
record ONLY what you need.

> How do I do that? It'd be easy if I could it were possible to write a
> select trigger, or if I had some hooks into the app, but as it is I am
> stumped. This is trickier than logging updates and inserts...

Really what the vendor should be doing is all access through stored procs.
Then the stored proc could handle security and handle logging.

> Thanks,
> John|||You would have to ensure that the users had to view that data by executing a
stored procedure, not with a direct SELECT statement.
I'm not sure how much control you have over the application, or if you can
modify it, but this is certainly a way of 'logging' SELECT statements
made against a table or set of tables. If you can, in fact, edit the
application then you can edit it to execute a procedure that simply inserts
into the log and selects as this <simplified>:

DECLARE PROCEDURE ViewPatientRecords
(
@.PatientID int
,@.UserID int
)
AS

INSERT INTO ViewLogTable(Patient_ID, Viewer_ID, View_Date)
VALUES(@.Patient, @.UserID, GETDATE())

SELECT *
FROM Patient_Record
WHERE Patient_ID = @.PatientID

"John" <jgoodlea@.fhcrc.org> wrote in message
news:80ff7fc9.0312151444.41c57adc@.posting.google.c om...
> Scenario:
> -I'm brand new to SQL Server 2000
> -I have a vendor supplied application running on SQLServer 2k.
> -I need to log which of my users has seen which data (from a
> particular table). It's a healthcare privacy thing.
> -The application does not do this and the vendor has no interest in
> implementing it, though it seems pretty easy if one has the app code.
> So I don't control the application or its code but I need to catch
> who's seen what. Note that I don't have to catch it all. In other
> words, I don't need to catch every instance, only whether user X has
> seen patient Y's data at all. So I'm talking about a fairly small data
> set. The end result I'm heading toward is a case in which a patient
> demands to know who's seen his health records. I go to a db table and
> say "select username from <viewlogtable> where patient_id = XXX".
> How do I do that? It'd be easy if I could it were possible to write a
> select trigger, or if I had some hooks into the app, but as it is I am
> stumped. This is trickier than logging updates and inserts...
> Thanks,
> John

Logging User Activity

I would like to log which users are running what queries on our SQL Server 2005. Is there a way to do this?

I have both domain users and SQL users using the server. In the Windows logs I can see when a domain user logs in, however, the SQL users are logged in as anonymous. Even so, I can't tell what the users are doing on the system.

I have several websites and other tools that use SQL logins. We did this so users wouldn't need to know a password. I would like to know which of those tools are being used. Since most tools use a specfic set of views, if I could measure the use of the users and those views, I would know which ones can be decommissioned.

If I should post this in another forum, please let me know. This seemed like the best place.

Rob

That is a task for SQL Profiler. If you are using Edition other than Express, you will have the Profiler tool. It should have been installed with SSMS and the other client tools.

Verify your Edition and we can give you more directed help.

|||

as Arnie said either you can use built in tool Profiler or you can use system SP to trace the system activities

Refer :

http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

Madhu

|||

Arnie Rowland wrote:

That is a task for SQL Profiler. If you are using Edition other than Express, you will have the Profiler tool. It should have been installed with SSMS and the other client tools.

Verify your Edition and we can give you more directed help.

Enterprise edition, I believe. However, I've tried to verify and I'm not sure how to do that. I do know it is not the Express edition. I did find the SQL Profiler program.

Rob

|||

You can verify your Edition, Service Pack, etc. using:

SELECT @.@.VERSION

(or)

SELECT

SERVERPROPERTY( Edition ),

SERVERPROPERTY( ProductLevel )

Here are some resouces that will guide you with using SQL Profiler.

Profiler -How To: Use SQL Profiler
http://msdn2.microsoft.com/en-us/library/ms979207.aspx

Profiler -Introduction
http://www.developer.com/db/article.php/3482216

Profiler -SQL Profiler Tips and Tricks
http://www.developer.com/tech/article.php/3490086

Profiler - Step-By-Step: An introduction to SQL Server Profiler
http://articles.techrepublic.com.com/5100-6329_11-5054787.html

Profiler -Tracing SQL Statements
http://sqljunkies.com/Article/9513605D-FF3B-45AE-8056-ADF30772C1A9.scuk

Profiler -Use SQL Profiler to diagnose SQL Server performance issues
http://articles.techrepublic.com.com/5100-1035_11-1043670.html

Profiler -Using Profiler to Identify Poorly Performing Queries
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Profiler -Using SQL Profiler
2000 http://www.microsoft.com/technet/prodtechnol/sql/70/tips/sqlprof.mspx
2005 http://msdn2.microsoft.com/en-us/library/ms181091.aspx

|||

Arnie - thank you. I did some initial setup but can see I need to trim down the events being logged.

Rob

Logging user activity

I want to find out how to turn/configure logging so I can "see" who makes
changes to the databases. Thanks
Run profiler all the time and log to a table or use a product like
Lumigent's Entegra.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
news:CBA06229-DC30-4773-A460-831ABB1115E2@.microsoft.com...
>I want to find out how to turn/configure logging so I can "see" who makes
> changes to the databases. Thanks
|||Patrick R. wrote:
> I want to find out how to turn/configure logging so I can "see" who makes
> changes to the databases. Thanks
If you want to "see" who makes changes to the database, you'll have to
create an audit mechanism on the tables that you want to track. See
http://www.nigelrivett.net/AuditTrailTrigger.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Logging user activity

I want to find out how to turn/configure logging so I can "see" who makes
changes to the databases. ThanksRun profiler all the time and log to a table or use a product like
Lumigent's Entegra.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
news:CBA06229-DC30-4773-A460-831ABB1115E2@.microsoft.com...
>I want to find out how to turn/configure logging so I can "see" who makes
> changes to the databases. Thanks|||Patrick R. wrote:
> I want to find out how to turn/configure logging so I can "see" who makes
> changes to the databases. Thanks
If you want to "see" who makes changes to the database, you'll have to
create an audit mechanism on the tables that you want to track. See
http://www.nigelrivett.net/AuditTrailTrigger.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Logging user activity

I want to find out how to turn/configure logging so I can "see" who makes
changes to the databases. ThanksRun profiler all the time and log to a table or use a product like
Lumigent's Entegra.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
news:CBA06229-DC30-4773-A460-831ABB1115E2@.microsoft.com...
>I want to find out how to turn/configure logging so I can "see" who makes
> changes to the databases. Thanks|||Patrick R. wrote:
> I want to find out how to turn/configure logging so I can "see" who makes
> changes to the databases. Thanks
If you want to "see" who makes changes to the database, you'll have to
create an audit mechanism on the tables that you want to track. See
http://www.nigelrivett.net/AuditTrailTrigger.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Logging User

Hi,
I am trying to log the exact user (a.k.a., Domain/User) into a column in X
data table. Currently, our users login with a connection string passing
Mixed Mode Authentication with username/pwd. Again, what we want to capture
domain/user login info - "SUSER_SNAME" captures the connection string info.
Thanks!Chris,
TMK this would require your end-users to log into SQL Server using Windows
Authenticated accounts which is a best practice. This approach is more
secure and allows for better auditing.
HTH
Jerry
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:OXlICL3fGHA.3456@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am trying to log the exact user (a.k.a., Domain/User) into a column in X
> data table. Currently, our users login with a connection string passing
> Mixed Mode Authentication with username/pwd. Again, what we want to
> capture domain/user login info - "SUSER_SNAME" captures the connection
> string info.
> Thanks!
>|||Thank you - now I have to figure out why are are then using Mixed Mode
authentication I guess.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236D8ja3fGHA.2032@.TK2MSFTNGP02.phx.gbl...
> Chris,
> TMK this would require your end-users to log into SQL Server using Windows
> Authenticated accounts which is a best practice. This approach is more
> secure and allows for better auditing.
> HTH
> Jerry
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:OXlICL3fGHA.3456@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I am trying to log the exact user (a.k.a., Domain/User) into a column in
>> X data table. Currently, our users login with a connection string
>> passing Mixed Mode Authentication with username/pwd. Again, what we want
>> to capture domain/user login info - "SUSER_SNAME" captures the connection
>> string info.
>> Thanks!
>

Logging User

Hi,
I am trying to log the exact user (a.k.a., Domain/User) into a column in X
data table. Currently, our users login with a connection string passing
Mixed Mode Authentication with username/pwd. Again, what we want to capture
domain/user login info - "SUSER_SNAME" captures the connection string info.
Thanks!Chris,
TMK this would require your end-users to log into SQL Server using Windows
Authenticated accounts which is a best practice. This approach is more
secure and allows for better auditing.
HTH
Jerry
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:OXlICL3fGHA.3456@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am trying to log the exact user (a.k.a., Domain/User) into a column in X
> data table. Currently, our users login with a connection string passing
> Mixed Mode Authentication with username/pwd. Again, what we want to
> capture domain/user login info - "SUSER_SNAME" captures the connection
> string info.
> Thanks!
>|||Thank you - now I have to figure out why are are then using Mixed Mode
authentication I guess.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%236D8ja3fGHA.2032@.TK2MSFTNGP02.phx.gbl...
> Chris,
> TMK this would require your end-users to log into SQL Server using Windows
> Authenticated accounts which is a best practice. This approach is more
> secure and allows for better auditing.
> HTH
> Jerry
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:OXlICL3fGHA.3456@.TK2MSFTNGP05.phx.gbl...
>

Logging use of stored procedures

Hello,
I would like to know if it is possible to log the use of stored procedures
in SQL 2000 (and later in SQL 2005)
We want to log every time if a user makes use of a stored procedure without
adding a entry in each Stored procedure.
Is this possible ? if yes, how ?
thanks,
Frank Wijten
You can use SQL Trace to implement you custom tracing.
Look at Books on line under the path:
SQLServer Database Engine -> Administering the database Engine -> Monitoring
and Tuning for Performance -> Monitoring Events -> Introducing SQL Trace.
You should find it really interesting and may be a way to satisfy your
requirements
Gilberto
"Frank Wijten" wrote:

> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure without
> adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
>
|||"Frank Wijten" <f.wijten@.info.umcn.nl_NO_SPAM> wrote in message
news:Ox$65tQdHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure
> without adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
As others have pointed out, this IS possible using SQL trace or Profiler.
HOWEVER, it can potentially be very costly in terms of performance.
Just be aware.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Logging use of stored procedures

Hello,
I would like to know if it is possible to log the use of stored procedures
in SQL 2000 (and later in SQL 2005)
We want to log every time if a user makes use of a stored procedure without
adding a entry in each Stored procedure.
Is this possible ? if yes, how ?
thanks,
Frank WijtenAfraid not - You would need to collect the data via a profiling session and
then take a look at the results to see your procedure usage.
"Frank Wijten" <f.wijten@.info.umcn.nl_NO_SPAM> wrote in message
news:Ox$65tQdHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure
> without adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>|||You can use SQL Trace to implement you custom tracing.
Look at Books on line under the path:
SQLServer Database Engine -> Administering the database Engine -> Monitoring
and Tuning for Performance -> Monitoring Events -> Introducing SQL Trace.
You should find it really interesting and may be a way to satisfy your
requirements
Gilberto
"Frank Wijten" wrote:

> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure withou
t
> adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
>|||"Frank Wijten" <f.wijten@.info.umcn.nl_NO_SPAM> wrote in message
news:Ox$65tQdHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure
> without adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
As others have pointed out, this IS possible using SQL trace or Profiler.
HOWEVER, it can potentially be very costly in terms of performance.
Just be aware.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Logging use of stored procedures

Hello,
I would like to know if it is possible to log the use of stored procedures
in SQL 2000 (and later in SQL 2005)
We want to log every time if a user makes use of a stored procedure without
adding a entry in each Stored procedure.
Is this possible ? if yes, how ?
thanks,
Frank WijtenAfraid not - You would need to collect the data via a profiling session and
then take a look at the results to see your procedure usage.
"Frank Wijten" <f.wijten@.info.umcn.nl_NO_SPAM> wrote in message
news:Ox$65tQdHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure
> without adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>|||You can use SQL Trace to implement you custom tracing.
Look at Books on line under the path:
SQLServer Database Engine -> Administering the database Engine -> Monitoring
and Tuning for Performance -> Monitoring Events -> Introducing SQL Trace.
You should find it really interesting and may be a way to satisfy your
requirements
Gilberto
"Frank Wijten" wrote:
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure without
> adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
>|||"Frank Wijten" <f.wijten@.info.umcn.nl_NO_SPAM> wrote in message
news:Ox$65tQdHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I would like to know if it is possible to log the use of stored procedures
> in SQL 2000 (and later in SQL 2005)
> We want to log every time if a user makes use of a stored procedure
> without adding a entry in each Stored procedure.
> Is this possible ? if yes, how ?
> thanks,
> Frank Wijten
>
As others have pointed out, this IS possible using SQL trace or Profiler.
HOWEVER, it can potentially be very costly in terms of performance.
Just be aware.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Logging Usage (logins)?

Hi everyone!

Can anyone tell me if it's possible to set up some kind of a trigger that will write out to a file or a table and log details of all database connections?
ie: user, datetime of connection, database connected to, etc.

Any help appreciated.

Cheers,
MeganHi Meagan ,

Use SQL Profiler and run a trace you will then see which user connected to which database and at which time they did so ...and more.

Regards
Burner

Logging to sql DB using Enterprise Library

HI all,

i want to log different activities of my application like (order approved, order rejected etc) to a sql database using Enterprise Library 2.0.can anyone help me by giving a link(step by step procedure for this).or explain how to create my own sample application to log some event to DB(by creating a new database and tables for this).Thanks in advance.

regards,

Jon.

Hi 2ujohn,

I'm sorry i'm not so familiar with enterprise library 2.0. But i think if i were in your case, i would create an event on "order approved" and "order rejected", and in the event handler, i would write some recoreds to a sql database(well, how to design the database schema is another story). Or , even more simple, i would try if i can programe in sql server directly, like this: assuming there is a table named Approved Order, everytime there is new record added to this table, i would create a corresponding record in another table: RecordLoging. And i woud use trigger for that.

Hope my suggestion can help

Logging to second disk -- what happens when it crashes?

Currently we have SQL Server running on a single RAID5 array.
The data and the logs are all written to this array. We don't
have huge volume of activity, but it is growing. Most activity
is from our website that interacts a lot with the database.

We have read that it is a good idea to separate the data files
from the log files, having each on separate disks (or in the
case of RAID, separate arrays). So, we are considering adding
a separate SCSI hard drive, or maybe a RAID 1 system, and have
sql server log to that instead.

My question is, what happens if that secondary drive or array
completely fails. Let's say it melts. Assuming the OS doesn't
crash (Windows 2003 Server), what will SQL Server 2000 do when
the drive stops working and it is trying to write its data
logging to it? Will it continue to function and write alert
events? Will it shutdown? If it shuts down, how do we alter its
configuration to tell it to use another drive for the logs?

Also, what kind of throughput would we need to be doing to see
any kind of benefit to making this change? It is easier to
justify the expense of another disk or RAID setup if it is
actually going to matter. ;-)

Thanks all!

Thomas"Thomas" <thomas-ggl-02@.data.iatn.net> wrote in message
news:a9081691.0406021521.54c0f399@.posting.google.c om...
> Currently we have SQL Server running on a single RAID5 array.
> The data and the logs are all written to this array. We don't
> have huge volume of activity, but it is growing. Most activity
> is from our website that interacts a lot with the database.
> We have read that it is a good idea to separate the data files
> from the log files, having each on separate disks (or in the
> case of RAID, separate arrays). So, we are considering adding
> a separate SCSI hard drive, or maybe a RAID 1 system, and have
> sql server log to that instead.

RAID 1 at the least. Don't use a single drive in this case.

> My question is, what happens if that secondary drive or array
> completely fails. Let's say it melts. Assuming the OS doesn't
> crash (Windows 2003 Server), what will SQL Server 2000 do when
> the drive stops working and it is trying to write its data
> logging to it? Will it continue to function and write alert
> events? Will it shutdown? If it shuts down, how do we alter its
> configuration to tell it to use another drive for the logs?

It will stop running. It can't write anything. Just the same as if you
lost your data drive.

At that point you pull out your disaster recovery plan and go from here.

We've had a few cases where either the log or data disks went off-line and
it was just a matter of bringing them back online through the RAID
controller interface. SQL 2000 picked up where it left off.

> Also, what kind of throughput would we need to be doing to see
> any kind of benefit to making this change? It is easier to
> justify the expense of another disk or RAID setup if it is
> actually going to matter. ;-)
> Thanks all!
> Thomas|||"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<kStvc.34031$j24.13232@.twister.nyroc.rr.com>...
> "Thomas" <thomas-ggl-02@.data.iatn.net> wrote in message
> news:a9081691.0406021521.54c0f399@.posting.google.c om...
> > My question is, what happens if that secondary drive or array
> > completely fails. Let's say it melts. Assuming the OS doesn't
> > crash (Windows 2003 Server), what will SQL Server 2000 do when
> > the drive stops working and it is trying to write its data
> > logging to it? Will it continue to function and write alert
> > events? Will it shutdown? If it shuts down, how do we alter its
> > configuration to tell it to use another drive for the logs?
> It will stop running. It can't write anything. Just the same as if you
> lost your data drive.
> At that point you pull out your disaster recovery plan and go from here.
> We've had a few cases where either the log or data disks went off-line and
> it was just a matter of bringing them back online through the RAID
> controller interface. SQL 2000 picked up where it left off.

Let's imagine that this new RAID 1 for the logging goes down hard,
controller malfunction or what have you, and it will take a few days
to be repaired. In the meantime our data drive is working fine, and
we want to move the logging back to that array. You mentioned that
SQL Server would "stop running." If it isn't running, how would we
be able to update its configuration to change the location of the
logfiles?

Thanks for your feedback and advice!

Regards,
Thomas|||"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0406030702.4308cd27@.posting.google.c om...
> "Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in
message news:<kStvc.34031$j24.13232@.twister.nyroc.rr.com>...
> > "Thomas" <thomas-ggl-02@.data.iatn.net> wrote in message
> > news:a9081691.0406021521.54c0f399@.posting.google.c om...
> > > My question is, what happens if that secondary drive or array
> > > completely fails. Let's say it melts. Assuming the OS doesn't
> > > crash (Windows 2003 Server), what will SQL Server 2000 do when
> > > the drive stops working and it is trying to write its data
> > > logging to it? Will it continue to function and write alert
> > > events? Will it shutdown? If it shuts down, how do we alter its
> > > configuration to tell it to use another drive for the logs?
> > It will stop running. It can't write anything. Just the same as if you
> > lost your data drive.
> > At that point you pull out your disaster recovery plan and go from here.
> > We've had a few cases where either the log or data disks went off-line
and
> > it was just a matter of bringing them back online through the RAID
> > controller interface. SQL 2000 picked up where it left off.
> Let's imagine that this new RAID 1 for the logging goes down hard,
> controller malfunction or what have you, and it will take a few days
> to be repaired. In the meantime our data drive is working fine, and
> we want to move the logging back to that array. You mentioned that
> SQL Server would "stop running." If it isn't running, how would we
> be able to update its configuration to change the location of the
> logfiles?

Well, first of all, if you lose your log device, you have to do a restore
from backup in any case. At that point simply restore the backup to a
different device using the MOVE option.

Now, if your master DB and other system DBs are on the failed device, you
need to startup SQL server manually and specify on the command line where
they are.

MS has multiple KB articles on this.

Turn the question around... what happens if the RAID device with your DATA
fails? (ironically in some ways this can be EASIER to recover from assuming
you have good backups.)

> Thanks for your feedback and advice!
> Regards,
> Thomas

Logging to Event Viewer "fails" but SQL Server logging works OK - why?

Greetings,

I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.

Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.

Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?

Thanks,

BCB

The answer is no - SSIS won't write to the Windows event log with one Dts.Log call. Writing to the event log takes a little more doing. The following code shows a method I used to do logging to the Windows event log. "rowsInserted" is a parameter that is specific to our business requirements. It can be omitted for most uses.

Public Sub WriteEventLogEntry(ByVal logMessage As String, ByVal entryType As EventLogEntryType, ByVal rowsInserted As Integer)

Try

Dim source As String

Dim log As String

Dim machine As String

' get system variables recorded in event log

Dim vars As Variables

Dts.VariableDispenser.LockOneForRead("System:ackageName", vars)

source = vars("PackageName").Value.ToString()

Dts.VariableDispenser.LockOneForRead("System::MachineName", vars)

machine = vars("MachineName").Value.ToString()

' log in Application group

log = "Application"

If Not EventLog.SourceExists(source, machine) Then

EventLog.CreateEventSource(source, log, machine)

End If

Dim eLog As New EventLog(log, machine, source)

If (rowsInserted >= 0) Then

eLog.WriteEntry(logMessage, entryType, rowsInserted)

Else

eLog.WriteEntry(logMessage, entryType) ' log does not allow negative numbers

End If

Catch ex As Exception

' do nothing - let processing continue

End Try

End Sub

Logging thousands of errors

I'm receiving these two errors again and again:

Windows cannot load extensible counter DLL MSSQL$MSFW, the first DWORD in data section is the Windows error code.

Windows cannot load extensible counter DLL MSSQL$SBSMONITORING, the first DWORD in data section is the Windows error code.

What might be generating them and how can I cure it?

The system is SBS 2003 R2 Premium.

Thanks.

Where are you seeing these errors?|||I am also getting thousands of these errors in the application log of 3 SBS machines.

They are all running SBS 2k3 sp1, at least 1 of them is R2 version of 2k3.
|||According to the KB article below, it is a permissions issue:

"To resolve this problem, you must grant the Read & Execute permission to the Network Service account on the performance counter DLL for SQL Server 2005 Analysis Services.

http://support.microsoft.com/kb/912399|||

The 912399 article says:

RESOLUTION

To resolve this problem, you must grant the Read & Execute permission to the Network Service account on the performance counter DLL for SQL Server 2005 Analysis Services. The DLL is located in the following folder:

C:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\Bin\Msmdctr90.dll

Additionally, you must grant Read permissions to the Network Service account on the "bin" directory and the "bin\en" directory for SQL Server 2005 Analysis Services. For example:

? C:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin ? C:\Program Files\Microsoft SQL Server\MSSQL.x\OLAP\bin\en

I didn't have those specific directories, but I gave Network Service permissions for any similarly named directories and it fixed the problem. Thanks

Any idea what created the problem to begin with?

|||

I have this problem for an earlier MSSQL server version which does not have the same structure. does anyone know the method for this?

logging the volume of data loaded other the network?

Hi,
how can I know or log the volume loaded by my DTS packages?
I need to evaluate the traffic and the bandwidth required.
Does the profiler can provide enough info?
For the moment I work on a copy of the production database. so all is local
on my server.
Thanks for your help
Jerome.
A couple of places. In the package properties, enable package logging. This
will give you counts and times it took to execute various tasks within the
package.
Also in package properties, there is an option to provide an output file for
execution details. Then, also in the same set of dialogs, you can choose to
have the package to log in the servers event logs.
Hope this helps.
Sincerely,
Anthony Thomas
"Jéjé" wrote:

> Hi,
> how can I know or log the volume loaded by my DTS packages?
> I need to evaluate the traffic and the bandwidth required.
> Does the profiler can provide enough info?
> For the moment I work on a copy of the production database. so all is local
> on my server.
> Thanks for your help
> Jerome.
>
>
|||but does these option display the amount of data?
in Mb, not in rows!
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:385122A2-440F-483D-B92D-60BCC3D05CE8@.microsoft.com...[vbcol=seagreen]
>A couple of places. In the package properties, enable package logging.
>This
> will give you counts and times it took to execute various tasks within the
> package.
> Also in package properties, there is an option to provide an output file
> for
> execution details. Then, also in the same set of dialogs, you can choose
> to
> have the package to log in the servers event logs.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> "Jj" wrote:
|||Jj,
You might want to look into a network monitoring tool, there's nothing
built-in that does it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jj wrote:
> Hi,
> how can I know or log the volume loaded by my DTS packages?
> I need to evaluate the traffic and the bandwidth required.
> Does the profiler can provide enough info?
> For the moment I work on a copy of the production database. so all is local
> on my server.
> Thanks for your help
> Jerome.
>
|||but my actual problem is:
all is on 1 server!
so I can't monitor any network activity, because there is no network
activity for the moment.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:u3K1ojmxEHA.1296@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Jj,
> You might want to look into a network monitoring tool, there's nothing
> built-in that does it.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Jj wrote:
|||If you want to monitor network activity, bytes, etc you may
want to look at using a Network sniffer tool or PerfMon and
the Network Interface object.
-Sue
On Tue, 9 Nov 2004 09:20:12 -0500, "Jj"
<willgart_A_@.hotmail_A_.com> wrote:

>but does these option display the amount of data?
>in Mb, not in rows!
>"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
>news:385122A2-440F-483D-B92D-60BCC3D05CE8@.microsoft.com...
>
|||There are still the performance counters, server as well as SQL Server that
will give I/O transferred, pages manipulated, etc. Also, the Profiler tool,
which can focus on that application/database/user/etc., will record CPU time,
and Read I/O and Write I/O. Sorry, it is only in bytes but I think you can
do the conversion.
Moreover, you can automate the launch of background trace with the use of
the system stored procedures and have the output loaded to a SQL Server table.
Sincerely,
Anthony Thomas
"Jéjé" wrote:

> but my actual problem is:
> all is on 1 server!
> so I can't monitor any network activity, because there is no network
> activity for the moment.
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:u3K1ojmxEHA.1296@.TK2MSFTNGP10.phx.gbl...
>

logging the volume of data loaded other the network?

Hi,
how can I know or log the volume loaded by my DTS packages?
I need to evaluate the traffic and the bandwidth required.
Does the profiler can provide enough info?
For the moment I work on a copy of the production database. so all is local
on my server.
Thanks for your help
Jerome.A couple of places. In the package properties, enable package logging. Thi
s
will give you counts and times it took to execute various tasks within the
package.
Also in package properties, there is an option to provide an output file for
execution details. Then, also in the same set of dialogs, you can choose to
have the package to log in the servers event logs.
Hope this helps.
Sincerely,
Anthony Thomas
"Jéjé" wrote:

> Hi,
> how can I know or log the volume loaded by my DTS packages?
> I need to evaluate the traffic and the bandwidth required.
> Does the profiler can provide enough info?
> For the moment I work on a copy of the production database. so all is loca
l
> on my server.
> Thanks for your help
> Jerome.
>
>|||but does these option display the amount of data?
in Mb, not in rows!
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:385122A2-440F-483D-B92D-60BCC3D05CE8@.microsoft.com...[vbcol=seagreen]
>A couple of places. In the package properties, enable package logging.
>This
> will give you counts and times it took to execute various tasks within the
> package.
> Also in package properties, there is an option to provide an output file
> for
> execution details. Then, also in the same set of dialogs, you can choose
> to
> have the package to log in the servers event logs.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> "Jj" wrote:
>|||Jj,
You might want to look into a network monitoring tool, there's nothing
built-in that does it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jj wrote:
> Hi,
> how can I know or log the volume loaded by my DTS packages?
> I need to evaluate the traffic and the bandwidth required.
> Does the profiler can provide enough info?
> For the moment I work on a copy of the production database. so all is loca
l
> on my server.
> Thanks for your help
> Jerome.
>|||but my actual problem is:
all is on 1 server!
so I can't monitor any network activity, because there is no network
activity for the moment.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:u3K1ojmxEHA.1296@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Jj,
> You might want to look into a network monitoring tool, there's nothing
> built-in that does it.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Jj wrote:|||If you want to monitor network activity, bytes, etc you may
want to look at using a Network sniffer tool or PerfMon and
the Network Interface object.
-Sue
On Tue, 9 Nov 2004 09:20:12 -0500, "Jj"
<willgart_A_@.hotmail_A_.com> wrote:

>but does these option display the amount of data?
>in Mb, not in rows!
>"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
>news:385122A2-440F-483D-B92D-60BCC3D05CE8@.microsoft.com...
>|||There are still the performance counters, server as well as SQL Server that
will give I/O transferred, pages manipulated, etc. Also, the Profiler tool,
which can focus on that application/database/user/etc., will record CPU time
,
and Read I/O and Write I/O. Sorry, it is only in bytes but I think you can
do the conversion.
Moreover, you can automate the launch of background trace with the use of
the system stored procedures and have the output loaded to a SQL Server tabl
e.
Sincerely,
Anthony Thomas
"Jéjé" wrote:

> but my actual problem is:
> all is on 1 server!
> so I can't monitor any network activity, because there is no network
> activity for the moment.
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:u3K1ojmxEHA.1296@.TK2MSFTNGP10.phx.gbl...
>