Record Locks

Saturday, February 25, 2012

Records Versioning/Auditing

Howdy,
Forgive me if this is the wrong group. I'm writing a ASP.Net application and
i need to track every change to a table's records. In SQL 2005, is there a
way to easily keep a record change history? Something with rules or triggers
or something? Or am I stuck comparing every field manually before i perform
the UPDATE?
Thanks a lot!
--
David Lozzi
Delphi Technology Solutions
Blog: www.lozzi.netA trigger can do what you need (i.e. putting a changed record into an audit
table prior to or after an update).
I recommend checking out ApexSQL's Audit. It can set up everything for you
with a mostly point-and-click interface.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
news:136C1D21-F4AD-4F15-B193-732576A972EA@.microsoft.com...
> Howdy,
> Forgive me if this is the wrong group. I'm writing a ASP.Net application
> and
> i need to track every change to a table's records. In SQL 2005, is there a
> way to easily keep a record change history? Something with rules or
> triggers
> or something? Or am I stuck comparing every field manually before i
> perform
> the UPDATE?
> Thanks a lot!
> --
> David Lozzi
> Delphi Technology Solutions
> Blog: www.lozzi.net|||Yeah, i saw them but at $600 per license, i'll figure it out myself.
thanks,
--
David Lozzi
Delphi Technology Solutions
Blog: www.lozzi.net
"TheSQLGuru" wrote:
> A trigger can do what you need (i.e. putting a changed record into an audit
> table prior to or after an update).
> I recommend checking out ApexSQL's Audit. It can set up everything for you
> with a mostly point-and-click interface.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
> news:136C1D21-F4AD-4F15-B193-732576A972EA@.microsoft.com...
> > Howdy,
> >
> > Forgive me if this is the wrong group. I'm writing a ASP.Net application
> > and
> > i need to track every change to a table's records. In SQL 2005, is there a
> > way to easily keep a record change history? Something with rules or
> > triggers
> > or something? Or am I stuck comparing every field manually before i
> > perform
> > the UPDATE?
> >
> > Thanks a lot!
> >
> > --
> > David Lozzi
> > Delphi Technology Solutions
> > Blog: www.lozzi.net
>
>|||$600 is a real bargain for what you get in the package. If you get paid $20
per hour that is only 30 hours before you hit the same amount. No way you
can build out a fully functional system like Audit can do for you in that
time frame.
Note too that the license is just for the client that sets up the auditing.
You can audit unlimited servers from that one purchase. See here,
http://www.apexsql.com/sql_tools_audit.asp, 4th bullet point from the top.
And they have a viewer to report on the audited information that is only $50
per client.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
news:F7CA333A-71F4-4B8A-BC90-FD23D60CF3DA@.microsoft.com...
> Yeah, i saw them but at $600 per license, i'll figure it out myself.
> thanks,
> --
> David Lozzi
> Delphi Technology Solutions
> Blog: www.lozzi.net
>
> "TheSQLGuru" wrote:
>> A trigger can do what you need (i.e. putting a changed record into an
>> audit
>> table prior to or after an update).
>> I recommend checking out ApexSQL's Audit. It can set up everything for
>> you
>> with a mostly point-and-click interface.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
>> news:136C1D21-F4AD-4F15-B193-732576A972EA@.microsoft.com...
>> > Howdy,
>> >
>> > Forgive me if this is the wrong group. I'm writing a ASP.Net
>> > application
>> > and
>> > i need to track every change to a table's records. In SQL 2005, is
>> > there a
>> > way to easily keep a record change history? Something with rules or
>> > triggers
>> > or something? Or am I stuck comparing every field manually before i
>> > perform
>> > the UPDATE?
>> >
>> > Thanks a lot!
>> >
>> > --
>> > David Lozzi
>> > Delphi Technology Solutions
>> > Blog: www.lozzi.net
>>

Records Versioning/Auditing

records per page in ReportViewer

Hi Everyone,

I can't find this to save my life! Is there a way to set the number of records that show per page on the ReportViewer? I'd like to return more per page then returns with the default setting.

thanks,

jekerry

You have to use expressions for that purpose. Have a look at this article:

http://msdn2.microsoft.com/en-us/library/ms251668(VS.80).aspx

Look for Page Breaks section in this article.

|||

Awesome!!! Thanks!

jekerry

Records per page

Hi.

I want to know how many records there are per page.

For example there may be a textbox showing the count of records per page.

What is the expression for this?

Any help would be appreciated.

I don't know if there is anything predefined in SQL for this. You may have to calculate it yourself by knowing the size of the report viewer and the size of your textbox fields.

