Question about Pages

I am looking into a locking issue and I want to find out the specific
rows that live on a page.
Sp_lock returns the following
page id - 1:3873267
But, I don't know of any way to view the data there or correlate it
with specific rows in a table. Is there a way to do this?1:3873267 means - datafile number 1, page number 3873267. You can try
undocummented DBCC PAGE statement to find the records on this page.
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <jbergmanster@.gmail.com> napisal w wiadomosci
news:1187028314.018378.50630@.m37g2000prh.googlegroups.com...
>I am looking into a locking issue and I want to find out the specific
> rows that live on a page.
> Sp_lock returns the following
> page id - 1:3873267
> But, I don't know of any way to view the data there or correlate it
> with specific rows in a table. Is there a way to do this?
>|||Have a look here:
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx
Andrew J. Kelly SQL MVP
<jbergmanster@.gmail.com> wrote in message
news:1187028314.018378.50630@.m37g2000prh.googlegroups.com...
>I am looking into a locking issue and I want to find out the specific
> rows that live on a page.
> Sp_lock returns the following
> page id - 1:3873267
> But, I don't know of any way to view the data there or correlate it
> with specific rows in a table. Is there a way to do this?
>|||Thank you, Andrew for that Blog article. That helped me find the
records I need. I have blogged about my testing of the locking issue
at http://jeffbergman.com/cs/blogs/csjeff/archive/2007/08/13/12.aspx
which describes a locking issue I was having with ADO.Net and the
SqlDataReader.
On Aug 13, 11:30 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> Have a look here:
> http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/62565...
> --
> Andrew J. Kelly SQL MVP
> <jbergmans...@.gmail.com> wrote in message
> news:1187028314.018378.50630@.m37g2000prh.googlegroups.com...
> >I am looking into a locking issue and I want to find out the specific
> > rows that live on a page.
> > Sp_lock returns the following
> > page id - 1:3873267
> > But, I don't know of any way to view the data there or correlate it
> > with specific rows in a table. Is there a way to do this?

Question about getting the latest identity field in a specific table

Dear All,

Suppose in the program a record is added to a table whose
primary key is a identity field. If I really want to get the lastest
value for that field after the insertion, is it the best way to use
IDENT_CURRENT() to obtain this value?

Thanks for your kind attention

Yours faithfully,
BennyI would rather use SCOPE_IDENTITY() or @.@.IDENTITY depending on the
requirements. SQL Server Books Online states that, IDENT_CURRENT is similar
to the 2000 identity functions SCOPE_IDENTITY and @.@.IDENTITY. All three
functions return last-generated identity values. However, the scope and
session on which 'last' is defined in each of these functions differ.

- IDENT_CURRENT returns the last identity value generated for a specific
table in any session and any scope.
- @.@.IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.
- SCOPE_IDENTITY returns the last identity value generated for any table in
the current session and the current scope.

--
- Anith
( Please reply to newsgroups only )|||Anith Sen (anith@.bizdatasolutions.com) writes:
> - IDENT_CURRENT returns the last identity value generated for a specific
> table in any session and any scope.

One important thing to clarify here is that IDENT_CURRENT() can be affected
by insertions by other processes, where as scope_identity and @.@.identity
cannot.

Thus, ident_current() is rarely the function you should call in application
code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Question about best practices..

