Reading .LDF

Dear all,
How to identity all those changes done in a db for any user in a specific
period of time?
I am not talking about put triggers in each object or something like that
(on the other hand such actions never will reach exhaustely all the actions
commited)
For example:
developer1 modify a view at 16:00 and then developer2 delete it.
Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
level (hexadecimal info)
I've been able to to order theses scenarios (values as follows belong to
field 'OPERATION':
0)AFTER TOTAL SHRINK (O KB)
LOP_BEGIN_CKPT
LOP_END_CKPT
LOP_FILE_HDR_MODIFY
1)FOR INSERT STATEMENT:
LOP_BEGIN_XACT
LOP_INSERT_ROWS
LOP_COMMIT_XACT
2)FOR UPDATE STATEMENT:
LOP_BEGIN_XACT
LOP_MODIFY_ROW
LOP_COMMIT_XACT
3)FOR CREATE OBJECT STATEMENT:
LOP_BEGIN_XACT
LOP_INSERT_ROWS
LOP_MARK_DDL
It seems very tricky but I suppose that there would be some method for
obtain such information. How can I do this?
Perhaps, it's a useless madness, obsessive.
--
Current location: Alicante (ES)Enric
Visit at
http://www.lumigent.com/ --explorer log for sql
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:A38C627F-9C4C-44B6-B012-F94BA91DBFB5@.microsoft.com...
> Dear all,
> How to identity all those changes done in a db for any user in a specific
> period of time?
> I am not talking about put triggers in each object or something like that
> (on the other hand such actions never will reach exhaustely all the
> actions
> commited)
> For example:
> developer1 modify a view at 16:00 and then developer2 delete it.
> Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
> level (hexadecimal info)
> I've been able to to order theses scenarios (values as follows belong to
> field 'OPERATION':
> 0)AFTER TOTAL SHRINK (O KB)
> LOP_BEGIN_CKPT
> LOP_END_CKPT
> LOP_FILE_HDR_MODIFY
> 1)FOR INSERT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_COMMIT_XACT
> 2)FOR UPDATE STATEMENT:
> LOP_BEGIN_XACT
> LOP_MODIFY_ROW
> LOP_COMMIT_XACT
>
> 3)FOR CREATE OBJECT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_MARK_DDL
>
> It seems very tricky but I suppose that there would be some method for
> obtain such information. How can I do this?
> Perhaps, it's a useless madness, obsessive.
> --
> Current location: Alicante (ES)|||> How to identity all those changes done in a db for any user in a specific
> period of time?
In hindsight:
http://www.aspfaq.com/2449
Moving forward:
http://www.aspfaq.com/2496|||Thanks a lot to both
--
Current location: Alicante (ES)
"Aaron Bertrand [SQL Server MVP]" wrote:

> In hindsight:
> http://www.aspfaq.com/2449
> Moving forward:
> http://www.aspfaq.com/2496
>
>|||There are 3rd party tools for mining information from the transaction log.
However, the intended purpose of this log is for maintaining transactions,
not auditing, so you may find it limiting if your intent is monitoring user
activity.
With a little studying and configuration, you can trace most any desired
event using SQL Server Profiler, which is a tool installed with SQL Server.
Depending on what event categories you choose, you can trace not just data
or object modifications but also things like SP executions and even table
selects. It is basically a service that runs on server, and it's output can
be archived to a file or table. You will need admin rights to use this tool.
http://msdn.microsoft.com/library/d...>
erf_86ib.asp
[url]http://www.sqlservercentral.com/columnists/bkelley/auditingwithsqlprofiler.asp[/ur
l]
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:A38C627F-9C4C-44B6-B012-F94BA91DBFB5@.microsoft.com...
> Dear all,
> How to identity all those changes done in a db for any user in a specific
> period of time?
> I am not talking about put triggers in each object or something like that
> (on the other hand such actions never will reach exhaustely all the
> actions
> commited)
> For example:
> developer1 modify a view at 16:00 and then developer2 delete it.
> Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
> level (hexadecimal info)
> I've been able to to order theses scenarios (values as follows belong to
> field 'OPERATION':
> 0)AFTER TOTAL SHRINK (O KB)
> LOP_BEGIN_CKPT
> LOP_END_CKPT
> LOP_FILE_HDR_MODIFY
> 1)FOR INSERT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_COMMIT_XACT
> 2)FOR UPDATE STATEMENT:
> LOP_BEGIN_XACT
> LOP_MODIFY_ROW
> LOP_COMMIT_XACT
>
> 3)FOR CREATE OBJECT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_MARK_DDL
>
> It seems very tricky but I suppose that there would be some method for
> obtain such information. How can I do this?
> Perhaps, it's a useless madness, obsessive.
> --
> Current location: Alicante (ES)

