Replication Trigger Problem (SQL 2005): Assistance Please

Our company is wanting to replicate a bunch of data down from our central server to tablet PC's. We really do not have anyone on staff with solid experience in replication, and have hit an error that we cannot seem to find any information whatsoever on.

Here is the problem:

When setting up a publication to replicate certain tables, we stop being able to do any updates on the tables. After some research, we were able to narrow the problem into the msrepl triggers the publication places on the table. In the update trigger of the table, the following line is being placed by SQL Server:

execute sp_mapdown_bitmap 0x0000000040, @.bm output

The sp_mapdown_bitmap call, passes through to a xp_mapdown_bitmap call (limited documentation I have found is it is suppose to be an extended stored procedure), which if I try to manually execute, does not exist on the server (Logged in as SA the entire time).

Commenting out that line in the replication stored procedures corrects the problem. However obviously we do not want to manually do that everytime we create a publication, and we have no idea what negative impacts may occur because of it.

We are getting this behavior on several SQL Server 2005's...

Additional notes on the environment:

-CLR Triggers written in VB, on most tables performing application functions

-SQL Server 2005 SP1 is on the servers, and SQL Server 2005 Express Edition is on the tablets.

-Again this problem only exists on some tables in the database.

Any assistance would be greatly appreciated.

Can you give us some more detail about the problem? When you say you cannot do any updates, do you mean the updates are taking a long time to return, or do you suspect the SPID is hung? How many rows are in the table?

|||

No the updates completely fail with error message, because the Update triggers for the replication have the line:

execute sp_mapdown_bitmap 0x0000000040, @.bm output

...placed in them, that makes a call to xp_mapdown_bitmap, which as far as I can tell does not exist on the server (If I try and execute xp_mapdown_bitmap from a query tool, I get the does not exist error).

This problem existed before SP1 and after. We have completely rebuilt the publication, made the publication only do one of the problem tables, nothing we can do seems to correct the error, except commenting the line out of the Triggers.

|||Without knowing the error, it's hard to pinpoint the problem. Are you getting an error message?|||

xp_mapdown_bitmap is not public, that is why you will get the does not exist error. Please give the specific error message when update fails so we can investigate further.

Thanks

Wanwen

|||

Ok, after some further investigation the problem seems to be occuring when one CLR Trigger, executes an update on anothre table that has the replication trigger on it. Commenting out the line sp_mapdown_bitmap from the replication trigger fixes the problem (on the table being updated). The error we get from inside the CLR trigger on the UPDATE command is the following:

Msg 6549, Level 16, State 1, Procedure AR_Invoice_InvoiceNumber, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'AR_Invoice_InvoiceNumber':
System.Exception: <Message>The user transaction that has been started in user defined routine, trigger or aggregate "AR_Invoice_Audit" is not ended upon exiting from it. This is not allowed and the transaction will be rolled back. Change application logic to enforce strict transaction nesting.</Message> > System.Data.SqlClient.SqlException: The user transaction that has been started in user defined routine, trigger or aggregate "AR_Invoice_Audit" is not ended upon exiting from it. This is not allowed and the transaction will be rolled back. Change application logic to enforce strict transaction nesting.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at SQLHVMS.Administration.Common.SetNumberColumn(SqlString TableName, SqlString ColumnName, SqlConnection Connection, String& Number)
System.Exception:
at SQLHVMS.Administration.Common.SetNumberColumn(SqlString TableName, SqlString ColumnName, SqlConnection Connection, String& Number)
at SQLHVMS.Receivables.Triggers.AR_Invoice_InvoiceNumber()
. User transaction, if any, will be rolled back.
The statement has been terminated.

--

Initially we had no transaction logic in the CLR trigger, since then we have tried placing a transaction around the contents of our CLR trigger (using sqlconnection.begintran, commit, etc) that seemed to have no effect.