Given the need to update data, is it better to have 500 procs that update a
table as needed for a specific situation or given a table like:
create table dbo.People
(
ID int identity(1,1) not null,
LastAccessed datetime null,
FirstName varchar(20) not null,
LastName varchar(30) not null,
Address1 varchar(50) not null,
Address2 varchar(50) not null,
City varchar(50) not null,
State char(2) not null
)
to have a proc like this that can be used for any update to this table:
create procedure UpdatePeople
@.ID int,
@.LastAccessed datetime=null,
@.FirstName varchar(20)=null,
@.LastName varchar(30)=null,
@.Address1 varchar(50)=null,
@.Address2 varchar(50)=nul,
@.City varchar(50)=null,
@.State char(2)=null
as
set nocount on;
update dbo.People
set LastAccessed=isnull(@.LastAccessed,LastAccessed),
FirstName=isnull(@.FirstName, FirstName),
LastName=isnull(@.LastName, LastName),
Address1=isnull(@.Address1, Address1),
Address2=isnull(@.Address2, Address2),
City=isnull(@.City, City),
State=isnull(@.State,State)
where ID=@.ID
return @.@.error
GO
Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
developers throwing hundreds of procs at me when it seems unecessary!Personally, I would prefer to have a CRUD interface that takes all the
values out, and then updates all of the values when saving.
The problem with trying to preserve a few bytes like you are doing, is that
now you can't overwrite an existing value with NULL. Yes, you can pass
Address1 = '' but is that really the same thing as NULL? I don't think so,
but it really depends on overall requirements.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23NHluITgHHA.5052@.TK2MSFTNGP05.phx.gbl...
> Given the need to update data, is it better to have 500 procs that update
> a table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
>
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAccessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
>
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
>
>|||Also, typically if you are updating a person's info, you're not updating a
single value. e.g. if someone moves, you need to update address1, address2,
city, state, zip, etc.
Do you really want to manage this set of procedures, and call them all
individually? I wouldn't:
dbo.Person_UpdateAddress1
dbo.Person_UpdateAddress2
dbo.Person_UpdateCity
dbo.Person_UpdateState
dbo.Person_UpdateZip
dbo.Person_UpdatePhone
dbo.Person_UpdateFax
...
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||On 17 Apr, 21:51, "Tim Greenwood" <tim_greenwood AT yahoo DOT com>
wrote:
> Given the need to update data, is it better to have 500 procs that update a
> table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAccessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
If you regularly need to update some small subset of columns then it
may be worth creating a separate proc for such a case. On grounds of
maintainability I would question the value of creating 500 such procs
unless it's essential to squeeze every last ounce of performance from
the database.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||That's exactly my point...no I wouldn't want to but that is exactly what I
keep getting from developers. I'd prefer CRUD interface as well but I'm
lacking in support right now. Unfortunately all developers have to pass
their code through me for verification as dba before it goes into final QA
environment.
So other than the idea of supporting only modified values the basic idea
here seems right on? IOW get rid of the whole isnull() on the updates?
The only problem we run into with that is if they've used a custom view or
proc that only queries a subset of data from multiple tables then they don't
have all the values to supply to a CRUD proc for updating...I know these
are extremely basic issues here, I'm just playing devils advocate. I have
some authority to leverage in the enforcement of these things but want to be
sure I'm coming from a best (or at least accepted) practices.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23RO3DPTgHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Also, typically if you are updating a person's info, you're not updating a
> single value. e.g. if someone moves, you need to update address1,
> address2, city, state, zip, etc.
> Do you really want to manage this set of procedures, and call them all
> individually? I wouldn't:
> dbo.Person_UpdateAddress1
> dbo.Person_UpdateAddress2
> dbo.Person_UpdateCity
> dbo.Person_UpdateState
> dbo.Person_UpdateZip
> dbo.Person_UpdatePhone
> dbo.Person_UpdateFax
> ...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>|||> So other than the idea of supporting only modified values the basic idea
> here seems right on? IOW get rid of the whole isnull() on the updates?
> The only problem we run into with that is if they've used a custom view or
> proc that only queries a subset of data from multiple tables then they
> don't have all the values to supply to a CRUD proc for updating...I know
> these are extremely basic issues here, I'm just playing devils advocate.
> I have some authority to leverage in the enforcement of these things but
> want to be sure I'm coming from a best (or at least accepted) practices.
We enforce that they get all the details from a generic _GetDetails
procedure if their intention is to update even only one of the values.
If they are just getting the data for display, then yes I could see why they
might argue that they only want a subset of the data.
But with the memory on servers these days, there is no reason why the app
can't store the whole row in memory. Or, in cases like a two-column report,
that segment of the code can just ignore the other columns.
The trade-off here is performance vs. maintenance. You need to make that
decision... we can't tell you what's best because we don't know what it will
take to convince your developers to do it your way, and we don't know what
the performance threshold is (e.g. when does pulling/updating a subset
really change the way the app behaves).
A|||I have developed a WhichFieldsUsed mechanism that enables a single sproc to
only update the field or fields in a table that the caller wishes to have
updated, and then only if they are different from the existing values. Drop
me an email if you are interested. kgboles a t earth link d o t net.
--
TheSQLGuru
President
Indicium Resources, Inc.|||I already know *how* to do that. My question wasn't so much how to do it as
what is the most accepted practice.
Thanks!
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23eMdKFfgHHA.5044@.TK2MSFTNGP05.phx.gbl...
>I have developed a WhichFieldsUsed mechanism that enables a single sproc to
>only update the field or fields in a table that the caller wishes to have
>updated, and then only if they are different from the existing values.
>Drop me an email if you are interested. kgboles a t earth link d o t net.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>

Question about best practices..

