Replication in SQL: March 2012

Friday, March 30, 2012

Replication Triggers on replicated tables.

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
>
>
>
>

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

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 ...

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...
>
>

Replication Trigger Problem (SQL 2005): Assistance Please

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

Here is the problem:

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

execute sp_mapdown_bitmap 0x0000000040, @.bm output

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

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

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

Additional notes on the environment:

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

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

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

Any assistance would be greatly appreciated.

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

|||

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

execute sp_mapdown_bitmap 0x0000000040, @.bm output

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

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

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

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

Thanks

Wanwen

|||

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

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

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

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

Jason

|||

Hi Jason,

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

Thank you for reporting the issue.

-Raymond

|||

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

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

|||

Hi Jason,

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

-Raymond

|||

Jason,

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

Will workaround mentioned by Raymond work for you?

Wanwen

|||

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

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

|||

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

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

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

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

to see if you have computed column or timestamp column.

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

|||

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

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

|||

Jason,

You could

1.drop that article from your publication

2.run snapshot and merge agent if necessary

3.drop the physical tables from publisher and subscriber

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

5.add that article back to your publication

6.rerun snapshot and merge agent.

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

Please let me know if you have any further questions.

Wanwen

|||Dear Raymond,

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

Replication Trigger Problem (SQL 2005): Assistance Please

Here is the problem:

execute sp_mapdown_bitmap 0x0000000040, @.bm output

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.

|||

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

|||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

|||

Jason

|||

Hi Jason,

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,

-Raymond

|||

Jason,

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?

|||

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

|||

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,

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

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.

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)
>
>

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 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

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

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?
>

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.

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

>
>

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

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

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)

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)

replication to new server

Hi
I have replication set up and working correctly. However we now have a new
SQL Server and want to set up replication to there(instead of the existing
server). Do we have to go through setting up the whole replication process
again or is there an easier way?
Thanks
I would start from scratch... Server names, etc are stored in the
registry... It is often better to be clean...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:5BB5248E-F8E2-45A7-8F04-9383C423DD16@.microsoft.com...
> Hi
> I have replication set up and working correctly. However we now have a new
> SQL Server and want to set up replication to there(instead of the existing
> server). Do we have to go through setting up the whole replication process
> again or is there an easier way?
> Thanks
|||Ok Thanks for the advice
"Wayne Snyder" wrote:

> I would start from scratch... Server names, etc are stored in the
> registry... It is often better to be clean...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:5BB5248E-F8E2-45A7-8F04-9383C423DD16@.microsoft.com...
>
>
|||Jonjo,
when you say 'set up replication to there' do you mean add a subscription?
If so then adding the subscription is much like any other subscription, and
EM can generate the scripts of an existing subscription to be used as a
template if you like - just change the servername using find and replace and
then run the script on the subscriber. If you mean you want to set up the
second server as a publisher/distributor and have the same publications,
then it depends on the complexity. If it's a few simple publications then
I'd start from scratch. If it's more complex, and you haven't documented
changes to custom profiles, post-snapshot filepaths etc then I'd script out
and edit the script before running it on the new publisher to be sure of
having a replica, and it'll also be much faster.
HTH,
Paul Ibison (SQL Server MVP)

replication to new server

Hi
I have replication set up and working correctly. However we now have a new
SQL Server and want to set up replication to there(instead of the existing
server). Do we have to go through setting up the whole replication process
again or is there an easier way?
ThanksI would start from scratch... Server names, etc are stored in the
registry... It is often better to be clean...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:5BB5248E-F8E2-45A7-8F04-9383C423DD16@.microsoft.com...
> Hi
> I have replication set up and working correctly. However we now have a new
> SQL Server and want to set up replication to there(instead of the existing
> server). Do we have to go through setting up the whole replication process
> again or is there an easier way?
> Thanks|||Ok Thanks for the advice
"Wayne Snyder" wrote:
> I would start from scratch... Server names, etc are stored in the
> registry... It is often better to be clean...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:5BB5248E-F8E2-45A7-8F04-9383C423DD16@.microsoft.com...
> > Hi
> >
> > I have replication set up and working correctly. However we now have a new
> > SQL Server and want to set up replication to there(instead of the existing
> > server). Do we have to go through setting up the whole replication process
> > again or is there an easier way?
> >
> > Thanks
>
>|||Jonjo,
when you say 'set up replication to there' do you mean add a subscription?
If so then adding the subscription is much like any other subscription, and
EM can generate the scripts of an existing subscription to be used as a
template if you like - just change the servername using find and replace and
then run the script on the subscriber. If you mean you want to set up the
second server as a publisher/distributor and have the same publications,
then it depends on the complexity. If it's a few simple publications then
I'd start from scratch. If it's more complex, and you haven't documented
changes to custom profiles, post-snapshot filepaths etc then I'd script out
and edit the script before running it on the new publisher to be sure of
having a replica, and it'll also be much faster.
HTH,
Paul Ibison (SQL Server MVP)sql