RDA with Identity column.

Hi Everyone:

I am new to Mobile programming. I am now working on a mobile project. I encounter an issue when I sync the data:

cause I can't modify the schema, so I have to use RDA instead of Merge replication on sql server 2005.

However, There is an identiy column on each table I will pull them down to local mobile database. And I will use those identity columns to connect tables. Even worse, the photo's new name will combine the photoID which is an identity column. There would an issue, if i sync data, the photoID would be same on different local mobile databases. And there would be generate same identity value when users sync data.

How can I avoid those issues? If you have any good ideas, please help me out so that I can meet the project deadline.

Thanks

James

Anyone knows how to manually handle identity management.

Cause my identity column will clash when Sync data.

James

|||

You need to call

ALTER TABLE <TableName> ALTER COLUMN <ColumnName> <ColumnDataType> IDENTITY(<Seed>,<Step>)

to set the different identity on the pulled table.

Note, only Seed and Step can be changed in the above table.

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

Hi Laxmi,

Thank you for your reply. I realize that I can use the command you gave to change the SEED range to avoid the clash. But if I change seed, when I push the data back to Remote SQL server 2005 database, should I disable the identity column on remote database, then push mobile client table data, then set identity column back on remote DB. It seems lots of work to manually handle identity column clash. Do we have any alternative way to do it, since I can't use merge replication to change the database schema, what about XML web service? I think should be same, but if we use Web Service, the performance should be down 10-15%, that's why i don't wanna use it since we choose SQL server 2005 as our database.

Your reply is really appreciated. Thanks!

James

|||

I need to point is that someone will work on remote db through classical ASP. If I change the identity Seed. It will effect mobile client?

If anybody has the same issues?

Thanks

James

|||

No need to worry about disabling the IDENTITY on server. RDA while pushing automatically does "SET IDENTITY_INSERT <TableName> ON/OFF" on SQL Server to get the same IDENTITY column values onto the server table.

Thanks,

Laxmi

|||

Thank Laxmi,

your reply does help me. I will try it out. But we have 200 hundred of Mobile clients. We have to set the identity range for each one.

Any other good ideas for solving the identity clash.

Thanks.

James

|||That is why there is Merge Replication! Why not go for that?|||

Because I can't modify the Sql server 2005 DB schema. They are using identity ID. I wish I could use Merge replication. I can save my code lines as well.

Thank you . Laxmi. You are the rock!

James

|||Same here, merge replication is not a option.

Anyone found a suitable solution for this challenge?

Thanx
|||What I have done is to set the ID range for each person. So that they won't clash.

RDA with Identity column.

Hi Everyone:

I am new to Mobile programming. I am now working on a mobile project. I encounter an issue when I sync the data:

cause I can't modify the schema, so I have to use RDA instead of Merge replication on sql server 2005.

However, There is an identiy column on each table I will pull them down to local mobile database. And I will use those identity columns to connect tables. Even worse, the photo's new name will combine the photoID which is an identity column. There would an issue, if i sync data, the photoID would be same on different local mobile databases. And there would be generate same identity value when users sync data.