Again what confuses me is commenting out that mapdown call in the replication update trigger immediately fixes the problem. So is there some issue with executing that statement when a CLR trigger is the source of the call of the update?

Jason

|||

Hi Jason,

This is actually a known issue with respect to the execution of extended procedure within SQLCLR. Unfortunately, the fix hasn't been fully approved for SP2 yet so I will attach your posting to the bug which will hopefully expedite the approval process. But since there really is no good workaround for your scenario (triggers are always executed in transaction context), you may want to push for a QFE by logging a bug @. http://lab.msdn.microsoft.com/productfeedback/, our internal reference number for this bug is SQLBU450991.

Thank you for reporting the issue.

-Raymond

|||

Thanks for the response. Can you please enlighten me on what the possible negative side effects of just commenting out the sp_mapdown_bitmap would be in one of the Replication triggers?

I sort of need a workaround for the mean time if possible...

|||

Hi Jason,

Since I am no merge replication expert, I can only imagine that commenting the call out will break merge replication. Ok, I should probably let the merge experts chime in on that. I do have this rather far-fetched idea of committing\starting transactions around the sp_mapdown_bitmap call in your trigger in an attempt to trick the server into thinking that the transaction balances out. How about making most of your trigger T-SQL and call CLR procs to do the things that you absolutely have to do in CLR?

-Raymond

|||

Jason,

sp_mapdown_bitmap is necessary to adjust the bitmap for updated columns when column id in the table is not continuous, e.g. there has been columns dropped from the table. Commenting out sp_mapdown_bitmap could lead to data non-convergence. It is not a valid workaround.

Will workaround mentioned by Raymond work for you?

Wanwen

|||

The transaction idea didnt seem to have any effect. And switching away from CLR would be a multi-week resolution to the problem, which would be hard to swallow...

If we are 100% sure there will be no added/removed columns, as a temporary workaround is commenting out that line not an option?

|||

The fact your update trigger is calling sp_mapdown_bitmap shows there is a need to do the map down adjustment. It could be because there has been dropped column, or table contains computed or timestamp column. You could run a simple query to find out:

select column_id from sys.columns where object_id = OBJECT_ID(<your_source_table>)

to see if column_id is continous from 1 to max column id

select * from sys.columns where object_id = OBJECT_ID(<your_source_table>) and (is_computed = 1 or system_type_id = type_id('timestamp'))

to see if you have computed column or timestamp column.

As I said before, commenting out calling sp_mapdown_bitmap in this case will lead to data non-convergence. We already brought up the issue and hopefully the bug could be fixed in SP2

|||

After running the queries, a "hole" did show up in the column id's in one of the tables causing the problem (there is no computed or timestamp columns). Is there anyway to close the gap or renumber the columns?

As much as I want to wait for SP2, we all know it could be quite awhile and we were really hoping to go live with the solution shortly...

|||

Jason,

You could

1.drop that article from your publication

2.run snapshot and merge agent if necessary

3.drop the physical tables from publisher and subscriber

4.recreate the table at publisher, make sure there are no holes in column id this time

5.add that article back to your publication

6.rerun snapshot and merge agent.

Or you could clean up your environment and start from beginning if that is more convenient for you.

Please let me know if you have any further questions.

Wanwen

|||Dear Raymond,

Please also see my post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=865299&SiteID=1&mode=1
since I am also running into a problem with CLR trigger nesting which seems related to the bug. Thanks in advance.

Replication Trigger Problem (SQL 2005): Assistance Please

Our company is wanting to replicate a bunch of data down from our central server to tablet PC's. We really do not have anyone on staff with solid experience in replication, and have hit an error that we cannot seem to find any information whatsoever on.

Here is the problem:

When setting up a publication to replicate certain tables, we stop being able to do any updates on the tables. After some research, we were able to narrow the problem into the msrepl triggers the publication places on the table. In the update trigger of the table, the following line is being placed by SQL Server:

execute sp_mapdown_bitmap 0x0000000040, @.bm output