Replication to multiple subscribers question

We have an application that runs with MS SQL 2000 (SP3a) which is used for
tracking information from multiple sources. We are shortly going to be
setting up replication of this database with a secondary location, with a
third location planned by mid year. We know that there will be other
locations added over the course of the next few years. Our concern is that
if we have the snapshot expire, we will have to regenerate the snapshot and
reapply it to ALL subscribers before we can add a new subscriber. This is a
problem for 2 reasons. First because the foreign key relationships we have
between many of the tables appears to require that we delete the database on
the subscriber before we can apply the snapshot, and second because the
nature of our subscribers will mean that most of them will be disconnected
for long periods and as the number of subscribers grows it will be
increasingly unlikely that we will have them all connected at the same time.
It appears that our other option is to not have the snapshot expire, but MS
warns that this may cause performance problems but I haven't seen anything
that quantifies the potential performance problems.
Can anyone tell me what the "right" way of doing this is? What sort of
performance problems are we looking at if we don't have the snapshot expire?
Our database consists of approximately 100 or so user tables and currently
is about 300MB of data.
TIA
Ron L.
Ron,
I suspect the performance problems being talked about are proportional to
the amount of changes taking place to the data once the snapshot has been
made. EG if your snapshot contained static lookup tables, then having a
long-lived snapshot file wouldn't really be an issue. However if your
snapshot was of a productlisting table containing 1 million rows, and
product prices were changed on a regular basis (my case) then after
application of the snapshot you might find your merge agent (or distributor)
would struggle to send down the accumulated changes. BTW, in this case the
problem would be least for merge assuming records could be repeatedly
changed, and it avoids the issue of not being able to run the distribution
cleanup agent that you'll have.
HTH,
Paul Ibison
|||Paul
Thanks for the response. My data tends to be less changes and more
additions - we have logging records for text logs and records tracking
movement of parts within systems. At the moment, our largest table has just
over 100,000 records and the next largest is in the 20,000 record range.
One other option we were looking at was to have seperate publications
for each (or a small group of) additional server, thus making the snapshot
regeneration have less impact. Do you have any feeling as to whether this
is a better approach?
Thanks,
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23FVkMhWLEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Ron,
> I suspect the performance problems being talked about are proportional to
> the amount of changes taking place to the data once the snapshot has been
> made. EG if your snapshot contained static lookup tables, then having a
> long-lived snapshot file wouldn't really be an issue. However if your
> snapshot was of a productlisting table containing 1 million rows, and
> product prices were changed on a regular basis (my case) then after
> application of the snapshot you might find your merge agent (or
distributor)
> would struggle to send down the accumulated changes. BTW, in this case the
> problem would be least for merge assuming records could be repeatedly
> changed, and it avoids the issue of not being able to run the distribution
> cleanup agent that you'll have.
> HTH,
> Paul Ibison
>
|||Ron,
I agree that this approach leads to more granularity and therefore is more
versatile for your needs, As long as the publications are distinct units
then it should be OK - eg if there are PK-FK relationships across
publications then it's a no-go, so it depends how related the tables are.
Regards,
Paul
|||Paul
What we are thinking is multiple publications covering the same set of
tables. Is this not going to work?
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e454$eeLEHA.1484@.tk2msftngp13.phx.gbl...
> Ron,
> I agree that this approach leads to more granularity and therefore is more
> versatile for your needs, As long as the publications are distinct units
> then it should be OK - eg if there are PK-FK relationships across
> publications then it's a no-go, so it depends how related the tables are.
> Regards,
> Paul
>
|||Ron,
I've not heard of this arrangement before, but it sounds feasible with merge
replication.
I guess the only downside is increased maintenance.
Cheers,
Paul

