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)

Reaading and Processing a Recordset from Com object

Hi everybody.

I need help with the next topic.

From a stored procedure in sqlserver, I need to call a com+ dll, this dll connect ot another database diferent to sqlserver y return this dll must return to stored procedure a recordser for ther processing.

I tried with sp_OACreate, sp_OAMethod, sp_OAGetProperty but I did not know how to process a recordset

Thanks

Erik

You can return the recordset data as a multi-dimensional array and this will be streamed to the client. You can use INSERT...EXEC on the server-side to capture the results into a temporary table for example. You can't return the recordset object directly since SQL Server will not know how to process it. See sp_OAMethod topic in BOL for more details.

rdlc show querystring parameter in page header

I pass in 3 querystring parameters to my web form. The Object Data Sources pick up these parameters

and select the appropriate records.

I want to display one of the querystring parameters in my Page Header, specifically the one for Fiscal Year.

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned...Therefore, I must get the querystring parameter that was originally passed in...

How do I populate the report control textbox with the value of querystring parameter?

Thanks!

Jim

JIM_LANGDON wrote:

I pass in 3 querystring parameters to my web form. The Object Data Sources pick up these parameters

and select the appropriate records.

I want to display one of the querystring parameters in my Page Header, specifically the one for Fiscal Year.

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned...Therefore, I must get the querystring parameter that was originally passed in...

How do I populate the report control textbox with the value of querystring parameter?

Thanks!

Jim

Are you using a report generator?

If not, then isn't this is as simple as setting the expression of the textbox to:

=Fields!FiscalYear.Value

|||

I don't know what you mean by "Report Generator"..isn't it SSRS?

I pass in the parameter via the call to the webform that contains the

reportviewer control -- webform.aspx?fy=2007.

The reportviewer control that is bound to the report.rdlc.

The RDLC has several datasources which show up as objectdatasource controls

on the web form. The objectdatasource controls are configured to use the appropriate

datasource method and the parameters are defined to use the querystring and "fy" as

the parameter name.

Doesn't the Fields! collection just have the datasource columns? I tried what you suggested

Fields!fy.Value, since the name in querystring is "fy"...no success.

|||

JIM_LANGDON wrote:

I tried what you suggested

Fields!fy.Value, since the name in querystring is "fy"...no success.

This is probably because you need to specify the dataset on the textbox. Right click the textbox and select properties. In the "containing group or dataset" textbox, you need to either select or enter the dataset that contains 'fy'.

Basically, you can treat a textbox similarly to a table/matrix. It can have a dataset associated with it and you can populate the textbox with any value from the parameters or fields list.

|||

But the querystring does not belong to a "dataset"....so I can't reference it with Fields!fieldname. Hmmm.

|||

Isn't fiscal year one of your fields in a dataset? Aren't you trying to get fiscal year in a textbox?

Why do you want to touch a querystring?

|||

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned...Therefore, I must get the querystring parameter that was originally passed in...

The goal is to display a specific querystring parameter in the page header of the report.

|||

JIM_LANGDON wrote:

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned

Yes, and you could conditionally specify what the Fiscal Year should default to if no records are returned.

If you'd like to pursue the querystring path to resolve this, feel free. I'm just somewhat confused with your reasoning.

|||

The Fiscal Year that is passed to the web form is 2006. If no Fiscal Year is passed, it will default to 2007. The records selected are based on the year that is passed in.

If I pass in the year 2006, I want the page header to display "Fiscal Year: 2006" and the body to display "No records found."

It seems clear to me that the querystring is the only place to logically capture the Fiscal Year. Is there another method you had in mind to meet the goal or another way to do things to accomplish the same thing?

|||

JIM_LANGDON wrote:

It seems clear to me that the querystring is the only place to logically capture the Fiscal Year. Is there another method you had in mind to meet the goal or another way to do things to accomplish the same thing?

Maybe I just don't know what you mean by "querystring".