Given the need to update data, is it better to have 500 procs that update a
table as needed for a specific situation or given a table like:
create table dbo.People
(
ID int identity(1,1) not null,
LastAccessed datetime null,
FirstName varchar(20) not null,
LastName varchar(30) not null,
Address1 varchar(50) not null,
Address2 varchar(50) not null,
City varchar(50) not null,
State char(2) not null
)
to have a proc like this that can be used for any update to this table:
create procedure UpdatePeople
@.ID int,
@.LastAccessed datetime=null,
@.FirstName varchar(20)=null,
@.LastName varchar(30)=null,
@.Address1 varchar(50)=null,
@.Address2 varchar(50)=nul,
@.City varchar(50)=null,
@.State char(2)=null
as
set nocount on;
update dbo.People
set LastAccessed=isnull(@.LastAccessed,LastAc
cessed),
FirstName=isnull(@.FirstName, FirstName),
LastName=isnull(@.LastName, LastName),
Address1=isnull(@.Address1, Address1),
Address2=isnull(@.Address2, Address2),
City=isnull(@.City, City),
State=isnull(@.State,State)
where ID=@.ID
return @.@.error
GO
Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
developers throwing hundreds of procs at me when it seems unecessary!Personally, I would prefer to have a CRUD interface that takes all the
values out, and then updates all of the values when saving.
The problem with trying to preserve a few bytes like you are doing, is that
now you can't overwrite an existing value with NULL. Yes, you can pass
Address1 = '' but is that really the same thing as NULL? I don't think so,
but it really depends on overall requirements.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23NHluITgHHA.5052@.TK2MSFTNGP05.phx.gbl...
> Given the need to update data, is it better to have 500 procs that update
> a table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
>
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAc
cessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
>
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
>
>|||Also, typically if you are updating a person's info, you're not updating a
single value. e.g. if someone moves, you need to update address1, address2,
city, state, zip, etc.
Do you really want to manage this set of procedures, and call them all
individually? I wouldn't:
dbo.Person_UpdateAddress1
dbo.Person_UpdateAddress2
dbo.Person_UpdateCity
dbo.Person_UpdateState
dbo.Person_UpdateZip
dbo.Person_UpdatePhone
dbo.Person_UpdateFax
...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||On 17 Apr, 21:51, "Tim Greenwood" <tim_greenwood AT yahoo DOT com>
wrote:
> Given the need to update data, is it better to have 500 procs that update
a
> table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAc
cessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
If you regularly need to update some small subset of columns then it
may be worth creating a separate proc for such a case. On grounds of
maintainability I would question the value of creating 500 such procs
unless it's essential to squeeze every last ounce of performance from
the database.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||That's exactly my point...no I wouldn't want to but that is exactly what I
keep getting from developers. I'd prefer CRUD interface as well but I'm
lacking in support right now. Unfortunately all developers have to pass
their code through me for verification as dba before it goes into final QA
environment.
So other than the idea of supporting only modified values the basic idea
here seems right on? IOW get rid of the whole isnull() on the updates?
The only problem we run into with that is if they've used a custom view or
proc that only queries a subset of data from multiple tables then they don't
have all the values to supply to a CRUD proc for updating...I know these
are extremely basic issues here, I'm just playing devils advocate. I have
some authority to leverage in the enforcement of these things but want to be
sure I'm coming from a best (or at least accepted) practices.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23RO3DPTgHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Also, typically if you are updating a person's info, you're not updating a
> single value. e.g. if someone moves, you need to update address1,
> address2, city, state, zip, etc.
> Do you really want to manage this set of procedures, and call them all
> individually? I wouldn't:
> dbo.Person_UpdateAddress1
> dbo.Person_UpdateAddress2
> dbo.Person_UpdateCity
> dbo.Person_UpdateState
> dbo.Person_UpdateZip
> dbo.Person_UpdatePhone
> dbo.Person_UpdateFax
> ...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>|||> So other than the idea of supporting only modified values the basic idea
> here seems right on? IOW get rid of the whole isnull() on the updates?
> The only problem we run into with that is if they've used a custom view or
> proc that only queries a subset of data from multiple tables then they
> don't have all the values to supply to a CRUD proc for updating...I know
> these are extremely basic issues here, I'm just playing devils advocate.
> I have some authority to leverage in the enforcement of these things but
> want to be sure I'm coming from a best (or at least accepted) practices.
We enforce that they get all the details from a generic _GetDetails
procedure if their intention is to update even only one of the values.
If they are just getting the data for display, then yes I could see why they
might argue that they only want a subset of the data.
But with the memory on servers these days, there is no reason why the app
can't store the whole row in memory. Or, in cases like a two-column report,
that segment of the code can just ignore the other columns.
The trade-off here is performance vs. maintenance. You need to make that
decision... we can't tell you what's best because we don't know what it will
take to convince your developers to do it your way, and we don't know what
the performance threshold is (e.g. when does pulling/updating a subset
really change the way the app behaves).
A|||I have developed a WhichFieldsUsed mechanism that enables a single sproc to
only update the field or fields in a table that the caller wishes to have
updated, and then only if they are different from the existing values. Drop
me an email if you are interested. kgboles a t earth link d o t net.
TheSQLGuru
President
Indicium Resources, Inc.|||I already know *how* to do that. My question wasn't so much how to do it as
what is the most accepted practice.
Thanks!
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23eMdKFfgHHA.5044@.TK2MSFTNGP05.phx.gbl...
>I have developed a WhichFieldsUsed mechanism that enables a single sproc to
>only update the field or fields in a table that the caller wishes to have
>updated, and then only if they are different from the existing values.
>Drop me an email if you are interested. kgboles a t earth link d o t net.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>