Replication to Mainframe Db2 OSZ

What would I need in order to setup replication between SQL2000 SP3 and MVS Subsystem running Db2 database . I mean, does it need any 3rd party tool or regular replication setup between SQL and Db2 is enoughAsk IBM for the DB2 Federated Server. If you have DB2 license, they should let you use that software free. It comes with a Replicator which allows you to replicate data from a different platform.

Replication to Mainframe Db2 OS390

Can I get drivers to setup Replication to Mainframe . I really don't want to install HIS
Thankscan someone please answer .

Thanks|||I don't think its possible without HIS.|||HIS what?

:D|||HIS stands for Host Integration Server, more from here (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074) .

Replication to IP addressed server

we have an outside company that replicates to us and we are known to them via an IP address. We get an error now...Sql Server 2005 now does not support a server Alias, IP address, or any other alternate name are not supports. Specify the actual Server Name

I tried putting in the hosts file on their server

168.168.110.2 SQL2005

able to connect to our server through Studio Manager...no problem....

replication....no dice

what is the work around to make this happen, I am not a network guru...know enough to kill someone, but I need to get this to work before I can move to the new production server

Thanks

John

Exactly what part is failing? Have you tried creating an alias and mapping it to the IP address?|||

When the outside company tries to set the Subscription up it fails since they try to enter the IP address. From what the network people tell me, we have a T1 line between us and open addresses so they can ping the IP address. But, we are on two completely different networks....2 different companies.

They tried to put it into the hosts file on their server (which is SQL 2000) and on the PC they were using studio manager on, but that did not help

When you say create an alias, what exactly are you referring to, since my network skills are enough to kill someone right now.

On their side, any network operations will take some paper work so we are hoping to keep it on the server....paper work just takes a bit too long sometimes

|||

the error we get

SQL Server Replication requires the actual server name to make a connection to the server.
Connects through a server alias, IP addres, or any other alternate name are not supported.
Specify the actual server name 'SQL2005'

|||

Hi,

When you setup subscription, do you specify the subscriber using IP address or server name? I saw you have do the mapping with host file. You may want to check the following:

1) Make sure the outside company can connect to your server using name "SQL2005" using osql or management studio.

2) after connect to SQL2005, try "select @.@.SERVERNAME". Make sure the server name is "SQL2005" if it is default instance, or "SQL2005\instancename" if it is named instance

3) when create subscription, enter "SQL2005" instead of IP address.

Let me know if it still doesn't work for you.

Thanks,

-Peng

|||

It seems we have gotten past one problem and on to a new problem.

It looks like the Alias took care of the problem.

now, when the wizard gets to "New subscription Wizard"

Choose one or more Subscribers and specify each subscrition database

We can choose "Subscriber" as "SQL2005" - which is the server

Great!

but, there are no "Subscription Database" to choose from

The sql user has access to the DB, they have "Connect Replication" and "Connect" permission. The sql user also has public permission to the DB.

What else could be missing?

I feel we are close, just trying to finish this

One other thing, I have read that SQL 2000 distributor will work with a sql 2005 Subscription if it is transactional, is that correct?

from another post "If you are using a transactional read-only subscriber then the subscriber can be up to two versions higher than the publisher. SQL 2000 publisher and SQL 2005 subscriber should work, "

|||

Isn't "SQL2005" the publisher machine? The "Subscriber" should be the machine on the other end, the one receiving the data. You may need to click on the "Add Subscriber" button below and add the machine as a valid subscriber.

|||

SQL2000 at the outside company is the publisher and distributor

they also set the subscription for us (Push).

I can not access there SQL Server at all since it has windows auth

We are SQL Server 2005 (not yet, maybe this weekend)

So they are pushing the data from their DB to us

SQL2000 -> SQL2005

which, according to the docs does work. There just seems to be some bloody security issue going on....seems Microsoft went overboard on some of this and now has made it a real pain in the neck to even get working.

What do they permissions have to be to get this working

|||

Is "SQL2005" the actual name of your machine?

|||