How can I avoid those issues? If you have any good ideas, please help me out so that I can meet the project deadline.

Thanks

James

Anyone knows how to manually handle identity management.

Cause my identity column will clash when Sync data.

James

|||

You need to call

ALTER TABLE <TableName> ALTER COLUMN <ColumnName> <ColumnDataType> IDENTITY(<Seed>,<Step>)

to set the different identity on the pulled table.

Note, only Seed and Step can be changed in the above table.

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

Hi Laxmi,

Thank you for your reply. I realize that I can use the command you gave to change the SEED range to avoid the clash. But if I change seed, when I push the data back to Remote SQL server 2005 database, should I disable the identity column on remote database, then push mobile client table data, then set identity column back on remote DB. It seems lots of work to manually handle identity column clash. Do we have any alternative way to do it, since I can't use merge replication to change the database schema, what about XML web service? I think should be same, but if we use Web Service, the performance should be down 10-15%, that's why i don't wanna use it since we choose SQL server 2005 as our database.

Your reply is really appreciated. Thanks!

James

|||

I need to point is that someone will work on remote db through classical ASP. If I change the identity Seed. It will effect mobile client?

If anybody has the same issues?

Thanks

James

|||

No need to worry about disabling the IDENTITY on server. RDA while pushing automatically does "SET IDENTITY_INSERT <TableName> ON/OFF" on SQL Server to get the same IDENTITY column values onto the server table.

Thanks,

Laxmi

|||

Thank Laxmi,

your reply does help me. I will try it out. But we have 200 hundred of Mobile clients. We have to set the identity range for each one.

Any other good ideas for solving the identity clash.

Thanks.

James

|||That is why there is Merge Replication! Why not go for that?|||

Because I can't modify the Sql server 2005 DB schema. They are using identity ID. I wish I could use Merge replication. I can save my code lines as well.

Thank you . Laxmi. You are the rock!

James

|||Same here, merge replication is not a option.

Anyone found a suitable solution for this challenge?

Thanx|||What I have done is to set the ID range for each person. So that they won't clash.

RDA with Identity column.

Hi Everyone:

I am new to Mobile programming. I am now working on a mobile project. I encounter an issue when I sync the data:

cause I can't modify the schema, so I have to use RDA instead of Merge replication on sql server 2005.

However, There is an identiy column on each table I will pull them down to local mobile database. And I will use those identity columns to connect tables. Even worse, the photo's new name will combine the photoID which is an identity column. There would an issue, if i sync data, the photoID would be same on different local mobile databases. And there would be generate same identity value when users sync data.

How can I avoid those issues? If you have any good ideas, please help me out so that I can meet the project deadline.

Thanks

James

Anyone knows how to manually handle identity management.

Cause my identity column will clash when Sync data.

James

|||

You need to call

ALTER TABLE <TableName> ALTER COLUMN <ColumnName> <ColumnDataType> IDENTITY(<Seed>,<Step>)

to set the different identity on the pulled table.

Note, only Seed and Step can be changed in the above table.

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

Hi Laxmi,

Thank you for your reply. I realize that I can use the command you gave to change the SEED range to avoid the clash. But if I change seed, when I push the data back to Remote SQL server 2005 database, should I disable the identity column on remote database, then push mobile client table data, then set identity column back on remote DB. It seems lots of work to manually handle identity column clash. Do we have any alternative way to do it, since I can't use merge replication to change the database schema, what about XML web service? I think should be same, but if we use Web Service, the performance should be down 10-15%, that's why i don't wanna use it since we choose SQL server 2005 as our database.

Your reply is really appreciated. Thanks!

James

|||

I need to point is that someone will work on remote db through classical ASP. If I change the identity Seed. It will effect mobile client?

If anybody has the same issues?

Thanks

James

|||