Question about best practices..

Given the need to update data, is it better to have 500 procs that update a
table as needed for a specific situation or given a table like:
create table dbo.People
(
ID int identity(1,1) not null,
LastAccessed datetime null,
FirstName varchar(20) not null,
LastName varchar(30) not null,
Address1 varchar(50) not null,
Address2 varchar(50) not null,
City varchar(50) not null,
State char(2) not null
)
to have a proc like this that can be used for any update to this table:
create procedure UpdatePeople
@.ID int,
@.LastAccessed datetime=null,
@.FirstName varchar(20)=null,
@.LastName varchar(30)=null,
@.Address1 varchar(50)=null,
@.Address2 varchar(50)=nul,
@.City varchar(50)=null,
@.State char(2)=null
as
set nocount on;
update dbo.People
set LastAccessed=isnull(@.LastAccessed,LastAccessed),
FirstName=isnull(@.FirstName, FirstName),
LastName=isnull(@.LastName, LastName),
Address1=isnull(@.Address1, Address1),
Address2=isnull(@.Address2, Address2),
City=isnull(@.City, City),
State=isnull(@.State,State)
where ID=@.ID
return @.@.error
GO
Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
developers throwing hundreds of procs at me when it seems unecessary!
Personally, I would prefer to have a CRUD interface that takes all the
values out, and then updates all of the values when saving.
The problem with trying to preserve a few bytes like you are doing, is that
now you can't overwrite an existing value with NULL. Yes, you can pass
Address1 = '' but is that really the same thing as NULL? I don't think so,
but it really depends on overall requirements.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23NHluITgHHA.5052@.TK2MSFTNGP05.phx.gbl...
> Given the need to update data, is it better to have 500 procs that update
> a table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
>
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAccessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
>
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
>
>
|||Also, typically if you are updating a person's info, you're not updating a
single value. e.g. if someone moves, you need to update address1, address2,
city, state, zip, etc.
Do you really want to manage this set of procedures, and call them all
individually? I wouldn't:
dbo.Person_UpdateAddress1
dbo.Person_UpdateAddress2
dbo.Person_UpdateCity
dbo.Person_UpdateState
dbo.Person_UpdateZip
dbo.Person_UpdatePhone
dbo.Person_UpdateFax
...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||On 17 Apr, 21:51, "Tim Greenwood" <tim_greenwood AT yahoo DOT com>
wrote:
> Given the need to update data, is it better to have 500 procs that update a
> table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAccessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
If you regularly need to update some small subset of columns then it
may be worth creating a separate proc for such a case. On grounds of
maintainability I would question the value of creating 500 such procs
unless it's essential to squeeze every last ounce of performance from
the database.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||That's exactly my point...no I wouldn't want to but that is exactly what I
keep getting from developers. I'd prefer CRUD interface as well but I'm
lacking in support right now. Unfortunately all developers have to pass
their code through me for verification as dba before it goes into final QA
environment.
So other than the idea of supporting only modified values the basic idea
here seems right on? IOW get rid of the whole isnull() on the updates?
The only problem we run into with that is if they've used a custom view or
proc that only queries a subset of data from multiple tables then they don't
have all the values to supply to a CRUD proc for updating...I know these
are extremely basic issues here, I'm just playing devils advocate. I have
some authority to leverage in the enforcement of these things but want to be
sure I'm coming from a best (or at least accepted) practices.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23RO3DPTgHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Also, typically if you are updating a person's info, you're not updating a
> single value. e.g. if someone moves, you need to update address1,
> address2, city, state, zip, etc.
> Do you really want to manage this set of procedures, and call them all
> individually? I wouldn't:
> dbo.Person_UpdateAddress1
> dbo.Person_UpdateAddress2
> dbo.Person_UpdateCity
> dbo.Person_UpdateState
> dbo.Person_UpdateZip
> dbo.Person_UpdatePhone
> dbo.Person_UpdateFax
> ...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
|||> So other than the idea of supporting only modified values the basic idea
> here seems right on? IOW get rid of the whole isnull() on the updates?
> The only problem we run into with that is if they've used a custom view or
> proc that only queries a subset of data from multiple tables then they
> don't have all the values to supply to a CRUD proc for updating...I know
> these are extremely basic issues here, I'm just playing devils advocate.
> I have some authority to leverage in the enforcement of these things but
> want to be sure I'm coming from a best (or at least accepted) practices.
We enforce that they get all the details from a generic _GetDetails
procedure if their intention is to update even only one of the values.
If they are just getting the data for display, then yes I could see why they
might argue that they only want a subset of the data.
But with the memory on servers these days, there is no reason why the app
can't store the whole row in memory. Or, in cases like a two-column report,
that segment of the code can just ignore the other columns.
The trade-off here is performance vs. maintenance. You need to make that
decision... we can't tell you what's best because we don't know what it will
take to convince your developers to do it your way, and we don't know what
the performance threshold is (e.g. when does pulling/updating a subset
really change the way the app behaves).
A
|||I have developed a WhichFieldsUsed mechanism that enables a single sproc to
only update the field or fields in a table that the caller wishes to have
updated, and then only if they are different from the existing values. Drop
me an email if you are interested. kgboles a t earth link d o t net.
TheSQLGuru
President
Indicium Resources, Inc.
|||I already know *how* to do that. My question wasn't so much how to do it as
what is the most accepted practice.
Thanks!
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23eMdKFfgHHA.5044@.TK2MSFTNGP05.phx.gbl...
>I have developed a WhichFieldsUsed mechanism that enables a single sproc to
>only update the field or fields in a table that the caller wishes to have
>updated, and then only if they are different from the existing values.
>Drop me an email if you are interested. kgboles a t earth link d o t net.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>