SQL2005 is the name of my server which is also a SQL2005 server

Windows 2003 Server - 64 bit, SQL Server 2005 Enterprise Edition 64 bit

The server will be renamed to what the production server is currently this weekend...right now we are just trying to test this replication issue.

We had the replication working a few minutes before we got a

the user is not associated with a trusted SQL Server Connection

Source:SQL2005

The user they are using on my server (SQL2005) now has every role and has every permission on the DB they are trying to replicate to.

|||

So you're saying you got past the problem with adding a subscription?

And now you're having connection problems? Can you describe what exactly was working, and what failed suddenly?

And I assume this is transactional replication?

|||

This is transactional replication.

First, we had the issues with trying to replication from 2000-> 2005 because of the IP address issue, that was fixed with using an Alias.

which the error we got clearly stated that a server alias could not be used....so I guess it can ....which the error screen sure does say that does not work. Of course, no help button on that screen to help a person out. That was one of the bigger huddles to get over.

the SQL User, since we allow both connections, has access to the DB and we gave them the ability to do "Connection Replication"

That does not seem to be enough.

The person on the SQL2000 server (who is using Studio manger by the way) was using the Subscription Wizard and could not even get any DBs in the drop down to select from

Now, I have given all permissions for the user for the DB and all roles for the entire Server.

He can now see the DB to replicate too on the SQL2000 Subscription Wizard and is able to select the DB.

So, we crossed through some security issue to allow that, but we have no clue which one.

This use to be a basic thing to really do, now it is complicated with too much security.

We are right now waiting to see if the subscription actually takes place......and waiting and waiting....atleast no errors yet.

So, in a nutshell

do a transactional replication from SQL2000 -> SQL2005

where the sql2005 can not do any of the setup because of security

the SQL2000 server has to be accessed with Studio manager - check

an Alias must be set up...even though the subscription wizard says that this is wrong

and then there is some permission besides "Connection replication" that has to be set that we can't figure out

|||

Couple things:

- To create a subscription and see the list of databases at the subscriber, you need to be a minimum of db_owner of the subscriber database, or be a member of the sysadmin role at the subscriber machine. This is documented in the "Security Role Requirements for Replication Setup" topic in SQL Server 2005 Books Online.

Since they connect to your subscriber machine using a standard login/password, that login needs to be at least a dbo of the subscriber database.

- Where do you see the "Connection replication" permission thing? If I remember correctly, you need to set permissions for the agents to connect to the publisher, distributor and subscriber machine. Did you start the snapshot agent, logreader agent and distribution agent yet? Do you see any failures?

|||

They are failing on the Distribution Agent...snapshot and logreader does work

Login failed for user "The User is not associated with a Trusted SQL Server Connection. The Process Could not connect to Subscrivber "SQL2005" The Step Failed

The outside company can use SQL Server Man Studio to connect to our server using the same account, so we know the account works.....

something else is messed up still.

"Connection replication" is a permission of the user on the DB that we are trying to replicate to .....seemed like something we needed.

But right now, the user has every permission there is

|||

By default, the subscription will be created and use windows authentication for all connections to the publisher, distributor and subscriber.

To change the security settings for the subscriber to use standard security, you need to modify the security settings for the distribution agent.

TO learn how to do this, see SQL 2005 Books Online topic "How to: View and Modify Replication Security Settings".

You can also access search for, or access this topic at msdn.com.

http://msdn2.microsoft.com/ms151761.aspx

Friday, March 30, 2012

Replication Triggers on replicated tables.

Replication triggers

replication triggers

Replication Triggers

Replication Trigger Problem (SQL 2005): Assistance Please

Replication Trigger Problem (SQL 2005): Assistance Please

Replication Transformation

Replication transcations

replication transaction logs

Replication Training

Replication Topology Question

replication tool

replication tool

Replication to trusted domain - naming convention problem?

Replication to the Non Default File Group

replication to sybase 12.5

replication to sql server by activeX

Replication to SQL Server 2005 Express

Replication to SQL 2005

Replication to server with different Port

replication to new server

replication to new server

Replication to multiple subscribers question

Replication to Mainframe Db2 OSZ

Replication to Mainframe Db2 OS390

Replication to IP addressed server

Replication in SQL

Blog Archive

About Me