Howdy all. I set up Immediate Updating replication on AdventureWorks on 2005
Person.Address and AddressType tables. When this type of replication is
created, a replication trigger is created on the Publisher. However, this
replication trigger fires off the other (User) trigger on the table when a
row is updated, and they continue to fire each other off. I caught all this
in Profiler so Im sure this is whats happening. Anyways, the following
message is then displayed:
Maximum Stored Proc, function, trigger, or view nesting level exceeded
(limit 32).
Here are the triggers:
ALTER trigger [Person].[sp_MSsync_upd_trig_Address_1] on [Person].[Address]
for update not for replication as
declare @.rc int
select @.rc = @.@.ROWCOUNT
if @.rc = 0 return
if update (msrepl_tran_version) return
update [Person].[Address] set msrepl_tran_version = newid() from
[Person].[Address], inserted
where [Person].[Address].[AddressID] = inserted.[AddressID]
ALTER TRIGGER [Person].[uAddress] ON [Person].[Address]
AFTER UPDATE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON;
UPDATE [Person].[Address]
SET [Person].[Address].[ModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[AddressID] = [Person].[Address].[AddressID];
END;
Someone must have encountered this before and have a workaround?
TIA, ChrisR
use set trigger order to have replication fire at the end or make your
triggers not for replication.
Can we see the table schema and triggers?
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
"ChrisR" <ChrisR@.foo.com> wrote in message
news:uN6YxlRMHHA.3556@.TK2MSFTNGP03.phx.gbl...
> Howdy all. I set up Immediate Updating replication on AdventureWorks on
> 2005 Person.Address and AddressType tables. When this type of replication
> is created, a replication trigger is created on the Publisher. However,
> this replication trigger fires off the other (User) trigger on the table
> when a row is updated, and they continue to fire each other off. I caught
> all this in Profiler so Im sure this is whats happening. Anyways, the
> following message is then displayed:
>
> Maximum Stored Proc, function, trigger, or view nesting level exceeded
> (limit 32).
>
> Here are the triggers:
>
> ALTER trigger [Person].[sp_MSsync_upd_trig_Address_1] on
> [Person].[Address] for update not for replication as
> declare @.rc int
> select @.rc = @.@.ROWCOUNT
>
> if @.rc = 0 return
> if update (msrepl_tran_version) return
> update [Person].[Address] set msrepl_tran_version = newid() from
> [Person].[Address], inserted
> where [Person].[Address].[AddressID] = inserted.[AddressID]
>
>
> ALTER TRIGGER [Person].[uAddress] ON [Person].[Address]
> AFTER UPDATE NOT FOR REPLICATION AS
> BEGIN
> SET NOCOUNT ON;
>
> UPDATE [Person].[Address]
> SET [Person].[Address].[ModifiedDate] = GETDATE()
> FROM inserted
> WHERE inserted.[AddressID] = [Person].[Address].[AddressID];
> END;
>
> Someone must have encountered this before and have a workaround?
>
>
> TIA, ChrisR
>
>
>
>
Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts
Friday, March 30, 2012
Replication triggers
Dear All,
the subscriber database contains no longer the (upd ,
ins , del) triggers of the replication.
How do i create them again , or do i need to drop the
subscription and do it all over again.
Please feedback urgently
Thanks in Advance
Dalia,
you should be able to script them out by creating a seaparate (fake)
subscription, and then apply them to the subscriber. I'd be a little
concerned that other details may be missing though and be tempted to
reinitialize. Also, I suspect that the excahnge type value is incorrect
which has caused this issue.
HTH,
Paul Ibison
|||I urge you to re-initialize your merge subscription(s), rerun your snapshot, and deploy it by rerunning your merge agent.
sql
the subscriber database contains no longer the (upd ,
ins , del) triggers of the replication.
How do i create them again , or do i need to drop the
subscription and do it all over again.
Please feedback urgently
Thanks in Advance
Dalia,
you should be able to script them out by creating a seaparate (fake)
subscription, and then apply them to the subscriber. I'd be a little
concerned that other details may be missing though and be tempted to
reinitialize. Also, I suspect that the excahnge type value is incorrect
which has caused this issue.
HTH,
Paul Ibison
|||I urge you to re-initialize your merge subscription(s), rerun your snapshot, and deploy it by rerunning your merge agent.
sql
replication triggers
Hi all:
Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
index 'uc1MSmerge_tombstone'.
The statement has been terminated.
that is the error i got when i try to delet a record from table which has
merge replication on.
i had a check that this error actually is from the trigger generated by
replication..
so any helps will be appreciated.
Thanks
Nick
"nick" schrieb:
> Hi all:
> Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
> index 'uc1MSmerge_tombstone'.
> The statement has been terminated.
> that is the error i got when i try to delet a record from table which has
> merge replication on.
> i had a check that this error actually is from the trigger generated by
> replication..
> so any helps will be appreciated.
> Thanks
> Nick
Run the merge agent and try again ...
Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
index 'uc1MSmerge_tombstone'.
The statement has been terminated.
that is the error i got when i try to delet a record from table which has
merge replication on.
i had a check that this error actually is from the trigger generated by
replication..
so any helps will be appreciated.
Thanks
Nick
"nick" schrieb:
> Hi all:
> Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
> index 'uc1MSmerge_tombstone'.
> The statement has been terminated.
> that is the error i got when i try to delet a record from table which has
> merge replication on.
> i had a check that this error actually is from the trigger generated by
> replication..
> so any helps will be appreciated.
> Thanks
> Nick
Run the merge agent and try again ...
Replication Triggers
Is there such a thing as a "Replication BLOB Trigger?"
Enover
I have never heard this term before, can you give us an example of what you
are talking about?
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
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:877A44BD-FFCE-4DE7-AF74-4D5B82072E6A@.microsoft.com...
> Is there such a thing as a "Replication BLOB Trigger?"
> --
> Enover
|||We are in the process of updating our software and trying to find ways to
reduce the update time. The vendor sent us an email stating that we need to
evaluate replication blob triggers and decide whether they need adjustment or
additions.
My first question (red-flag), was do we mess with replication triggers? If
we adjust the triggers, who would future hotfixes or Service Packs impact the
adjustments we made.
However, since I have never heard of such a term and could not find it any
documentation on Microsoft site, I thought I would ask. Do replication
triggers (update, delete , insert) treat ntext, text or image fields
different from other columns?
Enover
"Hilary Cotter" wrote:
> I have never heard this term before, can you give us an example of what you
> are talking about?
> --
> 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
> "Enover" <Enover@.discussions.microsoft.com> wrote in message
> news:877A44BD-FFCE-4DE7-AF74-4D5B82072E6A@.microsoft.com...
>
>
|||Press the vendor on what they are referring to. There is no such thing.
What they may be referring to is that certain replication types - queued,
immediate, and merge - do not handle text and image columns well.
For queued - From BOL - Queued Updating Considerations
The Subscriber cannot update or insert text or image values because they
cannot be read from the inserted or deleted tables inside the trigger.
Similarly, the Subscriber cannot update or insert text or image values using
WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher.
Instead, you could partition the text and image columns into a separate
table and modify the two tables within a transaction. Use merge replication
to synchronize these values. You cannot be assured there are no conflicts
because the update of the text or image table can occur if the data is not
well partitioned.
For immediate - From BOL - Immediate Updating Considerations
The Subscriber cannot update or insert text or image values because they
cannot be read from the inserted or deleted tables inside the trigger.
Similarly, the Subscriber cannot update or insert text or image values using
WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher.
Instead, you could partition the text and image columns into a separate
table and modify the two tables within a transaction. You could use merge
replication to synchronize these values if updates to text or image columns
are needed at the Subscriber. You can be assured there are no conflicts if
all updates follow this guideline because the update of the text or image
table cannot occur unless the main table was updated, which is protected by
2PC.
For merge - From BOL - Planning for Merge Replication
text and image Data Types in Merge Replication
Merge replication supports the replication of text, ntext, and image columns
only if they have been updated explicitly by an UPDATE statement because it
causes a trigger to fire that updates meta data ensuring that the
transaction gets propagated to other Subscribers.
Using only the WRITETEXT and UPDATETEXT operations will not propagate the
change to other sites. If your application uses WRITETEXT and UPDATETEXT to
update the text or ntext columns, explicitly add a dummy UPDATE statement
after the WRITETEXT or UPDATETEXT operations, within the same transaction,
to fire the trigger and thereby guarantee that the change will be propagated
to other sites.
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
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...[vbcol=seagreen]
> We are in the process of updating our software and trying to find ways to
> reduce the update time. The vendor sent us an email stating that we need
> to
> evaluate replication blob triggers and decide whether they need adjustment
> or
> additions.
> My first question (red-flag), was do we mess with replication triggers?
> If
> we adjust the triggers, who would future hotfixes or Service Packs impact
> the
> adjustments we made.
> However, since I have never heard of such a term and could not find it any
> documentation on Microsoft site, I thought I would ask. Do replication
> triggers (update, delete , insert) treat ntext, text or image fields
> different from other columns?
>
> --
> Enover
>
> "Hilary Cotter" wrote:
|||No, you do not want to "mess" with the triggers that replication creates.
That doesn't mean you can't do that, but you will not be supported if you
do.
Also, there is no such thing as replication blob triggers. Ask the vendor
give you an explicit example of what a replication blob trigger is including
sample code.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...[vbcol=seagreen]
> We are in the process of updating our software and trying to find ways to
> reduce the update time. The vendor sent us an email stating that we need
> to
> evaluate replication blob triggers and decide whether they need adjustment
> or
> additions.
> My first question (red-flag), was do we mess with replication triggers?
> If
> we adjust the triggers, who would future hotfixes or Service Packs impact
> the
> adjustments we made.
> However, since I have never heard of such a term and could not find it any
> documentation on Microsoft site, I thought I would ask. Do replication
> triggers (update, delete , insert) treat ntext, text or image fields
> different from other columns?
>
> --
> Enover
>
> "Hilary Cotter" wrote:
|||Thank you all for your help.
Enover
"Michael Hotek" wrote:
> No, you do not want to "mess" with the triggers that replication creates.
> That doesn't mean you can't do that, but you will not be supported if you
> do.
> Also, there is no such thing as replication blob triggers. Ask the vendor
> give you an explicit example of what a replication blob trigger is including
> sample code.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Enover" <Enover@.discussions.microsoft.com> wrote in message
> news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...
>
>
Enover
I have never heard this term before, can you give us an example of what you
are talking about?
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
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:877A44BD-FFCE-4DE7-AF74-4D5B82072E6A@.microsoft.com...
> Is there such a thing as a "Replication BLOB Trigger?"
> --
> Enover
|||We are in the process of updating our software and trying to find ways to
reduce the update time. The vendor sent us an email stating that we need to
evaluate replication blob triggers and decide whether they need adjustment or
additions.
My first question (red-flag), was do we mess with replication triggers? If
we adjust the triggers, who would future hotfixes or Service Packs impact the
adjustments we made.
However, since I have never heard of such a term and could not find it any
documentation on Microsoft site, I thought I would ask. Do replication
triggers (update, delete , insert) treat ntext, text or image fields
different from other columns?
Enover
"Hilary Cotter" wrote:
> I have never heard this term before, can you give us an example of what you
> are talking about?
> --
> 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
> "Enover" <Enover@.discussions.microsoft.com> wrote in message
> news:877A44BD-FFCE-4DE7-AF74-4D5B82072E6A@.microsoft.com...
>
>
|||Press the vendor on what they are referring to. There is no such thing.
What they may be referring to is that certain replication types - queued,
immediate, and merge - do not handle text and image columns well.
For queued - From BOL - Queued Updating Considerations
The Subscriber cannot update or insert text or image values because they
cannot be read from the inserted or deleted tables inside the trigger.
Similarly, the Subscriber cannot update or insert text or image values using
WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher.
Instead, you could partition the text and image columns into a separate
table and modify the two tables within a transaction. Use merge replication
to synchronize these values. You cannot be assured there are no conflicts
because the update of the text or image table can occur if the data is not
well partitioned.
For immediate - From BOL - Immediate Updating Considerations
The Subscriber cannot update or insert text or image values because they
cannot be read from the inserted or deleted tables inside the trigger.
Similarly, the Subscriber cannot update or insert text or image values using
WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher.
Instead, you could partition the text and image columns into a separate
table and modify the two tables within a transaction. You could use merge
replication to synchronize these values if updates to text or image columns
are needed at the Subscriber. You can be assured there are no conflicts if
all updates follow this guideline because the update of the text or image
table cannot occur unless the main table was updated, which is protected by
2PC.
For merge - From BOL - Planning for Merge Replication
text and image Data Types in Merge Replication
Merge replication supports the replication of text, ntext, and image columns
only if they have been updated explicitly by an UPDATE statement because it
causes a trigger to fire that updates meta data ensuring that the
transaction gets propagated to other Subscribers.
Using only the WRITETEXT and UPDATETEXT operations will not propagate the
change to other sites. If your application uses WRITETEXT and UPDATETEXT to
update the text or ntext columns, explicitly add a dummy UPDATE statement
after the WRITETEXT or UPDATETEXT operations, within the same transaction,
to fire the trigger and thereby guarantee that the change will be propagated
to other sites.
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
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...[vbcol=seagreen]
> We are in the process of updating our software and trying to find ways to
> reduce the update time. The vendor sent us an email stating that we need
> to
> evaluate replication blob triggers and decide whether they need adjustment
> or
> additions.
> My first question (red-flag), was do we mess with replication triggers?
> If
> we adjust the triggers, who would future hotfixes or Service Packs impact
> the
> adjustments we made.
> However, since I have never heard of such a term and could not find it any
> documentation on Microsoft site, I thought I would ask. Do replication
> triggers (update, delete , insert) treat ntext, text or image fields
> different from other columns?
>
> --
> Enover
>
> "Hilary Cotter" wrote:
|||No, you do not want to "mess" with the triggers that replication creates.
That doesn't mean you can't do that, but you will not be supported if you
do.
Also, there is no such thing as replication blob triggers. Ask the vendor
give you an explicit example of what a replication blob trigger is including
sample code.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...[vbcol=seagreen]
> We are in the process of updating our software and trying to find ways to
> reduce the update time. The vendor sent us an email stating that we need
> to
> evaluate replication blob triggers and decide whether they need adjustment
> or
> additions.
> My first question (red-flag), was do we mess with replication triggers?
> If
> we adjust the triggers, who would future hotfixes or Service Packs impact
> the
> adjustments we made.
> However, since I have never heard of such a term and could not find it any
> documentation on Microsoft site, I thought I would ask. Do replication
> triggers (update, delete , insert) treat ntext, text or image fields
> different from other columns?
>
> --
> Enover
>
> "Hilary Cotter" wrote:
|||Thank you all for your help.
Enover
"Michael Hotek" wrote:
> No, you do not want to "mess" with the triggers that replication creates.
> That doesn't mean you can't do that, but you will not be supported if you
> do.
> Also, there is no such thing as replication blob triggers. Ask the vendor
> give you an explicit example of what a replication blob trigger is including
> sample code.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Enover" <Enover@.discussions.microsoft.com> wrote in message
> news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...
>
>
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 
-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
-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 Transformation
I am currently trying to setup Transformation on a Transactional Publication.
When When I finish setting up all the transformation for the package and
click finish to save the DTS package I get the Message "SQL Server Enterprise
manager could not complete this operation." this message comes up when it is
in the "Adding task 1 of 1" phase in the Creating the DTS package for
replication wizard.
Any help would be greatly appriciated.
Thanks in advance.
Jeff Stokes
Hi Jeff:
Right click on Server name and click Properties. In the properties window,
click on Connections tab and check if "Enforce Distributed Transactions"
Checkbox is checked. If so, uncheck it and then recreate your publication.
HTH
"Jeff Stokes" wrote:
> I am currently trying to setup Transformation on a Transactional Publication.
> When When I finish setting up all the transformation for the package and
> click finish to save the DTS package I get the Message "SQL Server Enterprise
> manager could not complete this operation." this message comes up when it is
> in the "Adding task 1 of 1" phase in the Creating the DTS package for
> replication wizard.
> Any help would be greatly appriciated.
> Thanks in advance.
> Jeff Stokes
|||Mark,
Thanks for your response. I checked the server properties and "Enforce
Distributed Transactions" is unchecked. Any otherthoughts?
Jeff
Jeff Stokes
"Mark" wrote:
[vbcol=seagreen]
> Hi Jeff:
> Right click on Server name and click Properties. In the properties window,
> click on Connections tab and check if "Enforce Distributed Transactions"
> Checkbox is checked. If so, uncheck it and then recreate your publication.
> HTH
>
> "Jeff Stokes" wrote:
sql
When When I finish setting up all the transformation for the package and
click finish to save the DTS package I get the Message "SQL Server Enterprise
manager could not complete this operation." this message comes up when it is
in the "Adding task 1 of 1" phase in the Creating the DTS package for
replication wizard.
Any help would be greatly appriciated.
Thanks in advance.
Jeff Stokes
Hi Jeff:
Right click on Server name and click Properties. In the properties window,
click on Connections tab and check if "Enforce Distributed Transactions"
Checkbox is checked. If so, uncheck it and then recreate your publication.
HTH
"Jeff Stokes" wrote:
> I am currently trying to setup Transformation on a Transactional Publication.
> When When I finish setting up all the transformation for the package and
> click finish to save the DTS package I get the Message "SQL Server Enterprise
> manager could not complete this operation." this message comes up when it is
> in the "Adding task 1 of 1" phase in the Creating the DTS package for
> replication wizard.
> Any help would be greatly appriciated.
> Thanks in advance.
> Jeff Stokes
|||Mark,
Thanks for your response. I checked the server properties and "Enforce
Distributed Transactions" is unchecked. Any otherthoughts?
Jeff
Jeff Stokes
"Mark" wrote:
[vbcol=seagreen]
> Hi Jeff:
> Right click on Server name and click Properties. In the properties window,
> click on Connections tab and check if "Enforce Distributed Transactions"
> Checkbox is checked. If so, uncheck it and then recreate your publication.
> HTH
>
> "Jeff Stokes" wrote:
sql
Replication transcations
Hi All
I had stopped transactional replication for about 5 hours and now i have
restarted it. The replication is so slow. How can i calculate how many
transactions are left to be replicated? Both the log reader and distribution
agent are running although at times they time out.. I have tried to query the
table MSrepl_transcations but nothing makes sense to me.Thank you in advance.
query select * from distribution.dbo.msdistribution_status or execute
sp_browsereplcmds in the distribution database.
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
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:54BB99E9-1F10-4AE4-8747-3B99F27DE83B@.microsoft.com...
> Hi All
> I had stopped transactional replication for about 5 hours and now i have
> restarted it. The replication is so slow. How can i calculate how many
> transactions are left to be replicated? Both the log reader and
distribution
> agent are running although at times they time out.. I have tried to query
the
> table MSrepl_transcations but nothing makes sense to me.Thank you in
advance.
I had stopped transactional replication for about 5 hours and now i have
restarted it. The replication is so slow. How can i calculate how many
transactions are left to be replicated? Both the log reader and distribution
agent are running although at times they time out.. I have tried to query the
table MSrepl_transcations but nothing makes sense to me.Thank you in advance.
query select * from distribution.dbo.msdistribution_status or execute
sp_browsereplcmds in the distribution database.
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
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:54BB99E9-1F10-4AE4-8747-3B99F27DE83B@.microsoft.com...
> Hi All
> I had stopped transactional replication for about 5 hours and now i have
> restarted it. The replication is so slow. How can i calculate how many
> transactions are left to be replicated? Both the log reader and
distribution
> agent are running although at times they time out.. I have tried to query
the
> table MSrepl_transcations but nothing makes sense to me.Thank you in
advance.
replication transaction logs
Hello every body.
How can I find the tables or files where the replication log is stored.
I mean, I would like to know how the replication worked during the weekend
and what data insert, update or delete.
Thanks a lot, Lina
There is no log per se. However the commands are stored in the distribution
database, in the msrepl_commands table. You can read them using
sp_browsereplcmds.
However for named subscriptions these commands are removed every 10 minutes.
For anonymous subscriptions, these commands are removed by default every 2
days.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Lina Manjarres" <Lina Manjarres@.discussions.microsoft.com> wrote in message
news:642C6E7E-BC57-42E3-B7FE-E351E7A52859@.microsoft.com...
> Hello every body.
> How can I find the tables or files where the replication log is stored.
> I mean, I would like to know how the replication worked during the weekend
> and what data insert, update or delete.
> Thanks a lot, Lina
|||Lina,
you might also want to examine the contents of the _history tables in the
distribution database.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks a lot!
"Hilary Cotter" wrote:
> There is no log per se. However the commands are stored in the distribution
> database, in the msrepl_commands table. You can read them using
> sp_browsereplcmds.
> However for named subscriptions these commands are removed every 10 minutes.
> For anonymous subscriptions, these commands are removed by default every 2
> days.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Lina Manjarres" <Lina Manjarres@.discussions.microsoft.com> wrote in message
> news:642C6E7E-BC57-42E3-B7FE-E351E7A52859@.microsoft.com...
>
>
|||Thank you!
"Paul Ibison" wrote:
> Lina,
> you might also want to examine the contents of the _history tables in the
> distribution database.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
How can I find the tables or files where the replication log is stored.
I mean, I would like to know how the replication worked during the weekend
and what data insert, update or delete.
Thanks a lot, Lina
There is no log per se. However the commands are stored in the distribution
database, in the msrepl_commands table. You can read them using
sp_browsereplcmds.
However for named subscriptions these commands are removed every 10 minutes.
For anonymous subscriptions, these commands are removed by default every 2
days.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Lina Manjarres" <Lina Manjarres@.discussions.microsoft.com> wrote in message
news:642C6E7E-BC57-42E3-B7FE-E351E7A52859@.microsoft.com...
> Hello every body.
> How can I find the tables or files where the replication log is stored.
> I mean, I would like to know how the replication worked during the weekend
> and what data insert, update or delete.
> Thanks a lot, Lina
|||Lina,
you might also want to examine the contents of the _history tables in the
distribution database.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks a lot!
"Hilary Cotter" wrote:
> There is no log per se. However the commands are stored in the distribution
> database, in the msrepl_commands table. You can read them using
> sp_browsereplcmds.
> However for named subscriptions these commands are removed every 10 minutes.
> For anonymous subscriptions, these commands are removed by default every 2
> days.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Lina Manjarres" <Lina Manjarres@.discussions.microsoft.com> wrote in message
> news:642C6E7E-BC57-42E3-B7FE-E351E7A52859@.microsoft.com...
>
>
|||Thank you!
"Paul Ibison" wrote:
> Lina,
> you might also want to examine the contents of the _history tables in the
> distribution database.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Replication Training
We are a software development company that is beginning to incorporate
replication into two major projects. We would like to have someone help
us with replication consulting/training/troubleshooting help.
Does anyone know someone who can and would do this? Hillary? Paul?
Others?
Paul Barbin
DBA
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
If you are interested in someone in the States contact me offline. Paul is
in Europe, although he may be willing to travel.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"PBarbin" <pbarbin@.nospam.com> wrote in message
news:O2ZXEaaxEHA.2620@.TK2MSFTNGP10.phx.gbl...
> We are a software development company that is beginning to incorporate
> replication into two major projects. We would like to have someone help
> us with replication consulting/training/troubleshooting help.
> Does anyone know someone who can and would do this? Hillary? Paul?
> Others?
> Paul Barbin
> DBA
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hey Hillary, I see your book came out TODAY! Great. I'll be ordering
it shortly!! I thought the project had stalled (for good).
I noticed that he'd have to hop across the pond to consult here. I was
hoping you'd reply. Sounds stupid, but I'm not sure how to contact you.
Please contact me at:
pbarbin@.ledontspamwis.com
(remove 'dontspam' from the address)
Thanks.
Paul
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
replication into two major projects. We would like to have someone help
us with replication consulting/training/troubleshooting help.
Does anyone know someone who can and would do this? Hillary? Paul?
Others?
Paul Barbin
DBA
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
If you are interested in someone in the States contact me offline. Paul is
in Europe, although he may be willing to travel.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"PBarbin" <pbarbin@.nospam.com> wrote in message
news:O2ZXEaaxEHA.2620@.TK2MSFTNGP10.phx.gbl...
> We are a software development company that is beginning to incorporate
> replication into two major projects. We would like to have someone help
> us with replication consulting/training/troubleshooting help.
> Does anyone know someone who can and would do this? Hillary? Paul?
> Others?
> Paul Barbin
> DBA
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hey Hillary, I see your book came out TODAY! Great. I'll be ordering
it shortly!! I thought the project had stalled (for good).
I noticed that he'd have to hop across the pond to consult here. I was
hoping you'd reply. Sounds stupid, but I'm not sure how to contact you.
Please contact me at:
pbarbin@.ledontspamwis.com
(remove 'dontspam' from the address)
Thanks.
Paul
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Replication Topology Question
I have three locations each with multiple SQL 2000 databases that need to
stay in constant sync with the other locations. Which is the best type of
replication? Merge Replication or 2-way Transactional Replication?
TIA,
Kevin
Probably peer to peer in SQL 2005. However bi-directional transactional
would probably be best. Keep in mind you are locked into a schema with this.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:e4PRqHGTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>I have three locations each with multiple SQL 2000 databases that need to
>stay in constant sync with the other locations. Which is the best type of
>replication? Merge Replication or 2-way Transactional Replication?
> TIA,
> Kevin
>
|||Schema issues are the reason I posted this question in the first place. In
my test scenario, I have 3 servers doing merge replication back and forth to
each other. Each server subscribes to the other two. Everything was working
fine until I tried to add a column for testing. I got the following error
message:
Schema replication failed because database X on server X is not the original
Publisher of table
Any ideas?
Kevin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uMWCuhGTHHA.5016@.TK2MSFTNGP05.phx.gbl...
> Probably peer to peer in SQL 2005. However bi-directional transactional
> would probably be best. Keep in mind you are locked into a schema with
> this.
> --
> 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
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:e4PRqHGTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>
|||Do you have a hierarchy? IE Server A publishes to Server B which publishes
to Server C? Is so you must make the schema change at the top of the
hierarchy.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:emdknlGTHHA.2256@.TK2MSFTNGP02.phx.gbl...
> Schema issues are the reason I posted this question in the first place. In
> my test scenario, I have 3 servers doing merge replication back and forth
> to each other. Each server subscribes to the other two. Everything was
> working fine until I tried to add a column for testing. I got the
> following error message:
> Schema replication failed because database X on server X is not the
> original Publisher of table
> Any ideas?
> Kevin
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uMWCuhGTHHA.5016@.TK2MSFTNGP05.phx.gbl...
>
|||I do not have a heirarchy. The topology I would like to use is a sort of
triangle, with no one server being the master and all transactions shared.
Thanks,
kevin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OXSz4XHTHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Do you have a hierarchy? IE Server A publishes to Server B which publishes
> to Server C? Is so you must make the schema change at the top of the
> hierarchy.
> --
> 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
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:emdknlGTHHA.2256@.TK2MSFTNGP02.phx.gbl...
>
|||You are talking about a multi-master topology which merge replication does
not support. You will need to use peer-to-peer in SQL Server 2005 for this.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:eemppbHTHHA.1036@.TK2MSFTNGP03.phx.gbl...
>I do not have a heirarchy. The topology I would like to use is a sort of
>triangle, with no one server being the master and all transactions shared.
> Thanks,
> kevin
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OXSz4XHTHHA.1200@.TK2MSFTNGP02.phx.gbl...
>
|||Kevin - just checking why you don't set up one merge publisher and 2 merge
subscribers. Is it because we don't have alternative sync partners anymore
and you can't guarantee connectivity with the publisher?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Yes Paul, I need sort of a triangle setup with each node working
independently and then sharing information with the other two.
Hilary, is Peer-to-Peer replication available in 2005 Standard? I took a
quick look at the replication options on our 2005 boxes on Friday and didn't
see anything about Peer-to-Peer.
Kevin
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e3pPvcITHHA.4756@.TK2MSFTNGP06.phx.gbl...
> Kevin - just checking why you don't set up one merge publisher and 2 merge
> subscribers. Is it because we don't have alternative sync partners anymore
> and you can't guarantee connectivity with the publisher?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||It is only available on the Enterprise Editions of SQL Server 2005.
You will be able to get it working using pure bi-directional transactional
replication but will probably have to do some filtering to get it to work
correctly.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:e08ed4qTHHA.600@.TK2MSFTNGP05.phx.gbl...
> Yes Paul, I need sort of a triangle setup with each node working
> independently and then sharing information with the other two.
> Hilary, is Peer-to-Peer replication available in 2005 Standard? I took a
> quick look at the replication options on our 2005 boxes on Friday and
> didn't see anything about Peer-to-Peer.
> Kevin
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:e3pPvcITHHA.4756@.TK2MSFTNGP06.phx.gbl...
>
|||Kevin,
what you are proposing is not in conflict with the merge setup, as you
mention the data is exchanges with the other 2 nodes. If this is a
prerequisite, then the connectivity must exist and the merge setup remains
an option. It doesn't perform any where near as well as peer to peer (only
enterpriose as you mentioned
http://msdn2.microsoft.com/en-us/library/ms143761.aspx) , but has the
advantage of easily adapting to schema changes and conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
stay in constant sync with the other locations. Which is the best type of
replication? Merge Replication or 2-way Transactional Replication?
TIA,
Kevin
Probably peer to peer in SQL 2005. However bi-directional transactional
would probably be best. Keep in mind you are locked into a schema with this.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:e4PRqHGTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>I have three locations each with multiple SQL 2000 databases that need to
>stay in constant sync with the other locations. Which is the best type of
>replication? Merge Replication or 2-way Transactional Replication?
> TIA,
> Kevin
>
|||Schema issues are the reason I posted this question in the first place. In
my test scenario, I have 3 servers doing merge replication back and forth to
each other. Each server subscribes to the other two. Everything was working
fine until I tried to add a column for testing. I got the following error
message:
Schema replication failed because database X on server X is not the original
Publisher of table
Any ideas?
Kevin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uMWCuhGTHHA.5016@.TK2MSFTNGP05.phx.gbl...
> Probably peer to peer in SQL 2005. However bi-directional transactional
> would probably be best. Keep in mind you are locked into a schema with
> this.
> --
> 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
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:e4PRqHGTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>
|||Do you have a hierarchy? IE Server A publishes to Server B which publishes
to Server C? Is so you must make the schema change at the top of the
hierarchy.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:emdknlGTHHA.2256@.TK2MSFTNGP02.phx.gbl...
> Schema issues are the reason I posted this question in the first place. In
> my test scenario, I have 3 servers doing merge replication back and forth
> to each other. Each server subscribes to the other two. Everything was
> working fine until I tried to add a column for testing. I got the
> following error message:
> Schema replication failed because database X on server X is not the
> original Publisher of table
> Any ideas?
> Kevin
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uMWCuhGTHHA.5016@.TK2MSFTNGP05.phx.gbl...
>
|||I do not have a heirarchy. The topology I would like to use is a sort of
triangle, with no one server being the master and all transactions shared.
Thanks,
kevin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OXSz4XHTHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Do you have a hierarchy? IE Server A publishes to Server B which publishes
> to Server C? Is so you must make the schema change at the top of the
> hierarchy.
> --
> 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
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:emdknlGTHHA.2256@.TK2MSFTNGP02.phx.gbl...
>
|||You are talking about a multi-master topology which merge replication does
not support. You will need to use peer-to-peer in SQL Server 2005 for this.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:eemppbHTHHA.1036@.TK2MSFTNGP03.phx.gbl...
>I do not have a heirarchy. The topology I would like to use is a sort of
>triangle, with no one server being the master and all transactions shared.
> Thanks,
> kevin
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OXSz4XHTHHA.1200@.TK2MSFTNGP02.phx.gbl...
>
|||Kevin - just checking why you don't set up one merge publisher and 2 merge
subscribers. Is it because we don't have alternative sync partners anymore
and you can't guarantee connectivity with the publisher?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Yes Paul, I need sort of a triangle setup with each node working
independently and then sharing information with the other two.
Hilary, is Peer-to-Peer replication available in 2005 Standard? I took a
quick look at the replication options on our 2005 boxes on Friday and didn't
see anything about Peer-to-Peer.
Kevin
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e3pPvcITHHA.4756@.TK2MSFTNGP06.phx.gbl...
> Kevin - just checking why you don't set up one merge publisher and 2 merge
> subscribers. Is it because we don't have alternative sync partners anymore
> and you can't guarantee connectivity with the publisher?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||It is only available on the Enterprise Editions of SQL Server 2005.
You will be able to get it working using pure bi-directional transactional
replication but will probably have to do some filtering to get it to work
correctly.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:e08ed4qTHHA.600@.TK2MSFTNGP05.phx.gbl...
> Yes Paul, I need sort of a triangle setup with each node working
> independently and then sharing information with the other two.
> Hilary, is Peer-to-Peer replication available in 2005 Standard? I took a
> quick look at the replication options on our 2005 boxes on Friday and
> didn't see anything about Peer-to-Peer.
> Kevin
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:e3pPvcITHHA.4756@.TK2MSFTNGP06.phx.gbl...
>
|||Kevin,
what you are proposing is not in conflict with the merge setup, as you
mention the data is exchanges with the other 2 nodes. If this is a
prerequisite, then the connectivity must exist and the merge setup remains
an option. It doesn't perform any where near as well as peer to peer (only
enterpriose as you mentioned
http://msdn2.microsoft.com/en-us/library/ms143761.aspx) , but has the
advantage of easily adapting to schema changes and conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
replication tool
Is there any tool, commercial, freeware, by MS or something thats easy to
build that does synchronization of a SQL server database with an MA Access
database?
Now the schemas of both these databases are the same. But the data in them
can be added/edited and deleted independently.
Synchronization can happen manually when someone kicks it off. Need not be
real-time.
Thanks!
Girish
Hi Girish,
I have noticed you posted this issue in Newsgroup:
microsoft.public.sqlserver.replication. I will add a reply to that
newsgroup and closely monitor it if you have follow-up questions
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Girish,
I have noticed you posted this issue in Newsgroup:
microsoft.public.sqlserver.replication. I will add a reply to that
newsgroup and closely monitor it if you have follow-up questions
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
sql
build that does synchronization of a SQL server database with an MA Access
database?
Now the schemas of both these databases are the same. But the data in them
can be added/edited and deleted independently.
Synchronization can happen manually when someone kicks it off. Need not be
real-time.
Thanks!
Girish
Hi Girish,
I have noticed you posted this issue in Newsgroup:
microsoft.public.sqlserver.replication. I will add a reply to that
newsgroup and closely monitor it if you have follow-up questions
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Girish,
I have noticed you posted this issue in Newsgroup:
microsoft.public.sqlserver.replication. I will add a reply to that
newsgroup and closely monitor it if you have follow-up questions
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
sql
replication tool
Hi all,
does someone know any third party tool to manage MSSQLServer replication?
TIA
bruno reiter
Some of the monitoring tools out there (Quest, Idera, BMC, etc) have some
features which allow you to monitor replication, and replication jobs. They
don't allow you to create them.
You might want to look at these.
Exactly what features were you looking for? Easy Publication/Subscription
creating, Monitoring, Health monitoring, troubleshooting, etc.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"bruno reiter" <remove.this.br33@.bol.com.br> wrote in message
news:OJLUR2V$EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> does someone know any third party tool to manage MSSQLServer replication?
> TIA
> bruno reiter
>
|||sorry a little late ;-)
looking for monitoring, visualization (topology), manage (start/stop
some/any at one time), visual stats simple/consolidate
one big problem is refresh for large repl and SEM freeze!
br
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#O2eSqW$EHA.612@.TK2MSFTNGP09.phx.gbl...
> Some of the monitoring tools out there (Quest, Idera, BMC, etc) have some
> features which allow you to monitor replication, and replication jobs.
They[vbcol=seagreen]
> don't allow you to create them.
> You might want to look at these.
> Exactly what features were you looking for? Easy Publication/Subscription
> creating, Monitoring, Health monitoring, troubleshooting, etc.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "bruno reiter" <remove.this.br33@.bol.com.br> wrote in message
> news:OJLUR2V$EHA.2600@.TK2MSFTNGP09.phx.gbl...
replication?
>
does someone know any third party tool to manage MSSQLServer replication?
TIA
bruno reiter
Some of the monitoring tools out there (Quest, Idera, BMC, etc) have some
features which allow you to monitor replication, and replication jobs. They
don't allow you to create them.
You might want to look at these.
Exactly what features were you looking for? Easy Publication/Subscription
creating, Monitoring, Health monitoring, troubleshooting, etc.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"bruno reiter" <remove.this.br33@.bol.com.br> wrote in message
news:OJLUR2V$EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> does someone know any third party tool to manage MSSQLServer replication?
> TIA
> bruno reiter
>
|||sorry a little late ;-)
looking for monitoring, visualization (topology), manage (start/stop
some/any at one time), visual stats simple/consolidate
one big problem is refresh for large repl and SEM freeze!
br
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#O2eSqW$EHA.612@.TK2MSFTNGP09.phx.gbl...
> Some of the monitoring tools out there (Quest, Idera, BMC, etc) have some
> features which allow you to monitor replication, and replication jobs.
They[vbcol=seagreen]
> don't allow you to create them.
> You might want to look at these.
> Exactly what features were you looking for? Easy Publication/Subscription
> creating, Monitoring, Health monitoring, troubleshooting, etc.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "bruno reiter" <remove.this.br33@.bol.com.br> wrote in message
> news:OJLUR2V$EHA.2600@.TK2MSFTNGP09.phx.gbl...
replication?
>
Replication to trusted domain - naming convention problem?
We have a domain, say OUR_DOMAIN. Within this I replication running happily
for several months from MYSQLSERVER . We now have another "trusted" domain
at a co-location, OTHERDOMAIN.
To see a machine from the other I need to use MYSQLSERVER.OUR_DOMAIN.
However, when I set up a pull replication from the OTHERDOMAIN machine, say
MYSQLSUB, the task fails trying to connect to MYSQLSERVER, even though I
refer to it as MYSQLSERVER.OUR_DOMAIN.
The Pull subscription is created properly, and I go to the job list and run
the job. The step is
-Publisher MYSQLSERVER-PublisherDB [PublishDB] -Publication
[MyPubName] -Distributor [MYSQLSERVER] -SubscriptionType 1 -Subscriber
[MYSQLSUB] -SubscriberSecurityMode 1 -SubscriberDB [MyDBName]
However it is unable to connect, The process could not connect to
Distributor 'MYSQLSERVER'.
If I change the job to be publisher MYSQLSERVER.OUR_DOMAIN then I get the
error "The process could not retrieve security information from the
Subscriber for Distributor 'MYSQLSERVER'. The step failed." Which suggests
adding the domain to the publisher worked. However if I add the domain to
the distributor
-Publisher MYSQLSERVER.OUR_DOMAIN-PublisherDB [PublishDB] -Publication
[MyPubName] -Distributor [MYSQLSERVER.OUR_DOMAIN] -SubscriptionType
1 -Subscriber [MYSQLSUB] -SubscriberSecurityMode 1 -SubscriberDB [MyDBName]
I get "The process could not retrieve security information from the
Subscriber for Distributor MYSQLSERVER.OUR_DOMAIN'. The step failed."
Both agents are running under administrator level accounts on their
respective domains, and the subscriber account is in the Publication Access
List.
Any help with naming conventions used here or any resources or pointers
would be greatly appreciated.
Cheers!
Simon
I prefer to use NetBIOS names to register the servers in EM. Then I hide the
FQDN or the IP address of the server using Client Network Utility. I then
use a single account in both domains with the same password to connect to
both server and I also use this account to run the SQL Server agent account
under.
So on OUR_Domain the account would be SimonCar and the password could be
Racnomis. On OtherDomain the account would be SimonCar and the password
could be Racnomis. Another option is to use local machine accounts with the
same name and password.
A third option is to use SQL Server authentication.
One final point is that a patch has changed the way agent security works.
You must now run the agents with the SA job owner.
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
"Simon" <simoncar@.NotThisBittotalise.co.uk> wrote in message
news:%23LmtjUb8FHA.3132@.TK2MSFTNGP12.phx.gbl...
> We have a domain, say OUR_DOMAIN. Within this I replication running
> happily for several months from MYSQLSERVER . We now have another
> "trusted" domain at a co-location, OTHERDOMAIN.
> To see a machine from the other I need to use MYSQLSERVER.OUR_DOMAIN.
> However, when I set up a pull replication from the OTHERDOMAIN machine,
> say MYSQLSUB, the task fails trying to connect to MYSQLSERVER, even
> though I refer to it as MYSQLSERVER.OUR_DOMAIN.
> The Pull subscription is created properly, and I go to the job list and
> run the job. The step is
> -Publisher MYSQLSERVER-PublisherDB [PublishDB] -Publication
> [MyPubName] -Distributor [MYSQLSERVER] -SubscriptionType 1 -Subscriber
> [MYSQLSUB] -SubscriberSecurityMode 1 -SubscriberDB [MyDBName]
> However it is unable to connect, The process could not connect to
> Distributor 'MYSQLSERVER'.
> If I change the job to be publisher MYSQLSERVER.OUR_DOMAIN then I get the
> error "The process could not retrieve security information from the
> Subscriber for Distributor 'MYSQLSERVER'. The step failed." Which
> suggests adding the domain to the publisher worked. However if I add the
> domain to the distributor
> -Publisher MYSQLSERVER.OUR_DOMAIN-PublisherDB [PublishDB] -Publication
> [MyPubName] -Distributor [MYSQLSERVER.OUR_DOMAIN] -SubscriptionType
> 1 -Subscriber [MYSQLSUB] -SubscriberSecurityMode 1 -SubscriberDB
> [MyDBName]
> I get "The process could not retrieve security information from the
> Subscriber for Distributor MYSQLSERVER.OUR_DOMAIN'. The step failed."
> Both agents are running under administrator level accounts on their
> respective domains, and the subscriber account is in the Publication
> Access List.
> Any help with naming conventions used here or any resources or pointers
> would be greatly appreciated.
> Cheers!
> Simon
>
>
|||Hilary Cotter wrote:
> I prefer to use NetBIOS names to register the servers in EM. Then I
> hide the FQDN or the IP address of the server using Client Network
> Utility. I then use a single account in both domains with the same
> password to connect to both server and I also use this account to run
> the SQL Server agent account under.
> So on OUR_Domain the account would be SimonCar and the password could
> be Racnomis. On OtherDomain the account would be SimonCar and the
> password could be Racnomis. Another option is to use local machine
> accounts with the same name and password.
> A third option is to use SQL Server authentication.
>
The network guy has now set it so I can use just <servername>. I have set
the ReplData to be a share and point to that from the subscriber (it has
permissions to this share). The <Otherservername> is in the list of
subscribers but SQL claims it isn't from <Otherservername> when I request a
Pull subscription, but if I allow anonymous I can get it replicating fine.
The only downside of that I am aware of is the retention of replicated
transactions if I allow anonymous?
The same user/password seems simple enough for me to appreciate!
Thanks for your help.
Cheers!
Simon
|||> The <Otherservername> is in the
> list of subscribers but SQL claims it isn't from <Otherservername>
> when I request a Pull subscription, but if I allow anonymous I can
> get it replicating fine. The only downside of that I am aware of is
> the retention of replicated transactions if I allow anonymous?
There is some connectivity/authentication issue still here becuause if I add
the subscriber to the publisher from a copy of EM on the subscriber it
works, if I add the subscriber to subscribers through EM from my machine or
the publisher it doesn't recognise it even though the same machine name is
used.
for several months from MYSQLSERVER . We now have another "trusted" domain
at a co-location, OTHERDOMAIN.
To see a machine from the other I need to use MYSQLSERVER.OUR_DOMAIN.
However, when I set up a pull replication from the OTHERDOMAIN machine, say
MYSQLSUB, the task fails trying to connect to MYSQLSERVER, even though I
refer to it as MYSQLSERVER.OUR_DOMAIN.
The Pull subscription is created properly, and I go to the job list and run
the job. The step is
-Publisher MYSQLSERVER-PublisherDB [PublishDB] -Publication
[MyPubName] -Distributor [MYSQLSERVER] -SubscriptionType 1 -Subscriber
[MYSQLSUB] -SubscriberSecurityMode 1 -SubscriberDB [MyDBName]
However it is unable to connect, The process could not connect to
Distributor 'MYSQLSERVER'.
If I change the job to be publisher MYSQLSERVER.OUR_DOMAIN then I get the
error "The process could not retrieve security information from the
Subscriber for Distributor 'MYSQLSERVER'. The step failed." Which suggests
adding the domain to the publisher worked. However if I add the domain to
the distributor
-Publisher MYSQLSERVER.OUR_DOMAIN-PublisherDB [PublishDB] -Publication
[MyPubName] -Distributor [MYSQLSERVER.OUR_DOMAIN] -SubscriptionType
1 -Subscriber [MYSQLSUB] -SubscriberSecurityMode 1 -SubscriberDB [MyDBName]
I get "The process could not retrieve security information from the
Subscriber for Distributor MYSQLSERVER.OUR_DOMAIN'. The step failed."
Both agents are running under administrator level accounts on their
respective domains, and the subscriber account is in the Publication Access
List.
Any help with naming conventions used here or any resources or pointers
would be greatly appreciated.
Cheers!
Simon
I prefer to use NetBIOS names to register the servers in EM. Then I hide the
FQDN or the IP address of the server using Client Network Utility. I then
use a single account in both domains with the same password to connect to
both server and I also use this account to run the SQL Server agent account
under.
So on OUR_Domain the account would be SimonCar and the password could be
Racnomis. On OtherDomain the account would be SimonCar and the password
could be Racnomis. Another option is to use local machine accounts with the
same name and password.
A third option is to use SQL Server authentication.
One final point is that a patch has changed the way agent security works.
You must now run the agents with the SA job owner.
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
"Simon" <simoncar@.NotThisBittotalise.co.uk> wrote in message
news:%23LmtjUb8FHA.3132@.TK2MSFTNGP12.phx.gbl...
> We have a domain, say OUR_DOMAIN. Within this I replication running
> happily for several months from MYSQLSERVER . We now have another
> "trusted" domain at a co-location, OTHERDOMAIN.
> To see a machine from the other I need to use MYSQLSERVER.OUR_DOMAIN.
> However, when I set up a pull replication from the OTHERDOMAIN machine,
> say MYSQLSUB, the task fails trying to connect to MYSQLSERVER, even
> though I refer to it as MYSQLSERVER.OUR_DOMAIN.
> The Pull subscription is created properly, and I go to the job list and
> run the job. The step is
> -Publisher MYSQLSERVER-PublisherDB [PublishDB] -Publication
> [MyPubName] -Distributor [MYSQLSERVER] -SubscriptionType 1 -Subscriber
> [MYSQLSUB] -SubscriberSecurityMode 1 -SubscriberDB [MyDBName]
> However it is unable to connect, The process could not connect to
> Distributor 'MYSQLSERVER'.
> If I change the job to be publisher MYSQLSERVER.OUR_DOMAIN then I get the
> error "The process could not retrieve security information from the
> Subscriber for Distributor 'MYSQLSERVER'. The step failed." Which
> suggests adding the domain to the publisher worked. However if I add the
> domain to the distributor
> -Publisher MYSQLSERVER.OUR_DOMAIN-PublisherDB [PublishDB] -Publication
> [MyPubName] -Distributor [MYSQLSERVER.OUR_DOMAIN] -SubscriptionType
> 1 -Subscriber [MYSQLSUB] -SubscriberSecurityMode 1 -SubscriberDB
> [MyDBName]
> I get "The process could not retrieve security information from the
> Subscriber for Distributor MYSQLSERVER.OUR_DOMAIN'. The step failed."
> Both agents are running under administrator level accounts on their
> respective domains, and the subscriber account is in the Publication
> Access List.
> Any help with naming conventions used here or any resources or pointers
> would be greatly appreciated.
> Cheers!
> Simon
>
>
|||Hilary Cotter wrote:
> I prefer to use NetBIOS names to register the servers in EM. Then I
> hide the FQDN or the IP address of the server using Client Network
> Utility. I then use a single account in both domains with the same
> password to connect to both server and I also use this account to run
> the SQL Server agent account under.
> So on OUR_Domain the account would be SimonCar and the password could
> be Racnomis. On OtherDomain the account would be SimonCar and the
> password could be Racnomis. Another option is to use local machine
> accounts with the same name and password.
> A third option is to use SQL Server authentication.
>
The network guy has now set it so I can use just <servername>. I have set
the ReplData to be a share and point to that from the subscriber (it has
permissions to this share). The <Otherservername> is in the list of
subscribers but SQL claims it isn't from <Otherservername> when I request a
Pull subscription, but if I allow anonymous I can get it replicating fine.
The only downside of that I am aware of is the retention of replicated
transactions if I allow anonymous?
The same user/password seems simple enough for me to appreciate!
Thanks for your help.
Cheers!
Simon
|||> The <Otherservername> is in the
> list of subscribers but SQL claims it isn't from <Otherservername>
> when I request a Pull subscription, but if I allow anonymous I can
> get it replicating fine. The only downside of that I am aware of is
> the retention of replicated transactions if I allow anonymous?
There is some connectivity/authentication issue still here becuause if I add
the subscriber to the publisher from a copy of EM on the subscriber it
works, if I add the subscriber to subscribers through EM from my machine or
the publisher it doesn't recognise it even though the same machine name is
used.
Replication to the Non Default File Group
Hi,
I have a snapshot replication set up on a SQL 2K Server publishing to other SQL 2K servers. The publisher database has mutliple file groups - which is structured the same as the subscriber databases. The snapshot has been set to delete and recreate the tables on the subscribers - this is a performance decision as the tables are large and this is the better approach. The tables reside in their own file groups (but not the default). I want the replication agents to recreate these tables in the original file groups and not the default.
Is there a method within Replication to specify which file group to recreate the table in so it doesn't fill up the default file group?
Thanks in advance,
Pete
Hi Pete,
The tables on the subscriber will only be created on the default filegroup. The options are either to not drop tables and use delete instead or modify the default filegroup of the destination databases.
You can create a job that does the following:
1. sets the default filegroup of the subscriber database to secondary filegroup.
2. Agent starts and does the job.
3. Once agent job has finished and call a new job that will set the filegroup backup to primary.
In SQL 2005 you have the option to specify the scriptts that you want to execute before and after the snapshot is applied.
Jag
|||There is a third option. After the snapshot has been created, edit the script files on the distributor which hold the CREATE TABLE commands and correct them to use the correct file groups.
You can also setup a T/SQL script which can be run after the snapshot has been delivered. Write a script which rebuilds the clustered indexes of the tables on the correct file group. By rebuilding the clustered index and moving it to another file group you will move the tables to that file group as well.
replication to sybase 12.5
Where can I find a good document detailing how to setup replication from ms
sql 2005 to Sybase?
Great...
I need a live feed of inserts updates and deletes from a MS SQL 2005 table
to a Sybase table, do you have a general idea how I can accomplish this with
SSIS?
Thx!
"Paul Ibison" wrote:
> As far as I know, only Oracle and DB2 are supported
> (http://technet.microsoft.com/en-us/library/ms151864(SQL.90).aspx). Could
> use SSIS and factor your own solution instead.
> HTH,
> Paul Ibison
>
>
|||I would not use ssis for this. You can create a subscription in SQL Server,
and then use sp_browsereplcmds to get the stored procedure calls and then
run them on the sybase server. You can pipe the results of sp_browsereplcmds
to a results table and select the tranaction IDs you want to stop and start
at.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:44200793-FAA0-4F75-93F5-AD3671FBFD13@.microsoft.com...[vbcol=seagreen]
> Great...
> I need a live feed of inserts updates and deletes from a MS SQL 2005 table
> to a Sybase table, do you have a general idea how I can accomplish this
> with
> SSIS?
> Thx!
> "Paul Ibison" wrote:
|||So this will give allow me to perform transactional replication to a Sybase
server table?
"Hilary Cotter" wrote:
> I would not use ssis for this. You can create a subscription in SQL Server,
> and then use sp_browsereplcmds to get the stored procedure calls and then
> run them on the sybase server. You can pipe the results of sp_browsereplcmds
> to a results table and select the tranaction IDs you want to stop and start
> at.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:44200793-FAA0-4F75-93F5-AD3671FBFD13@.microsoft.com...
>
>
|||Sort of. If you capture these statements they can be run on a sybase server.
It is possible to programmatically do this.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:B54DE9D7-9E2A-48D1-A843-492AF75A40C9@.microsoft.com...[vbcol=seagreen]
> So this will give allow me to perform transactional replication to a
> Sybase
> server table?
>
> "Hilary Cotter" wrote:
|||Can you provide a bit more detail of how I could accomplish this
programmatically.
"Hilary Cotter" wrote:
> Sort of. If you capture these statements they can be run on a sybase server.
> It is possible to programmatically do this.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:B54DE9D7-9E2A-48D1-A843-492AF75A40C9@.microsoft.com...
>
>
|||here is an example:
--create the table status
--create table status(pk int not null identity primary key, minxact_seqno
varbinary(16), maxxact_seqno varbinary(16))
declare @.maxxact_seqno varbinary(16)
declare @.minxact_seqno varbinary(16)
select @.minxact_seqno =maxxact_seqno from status
if @.@.rowcount=0
select @.minxact_seqno =xact_seqno From msrepl_transactions order by
entry_time
select @.maxxact_seqno =xact_seqno From msrepl_transactions order by
entry_time desc
print @.minxact_seqno
print @.maxxact_seqno
exec sp_browsereplcmds @.minxact_seqno ,@.maxxact_seqno , @.results_table
='MyComamnds'
insert into status (maxxact_seqno, minxact_seqno ) values(@.maxxact_seqno,
@.minxact_seqno )
go
--now what you do is read through the table MyCommands and apply these
commands on Sybase, you can use ado or a linked server for this
--when you have read everything drop the table MyCommands and repeat this
job.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1E733A3A-B91F-492D-920E-52D414A89E80@.microsoft.com...[vbcol=seagreen]
> Can you provide a bit more detail of how I could accomplish this
> programmatically.
> "Hilary Cotter" wrote:
|||I'm back...with more questions
1st
I'm thinking that:
@.minxact_seqno ,@.maxxact_seqno
should read:
@.xact_seqno_start, @.xact_seqno_end
yes/no?
2nd
so it won't really be transactional replication, it'll be a scheduled job?
3rd
how will the distribution database get cleared of it's transactions, once
they've been applied to the sybase server. what do i use as a subscriber?
4th
I'm familiar with setting up linked servers,
what's the best way to read through the table and apply the transactions?
BTW, Thx a mill for your time and help!
"Hilary Cotter" wrote:
> here is an example:
> --create the table status
> --create table status(pk int not null identity primary key, minxact_seqno
> varbinary(16), maxxact_seqno varbinary(16))
> declare @.maxxact_seqno varbinary(16)
> declare @.minxact_seqno varbinary(16)
> select @.minxact_seqno =maxxact_seqno from status
> if @.@.rowcount=0
> select @.minxact_seqno =xact_seqno From msrepl_transactions order by
> entry_time
> select @.maxxact_seqno =xact_seqno From msrepl_transactions order by
> entry_time desc
> print @.minxact_seqno
> print @.maxxact_seqno
> exec sp_browsereplcmds @.minxact_seqno ,@.maxxact_seqno , @.results_table
> ='MyComamnds'
> insert into status (maxxact_seqno, minxact_seqno ) values(@.maxxact_seqno,
> @.minxact_seqno )
> go
> --now what you do is read through the table MyCommands and apply these
> commands on Sybase, you can use ado or a linked server for this
> --when you have read everything drop the table MyCommands and repeat this
> job.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1E733A3A-B91F-492D-920E-52D414A89E80@.microsoft.com...
>
>
|||Answers inline.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:A3B3337C-DEDD-4811-97D9-DBC1A4051684@.microsoft.com...
> I'm back...with more questions
> 1st
> I'm thinking that:
> @.minxact_seqno ,@.maxxact_seqno
> should read:
> @.xact_seqno_start, @.xact_seqno_end
> yes/no?
>
OK, but the naming convention I use implies the maxiumn one last collected
and the current maxiumn. Its all a naming convention - whatever works for
you
> 2nd
> so it won't really be transactional replication, it'll be a scheduled job?
It will be transactional replication to a dummy subscriber. You will need to
create some scheduled jobs to read the commands and then replay them on
Sybase.
> 3rd
> how will the distribution database get cleared of it's transactions, once
> they've been applied to the sybase server. what do i use as a subscriber?
Create a dummy database - a plain vanilla one on the publisher and
replicated there. Delete rows from it on a schedule to keep it small and
then set your min retention to 6 hours or so, a time interval which will
allow you to collect all the commands. The distribution clean up job will
remove them from the distribution database. But it doesn't know if they have
been applied on Sybase yet, so you will have to make sure you collect them
before they are deleted.
> 4th
> I'm familiar with setting up linked servers,
> what's the best way to read through the table and apply the transactions?
You will have to implement some sort of tracking column. Perhaps an tinyint
or int col with a default of 0, as you apply the command delete it from the
table. You can also create tables for each day or hour and then read them
until all the commands are applied and then drop them.
> BTW, Thx a mill for your time and help!
Your welcome[vbcol=seagreen]
> "Hilary Cotter" wrote:
|||Thanks for the comments, the picture is much clearer now.
I've made some progress.
1. I've installed the Sybase drivers
2. I've setup the oledb connection and successfully retrieved data from the
Sybase tables using the linked server.
I've now hit a snag and need your expertise.
The parameters for sp_browsereplcmds are no longer the same.
The @.results_table is no longer a parameter.
What's the alternative in 2005 to accomplish the same thing?
"Hilary Cotter" wrote:
> Answers inline.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:A3B3337C-DEDD-4811-97D9-DBC1A4051684@.microsoft.com...
> OK, but the naming convention I use implies the maxiumn one last collected
> and the current maxiumn. Its all a naming convention - whatever works for
> you
> It will be transactional replication to a dummy subscriber. You will need to
> create some scheduled jobs to read the commands and then replay them on
> Sybase.
> Create a dummy database - a plain vanilla one on the publisher and
> replicated there. Delete rows from it on a schedule to keep it small and
> then set your min retention to 6 hours or so, a time interval which will
> allow you to collect all the commands. The distribution clean up job will
> remove them from the distribution database. But it doesn't know if they have
> been applied on Sybase yet, so you will have to make sure you collect them
> before they are deleted.
> You will have to implement some sort of tracking column. Perhaps an tinyint
> or int col with a default of 0, as you apply the command delete it from the
> table. You can also create tables for each day or hour and then read them
> until all the commands are applied and then drop them.
> Your welcome
>
>
sql 2005 to Sybase?
Great...
I need a live feed of inserts updates and deletes from a MS SQL 2005 table
to a Sybase table, do you have a general idea how I can accomplish this with
SSIS?
Thx!
"Paul Ibison" wrote:
> As far as I know, only Oracle and DB2 are supported
> (http://technet.microsoft.com/en-us/library/ms151864(SQL.90).aspx). Could
> use SSIS and factor your own solution instead.
> HTH,
> Paul Ibison
>
>
|||I would not use ssis for this. You can create a subscription in SQL Server,
and then use sp_browsereplcmds to get the stored procedure calls and then
run them on the sybase server. You can pipe the results of sp_browsereplcmds
to a results table and select the tranaction IDs you want to stop and start
at.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:44200793-FAA0-4F75-93F5-AD3671FBFD13@.microsoft.com...[vbcol=seagreen]
> Great...
> I need a live feed of inserts updates and deletes from a MS SQL 2005 table
> to a Sybase table, do you have a general idea how I can accomplish this
> with
> SSIS?
> Thx!
> "Paul Ibison" wrote:
|||So this will give allow me to perform transactional replication to a Sybase
server table?
"Hilary Cotter" wrote:
> I would not use ssis for this. You can create a subscription in SQL Server,
> and then use sp_browsereplcmds to get the stored procedure calls and then
> run them on the sybase server. You can pipe the results of sp_browsereplcmds
> to a results table and select the tranaction IDs you want to stop and start
> at.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:44200793-FAA0-4F75-93F5-AD3671FBFD13@.microsoft.com...
>
>
|||Sort of. If you capture these statements they can be run on a sybase server.
It is possible to programmatically do this.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:B54DE9D7-9E2A-48D1-A843-492AF75A40C9@.microsoft.com...[vbcol=seagreen]
> So this will give allow me to perform transactional replication to a
> Sybase
> server table?
>
> "Hilary Cotter" wrote:
|||Can you provide a bit more detail of how I could accomplish this
programmatically.
"Hilary Cotter" wrote:
> Sort of. If you capture these statements they can be run on a sybase server.
> It is possible to programmatically do this.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:B54DE9D7-9E2A-48D1-A843-492AF75A40C9@.microsoft.com...
>
>
|||here is an example:
--create the table status
--create table status(pk int not null identity primary key, minxact_seqno
varbinary(16), maxxact_seqno varbinary(16))
declare @.maxxact_seqno varbinary(16)
declare @.minxact_seqno varbinary(16)
select @.minxact_seqno =maxxact_seqno from status
if @.@.rowcount=0
select @.minxact_seqno =xact_seqno From msrepl_transactions order by
entry_time
select @.maxxact_seqno =xact_seqno From msrepl_transactions order by
entry_time desc
print @.minxact_seqno
print @.maxxact_seqno
exec sp_browsereplcmds @.minxact_seqno ,@.maxxact_seqno , @.results_table
='MyComamnds'
insert into status (maxxact_seqno, minxact_seqno ) values(@.maxxact_seqno,
@.minxact_seqno )
go
--now what you do is read through the table MyCommands and apply these
commands on Sybase, you can use ado or a linked server for this
--when you have read everything drop the table MyCommands and repeat this
job.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1E733A3A-B91F-492D-920E-52D414A89E80@.microsoft.com...[vbcol=seagreen]
> Can you provide a bit more detail of how I could accomplish this
> programmatically.
> "Hilary Cotter" wrote:
|||I'm back...with more questions
1st
I'm thinking that:
@.minxact_seqno ,@.maxxact_seqno
should read:
@.xact_seqno_start, @.xact_seqno_end
yes/no?
2nd
so it won't really be transactional replication, it'll be a scheduled job?
3rd
how will the distribution database get cleared of it's transactions, once
they've been applied to the sybase server. what do i use as a subscriber?
4th
I'm familiar with setting up linked servers,
what's the best way to read through the table and apply the transactions?
BTW, Thx a mill for your time and help!
"Hilary Cotter" wrote:
> here is an example:
> --create the table status
> --create table status(pk int not null identity primary key, minxact_seqno
> varbinary(16), maxxact_seqno varbinary(16))
> declare @.maxxact_seqno varbinary(16)
> declare @.minxact_seqno varbinary(16)
> select @.minxact_seqno =maxxact_seqno from status
> if @.@.rowcount=0
> select @.minxact_seqno =xact_seqno From msrepl_transactions order by
> entry_time
> select @.maxxact_seqno =xact_seqno From msrepl_transactions order by
> entry_time desc
> print @.minxact_seqno
> print @.maxxact_seqno
> exec sp_browsereplcmds @.minxact_seqno ,@.maxxact_seqno , @.results_table
> ='MyComamnds'
> insert into status (maxxact_seqno, minxact_seqno ) values(@.maxxact_seqno,
> @.minxact_seqno )
> go
> --now what you do is read through the table MyCommands and apply these
> commands on Sybase, you can use ado or a linked server for this
> --when you have read everything drop the table MyCommands and repeat this
> job.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1E733A3A-B91F-492D-920E-52D414A89E80@.microsoft.com...
>
>
|||Answers inline.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:A3B3337C-DEDD-4811-97D9-DBC1A4051684@.microsoft.com...
> I'm back...with more questions
> 1st
> I'm thinking that:
> @.minxact_seqno ,@.maxxact_seqno
> should read:
> @.xact_seqno_start, @.xact_seqno_end
> yes/no?
>
OK, but the naming convention I use implies the maxiumn one last collected
and the current maxiumn. Its all a naming convention - whatever works for
you
> 2nd
> so it won't really be transactional replication, it'll be a scheduled job?
It will be transactional replication to a dummy subscriber. You will need to
create some scheduled jobs to read the commands and then replay them on
Sybase.
> 3rd
> how will the distribution database get cleared of it's transactions, once
> they've been applied to the sybase server. what do i use as a subscriber?
Create a dummy database - a plain vanilla one on the publisher and
replicated there. Delete rows from it on a schedule to keep it small and
then set your min retention to 6 hours or so, a time interval which will
allow you to collect all the commands. The distribution clean up job will
remove them from the distribution database. But it doesn't know if they have
been applied on Sybase yet, so you will have to make sure you collect them
before they are deleted.
> 4th
> I'm familiar with setting up linked servers,
> what's the best way to read through the table and apply the transactions?
You will have to implement some sort of tracking column. Perhaps an tinyint
or int col with a default of 0, as you apply the command delete it from the
table. You can also create tables for each day or hour and then read them
until all the commands are applied and then drop them.
> BTW, Thx a mill for your time and help!
Your welcome
[vbcol=seagreen]> "Hilary Cotter" wrote:
|||Thanks for the comments, the picture is much clearer now.
I've made some progress.
1. I've installed the Sybase drivers
2. I've setup the oledb connection and successfully retrieved data from the
Sybase tables using the linked server.
I've now hit a snag and need your expertise.
The parameters for sp_browsereplcmds are no longer the same.
The @.results_table is no longer a parameter.
What's the alternative in 2005 to accomplish the same thing?
"Hilary Cotter" wrote:
> Answers inline.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:A3B3337C-DEDD-4811-97D9-DBC1A4051684@.microsoft.com...
> OK, but the naming convention I use implies the maxiumn one last collected
> and the current maxiumn. Its all a naming convention - whatever works for
> you
> It will be transactional replication to a dummy subscriber. You will need to
> create some scheduled jobs to read the commands and then replay them on
> Sybase.
> Create a dummy database - a plain vanilla one on the publisher and
> replicated there. Delete rows from it on a schedule to keep it small and
> then set your min retention to 6 hours or so, a time interval which will
> allow you to collect all the commands. The distribution clean up job will
> remove them from the distribution database. But it doesn't know if they have
> been applied on Sybase yet, so you will have to make sure you collect them
> before they are deleted.
> You will have to implement some sort of tracking column. Perhaps an tinyint
> or int col with a default of 0, as you apply the command delete it from the
> table. You can also create tables for each day or hour and then read them
> until all the commands are applied and then drop them.
> Your welcome
>
>
replication to sql server by activeX
Hello
i have a an application developed in vb6.0 and back end sql server 2000.
i have a batch file that i created to replicate the data from the sql server
2000 on my pc(local) to replicate on another sql server 2000 which is found
on another machine and the batch file is working fine.
i would like to implement it in my VB application.That is the user will
have a button on the program and on click this will do the replication that
is being done by the batch file for the moment.
i can call the batch file but we would like to do it by activeX.
Is there any activeX that already exist?
how can i do this by using activeX in VB6.0
If you need additional info please let me know..
Thx a lot
Nazeedah
try this:
http://www.indexserverfaq.com/replic...excontrols.zip
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
"Nazeedah" <Nazeedah@.discussions.microsoft.com> wrote in message
news:53D7A4A9-EBEB-4F76-AF9B-5EF670EBC707@.microsoft.com...
> Hello
> i have a an application developed in vb6.0 and back end sql server 2000.
> i have a batch file that i created to replicate the data from the sql
> server
> 2000 on my pc(local) to replicate on another sql server 2000 which is
> found
> on another machine and the batch file is working fine.
> i would like to implement it in my VB application.That is the user will
> have a button on the program and on click this will do the replication
> that
> is being done by the batch file for the moment.
> i can call the batch file but we would like to do it by activeX.
> Is there any activeX that already exist?
> how can i do this by using activeX in VB6.0
> If you need additional info please let me know..
> Thx a lot
> Nazeedah
>
|||hello
Thank you for the link. its very interesting but i am bit lost in all the
code. could you please tell me which one is more specific in my case
Thank you a lot
"Nazeedah" wrote:
> Hello
> i have a an application developed in vb6.0 and back end sql server 2000.
> i have a batch file that i created to replicate the data from the sql server
> 2000 on my pc(local) to replicate on another sql server 2000 which is found
> on another machine and the batch file is working fine.
> i would like to implement it in my VB application.That is the user will
> have a button on the program and on click this will do the replication that
> is being done by the batch file for the moment.
> i can call the batch file but we would like to do it by activeX.
> Is there any activeX that already exist?
> how can i do this by using activeX in VB6.0
> If you need additional info please let me know..
> Thx a lot
> Nazeedah
>
|||its code sample 5
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
"Nazeedah" <Nazeedah@.discussions.microsoft.com> wrote in message
news:BEC8AF2F-7EEB-4AF4-B269-7BDF5917BEA7@.microsoft.com...[vbcol=seagreen]
> hello
> Thank you for the link. its very interesting but i am bit lost in all the
> code. could you please tell me which one is more specific in my case
> Thank you a lot
> "Nazeedah" wrote:
sql
i have a an application developed in vb6.0 and back end sql server 2000.
i have a batch file that i created to replicate the data from the sql server
2000 on my pc(local) to replicate on another sql server 2000 which is found
on another machine and the batch file is working fine.
i would like to implement it in my VB application.That is the user will
have a button on the program and on click this will do the replication that
is being done by the batch file for the moment.
i can call the batch file but we would like to do it by activeX.
Is there any activeX that already exist?
how can i do this by using activeX in VB6.0
If you need additional info please let me know..
Thx a lot
Nazeedah
try this:
http://www.indexserverfaq.com/replic...excontrols.zip
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
"Nazeedah" <Nazeedah@.discussions.microsoft.com> wrote in message
news:53D7A4A9-EBEB-4F76-AF9B-5EF670EBC707@.microsoft.com...
> Hello
> i have a an application developed in vb6.0 and back end sql server 2000.
> i have a batch file that i created to replicate the data from the sql
> server
> 2000 on my pc(local) to replicate on another sql server 2000 which is
> found
> on another machine and the batch file is working fine.
> i would like to implement it in my VB application.That is the user will
> have a button on the program and on click this will do the replication
> that
> is being done by the batch file for the moment.
> i can call the batch file but we would like to do it by activeX.
> Is there any activeX that already exist?
> how can i do this by using activeX in VB6.0
> If you need additional info please let me know..
> Thx a lot
> Nazeedah
>
|||hello
Thank you for the link. its very interesting but i am bit lost in all the
code. could you please tell me which one is more specific in my case
Thank you a lot
"Nazeedah" wrote:
> Hello
> i have a an application developed in vb6.0 and back end sql server 2000.
> i have a batch file that i created to replicate the data from the sql server
> 2000 on my pc(local) to replicate on another sql server 2000 which is found
> on another machine and the batch file is working fine.
> i would like to implement it in my VB application.That is the user will
> have a button on the program and on click this will do the replication that
> is being done by the batch file for the moment.
> i can call the batch file but we would like to do it by activeX.
> Is there any activeX that already exist?
> how can i do this by using activeX in VB6.0
> If you need additional info please let me know..
> Thx a lot
> Nazeedah
>
|||its code sample 5
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
"Nazeedah" <Nazeedah@.discussions.microsoft.com> wrote in message
news:BEC8AF2F-7EEB-4AF4-B269-7BDF5917BEA7@.microsoft.com...[vbcol=seagreen]
> hello
> Thank you for the link. its very interesting but i am bit lost in all the
> code. could you please tell me which one is more specific in my case
> Thank you a lot
> "Nazeedah" wrote:
sql
Replication to SQL Server 2005 Express
Hello.
I have a SQL Server 2005 Enterprise Edition with a database. I want to make
a merge replication of that database to some machines with SQL Server 2005
Express Edition's installed. I have created a merge replication publishment
and if I make a local subscription on the SQL Server 2005 Enterprise Edition
everything works great. But if I make a subscription on a SQL Server 2005
Express Edition which is in the same network with the publisher, the
databases do not synchronize (if I make a pull subscription I have an
'Uninitialized subscripition' warning in the Replication Monitor and if I
make a push subscription the status is 'Synchronizing' but my data is not
replicated).
I tried to do things like enabling remote access to the SQL Express server
and disabling the firewall on that machine, but it still doesn't work. What
should I do?
Thank you in advance,
Stefan Filip
Run profiler on the subscriber/express machine to find out where it is
getting stuck.
http://www.zetainteractive.com - Shift Happens!
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
"Stefan Filip" <StefanFilip@.discussions.microsoft.com> wrote in message
news:2BA863F6-637D-4D0D-8AB2-8FB97EE8AD59@.microsoft.com...
> Hello.
> I have a SQL Server 2005 Enterprise Edition with a database. I want to
> make
> a merge replication of that database to some machines with SQL Server 2005
> Express Edition's installed. I have created a merge replication
> publishment
> and if I make a local subscription on the SQL Server 2005 Enterprise
> Edition
> everything works great. But if I make a subscription on a SQL Server 2005
> Express Edition which is in the same network with the publisher, the
> databases do not synchronize (if I make a pull subscription I have an
> 'Uninitialized subscripition' warning in the Replication Monitor and if I
> make a push subscription the status is 'Synchronizing' but my data is not
> replicated).
> I tried to do things like enabling remote access to the SQL Express server
> and disabling the firewall on that machine, but it still doesn't work.
> What
> should I do?
> Thank you in advance,
> Stefan Filip
I have a SQL Server 2005 Enterprise Edition with a database. I want to make
a merge replication of that database to some machines with SQL Server 2005
Express Edition's installed. I have created a merge replication publishment
and if I make a local subscription on the SQL Server 2005 Enterprise Edition
everything works great. But if I make a subscription on a SQL Server 2005
Express Edition which is in the same network with the publisher, the
databases do not synchronize (if I make a pull subscription I have an
'Uninitialized subscripition' warning in the Replication Monitor and if I
make a push subscription the status is 'Synchronizing' but my data is not
replicated).
I tried to do things like enabling remote access to the SQL Express server
and disabling the firewall on that machine, but it still doesn't work. What
should I do?
Thank you in advance,
Stefan Filip
Run profiler on the subscriber/express machine to find out where it is
getting stuck.
http://www.zetainteractive.com - Shift Happens!
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
"Stefan Filip" <StefanFilip@.discussions.microsoft.com> wrote in message
news:2BA863F6-637D-4D0D-8AB2-8FB97EE8AD59@.microsoft.com...
> Hello.
> I have a SQL Server 2005 Enterprise Edition with a database. I want to
> make
> a merge replication of that database to some machines with SQL Server 2005
> Express Edition's installed. I have created a merge replication
> publishment
> and if I make a local subscription on the SQL Server 2005 Enterprise
> Edition
> everything works great. But if I make a subscription on a SQL Server 2005
> Express Edition which is in the same network with the publisher, the
> databases do not synchronize (if I make a pull subscription I have an
> 'Uninitialized subscripition' warning in the Replication Monitor and if I
> make a push subscription the status is 'Synchronizing' but my data is not
> replicated).
> I tried to do things like enabling remote access to the SQL Express server
> and disabling the firewall on that machine, but it still doesn't work.
> What
> should I do?
> Thank you in advance,
> Stefan Filip
Replication to SQL 2005
Hello all,
We have merge replication setup between 3 servers. SQL1, SQL2 and SQL3.
We have installed SQL2005 on a ReportingServer and want to be able to
popluate the database on SQL2005 with data from SQL1. How can we set this up?
We want to avoide setting up the whole replication process again as the
database is 25 GB and SQL3 is updated via a telephone line.
Thanks
Regards
Ishan
Ishan,
if the ReportingServer is RO apart from the replication synchronization, I'd
use transactional replication. I'd also consider dong a nosync
initialization to set things up with a database of this size.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
We have merge replication setup between 3 servers. SQL1, SQL2 and SQL3.
We have installed SQL2005 on a ReportingServer and want to be able to
popluate the database on SQL2005 with data from SQL1. How can we set this up?
We want to avoide setting up the whole replication process again as the
database is 25 GB and SQL3 is updated via a telephone line.
Thanks
Regards
Ishan
Ishan,
if the ReportingServer is RO apart from the replication synchronization, I'd
use transactional replication. I'd also consider dong a nosync
initialization to set things up with a database of this size.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Replication to server with different Port
I have a SQL server running on a remote location that has a different port
enabled to connect other than the default one, which means that when I want
to register I have to specify the port number as well eg.
Server1\InstanceName,4001 .
I am trying to carry out merge replication between the server and the MSDE
on my box. I have set up the pubisher, registered the SQL Server from
Enterprise Manager on my machine and on setting up the subscriber I am able
to view the publication in the list of registered servers. Creating the
subscription works fine, but when I try to sync , I get the error 'The
process could not connect to Distributor 'Server1\InstanceName' . I feel that
this is because of the port since it is not mentioned in the message and SQL
is trying to use the default port. Is there some workaround for this?
Jax,
for a named instance, there is no default port - it is assigned at creation
from the list of available ports, and your port number won't necessarily be
the same as mine, for the first named instance. Usually the port number is
not specified in the replication definition, and it is dynamically picked up
(hence the slammer virus on UDP, port 1434). Do you have 1434 blocked? If
not, then please try designing without the port number and see if this
works.
Also, The SQL Server Agent service (SQLServerAgent) at the client should not
use the LocalSystem account. It needs to use a standard domain account.
Finally, please check that the agents use impersonation.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
enabled to connect other than the default one, which means that when I want
to register I have to specify the port number as well eg.
Server1\InstanceName,4001 .
I am trying to carry out merge replication between the server and the MSDE
on my box. I have set up the pubisher, registered the SQL Server from
Enterprise Manager on my machine and on setting up the subscriber I am able
to view the publication in the list of registered servers. Creating the
subscription works fine, but when I try to sync , I get the error 'The
process could not connect to Distributor 'Server1\InstanceName' . I feel that
this is because of the port since it is not mentioned in the message and SQL
is trying to use the default port. Is there some workaround for this?
Jax,
for a named instance, there is no default port - it is assigned at creation
from the list of available ports, and your port number won't necessarily be
the same as mine, for the first named instance. Usually the port number is
not specified in the replication definition, and it is dynamically picked up
(hence the slammer virus on UDP, port 1434
). Do you have 1434 blocked? Ifnot, then please try designing without the port number and see if this
works.
Also, The SQL Server Agent service (SQLServerAgent) at the client should not
use the LocalSystem account. It needs to use a standard domain account.
Finally, please check that the agents use impersonation.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Subscribe to:
Posts (Atom)