No need to worry about disabling the IDENTITY on server. RDA while pushing automatically does "SET IDENTITY_INSERT <TableName> ON/OFF" on SQL Server to get the same IDENTITY column values onto the server table.

Thanks,

Laxmi

|||

Thank Laxmi,

your reply does help me. I will try it out. But we have 200 hundred of Mobile clients. We have to set the identity range for each one.

Any other good ideas for solving the identity clash.

Thanks.

James

|||That is why there is Merge Replication! Why not go for that?|||

Because I can't modify the Sql server 2005 DB schema. They are using identity ID. I wish I could use Merge replication. I can save my code lines as well.

Thank you . Laxmi. You are the rock!

James

|||Same here, merge replication is not a option.

Anyone found a suitable solution for this challenge?

Thanx|||What I have done is to set the ID range for each person. So that they won't clash.

RDA Push with Identity column

Hi Everyone:

I am new to Mobile programming. I am now working on a small mobile app.

I encounter an issue when I sync the data:

Using RDA Pull, I can create the database and populate my table fine on my pocket pc device.

After updating the data on the device, I encounter an error when I try to Push my table back to the back-end SQL Server 2005 DB.

Error: "The identity range was not established."

VB CODE:
Private Sub RdaConnKeyHeaderPush()

Dim RemoteAccess As New Data.SqlServerCe.SqlCeRemoteDataAccess
Dim RDAConnectingString As String
RemoteAccess.InternetLogin = "<mylogin>"
RemoteAccess.InternetPassword = "<mypassword>"
RDAConnectingString = "Provider=SQLOLEDB;Persist Security Info=True;Data Source=<MySQLServer>;Initial Catalog=MobileKeyDB;" & _
"User ID=MobileUser;Password=<mypassword>"
RemoteAccess.InternetUrl = "https://www.<mysite>.com/SQLMobile/sqlcesa30.dll"
RemoteAccess.LocalConnectionString = "Data Source=Program Files\SQL Mobile\en\MobileKeyDB.sdf; Password = <mypassword>"
Cursor.Current = Cursors.WaitCursor
Try

RemoteAccess.Push("KeyHeader", RDAConnectingString, Data.SqlServerCe.RdaBatchOption.BatchingOn)

Catch RDAConnectionException As Exception
MessageBox.Show("Can not push Header Data: " & RDAConnectionException.ToString, "Loading Key Tracker")
Finally

RemoteAccess.Dispose()
End Try
Cursor.Current = Cursors.Default
End Sub