The sp_mapdown_bitmap call, passes through to a xp_mapdown_bitmap call (limited documentation I have found is it is suppose to be an extended stored procedure), which if I try to manually execute, does not exist on the server (Logged in as SA the entire time).

Commenting out that line in the replication stored procedures corrects the problem. However obviously we do not want to manually do that everytime we create a publication, and we have no idea what negative impacts may occur because of it.

We are getting this behavior on several SQL Server 2005's...

Additional notes on the environment:

-CLR Triggers written in VB, on most tables performing application functions

-SQL Server 2005 SP1 is on the servers, and SQL Server 2005 Express Edition is on the tablets.

-Again this problem only exists on some tables in the database.

Any assistance would be greatly appreciated.

Can you give us some more detail about the problem? When you say you cannot do any updates, do you mean the updates are taking a long time to return, or do you suspect the SPID is hung? How many rows are in the table?

|||

No the updates completely fail with error message, because the Update triggers for the replication have the line:

execute sp_mapdown_bitmap 0x0000000040, @.bm output

...placed in them, that makes a call to xp_mapdown_bitmap, which as far as I can tell does not exist on the server (If I try and execute xp_mapdown_bitmap from a query tool, I get the does not exist error).

This problem existed before SP1 and after. We have completely rebuilt the publication, made the publication only do one of the problem tables, nothing we can do seems to correct the error, except commenting the line out of the Triggers.

|||Without knowing the error, it's hard to pinpoint the problem. Are you getting an error message?|||

xp_mapdown_bitmap is not public, that is why you will get the does not exist error. Please give the specific error message when update fails so we can investigate further.

Thanks

Wanwen

|||

Ok, after some further investigation the problem seems to be occuring when one CLR Trigger, executes an update on anothre table that has the replication trigger on it. Commenting out the line sp_mapdown_bitmap from the replication trigger fixes the problem (on the table being updated). The error we get from inside the CLR trigger on the UPDATE command is the following:

Msg 6549, Level 16, State 1, Procedure AR_Invoice_InvoiceNumber, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'AR_Invoice_InvoiceNumber':
System.Exception: <Message>The user transaction that has been started in user defined routine, trigger or aggregate "AR_Invoice_Audit" is not ended upon exiting from it. This is not allowed and the transaction will be rolled back. Change application logic to enforce strict transaction nesting.</Message> > System.Data.SqlClient.SqlException: The user transaction that has been started in user defined routine, trigger or aggregate "AR_Invoice_Audit" is not ended upon exiting from it. This is not allowed and the transaction will be rolled back. Change application logic to enforce strict transaction nesting.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at SQLHVMS.Administration.Common.SetNumberColumn(SqlString TableName, SqlString ColumnName, SqlConnection Connection, String& Number)
System.Exception:
at SQLHVMS.Administration.Common.SetNumberColumn(SqlString TableName, SqlString ColumnName, SqlConnection Connection, String& Number)
at SQLHVMS.Receivables.Triggers.AR_Invoice_InvoiceNumber()
. User transaction, if any, will be rolled back.
The statement has been terminated.

--

Initially we had no transaction logic in the CLR trigger, since then we have tried placing a transaction around the contents of our CLR trigger (using sqlconnection.begintran, commit, etc) that seemed to have no effect.

Again what confuses me is commenting out that mapdown call in the replication update trigger immediately fixes the problem. So is there some issue with executing that statement when a CLR trigger is the source of the call of the update?

Jason

|||

Hi Jason,

This is actually a known issue with respect to the execution of extended procedure within SQLCLR. Unfortunately, the fix hasn't been fully approved for SP2 yet so I will attach your posting to the bug which will hopefully expedite the approval process. But since there really is no good workaround for your scenario (triggers are always executed in transaction context), you may want to push for a QFE by logging a bug @. http://lab.msdn.microsoft.com/productfeedback/, our internal reference number for this bug is SQLBU450991.

