Is table updated?

How can I tell if a table is regularly updated, or the last time that it was
updated?SQL Server doesn't track usage information by default. You can use a third
party product or build the tracking in yourself. Some treatments here:
http://www.aspfaq.com/2496
http://www.aspfaq.com/2448
--
http://www.aspfaq.com/
(Reverse address to reply.)
"NeverQuit" <NeverQuit@.discussions.microsoft.com> wrote in message
news:AC4F1032-96DF-4624-93E6-8AD3988EDA9D@.microsoft.com...
> How can I tell if a table is regularly updated, or the last time that it
was
> updated?|||Thanks. Is there a way to trace DML on this table?
"Aaron [SQL Server MVP]" wrote:
> SQL Server doesn't track usage information by default. You can use a third
> party product or build the tracking in yourself. Some treatments here:
> http://www.aspfaq.com/2496
> http://www.aspfaq.com/2448
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "NeverQuit" <NeverQuit@.discussions.microsoft.com> wrote in message
> news:AC4F1032-96DF-4624-93E6-8AD3988EDA9D@.microsoft.com...
> > How can I tell if a table is regularly updated, or the last time that it
> was
> > updated?
>
>|||You mean UPDATE, INSERT, DELETE? Yes, did you look at the links I posted?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"NeverQuit" <NeverQuit@.discussions.microsoft.com> wrote in message
news:D7A04761-2125-4A4F-AF5C-9BCB47CB37EB@.microsoft.com...
> Thanks. Is there a way to trace DML on this table?
> "Aaron [SQL Server MVP]" wrote:
> > SQL Server doesn't track usage information by default. You can use a
third
> > party product or build the tracking in yourself. Some treatments here:
> >
> > http://www.aspfaq.com/2496
> > http://www.aspfaq.com/2448
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "NeverQuit" <NeverQuit@.discussions.microsoft.com> wrote in message
> > news:AC4F1032-96DF-4624-93E6-8AD3988EDA9D@.microsoft.com...
> > > How can I tell if a table is regularly updated, or the last time that
it
> > was
> > > updated?
> >
> >
> >sql

Is table updated?

How can I tell if a table is regularly updated, or the last time that it was
updated?
SQL Server doesn't track usage information by default. You can use a third
party product or build the tracking in yourself. Some treatments here:
http://www.aspfaq.com/2496
http://www.aspfaq.com/2448
http://www.aspfaq.com/
(Reverse address to reply.)
"NeverQuit" <NeverQuit@.discussions.microsoft.com> wrote in message
news:AC4F1032-96DF-4624-93E6-8AD3988EDA9D@.microsoft.com...
> How can I tell if a table is regularly updated, or the last time that it
was
> updated?
|||Thanks. Is there a way to trace DML on this table?
"Aaron [SQL Server MVP]" wrote:

> SQL Server doesn't track usage information by default. You can use a third
> party product or build the tracking in yourself. Some treatments here:
> http://www.aspfaq.com/2496
> http://www.aspfaq.com/2448
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "NeverQuit" <NeverQuit@.discussions.microsoft.com> wrote in message
> news:AC4F1032-96DF-4624-93E6-8AD3988EDA9D@.microsoft.com...
> was
>
>
|||You mean UPDATE, INSERT, DELETE? Yes, did you look at the links I posted?
http://www.aspfaq.com/
(Reverse address to reply.)
"NeverQuit" <NeverQuit@.discussions.microsoft.com> wrote in message
news:D7A04761-2125-4A4F-AF5C-9BCB47CB37EB@.microsoft.com...[vbcol=seagreen]
> Thanks. Is there a way to trace DML on this table?
> "Aaron [SQL Server MVP]" wrote:
third[vbcol=seagreen]
it[vbcol=seagreen]

Is table updated?

How can I tell if a table is regularly updated, or the last time that it was
updated?SQL Server doesn't track usage information by default. You can use a third
party product or build the tracking in yourself. Some treatments here:
http://www.aspfaq.com/2496
http://www.aspfaq.com/2448
http://www.aspfaq.com/
(Reverse address to reply.)
"NeverQuit" <NeverQuit@.discussions.microsoft.com> wrote in message
news:AC4F1032-96DF-4624-93E6-8AD3988EDA9D@.microsoft.com...
> How can I tell if a table is regularly updated, or the last time that it
was
> updated?|||Thanks. Is there a way to trace DML on this table?
"Aaron [SQL Server MVP]" wrote:

> SQL Server doesn't track usage information by default. You can use a thir
d
> party product or build the tracking in yourself. Some treatments here:
> http://www.aspfaq.com/2496
> http://www.aspfaq.com/2448
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "NeverQuit" <NeverQuit@.discussions.microsoft.com> wrote in message
> news:AC4F1032-96DF-4624-93E6-8AD3988EDA9D@.microsoft.com...
> was
>
>|||You mean UPDATE, INSERT, DELETE? Yes, did you look at the links I posted?
http://www.aspfaq.com/
(Reverse address to reply.)
"NeverQuit" <NeverQuit@.discussions.microsoft.com> wrote in message
news:D7A04761-2125-4A4F-AF5C-9BCB47CB37EB@.microsoft.com...[vbcol=seagreen]
> Thanks. Is there a way to trace DML on this table?
> "Aaron [SQL Server MVP]" wrote:
>
third[vbcol=seagreen]
it[vbcol=seagreen]

is sp_replcounters accurate ?

How does sp_replcounters keep itself updated ? I cant seem to get it
refreshing. The replicated transactions for a database is around 8 and is
not decreasing althought the log reader agent is running.
Do you use it for accurate info to track any issues between the publisher
and the distribution database ?
It is somewhat accurate if your agents are running continuously. It uses an
extended stored procedure, so its hidden as to how it actually works.
Hilary Cotter
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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uvRnZNdWFHA.2692@.TK2MSFTNGP15.phx.gbl...
> How does sp_replcounters keep itself updated ? I cant seem to get it
> refreshing. The replicated transactions for a database is around 8 and is
> not decreasing althought the log reader agent is running.
> Do you use it for accurate info to track any issues between the publisher
> and the distribution database ?
>

Is Replication the correct solution?

Hello,
Goal: Maintain a copy of two of our live databases from server A on server
B that are updated hourly. The databases on server B are intended to be read
from, not updated. Database 1 is about 500 MB, database 2 is about 3.8 GB.
Problems: 1) Transactional replication requires a primary key for each
table, not all the tables have primary keys. 2) When I try to set up
snapshot replication I get a warning message about the Identity Property will
not be transferred. I have at least 100 tables with the identity property.
3) Merge replication does not seem suited to my application.
Am I correct with what I am thinking as stated above? Can replication be
used to do what I am trying to do? If so, what would you recommend? I
appreciate any suggestions that can be offered.
IMHO replication is a good way for this.
Why don't you create PKs on the replication tables?
Thomas
On Wed, 9 Mar 2005 08:49:08 -0800, "Dave Johannson"
<DaveJohannson@.discussions.microsoft.com> wrote:

>Hello,
>Goal: Maintain a copy of two of our live databases from server A on server
>B that are updated hourly. The databases on server B are intended to be read
>from, not updated. Database 1 is about 500 MB, database 2 is about 3.8 GB.
>Problems: 1) Transactional replication requires a primary key for each
>table, not all the tables have primary keys. 2) When I try to set up
>snapshot replication I get a warning message about the Identity Property will
>not be transferred. I have at least 100 tables with the identity property.
>3) Merge replication does not seem suited to my application.
>Am I correct with what I am thinking as stated above? Can replication be
>used to do what I am trying to do? If so, what would you recommend? I
>appreciate any suggestions that can be offered.
|||I am new to replication, please explain a little about IMHO.
One of the databases in question is an ERP database, I am uncertain how the
schema update scripts from the vendor will behave if I change things on the
tables, also, I am not certain how the application will handle changes to the
tables, and lastly management is not comfortable with such modifications, so
I shy away from adding PKs to the tables that don't have them.
"Thomas Hase" wrote:

> IMHO replication is a good way for this.
> Why don't you create PKs on the replication tables?
> Thomas
>
> On Wed, 9 Mar 2005 08:49:08 -0800, "Dave Johannson"
> <DaveJohannson@.discussions.microsoft.com> wrote:
>
>
|||IMHO == In My Humble Opinion
Without PKs you can't use stanardreplication.
If you uncertain, ask your vendor about adding PKs.
If you don't add FKs to the PKs, there is IMHO no influence on
your application. It may be a little bit smaler performance.
Most databases have PKs and FKs to implement
referential integrity at database level.
On Wed, 9 Mar 2005 09:39:02 -0800, "Dave Johannson"
<DaveJohannson@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am new to replication, please explain a little about IMHO.
>One of the databases in question is an ERP database, I am uncertain how the
>schema update scripts from the vendor will behave if I change things on the
>tables, also, I am not certain how the application will handle changes to the
>tables, and lastly management is not comfortable with such modifications, so
>I shy away from adding PKs to the tables that don't have them.
>"Thomas Hase" wrote:
|||Is there a way around the problem of the identity property not being
transferred when using snapshot replication? If it is possilbe to make
snapshot replication work then I would not have to add PKs to the tables.
"Thomas Hase" wrote:

> IMHO == In My Humble Opinion
> Without PKs you can't use stanardreplication.
> If you uncertain, ask your vendor about adding PKs.
> If you don't add FKs to the PKs, there is IMHO no influence on
> your application. It may be a little bit smaler performance.
> Most databases have PKs and FKs to implement
> referential integrity at database level.
>
>
> On Wed, 9 Mar 2005 09:39:02 -0800, "Dave Johannson"
> <DaveJohannson@.discussions.microsoft.com> wrote:
>
>
|||As you are interested in having the identity property at
the subscriber, I suspect this is some sort of didaster
recovery strategy. In that case, you might want to
consider log shipping, which would better satisfy your
requirements.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Is my UPDATE statement buggy?

