Automatic Replication Trigger

HI All,
Using SQL Server 2000 SP3.
Replication is set up on a few databases, on each of the tables that are
replicated the following trigger appears:
create trigger [sp_MSsync_upd_trig_Voucher_1] on [dbo].[Voucher] for
update as
declare @.rc int
select @.rc = @.@.ROWCOUNT
if @.rc = 0 return
if update (msrepl_tran_version) return
update [dbo].[Voucher]
set msrepl_tran_version = newid()
from [dbo].[Voucher], inserted
where [dbo].[Voucher].[Vouch_Key] = inserted.[Vouch_Key]
This trigger is causing a significant slowdown when called and I would
like to remove it. However I do not want to break the replication.
I need to understand what has created this trigger and why. I belive
that this has been implemented as a workaround for some problem that was
encountered by the replication process but this is merely speculation on
my behalf.
Your help is very much appreciated.
Brian.
*** Sent via Developersdex http://www.codecomments.com ***
It looks like you are using immediate or queued updating which puts these
triggers in place on the publisher. I can't comment on Microsoft's logic
behind this proc. Basically what it does is assigns a new value to the guid
column if the guid column it self is not modified/supplied.
I would look at whether you really need immediate updating or bi-directional
replication in general as bi-directional transactional replication offers
some of the functionality but will not cause the performance degradation.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Brian Wotherspoon" <bwotherspoon2002@.yahoo.co.uk> wrote in message
news:uU1y0aSSGHA.4956@.TK2MSFTNGP09.phx.gbl...
>
> HI All,
> Using SQL Server 2000 SP3.
> Replication is set up on a few databases, on each of the tables that are
> replicated the following trigger appears:
> create trigger [sp_MSsync_upd_trig_Voucher_1] on [dbo].[Voucher] for
> update as
> declare @.rc int
> select @.rc = @.@.ROWCOUNT
> if @.rc = 0 return
> if update (msrepl_tran_version) return
> update [dbo].[Voucher]
> set msrepl_tran_version = newid()
> from [dbo].[Voucher], inserted
> where [dbo].[Voucher].[Vouch_Key] = inserted.[Vouch_Key]
>
> This trigger is causing a significant slowdown when called and I would
> like to remove it. However I do not want to break the replication.
> I need to understand what has created this trigger and why. I belive
> that this has been implemented as a workaround for some problem that was
> encountered by the replication process but this is merely speculation on
> my behalf.
> Your help is very much appreciated.
> Brian.
> *** Sent via Developersdex http://www.codecomments.com ***
|||Thanks for your response here. What is required is one-way replication
as we have a primary server that needs to replicate information to a
backup server.
I presume that this is a different configuration from immediate/queued
updating. I thought that transactional replication is what was set up
but perhaps this is not the case?
Can the specific kind of replication be altered in situ or would it need
to be dropped and recreated?
Again many thanks for any help!
Brian.
*** Sent via Developersdex http://www.codecomments.com ***
|||Use transactional replication if you have no requirement for
bi-directionality. to fix this drop the subscriber, and then disable the
triggers, then redeploy the subscriber as a plain vanilla subscriber.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Brian Wotherspoon" <bwotherspoon2002@.yahoo.co.uk> wrote in message
news:OarBgsZSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>
> Thanks for your response here. What is required is one-way replication
> as we have a primary server that needs to replicate information to a
> backup server.
> I presume that this is a different configuration from immediate/queued
> updating. I thought that transactional replication is what was set up
> but perhaps this is not the case?
> Can the specific kind of replication be altered in situ or would it need
> to be dropped and recreated?
> Again many thanks for any help!
> Brian.
> *** Sent via Developersdex http://www.codecomments.com ***

Automatic deletion to allow import

Greetings All
I'm new to SQL. I'm using Standard edition (SP3)
I have recently had to sort out the import of a Unisys dbs into our SQL
server. This dbs needs to be imported a minimum of 3 times per week. It won'
t
overwrite an existing dbs so the only way I can think to do this is by
deleting the existing dbs before the scheduled import. I know Unisys can
provide a componant that allows synchronisation but as with everything that
costs mucho money.
Is there a was to delete the existing dbs automatically so that the dbs will
be imported through Data Transformation Services, local packages?
Or am I, due to my lack of knowledge going about this the wrong way.
Andy
--
Ah! thats what pushing the button does.!!!I don't know what a dbs is. Is it a table? And entire database of
many tables?
It is possible for DTS to delete the rows in a table before importing
new data. In the DTS wizard click on the Transform column to find the
option, to the right of the targe table you select. Save the package.
Roy
On Tue, 25 Jul 2006 08:18:01 -0700, sage
<sage@.discussions.microsoft.com> wrote:

>Greetings All
>I'm new to SQL. I'm using Standard edition (SP3)
>I have recently had to sort out the import of a Unisys dbs into our SQL
>server. This dbs needs to be imported a minimum of 3 times per week. It won
't
>overwrite an existing dbs so the only way I can think to do this is by
>deleting the existing dbs before the scheduled import. I know Unisys can
>provide a componant that allows synchronisation but as with everything that
>costs mucho money.
>Is there a was to delete the existing dbs automatically so that the dbs wil
l
>be imported through Data Transformation Services, local packages?
>Or am I, due to my lack of knowledge going about this the wrong way.
>Andy|||Thanks Roy
dbs = database (Sorry for confusing you)
The problem is we have at least 150 tables.
A coleague found the "Drop" command .. do you know if this could be used?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:

> I don't know what a dbs is. Is it a table? And entire database of
> many tables?
> It is possible for DTS to delete the rows in a table before importing
> new data. In the DTS wizard click on the Transform column to find the
> option, to the right of the targe table you select. Save the package.
> Roy
>
> On Tue, 25 Jul 2006 08:18:01 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
>
>|||Are you figuring on...
Ddropping and re-creating the database?
Dropping and recreating the tables?
Truncating the tables and reloading the data?
What tool are you using for the load? Something from Unisys? Have
you already gone through the process the first time, and just trying
to make it run three times a week? Or is that first load still ahead
of you?
In general, DROP will let you drop a database or a table. TRUNCATE or
DELETE will let you remove the data from a table.
I have always found that the table definitions generated by automatic
load tools leave something - usually a great deal - to be desired.
Having all the character columns end up as NVARCHAR(4000), and all the
numbers end up as FLOAT, is not my idea of good data type choices. So
what I generally do is load the first time using whatever tool builds
the tables, then script the tables and fix the definiions. Those new
table definiions replace the first versions of tables, and I then
rework the load process to load into existing tables rather than
creating them.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 06:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:

>Thanks Roy
>dbs = database (Sorry for confusing you)
>The problem is we have at least 150 tables.
>A coleague found the "Drop" command .. do you know if this could be used?
>Andy|||Well we have successfully done a copy already and found that if we wanted to
keep the database uptodate we needed to copy 3 times per week (for our
purposes) If the database exists it fails to copy. So we had looked at
deleting manually but then someone suggested that if we could incorporate
this in the import then that would solve it all in one go.
Basically I'm just trying to achive a copy of the Unisys database to the SQL
server using DTS on a schedual.
As for the table definitions I have a colleague who may have the skills
required. If we achive this can the import into existing tables be done via
DTS?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:

> Are you figuring on...
> Ddropping and re-creating the database?
> Dropping and recreating the tables?
> Truncating the tables and reloading the data?
> What tool are you using for the load? Something from Unisys? Have
> you already gone through the process the first time, and just trying
> to make it run three times a week? Or is that first load still ahead
> of you?
> In general, DROP will let you drop a database or a table. TRUNCATE or
> DELETE will let you remove the data from a table.
> I have always found that the table definitions generated by automatic
> load tools leave something - usually a great deal - to be desired.
> Having all the character columns end up as NVARCHAR(4000), and all the
> numbers end up as FLOAT, is not my idea of good data type choices. So
> what I generally do is load the first time using whatever tool builds
> the tables, then script the tables and fix the definiions. Those new
> table definiions replace the first versions of tables, and I then
> rework the load process to load into existing tables rather than
> creating them.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 06:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
>
>|||If you have already loaded the data onece you are well along.
Yes, import to existing tables can be done using DTS. If you start
with the import wizard, there is a spot where you can choose an
existing table (the Destination column), and next to that (the
Transform column) specify that the existing data in the table is to be
deleted before the fresh data is loaded. I generally use the wizard
to get me started, telling it to save the package, then edit the
package as required.
Once choice you have to make is whether to make one huge DTS package,
an individual package for each table, or something in between.
Combining all the tables into one package is a bit less work to set
up, perhaps, and simpler to schedule, but has major shortocomings when
you need to run just one bit. Also, the tools for editing packages,
with the graphical representation of the objects, isn't the greatest
for working with large numbers of objects. So I would be inclined
toward an individual package for each table. A compromise would be to
use individual packages for large or problematic tables, and then
group the rest of the tables into logical sets, a package for each.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 07:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:

>Well we have successfully done a copy already and found that if we wanted t
o
>keep the database uptodate we needed to copy 3 times per week (for our
>purposes) If the database exists it fails to copy. So we had looked at
>deleting manually but then someone suggested that if we could incorporate
>this in the import then that would solve it all in one go.
>Basically I'm just trying to achive a copy of the Unisys database to the SQ
L
>server using DTS on a schedual.
>As for the table definitions I have a colleague who may have the skills
>required. If we achive this can the import into existing tables be done via
>DTS?
>Andy|||Thanks very much for this Roy. I apriciate you taking the time to expliain
it. I think I'll follow your guidence here and although more work initialy,
do seperate packages. I'll see how it goes
Thank you once again
Andy
"Roy Harvey" wrote:

> If you have already loaded the data onece you are well along.
> Yes, import to existing tables can be done using DTS. If you start
> with the import wizard, there is a spot where you can choose an
> existing table (the Destination column), and next to that (the
> Transform column) specify that the existing data in the table is to be
> deleted before the fresh data is loaded. I generally use the wizard
> to get me started, telling it to save the package, then edit the
> package as required.
> Once choice you have to make is whether to make one huge DTS package,
> an individual package for each table, or something in between.
> Combining all the tables into one package is a bit less work to set
> up, perhaps, and simpler to schedule, but has major shortocomings when
> you need to run just one bit. Also, the tools for editing packages,
> with the graphical representation of the objects, isn't the greatest
> for working with large numbers of objects. So I would be inclined
> toward an individual package for each table. A compromise would be to
> use individual packages for large or problematic tables, and then
> group the rest of the tables into logical sets, a package for each.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 07:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
>
>

Automatic deletion to allow import

Greetings All
I'm new to SQL. I'm using Standard edition (SP3)
I have recently had to sort out the import of a Unisys dbs into our SQL
server. This dbs needs to be imported a minimum of 3 times per week. It won't
overwrite an existing dbs so the only way I can think to do this is by
deleting the existing dbs before the scheduled import. I know Unisys can
provide a componant that allows synchronisation but as with everything that
costs mucho money.
Is there a was to delete the existing dbs automatically so that the dbs will
be imported through Data Transformation Services, local packages?
Or am I, due to my lack of knowledge going about this the wrong way.
Andy
--
Ah! thats what pushing the button does.!!!I don't know what a dbs is. Is it a table? And entire database of
many tables?
It is possible for DTS to delete the rows in a table before importing
new data. In the DTS wizard click on the Transform column to find the
option, to the right of the targe table you select. Save the package.
Roy
On Tue, 25 Jul 2006 08:18:01 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Greetings All
>I'm new to SQL. I'm using Standard edition (SP3)
>I have recently had to sort out the import of a Unisys dbs into our SQL
>server. This dbs needs to be imported a minimum of 3 times per week. It won't
>overwrite an existing dbs so the only way I can think to do this is by
>deleting the existing dbs before the scheduled import. I know Unisys can
>provide a componant that allows synchronisation but as with everything that
>costs mucho money.
>Is there a was to delete the existing dbs automatically so that the dbs will
>be imported through Data Transformation Services, local packages?
>Or am I, due to my lack of knowledge going about this the wrong way.
>Andy|||Thanks Roy
dbs = database (Sorry for confusing you)
The problem is we have at least 150 tables.
A coleague found the "Drop" command .. do you know if this could be used?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> I don't know what a dbs is. Is it a table? And entire database of
> many tables?
> It is possible for DTS to delete the rows in a table before importing
> new data. In the DTS wizard click on the Transform column to find the
> option, to the right of the targe table you select. Save the package.
> Roy
>
> On Tue, 25 Jul 2006 08:18:01 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Greetings All
> >
> >I'm new to SQL. I'm using Standard edition (SP3)
> >I have recently had to sort out the import of a Unisys dbs into our SQL
> >server. This dbs needs to be imported a minimum of 3 times per week. It won't
> >overwrite an existing dbs so the only way I can think to do this is by
> >deleting the existing dbs before the scheduled import. I know Unisys can
> >provide a componant that allows synchronisation but as with everything that
> >costs mucho money.
> >
> >Is there a was to delete the existing dbs automatically so that the dbs will
> >be imported through Data Transformation Services, local packages?
> >
> >Or am I, due to my lack of knowledge going about this the wrong way.
> >
> >Andy
>|||Are you figuring on...
Ddropping and re-creating the database?
Dropping and recreating the tables?
Truncating the tables and reloading the data?
What tool are you using for the load? Something from Unisys? Have
you already gone through the process the first time, and just trying
to make it run three times a week? Or is that first load still ahead
of you?
In general, DROP will let you drop a database or a table. TRUNCATE or
DELETE will let you remove the data from a table.
I have always found that the table definitions generated by automatic
load tools leave something - usually a great deal - to be desired.
Having all the character columns end up as NVARCHAR(4000), and all the
numbers end up as FLOAT, is not my idea of good data type choices. So
what I generally do is load the first time using whatever tool builds
the tables, then script the tables and fix the definiions. Those new
table definiions replace the first versions of tables, and I then
rework the load process to load into existing tables rather than
creating them.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 06:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Thanks Roy
>dbs = database (Sorry for confusing you)
>The problem is we have at least 150 tables.
>A coleague found the "Drop" command .. do you know if this could be used?
>Andy|||Well we have successfully done a copy already and found that if we wanted to
keep the database uptodate we needed to copy 3 times per week (for our
purposes) If the database exists it fails to copy. So we had looked at
deleting manually but then someone suggested that if we could incorporate
this in the import then that would solve it all in one go.
Basically I'm just trying to achive a copy of the Unisys database to the SQL
server using DTS on a schedual.
As for the table definitions I have a colleague who may have the skills
required. If we achive this can the import into existing tables be done via
DTS?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> Are you figuring on...
> Ddropping and re-creating the database?
> Dropping and recreating the tables?
> Truncating the tables and reloading the data?
> What tool are you using for the load? Something from Unisys? Have
> you already gone through the process the first time, and just trying
> to make it run three times a week? Or is that first load still ahead
> of you?
> In general, DROP will let you drop a database or a table. TRUNCATE or
> DELETE will let you remove the data from a table.
> I have always found that the table definitions generated by automatic
> load tools leave something - usually a great deal - to be desired.
> Having all the character columns end up as NVARCHAR(4000), and all the
> numbers end up as FLOAT, is not my idea of good data type choices. So
> what I generally do is load the first time using whatever tool builds
> the tables, then script the tables and fix the definiions. Those new
> table definiions replace the first versions of tables, and I then
> rework the load process to load into existing tables rather than
> creating them.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 06:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Thanks Roy
> >
> >dbs = database (Sorry for confusing you)
> >
> >The problem is we have at least 150 tables.
> >A coleague found the "Drop" command .. do you know if this could be used?
> >
> >Andy
>|||If you have already loaded the data onece you are well along.
Yes, import to existing tables can be done using DTS. If you start
with the import wizard, there is a spot where you can choose an
existing table (the Destination column), and next to that (the
Transform column) specify that the existing data in the table is to be
deleted before the fresh data is loaded. I generally use the wizard
to get me started, telling it to save the package, then edit the
package as required.
Once choice you have to make is whether to make one huge DTS package,
an individual package for each table, or something in between.
Combining all the tables into one package is a bit less work to set
up, perhaps, and simpler to schedule, but has major shortocomings when
you need to run just one bit. Also, the tools for editing packages,
with the graphical representation of the objects, isn't the greatest
for working with large numbers of objects. So I would be inclined
toward an individual package for each table. A compromise would be to
use individual packages for large or problematic tables, and then
group the rest of the tables into logical sets, a package for each.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 07:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Well we have successfully done a copy already and found that if we wanted to
>keep the database uptodate we needed to copy 3 times per week (for our
>purposes) If the database exists it fails to copy. So we had looked at
>deleting manually but then someone suggested that if we could incorporate
>this in the import then that would solve it all in one go.
>Basically I'm just trying to achive a copy of the Unisys database to the SQL
>server using DTS on a schedual.
>As for the table definitions I have a colleague who may have the skills
>required. If we achive this can the import into existing tables be done via
>DTS?
>Andy|||Thanks very much for this Roy. I apriciate you taking the time to expliain
it. I think I'll follow your guidence here and although more work initialy,
do seperate packages. I'll see how it goes
Thank you once again
Andy
"Roy Harvey" wrote:
> If you have already loaded the data onece you are well along.
> Yes, import to existing tables can be done using DTS. If you start
> with the import wizard, there is a spot where you can choose an
> existing table (the Destination column), and next to that (the
> Transform column) specify that the existing data in the table is to be
> deleted before the fresh data is loaded. I generally use the wizard
> to get me started, telling it to save the package, then edit the
> package as required.
> Once choice you have to make is whether to make one huge DTS package,
> an individual package for each table, or something in between.
> Combining all the tables into one package is a bit less work to set
> up, perhaps, and simpler to schedule, but has major shortocomings when
> you need to run just one bit. Also, the tools for editing packages,
> with the graphical representation of the objects, isn't the greatest
> for working with large numbers of objects. So I would be inclined
> toward an individual package for each table. A compromise would be to
> use individual packages for large or problematic tables, and then
> group the rest of the tables into logical sets, a package for each.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 07:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Well we have successfully done a copy already and found that if we wanted to
> >keep the database uptodate we needed to copy 3 times per week (for our
> >purposes) If the database exists it fails to copy. So we had looked at
> >deleting manually but then someone suggested that if we could incorporate
> >this in the import then that would solve it all in one go.
> >
> >Basically I'm just trying to achive a copy of the Unisys database to the SQL
> >server using DTS on a schedual.
> >
> >As for the table definitions I have a colleague who may have the skills
> >required. If we achive this can the import into existing tables be done via
> >DTS?
> >
> >Andy
>