Thank you for reporting the issue.

-Raymond

|||

Thanks for the response. Can you please enlighten me on what the possible negative side effects of just commenting out the sp_mapdown_bitmap would be in one of the Replication triggers?

I sort of need a workaround for the mean time if possible...

|||

Hi Jason,

Since I am no merge replication expert, I can only imagine that commenting the call out will break merge replication. Ok, I should probably let the merge experts chime in on that. I do have this rather far-fetched idea of committing\starting transactions around the sp_mapdown_bitmap call in your trigger in an attempt to trick the server into thinking that the transaction balances out. How about making most of your trigger T-SQL and call CLR procs to do the things that you absolutely have to do in CLR?

-Raymond

|||

Jason,

sp_mapdown_bitmap is necessary to adjust the bitmap for updated columns when column id in the table is not continuous, e.g. there has been columns dropped from the table. Commenting out sp_mapdown_bitmap could lead to data non-convergence. It is not a valid workaround.

Will workaround mentioned by Raymond work for you?

Wanwen

|||

The transaction idea didnt seem to have any effect. And switching away from CLR would be a multi-week resolution to the problem, which would be hard to swallow...

If we are 100% sure there will be no added/removed columns, as a temporary workaround is commenting out that line not an option?

|||

The fact your update trigger is calling sp_mapdown_bitmap shows there is a need to do the map down adjustment. It could be because there has been dropped column, or table contains computed or timestamp column. You could run a simple query to find out:

select column_id from sys.columns where object_id = OBJECT_ID(<your_source_table>)

to see if column_id is continous from 1 to max column id

select * from sys.columns where object_id = OBJECT_ID(<your_source_table>) and (is_computed = 1 or system_type_id = type_id('timestamp'))

to see if you have computed column or timestamp column.

As I said before, commenting out calling sp_mapdown_bitmap in this case will lead to data non-convergence. We already brought up the issue and hopefully the bug could be fixed in SP2

|||

After running the queries, a "hole" did show up in the column id's in one of the tables causing the problem (there is no computed or timestamp columns). Is there anyway to close the gap or renumber the columns?

As much as I want to wait for SP2, we all know it could be quite awhile and we were really hoping to go live with the solution shortly...

|||

Jason,

You could

1.drop that article from your publication

2.run snapshot and merge agent if necessary

3.drop the physical tables from publisher and subscriber

4.recreate the table at publisher, make sure there are no holes in column id this time

5.add that article back to your publication

6.rerun snapshot and merge agent.

Or you could clean up your environment and start from beginning if that is more convenient for you.

Please let me know if you have any further questions.

Wanwen

|||Dear Raymond,

Please also see my post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=865299&SiteID=1&mode=1
since I am also running into a problem with CLR trigger nesting which seems related to the bug. Thanks in advance.

replication problems with structure/trigger/identity