QA has told me that on two occassions the following sp updated all rows to
the same @.BillerID value. Could this happen? Is my TSQL buggy? This is the
last step of a lengthy data migration process. The SPs are executed by a
C#/.Net forms app.
DDL statements for all three tables follow (Payer and PayerXref exist in
seperate database on the same server). Thanks in advance.
--The SP
/*
CREATED BY: KEVIN WILLIAMS
CREATED ON: 10/24/2005
NOTES:
will set the BillerId fields in PayerDB.dbo.Payer and PayerDB.dbo.Account to
the BillerId
passed by the user.
These fields now hold the primary key of the V3 tables
*/
CREATE PROCEDURE dbo.usp_MigratePayerService_BillerId
@.CREDID BIGINT,
@.BILLERID BIGINT
AS
DECLARE @.errnum INT
SET NOCOUNT ON
----
UPDATE PayerDB.dbo.Payer
SET BillerId = @.BILLERID
FROM Migration.dbo.PayerXref X
JOIN PayerDB.dbo.Payer P
ON(X.AccountUserID = P.BillerId)
WHERE X.BillerId = @.BILLERID
----
SET @.errnum = @.@.ERROR
IF @.errnum<>0
BEGIN
GOTO BAILOUT
END
----
UPDATE PayerDB.dbo.Account
SET BillerId = @.BILLERID
FROM Migration.dbo.AccountXref X
JOIN PayerDB.dbo.Account A
ON(X.AccountID_TP3 = A.BillerId)
WHERE x.BillerId = @.BILLERID
----
SET @.errnum = @.@.ERROR
----
BAILOUT:
RETURN @.errnum
--End of SP
--Create statements for tables
CREATE TABLE dbo.Payer
(PayerId bigint IDENTITY (1, 1) NOT NULL ,
Identifier varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
BillerId bigint NOT NULL)
--other fields removed for sake of space
CREATE TABLE dbo.PayerXref
(AccountUserID bigint NOT NULL ,
PayerId bigint NULL ,
BillerId bigint NOT NULL ,
Migrated bit NOT NULL CONSTRAINT DF_PayerXref_Migrated DEFAULT (0),
ErrorDescription varchar (4000) COLLATE Latin1_General_CI_AS NULL)instead of:
UPDATE PayerDB.dbo.Payer
SET BillerId = @.BILLERID
FROM Migration.dbo.PayerXref X
JOIN PayerDB.dbo.Payer P
ON(X.AccountUserID = P.BillerId)
WHERE X.BillerId = @.BILLERID
use the alias, P:
UPDATE P
SET BillerId = @.BILLERID
FROM Migration.dbo.PayerXref X
JOIN PayerDB.dbo.Payer P
ON(X.AccountUserID = P.BillerId)
WHERE X.BillerId = @.BILLERID
kevin wrote:
> QA has told me that on two occassions the following sp updated all rows t
o
> the same @.BillerID value. Could this happen? Is my TSQL buggy? This is t
he
> last step of a lengthy data migration process. The SPs are executed by a
> C#/.Net forms app.
> DDL statements for all three tables follow (Payer and PayerXref exist in
> seperate database on the same server). Thanks in advance.
> --The SP
> /*
> CREATED BY: KEVIN WILLIAMS
> CREATED ON: 10/24/2005
> NOTES:
> will set the BillerId fields in PayerDB.dbo.Payer and PayerDB.dbo.Account
to
> the BillerId
> passed by the user.
> These fields now hold the primary key of the V3 tables
> */
> CREATE PROCEDURE dbo.usp_MigratePayerService_BillerId
> @.CREDID BIGINT,
> @.BILLERID BIGINT
> AS
> DECLARE @.errnum INT
> SET NOCOUNT ON
> ----
> UPDATE PayerDB.dbo.Payer
> SET BillerId = @.BILLERID
> FROM Migration.dbo.PayerXref X
> JOIN PayerDB.dbo.Payer P
> ON(X.AccountUserID = P.BillerId)
> WHERE X.BillerId = @.BILLERID
> ----
> SET @.errnum = @.@.ERROR
> IF @.errnum<>0
> BEGIN
> GOTO BAILOUT
> END
> ----
> UPDATE PayerDB.dbo.Account
> SET BillerId = @.BILLERID
> FROM Migration.dbo.AccountXref X
> JOIN PayerDB.dbo.Account A
> ON(X.AccountID_TP3 = A.BillerId)
> WHERE x.BillerId = @.BILLERID
> ----
> SET @.errnum = @.@.ERROR
> ----
> BAILOUT:
> RETURN @.errnum
> --End of SP
> --Create statements for tables
> CREATE TABLE dbo.Payer
> (PayerId bigint IDENTITY (1, 1) NOT NULL ,
> Identifier varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> BillerId bigint NOT NULL)
> --other fields removed for sake of space
> CREATE TABLE dbo.PayerXref
> (AccountUserID bigint NOT NULL ,
> PayerId bigint NULL ,
> BillerId bigint NOT NULL ,
> Migrated bit NOT NULL CONSTRAINT DF_PayerXref_Migrated DEFAULT (0),
> ErrorDescription varchar (4000) COLLATE Latin1_General_CI_AS NULL)
>|||Thanks for the input Dave.
Please explain how yours is different than mine... other than less typing?
Kevin
"Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:

> instead of:
> UPDATE PayerDB.dbo.Payer
> SET BillerId = @.BILLERID
> FROM Migration.dbo.PayerXref X
> JOIN PayerDB.dbo.Payer P
> ON(X.AccountUserID = P.BillerId)
> WHERE X.BillerId = @.BILLERID
> use the alias, P:
> UPDATE P
> SET BillerId = @.BILLERID
> FROM Migration.dbo.PayerXref X
> JOIN PayerDB.dbo.Payer P
> ON(X.AccountUserID = P.BillerId)
> WHERE X.BillerId = @.BILLERID
>
> kevin wrote:
>|||Sorry, it was ambiguous to me, but it after running some tests to
convince myself, it wasn't ambiguous to SQL Server.
Anyway, what's your data look like? Can you post the DDL of those two
tables? I don't see a problem with your T-SQL, per se.
-Dave
kevin wrote:
> Thanks for the input Dave.
> Please explain how yours is different than mine... other than less typing?
> Kevin
> "Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:
>|||You do know that the proprietrary UPDATE.. FROM syntax is ambigous and
not portable? First it r makes no sense in terms of the SQL language
model. A FROM clause is always suppose effectively materialize a
working table that disappears at the end of the statement. Likewise,
an alias is supposed to act as it materializes a new working table with
the data from the original table expression in it. To be consistent,
this syntax says that you have done nothing to the base table.
Sybase and some other vendors had the same syntax but with different
semantics. Worst of both worlds!
And on top of that, it is unpredictable. This is a simple example from
Adam Machanic
CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Foo VALUES ('A', 0);
INSERT INTO Foo VALUES ('B', 0);
INSERT INTO Foo VALUES ('C', 0);
CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Bar VALUES ('A', 1);
INSERT INTO Bar VALUES ('A', 2);
INSERT INTO Bar VALUES ('B', 1);
INSERT INTO Bar VALUES ('C', 1);
You run this proprietary UPDATE with a FROM clause:
UPDATE Foo
SET Foo.col_b = Bar.col_b
FROM Foo INNER JOIN Bar
ON Foo.col_a = Bar.col_a;
The result of the update cannot be determined. The value of the column
will depend upon either order of insertion, (if there are no clustered
indexes present), or on order of clustering (but only if the cluster
isn't fragmented).
What you wanted was more like this:
UPDATE Payers -- more than one?
SET biller_id = @.my_biller_id
WHERE EXISTS
(SELECT *
FROM Migration.bo.PayerXref AS X
WHERE X.account_user_id = Payers.biller_id);
UPDATE Accounts
SET biller_id = @.my_biller_id
WHERE EXISTS
(SELECT *
FROM Migration.bo.PayerXref AS X
WHERE X.account_user_id_tp3 = Payers.biller_id);
How many different names does the biller_id data element have/ At
least three names for the *same* data element, which is proof that the
design is a screwed up mess.
The DDL you did post made no sense. No keys, names like "identifier"
that is huge (the only real code I know that is that long is the IBAN),
you have biller_id in a Payers table, NULLs where they should not be,
etfc.|||--CELKO--,
Thanks for your input.

> How many different names does the biller_id data element have/ At
> least three names for the *same* data element, which is proof that the
> design is a screwed up mess.
Not sure what you exactly mean by "different names". The biller is our
customer and the payer is the biller's customer. its a one to many
relationship. Payers may have many Accounts. One-to-Many again. If you ar
e
referring to the fact that the biller_id is on both tables, I agree with you
(see #1 below).

> The DDL you did post made no sense. No keys, names like "identifier"
> that is huge (the only real code I know that is that long is the IBAN),
> you have biller_id in a Payers table, NULLs where they should not be,
> etfc.
See #3 below
1. I am simply the contractor migrating data from an old db schema to a new
one. I did not design the layout or select the object names so I have no
need to defend or explain them. Even so, what is so reprehensible about
"Identifier" as a field name and how do you "know" that there are "...NULLs
where they should not be"? Shouldn't they be where your business needs say
they should be?
2. About proprietary syntax: I am using SQL Server and not SYBASE or SAS or
MYSQL etc. This is a SQL Server forum, not an ANSI SQL forum. My employer
is 100% Microsoft pimped and a damn good Ho at that. A Ho would be a fool
not to ride in the Pimp's caddy. She paid for it!
3.The Payer table does have a primary key on the IDENTITY field. I assumed
that that was a given... but there is a saying about assuming.
CREATE TABLE [dbo].[Payer] (
[PayerId] [bigint] IDENTITY (1, 1) NOT NULL ,
[Identifier] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BillerId] [bigint] NOT NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Street1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Street2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountryCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_Payer_CountryCode] DEFAULT ('USA'),
[PostalCode] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PasswordHash] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PIN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PersistPaymentInfo] [bit] NOT NULL CONSTRAINT
[DF_Payer_PersistPaymentInfo] DEFAULT (1),
[ExpirationDate] [datetime] NULL ,
[Created] [datetime] NOT NULL CONSTRAINT [DF_Payer_Created] DEFAULT
(getdate()),
[CreatedBy] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_Payer_CreatedBy] DEFAULT ('System'),
[Modified] [datetime] NULL ,
[ModifiedBy] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF__Payer__rowguid__3D7E1B63] DEFAULT (newid()),
CONSTRAINT [PK_payer_1] PRIMARY KEY CLUSTERED
(
[PayerId]
) ON [PRIMARY]
) ON [PRIMARY]
GO