I think everything that you have mentioned so far can be accomplished by using a dataset (as either text or stored procedure).

|||

Sounds like what you have to do is one of the following:

add a new parameter to the report (with no valid values) and set the value of this parameter from the querystring. In your report textbox use Parameters!<your param name>.value to display the value

OR

create a new dataset in your report and load the querystring parameters into it in code, pass this additional dataset to the report

|||

The report page is initiated by a link <a>, html anchor tag.

<a href="report_web_form.aspx?fy=2006">Run This Report</a>

The href property assignment is what I mean by "querystring".

The dataset that is generated uses the 2006 value to determine what

records to select. If no records are returned, you cannot use the dataset to

get the value 2006. I don't see how this can be accomplished using a dataset.

|||

Thanks Adam..

From your first solution how do I "set the value of this parameter from the querystring". Where is that done. Are we talking about an assignment in the page load event of the report web form?

From your second solution how do I "load the querystring parameters into it in code"?

Sorry, I just need a bit more explanation.

|||

OK, here we go. The solution. Thanks to the exhausting yet always productive discussion today.

1. Define a report parameter in the report.rdlc:

Name: FISCAL_YEAR

Data Type: String

"Hidden" checkbox checked.

Default Values: "null" checkbox checked.

2. Place a text box in the page header of the report.rdlc with the value "=parameters!FISCAL_YEAR.Value".

3. Place the following code in the report web form:

Imports Microsoft.Reporting.WebForms

Partical Class reportWebForm

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim _fiscalYearParameter As New ReportParameter("FISCAL_YEAR", "2007")

_fiscalYearParameter.Values(0) = Request.QueryString("fy")

ReportViewer.LocalReport.SetParameters(New ReportParameter() {_fiscalYearParameter})

End Sub

RDLC Object datasource can't get System.Web through webform reportviewer

I am running an RDLC with a object datasource. In the object I try to
access System.Web.Httphandler to get the session, but it is nothing.
The reportviewer running my rdlc from a webform, and it seems as though
there is a setting or something to enable the object datasource access
to the session but I cannot figure it out.
Can someone help?Currently I get this error when it tries to access
System.Web.Httphandler.Current
Microsoft.Reporting.WebForms.AspNetSessionExpiredException|||lotta typos today, I am accessing:
System.Web.HttpContext.Current.Session("PhysicalWebPath")
and Current is Nothing|||This is not too surprising. The web control uses web services. I am not sure
it has a physical web path. What are you trying to determine?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <gfricke@.gmail.com> wrote in message
news:1132681439.261358.193190@.g43g2000cwa.googlegroups.com...
> lotta typos today, I am accessing:
> System.Web.HttpContext.Current.Session("PhysicalWebPath")
> and Current is Nothing
>|||Just trying to grab a session value, so if its a web service that the
web control uses, is there an EnableSession true setting I cause use in
my object datasource like I do in a standard webservice to share the
session?|||Sorry, can't help you there. I have been using the winform control.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <gfricke@.gmail.com> wrote in message
news:1132689249.764262.282850@.f14g2000cwb.googlegroups.com...
> Just trying to grab a session value, so if its a web service that the
> web control uses, is there an EnableSession true setting I cause use in
> my object datasource like I do in a standard webservice to share the
> session?
>|||On a side note, can I see these webservices? Like does it create a
asmx file or something I can connect to in IE for the object
datasources the reportviewer communicates with?|||The webservices are documented and you can use them yourself. Prior to 2005
people would (and still can) roll there own, not using the control. The
previous control (really a sample) used URL integration and so had some
difficulties.
In Books Online look for web services and you will see lots of
documentation.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <gfricke@.gmail.com> wrote in message
news:1132690648.480837.290540@.o13g2000cwo.googlegroups.com...
> On a side note, can I see these webservices? Like does it create a
> asmx file or something I can connect to in IE for the object
> datasources the reportviewer communicates with?
>