Hello alltogether,
i'm working on a database-plattform which should work with
fail-over-technics.
Therefor 2 identic machines with internal RAID are available, which work
in single-host-netloadbalance on win2003-server.
Because of the internal RAID and caused by costs, a solution without
external SCSI-RAID and win2003-clustering-mechanism is looked for.
First i tried to use merge-replication but the rowguid-column, which is
added, makes the application unable to use the database (sql-statements
without column-specification). The application is bought and not easy
adaptable.
Second i tried transaction-replication, which seamed to work, but no
key- and identity-attributes are replicated. When the first machine now
stops and second gets master, there are problems caused by the missing
identities (auto-increment). I got errors caused by triggers too.
Manual copy of the structure with key- and identity-attributes and
triggers causes new problems when the replication is active (i.e. a
replication insert datas in a table with a trigger, the trigger on the
master takes effect, the replication tries to copy the effected datas,
but the trigger on the slave already work with the datas).
Actual way is a differential copy periodical from master to slave, but
is not nice and just a periodical actual slave.
Any ideas for more solutions?
PS: Sorry for X-Post, but i'm not sure, if there are "active" reader of
"m.p.s.r"
Thanks & Greets
Marko Damaschke
trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
student of applied computer science at Chemnitz University of Technology
Hello,
i got an answer from Paul Ibison via a website-based newsforum, but
don't want to get a profile there - so the answer here.
Thanks for the idea via Log Shipping but i see there a problem with the
state of standby-database. The database has to be in "NORECOVERY-Mode",
when i understood it correct, while the whole Log-Shipping-process.
But how to "trigger" it, when suddenly the primary machine crashes?
Somehow the recover-mode has to be started.
Write-access is necessary and without manual actions the db is read-only.
We need automated failover without manual administrator actions.
Thanks & Greets
Marko Damaschke
Trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
Student of applied computer science at Chemnitz University of Technology
|||Marko,
the only automatic failover is from clustering. Log-
shipping, replication each have a manual failover. SQL
Server 2005 has database mirroring, but this is a few
months away from release yet. There are 3rd party tools -
DataMirror, but I don't know offhand if these offer
automatic failover.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication or Update via Trigger ?