TABLE DEFINITION:
USE [MobileKeyDB]
GO
/****** Object: Table [dbo].[KeyHeader] Script Date: 07/11/2007 09:48:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KeyHeader](
[trans_id] [int] IDENTITY(1,1) NOT NULL,
[user_id] [int] NOT NULL,
[date_stamp] [datetime] NOT NULL,
[signature] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[status] [int] NOT NULL,
[id] [int] NOT NULL,
[date_stamp2] [datetime] NOT NULL,
CONSTRAINT [PK_KeyHeader] PRIMARY KEY CLUSTERED
(
[trans_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Anyone have any insight on a solution? Thanks.

For synch scenarios, you should always avoid identity columns, for an elaboration, see http://blogs.msdn.com/synchronizer/archive/2007/06/28/stay-away-from-auto-increment-columns.aspx|||

Ok....then I will try using the unique identifier.

My problem is here that I am unsure of how to add the guid to my insert statement in my application.

I have a dataset created that has an Insert menthod for a my table.

How do I set the properties of this Insert statement to use a GUID in it's execution?

Thanks.

|||Modify your insert command to use NEWID(), see http://msdn2.microsoft.com/en-us/library/ms173249.aspx|||

Ok...thanks for your help.

I tried inserting the NEWID() method into my sql insert statement, however when I run my application in debug mode,

I get an Null Value Exception on that UniqueIdentifier field....what would the best way to identify where and why this is happening?

The error occurs when I try to close the form by clicking 'ok' after entering a record......

Error....

NoNullAllowedException was unhandled

Column 'ID' does not allow nulls.

Code Point where error is generated....

Private Sub KeysEditViewDialog_Closing(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing

Me.KeysBindingSource.EndEdit()

End Sub

Some Dataset Code.....is the null error on the sql side or app side, I am not sure of this?

Me._adapter.InsertCommand = New System.Data.SqlServerCe.SqlCeCommand

Me._adapter.InsertCommand.Connection = Me.Connection

Me._adapter.InsertCommand.CommandText = "INSERT INTO Keys"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&" (key_number, key_desc, building, inv_qty,"& _

" avail_qty, id)"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&"VALUES (@.p1, @.p2, @.p3, @.p4, @.p5, NEWID())"

Me._adapter.InsertCommand.CommandType = System.Data.CommandType.Text

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p1"

param.Size = 100

param.IsNullable = true

param.SourceColumn = "key_number"

Me._adapter.InsertCommand.Parameters.Add(param)

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p2"

param.Size = 100

param.IsNullable = true

param.SourceColumn = "key_desc"

Me._adapter.InsertCommand.Parameters.Add(param)

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p3"

param.Size = 100

param.IsNullable = true

param.SourceColumn = "building"

Me._adapter.InsertCommand.Parameters.Add(param)

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p4"

param.DbType = System.Data.DbType.Int32

param.Size = 4

param.IsNullable = true

param.SourceColumn = "inv_qty"

Me._adapter.InsertCommand.Parameters.Add(param)

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p5"

param.Size = 100

param.IsNullable = true

param.SourceColumn = "avail_qty"

Me._adapter.InsertCommand.Parameters.Add(param)

RDA Push Command Failing ...

Hi,

I am using the Pull command to pull two fields, on is the primary ID (int) non identity and the other is Description which comes down as an ntext type. This works fine but if I change the description and use the push command I get the following error:-

The Query processor could not produce a query from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

I am really stuck with this one so if anyone can shed some light on it I would be much appreciated.

Cheers,

Jiggy!

Hi All,

I have found the problem. Basically I am intergrating with another desktop / web based application making a PDA version so the SQL Server Database is already in place. I have found that each table has a cluster index on the primary key for performance issues. Can someone please advice if there is a fix for this or is the Push command not compatible with Clustered Index's?

Cheers very much,

Jiggy!

|||Just so you do not feel alone in the world... and MAYBE point you in a useful direction...

This has to do with the parameters being set by the connection, most likely. The way to test this is to run the same SQL statement in Query Analyzer and see if it works, if it does, then you need to attempt to set different paramters on your connection and/or command.

Hope this helps a little, if you find the solution, and/or paramters to pass, I'd love to hear about it.

-Joshua

RDA Push Command Failing ...

Hi,

I am using the Pull command to pull two fields, on is the primary ID (int) non identity and the other is Description which comes down as an ntext type. This works fine but if I change the description and use the push command I get the following error:-

The Query processor could not produce a query from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

I am really stuck with this one so if anyone can shed some light on it I would be much appreciated.

Cheers,

Jiggy!

Hi All,

I have found the problem. Basically I am intergrating with another desktop / web based application making a PDA version so the SQL Server Database is already in place. I have found that each table has a cluster index on the primary key for performance issues. Can someone please advice if there is a fix for this or is the Push command not compatible with Clustered Index's?

Cheers very much,

Jiggy!

|||Just so you do not feel alone in the world... and MAYBE point you in a useful direction...

This has to do with the parameters being set by the connection, most likely. The way to test this is to run the same SQL statement in Query Analyzer and see if it works, if it does, then you need to attempt to set different paramters on your connection and/or command.

Hope this helps a little, if you find the solution, and/or paramters to pass, I'd love to hear about it.

-Joshua