Question - Replicating schema without data for specific columns

Good morning,

I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.

How do I do this?

I have searched the forum for some ideas, but did not find any.

Thanks in advance...

you have several options. One is to create a custom script using sp_addarticle, another is to create an indexed view on the publisher which looks like the table on the subscriber (ie missing the columns) and replicated that as a table on the subcsriber, and the third is to use sp_articlecolumn and sp_articleview as illustrated here.

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

Thanks for the response Hilary. I will want the column replicated, but not the data for that column. Meaning, I want all the columns and all the data EXCEPT for the data for a specific column. Do your suggestions still apply?

Thanks...

Scott

|||

So, in the documentation for sp_articleview, I read the following:

To publish a vertically filtered table (that is, to filter columns) first run sp_addarticle with no sync_object parameter, run sp_articlecolumn (Transact-SQL) once for each column to be replicated (defining the vertical filter), and then run sp_articleview to create the view that defines the published article.

I'm not sure this gives me what I need, but I may be confused (still). When I run sp_articlecoumn, do I still do that for the column I don't want replicated? According to the statement above, I need to run it "for each column to be replicated".

I want the schema to be replicated, but not the data for that column. Am i missing something?

Thanks...

|||you need a custom sync object for something like this. If you post the schema of the table and what you want it to look like on the other side I'll try to generate it.|||

Hi Hilary,

Here is what I am looking for:

Publisher: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates 1

2 5 rlindey 1

etc...

When synchronized to the subscriber, I want the following:

Subscriber: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates

2 5 rlindey

etc...

So, I want the SCHEMA for the entire T_Subscribers table to synchronize (including the sub_ReadOnlyStatus column) and the DATA for all columns EXCEPT the subReadOnlyStatus column.

Does this make sense?

I truly appreciate your help...

Scott