Hi SQL Gurus,
I have an application where the end user insist that there should be nine(9)
databases, same server within the system.
There are 'shared tables' on one of the database where any
insert/update/delete on those 'shared tables' must appear immediately on
other databases.
In this case, should I use replication or doing updates via Trigger ?
Thanks for your comments,
KristI am not sure why he/she is insisting for identical databases? Perhaps,
instead of creating tables in all these databases, you can opt to create
views which simply SELECT from a table in a single a database. You don't
have to worry about the data being in sync either.
Replication is not a solution for addressing such requirements. You can opt
for a trigger, but with the information from your post, I rather would
conclude it is an overkill to support redundant data for no apparent
reasons.
--
- Anith
( Please reply to newsgroups only )|||Hi Anith,
I used to think of View.
But I need to create FK from transaction table to these 'shared tables'
e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
and Area are 'shared tables'
How can I do this with view ?
Thanks,
Krist
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I am not sure why he/she is insisting for identical databases? Perhaps,
> instead of creating tables in all these databases, you can opt to create
> views which simply SELECT from a table in a single a database. You don't
> have to worry about the data being in sync either.
> Replication is not a solution for addressing such requirements. You can
opt
> for a trigger, but with the information from your post, I rather would
> conclude it is an overkill to support redundant data for no apparent
> reasons.
> --
> - Anith
> ( Please reply to newsgroups only )
>|||Krist, first find out the reason that your user needs 9 databases.
1. If is for security then perhaps create 9 different users or roles instead
and assign permissions accordingly.
2. If it's a limitation in the front end application then perhaps you'll
need to stick with several databases
3. If the user later wants to distribute the 9 databases to 9 different
servers then cross-database views become more tricky.
It's a rather strange requirement, and the underlying reason for it probably
constrains your options even further.
"tristant" <krislioe@.cbn.net.id> wrote in message
news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> Hi Anith,
> I used to think of View.
> But I need to create FK from transaction table to these 'shared tables'
> e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
> and Area are 'shared tables'
> How can I do this with view ?
> Thanks,
> Krist
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > I am not sure why he/she is insisting for identical databases? Perhaps,
> > instead of creating tables in all these databases, you can opt to create
> > views which simply SELECT from a table in a single a database. You don't
> > have to worry about the data being in sync either.
> >
> > Replication is not a solution for addressing such requirements. You can
> opt
> > for a trigger, but with the information from your post, I rather would
> > conclude it is an overkill to support redundant data for no apparent
> > reasons.
> >
> > --
> > - Anith
> > ( Please reply to newsgroups only )
> >
> >
>|||Hi Anthony,
You are absolutely right : the reason the number (3)
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
So, that is my reason, what options do I have now ?
Thanks,
Krist
"Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> Krist, first find out the reason that your user needs 9 databases.
> 1. If is for security then perhaps create 9 different users or roles
instead
> and assign permissions accordingly.
> 2. If it's a limitation in the front end application then perhaps you'll
> need to stick with several databases
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
> It's a rather strange requirement, and the underlying reason for it
probably
> constrains your options even further.
> "tristant" <krislioe@.cbn.net.id> wrote in message
> news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > Hi Anith,
> > I used to think of View.
> > But I need to create FK from transaction table to these 'shared tables'
> > e.g : SalesOrder must have FK to Salesman and Area table, where
Salesman
> > and Area are 'shared tables'
> >
> > How can I do this with view ?
> >
> > Thanks,
> > Krist
> >
> > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > I am not sure why he/she is insisting for identical databases?
Perhaps,
> > > instead of creating tables in all these databases, you can opt to
create
> > > views which simply SELECT from a table in a single a database. You
don't
> > > have to worry about the data being in sync either.
> > >
> > > Replication is not a solution for addressing such requirements. You
can
> > opt
> > > for a trigger, but with the information from your post, I rather would
> > > conclude it is an overkill to support redundant data for no apparent
> > > reasons.
> > >
> > > --
> > > - Anith
> > > ( Please reply to newsgroups only )
> > >
> > >
> >
> >
>|||Your options:
1. Partitioned views
2. Replication
3. Log shipping (backup & restore)
"tristant" <krislioe@.cbn.net.id> wrote in message
news:OQxaWDgxDHA.1760@.TK2MSFTNGP10.phx.gbl...
> Hi Anthony,
> You are absolutely right : the reason the number (3)
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> So, that is my reason, what options do I have now ?
> Thanks,
> Krist
> "Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
> news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> > Krist, first find out the reason that your user needs 9 databases.
> > 1. If is for security then perhaps create 9 different users or roles
> instead
> > and assign permissions accordingly.
> > 2. If it's a limitation in the front end application then perhaps you'll
> > need to stick with several databases
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> >
> > It's a rather strange requirement, and the underlying reason for it
> probably
> > constrains your options even further.
> >
> > "tristant" <krislioe@.cbn.net.id> wrote in message
> > news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > > Hi Anith,
> > > I used to think of View.
> > > But I need to create FK from transaction table to these 'shared
tables'
> > > e.g : SalesOrder must have FK to Salesman and Area table, where
> Salesman
> > > and Area are 'shared tables'
> > >
> > > How can I do this with view ?
> > >
> > > Thanks,
> > > Krist
> > >
> > > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > > I am not sure why he/she is insisting for identical databases?
> Perhaps,
> > > > instead of creating tables in all these databases, you can opt to
> create
> > > > views which simply SELECT from a table in a single a database. You
> don't
> > > > have to worry about the data being in sync either.
> > > >
> > > > Replication is not a solution for addressing such requirements. You
> can
> > > opt
> > > > for a trigger, but with the information from your post, I rather
would
> > > > conclude it is an overkill to support redundant data for no apparent
> > > > reasons.
> > > >
> > > > --
> > > > - Anith
> > > > ( Please reply to newsgroups only )
> > > >
> > > >
> > >
> > >
> >
> >
>

Replication of Triggers

If I create a trigger on a table that is already in a Trans Pub, will
the trigger be replicated?
IF so how can I prevent this?
I have different triggers that affect different tables on the
subscriber and the publisher.
TIA,
Larry...
No, the trigger won't get replicated.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Triggers will be replicated if a new snapshot is generated and you have
elected to replicate User Triggers and you have issued an
sp_refreshsubscriptions.
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
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1129581351.100366.214720@.g49g2000cwa.googlegr oups.com...
> If I create a trigger on a table that is already in a Trans Pub, will
> the trigger be replicated?
> IF so how can I prevent this?
> I have different triggers that affect different tables on the
> subscriber and the publisher.
> TIA,
> Larry...
>
|||thanks guys