Records number of a table

Dear Sir,

Instead of using SELECT count(*) From TABLE_NAME,

Is there any property of a Table that provide the total_records of this table?

Many Thanks

Xueliang

There's a quick way that returns a rowcount that is not necessarily 100% accurate, but that is good enough for most purposes, see below.

Chris

SELECT rows AS [RowCount]

FROM sysindexes

WHERE OBJECT_NAME(id) = '<Table Name>'

AND indid <= 1

|||

if u hv any mumeric fields ..u can chk out by diiference.. like auto id can sense this..or if product id p001 to p022 u can chk this too,

or the solutin by chris may work i hvnt tried..

|||

great job done sir..

sir, will it b common for any table?

will u plz explain syntax?

|||just wonder is there anything like row_counts_fuction(TABLE_NAME) ?

I think the SQL Server System should keep the total records number somewhere.

Thanks a lot

Xueliang|||

Chris Howarth rights. Statement in his answer doesn't count rows in table, but gets it from system table.

If you want function, you could create it:

create function row_counts_fuction(@.table_name varchar(50)) returns int

begin

declare @.result int

SELECT @.result = rows

FROM sysindexes

WHERE OBJECT_NAME(id) = @.table_name

AND indid <= 1

return @.result

end

Then:

select count(*) from Sales.SalesOrderDetail

--Returns 121317, works 0,21 sec

select dbo.row_counts_fuction('SalesOrderDetail')

--Returns 121317, works 0,00 sec

|||

>will u plz explain syntax?

Rather than me re-iterate what Microsoft say about sysindexes, let me point you towards the relevant topic in BOL - here you can see why the 'indid' column is included in the WHERE clause of the query.

http://msdn2.microsoft.com/en-us/library/ms190283.aspx

Chris

|||

If AUTO_CREATE_STATISTICS is not ON and not manually updated, then do I get Proper record

count from sysindexes ?

|||

To improve accuracy you should run DBCC UPDATEUSAGE before running the query.

See the following links for more info:

SQL Server 2000

http://msdn2.microsoft.com/en-gb/library/aa258283(sql.80).aspx

SQL Server 2005

http://msdn2.microsoft.com/en-us/library/ms188414.aspx

Apparently, according to MS, there should be no need to run the command in SQL Server 2005 as the statistics are maintained correctly.

Chris

Records not updating in Script Component

I am seeing a strange behavior in a script component that I can't figure out.

Basically, there is a field in my script that checks to see if a string field is length of 1. If it is, it updates the field with some value. The strange behavior is that it picks up some of the rows but not others.

If Row.FieldValue.Length = 1 Then

Row.FieldValue= "Some Value"

End If

I've tried variations such as:
If left(Row.FieldValue.,1) = "-" Then

Row.FieldValue= "Some Value"

End If

But I get the same results. Any Ideas?Does [Field] need to be trimmed? Might some data contain trailing white spaces?|||

No, I've tried every combination of Trim, left, equals etc...that I can think of. The funny thing is that it picks up some of the fields and misses others.

Very puzzling. I was hoping to hear of some rare condition on this board that sounds similar to this, but I have yet to hear of any similar situations.

The code that I am talking about is incredibly simple, as shown in my previous post.

|||What happens when [Field] is null?|||

You could fire some debug iformation to help you. Something like this maybe:

Me.ComponentMetadata.FireInformation(,,Row.FieldValue.ToString() + " - " + Row.FieldValue.Length.ToString(), ....

-Jamie

|||

Thanks, that did it. I am relatively new to SSIS, never really delved into the debug information before.

that is very helpful.

|||

KML67 wrote:
Thanks, that did it. I am relatively new to SSIS, never really delved into the debug information before.
that is very helpful.

Great! But what solved your issue?|||

Basically, matching the string by length, equal, explicit match was working fine all along.

There was some logic that followed this string test that was providing the error. The error was very subtle, so it did not always produce the same value for the same input (depending on multiple variables), but at quick glance (watching the data flow into and out of the component with dataviewers) it looked as like there was an inconsistency in the component itself.

Jamie's post was very helpful, I am new to SSIS and wasn't familiar with the fireInformation method. That allowed me to solve it in a second.

Thanks for the tips, this board is a great asset.

Saturday, February 25, 2012

Records Versioning/Auditing

Records Versioning/Auditing

Records Versioning/Auditing

records per page in ReportViewer

Records per page

Records number of a table

Records not updating in Script Component

Record Locks

Blog Archive

About Me