|||create database SQLScott
GO
create database SQLScottSubscriber
GO
use SQLScott
GO
create table T_Subscribers(subID int not null identity primary key,
sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(1,'bgates')
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(5,'rlindey')
GO
sp_replicationdboption 'SQLScott','publish','true'
GO
sp_addpublication 'SQLScott', @.status='active'
GO
sp_addpublication_snapshot 'SQLScott'
GO

CREATE VIEW Custom_Sync_Object

AS
SELECT subID, sub_BranchID, sub_UserName, convert(int,null) as sub_ReadOnlyStatus
FROM dbo.T_Subscribers
GO

sp_addarticle @.publication='SQLScott', @.article='SQLScott', @.source_object='T_Subscribers', @.sync_object='Custom_Sync_object',
@.schema_option=0x00, @.creation_script='c:\test.sql'
--test.sql looks like this:

--create table T_Subscribers(subID int not null identity not for replication primary key,
--sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
--GO|||

OK, I see what you are doing, but I have a question about the view. If the view is a standard view, won't that need to be created first prior to synching the table?

Meaning, in our environment, the tables are being synched in one publication and the views/stored procs/functions in a second publication. How does that affect this? Sorry for all the questions, just really trying understand all the 'ins and outs' of this...

Thanks Hilary. I cannot thank you enough. You have been extremely helpful and it is greatly appreciated.

Scott

|||You create it on the publisher. You don't need to replicate it, the bcp process references it while generating the bcp files.

It will need to be created prior to adding the article to the publication.|||

Hi Hilary,

I was all excited about putting this into action then ran into the realization that i failed to mention that this is Merge replication, thus using sp_addmergearticle. Please tell me i'm not back at square one... :-)

|||

So, I tried the following:

EXEC sp_addmergearticle
@.publication = 'SQLScott',
@.article = 'T_Subscribers',
@.source_object = 'Custom_Sync_Object', ...

Unfortunately, that did not work. I understand what your previous example is doing, and I would like to think there is a way to do the same thing on Merge Replication...

Again, thank you soooo much for your help...

Scott

|||With merge replication they have to be the same schema.|||

Do you mean that @.article and @.source_object must be the same schema? So, am I out of luck with this?

Question - Replicating schema without data for specific columns

Good morning,

I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.

How do I do this?

I have searched the forum for some ideas, but did not find any.

Thanks in advance...

you have several options. One is to create a custom script using sp_addarticle, another is to create an indexed view on the publisher which looks like the table on the subscriber (ie missing the columns) and replicated that as a table on the subcsriber, and the third is to use sp_articlecolumn and sp_articleview as illustrated here.

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

Thanks for the response Hilary. I will want the column replicated, but not the data for that column. Meaning, I want all the columns and all the data EXCEPT for the data for a specific column. Do your suggestions still apply?

Thanks...

Scott

|||

So, in the documentation for sp_articleview, I read the following:

To publish a vertically filtered table (that is, to filter columns) first run sp_addarticle with no sync_object parameter, run sp_articlecolumn (Transact-SQL) once for each column to be replicated (defining the vertical filter), and then run sp_articleview to create the view that defines the published article.

I'm not sure this gives me what I need, but I may be confused (still). When I run sp_articlecoumn, do I still do that for the column I don't want replicated? According to the statement above, I need to run it "for each column to be replicated".

I want the schema to be replicated, but not the data for that column. Am i missing something?

Thanks...

|||you need a custom sync object for something like this. If you post the schema of the table and what you want it to look like on the other side I'll try to generate it.|||

Hi Hilary,

Here is what I am looking for:

Publisher: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates 1

2 5 rlindey 1

etc...

When synchronized to the subscriber, I want the following:

Subscriber: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates

2 5 rlindey

etc...

So, I want the SCHEMA for the entire T_Subscribers table to synchronize (including the sub_ReadOnlyStatus column) and the DATA for all columns EXCEPT the subReadOnlyStatus column.

Does this make sense?

I truly appreciate your help...

Scott

|||create database SQLScott
GO
create database SQLScottSubscriber
GO
use SQLScott
GO
create table T_Subscribers(subID int not null identity primary key,
sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(1,'bgates')
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(5,'rlindey')
GO
sp_replicationdboption 'SQLScott','publish','true'
GO
sp_addpublication 'SQLScott', @.status='active'
GO
sp_addpublication_snapshot 'SQLScott'
GO

CREATE VIEW Custom_Sync_Object

AS
SELECT subID, sub_BranchID, sub_UserName, convert(int,null) as sub_ReadOnlyStatus
FROM dbo.T_Subscribers
GO

sp_addarticle @.publication='SQLScott', @.article='SQLScott', @.source_object='T_Subscribers', @.sync_object='Custom_Sync_object',
@.schema_option=0x00, @.creation_script='c:\test.sql'
--test.sql looks like this:

--create table T_Subscribers(subID int not null identity not for replication primary key,
--sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
--GO|||

OK, I see what you are doing, but I have a question about the view. If the view is a standard view, won't that need to be created first prior to synching the table?

Meaning, in our environment, the tables are being synched in one publication and the views/stored procs/functions in a second publication. How does that affect this? Sorry for all the questions, just really trying understand all the 'ins and outs' of this...

Thanks Hilary. I cannot thank you enough. You have been extremely helpful and it is greatly appreciated.

Scott

|||You create it on the publisher. You don't need to replicate it, the bcp process references it while generating the bcp files.

It will need to be created prior to adding the article to the publication.|||

Hi Hilary,

I was all excited about putting this into action then ran into the realization that i failed to mention that this is Merge replication, thus using sp_addmergearticle. Please tell me i'm not back at square one... :-)

|||

So, I tried the following:

EXEC sp_addmergearticle
@.publication = 'SQLScott',
@.article = 'T_Subscribers',
@.source_object = 'Custom_Sync_Object', ...

Unfortunately, that did not work. I understand what your previous example is doing, and I would like to think there is a way to do the same thing on Merge Replication...

Again, thank you soooo much for your help...

Scott

|||With merge replication they have to be the same schema.|||

Do you mean that @.article and @.source_object must be the same schema? So, am I out of luck with this?

Question - Replicating schema without data for specific columns

Good morning,

I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.

How do I do this?

I have searched the forum for some ideas, but did not find any.

Thanks in advance...

you have several options. One is to create a custom script using sp_addarticle, another is to create an indexed view on the publisher which looks like the table on the subscriber (ie missing the columns) and replicated that as a table on the subcsriber, and the third is to use sp_articlecolumn and sp_articleview as illustrated here.

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

Thanks for the response Hilary. I will want the column replicated, but not the data for that column. Meaning, I want all the columns and all the data EXCEPT for the data for a specific column. Do your suggestions still apply?

Thanks...

Scott

|||

So, in the documentation for sp_articleview, I read the following:

To publish a vertically filtered table (that is, to filter columns) first run sp_addarticle with no sync_object parameter, run sp_articlecolumn (Transact-SQL) once for each column to be replicated (defining the vertical filter), and then run sp_articleview to create the view that defines the published article.

I'm not sure this gives me what I need, but I may be confused (still). When I run sp_articlecoumn, do I still do that for the column I don't want replicated? According to the statement above, I need to run it "for each column to be replicated".

I want the schema to be replicated, but not the data for that column. Am i missing something?

Thanks...

|||you need a custom sync object for something like this. If you post the schema of the table and what you want it to look like on the other side I'll try to generate it.|||

Hi Hilary,

Here is what I am looking for:

Publisher: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates 1

2 5 rlindey 1

etc...

When synchronized to the subscriber, I want the following:

Subscriber: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates

2 5 rlindey

etc...

So, I want the SCHEMA for the entire T_Subscribers table to synchronize (including the sub_ReadOnlyStatus column) and the DATA for all columns EXCEPT the subReadOnlyStatus column.

Does this make sense?

I truly appreciate your help...

Scott

|||create database SQLScott
GO
create database SQLScottSubscriber
GO
use SQLScott
GO
create table T_Subscribers(subID int not null identity primary key,
sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(1,'bgates')
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(5,'rlindey')
GO
sp_replicationdboption 'SQLScott','publish','true'
GO
sp_addpublication 'SQLScott', @.status='active'
GO
sp_addpublication_snapshot 'SQLScott'
GO

CREATE VIEW Custom_Sync_Object

AS
SELECT subID, sub_BranchID, sub_UserName, convert(int,null) as sub_ReadOnlyStatus
FROM dbo.T_Subscribers
GO

sp_addarticle @.publication='SQLScott', @.article='SQLScott', @.source_object='T_Subscribers', @.sync_object='Custom_Sync_object',
@.schema_option=0x00, @.creation_script='c:\test.sql'
--test.sql looks like this:

--create table T_Subscribers(subID int not null identity not for replication primary key,
--sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
--GO|||

OK, I see what you are doing, but I have a question about the view. If the view is a standard view, won't that need to be created first prior to synching the table?

Meaning, in our environment, the tables are being synched in one publication and the views/stored procs/functions in a second publication. How does that affect this? Sorry for all the questions, just really trying understand all the 'ins and outs' of this...

Thanks Hilary. I cannot thank you enough. You have been extremely helpful and it is greatly appreciated.

Scott

|||You create it on the publisher. You don't need to replicate it, the bcp process references it while generating the bcp files.

It will need to be created prior to adding the article to the publication.|||

Hi Hilary,

I was all excited about putting this into action then ran into the realization that i failed to mention that this is Merge replication, thus using sp_addmergearticle. Please tell me i'm not back at square one... :-)

|||

So, I tried the following:

EXEC sp_addmergearticle
@.publication = 'SQLScott',
@.article = 'T_Subscribers',
@.source_object = 'Custom_Sync_Object', ...

Unfortunately, that did not work. I understand what your previous example is doing, and I would like to think there is a way to do the same thing on Merge Replication...

Again, thank you soooo much for your help...

Scott

|||With merge replication they have to be the same schema.|||

Do you mean that @.article and @.source_object must be the same schema? So, am I out of luck with this?

Question - Replicating schema without data for specific columns

Good morning,

I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.

How do I do this?

I have searched the forum for some ideas, but did not find any.

Thanks in advance...

you have several options. One is to create a custom script using sp_addarticle, another is to create an indexed view on the publisher which looks like the table on the subscriber (ie missing the columns) and replicated that as a table on the subcsriber, and the third is to use sp_articlecolumn and sp_articleview as illustrated here.

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

Thanks for the response Hilary. I will want the column replicated, but not the data for that column. Meaning, I want all the columns and all the data EXCEPT for the data for a specific column. Do your suggestions still apply?

Thanks...

Scott

|||

So, in the documentation for sp_articleview, I read the following:

To publish a vertically filtered table (that is, to filter columns) first run sp_addarticle with no sync_object parameter, run sp_articlecolumn (Transact-SQL) once for each column to be replicated (defining the vertical filter), and then run sp_articleview to create the view that defines the published article.

I'm not sure this gives me what I need, but I may be confused (still). When I run sp_articlecoumn, do I still do that for the column I don't want replicated? According to the statement above, I need to run it "for each column to be replicated".

I want the schema to be replicated, but not the data for that column. Am i missing something?

Thanks...

|||you need a custom sync object for something like this. If you post the schema of the table and what you want it to look like on the other side I'll try to generate it.|||

Hi Hilary,

Here is what I am looking for:

Publisher: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates 1

2 5 rlindey 1

etc...

When synchronized to the subscriber, I want the following:

Subscriber: T_Subscribers

sub_ID sub_BranchID sub_UserName sub_ReadOnlyStatus

-- - -

1 1 bgates

2 5 rlindey

etc...

So, I want the SCHEMA for the entire T_Subscribers table to synchronize (including the sub_ReadOnlyStatus column) and the DATA for all columns EXCEPT the subReadOnlyStatus column.

Does this make sense?

I truly appreciate your help...

Scott

|||create database SQLScott
GO
create database SQLScottSubscriber
GO
use SQLScott
GO
create table T_Subscribers(subID int not null identity primary key,
sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(1,'bgates')
GO
insert into T_Subscribers(sub_BranchID, sub_UserName)
values(5,'rlindey')
GO
sp_replicationdboption 'SQLScott','publish','true'
GO
sp_addpublication 'SQLScott', @.status='active'
GO
sp_addpublication_snapshot 'SQLScott'
GO

CREATE VIEW Custom_Sync_Object

AS
SELECT subID, sub_BranchID, sub_UserName, convert(int,null) as sub_ReadOnlyStatus
FROM dbo.T_Subscribers
GO

sp_addarticle @.publication='SQLScott', @.article='SQLScott', @.source_object='T_Subscribers', @.sync_object='Custom_Sync_object',
@.schema_option=0x00, @.creation_script='c:\test.sql'
--test.sql looks like this:

--create table T_Subscribers(subID int not null identity not for replication primary key,
--sub_BranchID int, sub_UserName varchar(20), sub_ReadOnlyStatus int)
--GO|||

OK, I see what you are doing, but I have a question about the view. If the view is a standard view, won't that need to be created first prior to synching the table?

Meaning, in our environment, the tables are being synched in one publication and the views/stored procs/functions in a second publication. How does that affect this? Sorry for all the questions, just really trying understand all the 'ins and outs' of this...

Thanks Hilary. I cannot thank you enough. You have been extremely helpful and it is greatly appreciated.

Scott

|||You create it on the publisher. You don't need to replicate it, the bcp process references it while generating the bcp files.

It will need to be created prior to adding the article to the publication.|||

Hi Hilary,

I was all excited about putting this into action then ran into the realization that i failed to mention that this is Merge replication, thus using sp_addmergearticle. Please tell me i'm not back at square one... :-)

|||

So, I tried the following:

EXEC sp_addmergearticle
@.publication = 'SQLScott',
@.article = 'T_Subscribers',
@.source_object = 'Custom_Sync_Object', ...

Unfortunately, that did not work. I understand what your previous example is doing, and I would like to think there is a way to do the same thing on Merge Replication...

Again, thank you soooo much for your help...

Scott

|||With merge replication they have to be the same schema.|||

Do you mean that @.article and @.source_object must be the same schema? So, am I out of luck with this?