Showing posts with label article. Show all posts
Showing posts with label article. Show all posts

Friday, March 30, 2012

Merge Replication: Dropping an article from existing Publication

Hi All,
How to drop a single article (TABLE) from merge replication keeping
publication intact.
Regards
Javed Iqbal
Javed,
unlike transactional, this is not possible. The nearest you can get is to
drop the publication hten recreate it without the article and do a nosync
initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Thanks for the help.
Actually I want to change a column width in a replicated table which is
appearing at first position.
I can add dummy column then update dummy column with data. Then drop the
main column. Recreate main column with new size and update it. And finally
removing the dummy column using sp_repladdcolumn and sp_repldropcolumn.
But it will add the column at last position.
Is there any way to redefine column position on a replicated table?
OR
How to add a column at specific position?
Thanks in advance.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ucTtJkYTGHA.4132@.TK2MSFTNGP11.phx.gbl...
> Javed,
> unlike transactional, this is not possible. The nearest you can get is to
> drop the publication hten recreate it without the article and do a nosync
> initialization.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Javed,
ideally the application is not dependant on the column position. We have no
TSQL to specify it, and the only practical way is to recreate the table. In
terms of merge, this would be a reinitialization. If at all possible, make
your application use column names rather than position numbers. As an
interim measure can you perhaps define a view?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul for your prompt help.
Ideally my TSQL should refer column names but in my applications I am not
sure about what programmers have coded in TSQL. And it is not possible to
check those programs .
There are many reports based on this table hence I do not want to play with
the column position.
Correct me if I wrong at following.
Scenario - Merge Replication with NOSYNC.
I have only option to drop subscription / publication and recreate it with
the earlier generated scripts.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23x2oERZTGHA.1160@.TK2MSFTNGP09.phx.gbl...
> Javed,
> ideally the application is not dependant on the column position. We have
no
> TSQL to specify it, and the only practical way is to recreate the table.
In
> terms of merge, this would be a reinitialization. If at all possible, make
> your application use column names rather than position numbers. As an
> interim measure can you perhaps define a view?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Javed,
a nosync initialization would be ok. You could change the column position on
the publisher and subscriber before synchronising. In this case you'd take
over control of the identity range management. You could only use the
previous scripts if they were themselves defined as nosync.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes Paul,
I am using NOSYNC since MERGE Replication is created / started.
Identities are ODD on Publisher EVEN on subscriber.
Any further inputs are highly appreciated.
Thanks a lot again Paul.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ecN9afZTGHA.6084@.TK2MSFTNGP14.phx.gbl...
> Javed,
> a nosync initialization would be ok. You could change the column position
on
> the publisher and subscriber before synchronising. In this case you'd take
> over control of the identity range management. You could only use the
> previous scripts if they were themselves defined as nosync.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

Monday, March 26, 2012

Merge Replication walkthrough

I am very new to replication, so I am looking for a great walk-through for setting up Merge Replication. Does anyone know of a good article/example that provides this information? I have been through BOL and I'm having trouble putting the pieces together.

Thanks...

Scott

Hello Scott,

Here's a link which describes SQL Server Merge Replication step by step. Although it is for SQL Server 2000, it is gonna give you an idea about it.

Merge Replication Setup, step by step:

http://www.databasejournal.com/features/mssql/article.php/1438231

Merge Replication Overview (BOL)

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

Ekrem ?nsoy

|||

Thanks Ekrem,

I have gone through those, and i think i'm close but I am getting the following error(s):

"DEV-PUBLISHER-MergeTest-Publisher-pub_mergetest-LATI12-1 failed. The initial snapshot for publication 'pub_mergetest' is not yet available. Start the Snapshot Agent to generate the snapshot for this publication. If this snapshot is currently being generated, wait for the process to complete and restart the synchronization."

So, I right mouse click on the Publisher and select "View Snapshop Agent Status". When that dialog comes up, it shows the agent as stopped. When I click the Start button, I get the following error:

"Executed as user: dev-publisher\repl_Snapshot. The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed."

I looked at the job steps and Replication Monitor and it basically tells me the same thing (that my initial snapshot does't exist). Any idea what I am missing or did wrong?

Thanks...

|||

I got it to work. I didn't have permissions set up correctly. I had to add the correct user accounts and permissions in SSMS and then set up the correct access permissions to the snapshot location directory. Works like a champ now.

Monday, March 19, 2012

Merge replication on MSDE using VPN

I suspect your issue is that you don't have an alias, but
have a look at this article which should help:
http://support.microsoft.com/?id=321822
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
Thanks for the reponse. I have created an alias for the msde at the server
and registered it in EM. I was still getting the same error so I enabled
output logging for the merge agent. Apon inspection of the log it indicated a
logintimeout error had occurred and suggested increase the timeout value. I
created a new profile for the merge agent extending both the logintimeout and
the querytimeout parameters. I stopped and started the merge agent and now
the log contains the following message ("Cannot generate SSPI context"). Just
to recap, I am trying to replicate a database using SQL 2k sp3 enterprise
(windows 2003 server) and msde 2k sp3 (xp professional) between two
non-trusted domains using a vpn.
Thanks Jeff
"Paul Ibison" wrote:

> I suspect your issue is that you don't have an alias, but
> have a look at this article which should help:
> http://support.microsoft.com/?id=321822
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Can you change to sql serurity if you don't have it
already. Also, change the jobs to be owned by sa. If the
VPN is set up correctly, I believe you should be able to
assign rights to the replication share for the sql server
agent of the pull job. In my case I used FTP but this
shouldn't be necessary. This error message has loads of
potential causes mostly connected with authentication
that you can find in Google. For my part, I have seen it
when the system clocks weren't synchronized at all
accurately.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

>--Original Message--
>Hi Paul,
> Thanks for the reponse. I have created an alias for the
msde at the server
>and registered it in EM. I was still getting the same
error so I enabled
>output logging for the merge agent. Apon inspection of
the log it indicated a
>logintimeout error had occurred and suggested increase
the timeout value. I
>created a new profile for the merge agent extending both
the logintimeout and
>the querytimeout parameters. I stopped and started the
merge agent and now
>the log contains the following message ("Cannot generate
SSPI context"). Just
>to recap, I am trying to replicate a database using SQL
2k sp3 enterprise
>(windows 2003 server) and msde 2k sp3 (xp professional)
between two[vbcol=seagreen]
>non-trusted domains using a vpn.
>Thanks Jeff
>"Paul Ibison" wrote:
but[vbcol=seagreen]
www.replicationanswers.com
>.
>
|||Hi Paul,
I set the SQL Server Agent service on both machines to run under a local
windows account with the same name and password and everything is working
fine now. Is there a preferred configuration for security purposes. Our SQL
Server Services have always run under a network account. I have read several
articles on how to make this work but, I would like to make sure I am paying
significant attention to security. If you know of a good source for
replication security let me know.
Thanks for all you help! Jeff
"Paul Ibison" wrote:

> Can you change to sql serurity if you don't have it
> already. Also, change the jobs to be owned by sa. If the
> VPN is set up correctly, I believe you should be able to
> assign rights to the replication share for the sql server
> agent of the pull job. In my case I used FTP but this
> shouldn't be necessary. This error message has loads of
> potential causes mostly connected with authentication
> that you can find in Google. For my part, I have seen it
> when the system clocks weren't synchronized at all
> accurately.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
> msde at the server
> error so I enabled
> the log it indicated a
> the timeout value. I
> the logintimeout and
> merge agent and now
> SSPI context"). Just
> 2k sp3 enterprise
> between two
> but
> www.replicationanswers.com
>
|||What you have set up is pass-through security. As you
say, normally between computers Active Directory users
are used, or Workgroup users.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Jeff,
I don't know what your deployment timeframe is but SQL 2005 has the ability
to perform synchronization over HTTPS. Is this something you would be
interested in trying?
Philip Vaughn
Program Manager
SQL Server Replication
This message is provided "AS IS" with no warranties, and confers no rights.
"Jeff Rowley" <JeffRowley@.discussions.microsoft.com> wrote in message
news:4C8310C5-AC8D-4FD9-8152-1BFCADE59A29@.microsoft.com...[vbcol=seagreen]
> Hi Paul,
> I set the SQL Server Agent service on both machines to run under a local
> windows account with the same name and password and everything is working
> fine now. Is there a preferred configuration for security purposes. Our
> SQL
> Server Services have always run under a network account. I have read
> several
> articles on how to make this work but, I would like to make sure I am
> paying
> significant attention to security. If you know of a good source for
> replication security let me know.
> Thanks for all you help! Jeff
> "Paul Ibison" wrote:

Monday, March 12, 2012

Merge Replication Issue

Hi,
I am trying to delete a Merge Publication on SQL Server 2000 SP3 and it won't work because an article (a table) that was part of the publication was deleted and recreated before dropping the publication and now when I run sp_dropmergepublication or sp_dropmergearticle, it fails because of that table.

Please HelpWhat error message you are getting? Have all subscriptions been dropped?

Merge replication in 'SQL Server'

Hi,
I read Paul Ibison's article about "Replication Across Non-Trusted Domains
or Using the Internet".
Me (and my company) need to take a very tough decision whether using SQL
server 2005 merge replication or building our own synchronization engine.
Our big problems are:
1. Everything has to work over the internet (over port 443 only), even port
21 for ftp is not an issue…
2. Out product is for customers overseas without the ability to go through
an integration process at the client site. It means we must use some
automatic process configuring the publisher or subscriber and users running
processes, etc.
3. The installation must work from an installation cd (both client and
server), without the need to manually configure domain users to run certain
replication agents – it must also be automatically.
Which one (or all) of the demands above is possible?
We are willing to make small changes –only after we definitely know that we
must open port 21 for example.
I understood that the implementation of offline clients (doesn't matter if
it's PDA with SQL CE/Mobile or windows XP with MSDE/Express) and Merge
replication in the SQL Server is good in theory but difficult to manage and
requires a lot of extra handling issues like opening ports and adding users
granted to run the processes for the replication process.
Are there any improvements in 2005 in these issues (I know about the option
to replicate through iis so the port problem is now solved?!).
My most important request is that I'll be glad if anybody knows and can
write about products in the open market which used this architecture.
Thanks,
Gil.
I'm in a crucial
Is it true? Are there any improvements in 2005 in these issues (I know about
the option to replicate through iis so the port problem is now solved?!).
I'll be glad if anybody knows and can write about products in the open
market which used this architecture.
Thanks,
Gil.
1) no problem, you can configure sql server to run on any port - have a
look at http://support.microsoft.com/default...b;en-us;823938 in
the section marked. However, port 443 is for https, did you want your SQL
Server traffic encrypted?
Configuring an instance of SQL Server to use a static port
2) you can script out replication jobs by right clicking on a publication
and selecting - generate sql script. You can generate the scripts necessary
to enable replication by right clicking on the replication folder and
selecting Generate SQL Scripts, and then selecting Distributor properties.
3) you can configure autorun.inf to run an executable which will run the
scripts generated above
Everything you list is possible - you suddenly start talking about SQL CE -
if your clients are running SQL CE, you will need a web server to
synchronize with. You could run over port 443 for this. I would not
characterize "Merge replication in the SQL Server is good in theory but
difficult to manage and requires a lot of extra handling issues like opening
ports and adding users granted to run the processes for the replication
process."
This is simply not true. Merge replication or any form of replication only
needs port 1433 (or whatever port you run SQL Server on) open. If you are
deploying your snapshot over the internet you will probably want port 21
open as well. Regarding the addition of users, this could be valid. You can
run pull agents on the subscribers which will connect to the publisher under
different accounts if you are doing filtering by suser_name(). But by
default you probably would not have to do this. I normally use pass through
authentication which uses the same account names.
There are lots of improvements, fixes, and features in SQL 2005. Web
synchronization is one of them. Although the documentation talks about
running over port 1433 (https) in addition to 80(http), I believe this is a
mistake.
I worked on a replication topology with over 60 merge clients. We had our
share of problems, but in general it was highly stable.
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
"Gil" <Gil@.discussions.microsoft.com> wrote in message
news:123D2E11-371E-419E-8B5F-D4710F95A008@.microsoft.com...
> Hi,
> I read Paul Ibison's article about "Replication Across Non-Trusted Domains
> or Using the Internet".
> Me (and my company) need to take a very tough decision whether using SQL
> server 2005 merge replication or building our own synchronization engine.
> Our big problems are:
> 1. Everything has to work over the internet (over port 443 only), even
port
> 21 for ftp is not an issue.
> 2. Out product is for customers overseas without the ability to go through
> an integration process at the client site. It means we must use some
> automatic process configuring the publisher or subscriber and users
running
> processes, etc.
> 3. The installation must work from an installation cd (both client and
> server), without the need to manually configure domain users to run
certain
> replication agents - it must also be automatically.
> Which one (or all) of the demands above is possible?
> We are willing to make small changes -only after we definitely know that
we
> must open port 21 for example.
> I understood that the implementation of offline clients (doesn't matter if
> it's PDA with SQL CE/Mobile or windows XP with MSDE/Express) and Merge
> replication in the SQL Server is good in theory but difficult to manage
and
> requires a lot of extra handling issues like opening ports and adding
users
> granted to run the processes for the replication process.
> Are there any improvements in 2005 in these issues (I know about the
option
> to replicate through iis so the port problem is now solved?!).
> My most important request is that I'll be glad if anybody knows and can
> write about products in the open market which used this architecture.
> Thanks,
> Gil.
>
> I'm in a crucial
> Is it true? Are there any improvements in 2005 in these issues (I know
about
> the option to replicate through iis so the port problem is now solved?!).
> I'll be glad if anybody knows and can write about products in the open
> market which used this architecture.
> Thanks,
> Gil.
>
>
|||Thank you for your answer...
But I guess I wasn't completely clear.
We plan to use merge replication with new web replication feature available
in "SQL server 2005", and I wanted to know whether there is an option to work
with SSL over http without the need for opening the ftp port?
We definitely cannot open port 1433 because the replication will go over the
internet – and Windows authentication is not possible too.
There is also a need to use SUSER_SNAME() function in order filter by the
connected user. Is there a workaround of doing it without giving special
permissions to the user? I noticed there are security problems impersonating
the users just like that, these are the most annoying parts
Thanks,
Gil.
"Hilary Cotter" wrote:

> 1) no problem, you can configure sql server to run on any port - have a
> look at http://support.microsoft.com/default...b;en-us;823938 in
> the section marked. However, port 443 is for https, did you want your SQL
> Server traffic encrypted?
> Configuring an instance of SQL Server to use a static port
> 2) you can script out replication jobs by right clicking on a publication
> and selecting - generate sql script. You can generate the scripts necessary
> to enable replication by right clicking on the replication folder and
> selecting Generate SQL Scripts, and then selecting Distributor properties.
> 3) you can configure autorun.inf to run an executable which will run the
> scripts generated above
> Everything you list is possible - you suddenly start talking about SQL CE -
> if your clients are running SQL CE, you will need a web server to
> synchronize with. You could run over port 443 for this. I would not
> characterize "Merge replication in the SQL Server is good in theory but
> difficult to manage and requires a lot of extra handling issues like opening
> ports and adding users granted to run the processes for the replication
> process."
> This is simply not true. Merge replication or any form of replication only
> needs port 1433 (or whatever port you run SQL Server on) open. If you are
> deploying your snapshot over the internet you will probably want port 21
> open as well. Regarding the addition of users, this could be valid. You can
> run pull agents on the subscribers which will connect to the publisher under
> different accounts if you are doing filtering by suser_name(). But by
> default you probably would not have to do this. I normally use pass through
> authentication which uses the same account names.
> There are lots of improvements, fixes, and features in SQL 2005. Web
> synchronization is one of them. Although the documentation talks about
> running over port 1433 (https) in addition to 80(http), I believe this is a
> mistake.
> I worked on a replication topology with over 60 merge clients. We had our
> share of problems, but in general it was highly stable.
> --
> 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
> "Gil" <Gil@.discussions.microsoft.com> wrote in message
> news:123D2E11-371E-419E-8B5F-D4710F95A008@.microsoft.com...
> port
> running
> certain
> we
> and
> users
> option
> about
>
>
|||Re the port issue - I asked this question on the Beta newsgroup - for Merge
over the internet in SQL 2005, is HTTP (80) supported or must it be HTTPS
(443) and the reply was HTTPS only. (which makes it a pain fully testing the
whole process on XP which doesn't support certificate server). At the time I
noticed that the advice in BOL for the initial Beta2 version had some typos
and mixed the 2 - HTTP and HTTPS, or was at best misleading. I haven't
checked the new BOL yet on this issue.
Cheers,
Paul Ibison
|||Hi,
Can someone give me some real-world example of a product (not an IT project
- where a DBA be in the integration process, a product for customers
overseas) which is using web merge replication (over http only) ?
Doesn't matter if the Client is windows XP or mobile.
Thanks,
Gil.
"Paul Ibison" wrote:

> Re the port issue - I asked this question on the Beta newsgroup - for Merge
> over the internet in SQL 2005, is HTTP (80) supported or must it be HTTPS
> (443) and the reply was HTTPS only. (which makes it a pain fully testing the
> whole process on XP which doesn't support certificate server). At the time I
> noticed that the advice in BOL for the initial Beta2 version had some typos
> and mixed the 2 - HTTP and HTTPS, or was at best misleading. I haven't
> checked the new BOL yet on this issue.
> Cheers,
> Paul Ibison
>
>
|||Gil,
I'd also be interested to hear of any such products but I think we'll be
lucky - SQL Server's only in Beta2 at this stage
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Friday, March 9, 2012

Merge replication- How to drop article and than add it again

Hi All,
I need to drop an article from live merge publication and than add it back
again with different schema (column, PK etc’) . I can’t use
sp_dropsubscription for every subscriber and sp_droparticle because it is not
transactional / snapshot publication.
Do I have any other alternative other than reinitialize the whole
publication? .
Thanks,
Eyal
Unfortunately not in the case of merge.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||There is more info here
http://msdn.microsoft.com/library/de...limpl_22pf.asp
about managing schema changes with merge replication.
Thanks
Nabila Lacey
"? ??" <nospameyalSchapira@.hotmail.com> wrote in message
news:05D80E20-9EC0-4A00-BF56-210C868F3617@.microsoft.com...
> Hi All,
> I need to drop an article from live merge publication and than add it
back
> again with different schema (column, PK etc') . I can't use
> sp_dropsubscription for every subscriber and sp_droparticle because it is
not
> transactional / snapshot publication.
> Do I have any other alternative other than reinitialize the whole
> publication? .
>
> Thanks,
> Eyal

Wednesday, March 7, 2012

Merge Replication Error - Failed to enumerate changes in the filtered article

Hi,
I am using Merge Replication and have filtered some of the tables for teh
subscriptions, however, everything was working fine, then all of a sudden, I
get this error. (See below)
Does anyone know what would cause this and how I can overcome it?
Thanks in Advance
Warren
************************************************** *
******************ERROR DETAILS******************
************************************************** *
Last command: {call sp_MSsetupbelongs(?,?,?,?,?,1,?,?,1,?,?,?,?,?,?)}
Error Message: Failed to enumerate changes in the filtered articles.
Error Details:
Failed to enumerate changes in the filtered articles.
(Source: Merge Replication Provider (Agent); Error number: -2147200925)
Incorrect syntax near the keyword 'where'.
(Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
Incorrect syntax near the keyword 'and'.
(Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
************************************************** *
*******************END OF ERROR******************
************************************************** *
Could you provide more information on your database and replication filters
?
Are replicated objects owned by dbo ?
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:O%23o3zZrKFHA.2640@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am using Merge Replication and have filtered some of the tables for teh
> subscriptions, however, everything was working fine, then all of a sudden,
I
> get this error. (See below)
> Does anyone know what would cause this and how I can overcome it?
> Thanks in Advance
> Warren
> ************************************************** *
> ******************ERROR DETAILS******************
> ************************************************** *
> Last command: {call sp_MSsetupbelongs(?,?,?,?,?,1,?,?,1,?,?,?,?,?,?)}
> Error Message: Failed to enumerate changes in the filtered articles.
> Error Details:
> Failed to enumerate changes in the filtered articles.
> (Source: Merge Replication Provider (Agent); Error number: -2147200925)
> ----
--
> --
> Incorrect syntax near the keyword 'where'.
> (Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
> ----
--
> --
> Incorrect syntax near the keyword 'and'.
> (Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
> ----
--
> --
> ************************************************** *
> *******************END OF ERROR******************
> ************************************************** *
>
|||Hi,
Thanks for your reply.
The objects are owned by DBO.
We are using Merge replication will pull subscriber.
Some tables are filtered, in the publication properties, if I go to FILTER
ROWS, then FILTER CLAUSE column, some tables are filtered like so
depot_system_id = 'xxxxx-xxxxx-xxxxx-xxxxxx'
where x makes up the guid.
is that enough information? what else do you need?
Many thanks
"Kestas" <kicker.lt@.noospamm-tut.by> wrote in message
news:%232ddCNsKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Could you provide more information on your database and replication
filters[vbcol=seagreen]
> ?
> Are replicated objects owned by dbo ?
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:O%23o3zZrKFHA.2640@.TK2MSFTNGP09.phx.gbl...
teh[vbcol=seagreen]
sudden,
> I
> ----
> --
> ----
> --
> ----
> --
>
|||Also would be good to know which exact SQL Server version you are runing
SELECT @.@.VERSION
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:%23nEFf6uKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Thanks for your reply.
> The objects are owned by DBO.
> We are using Merge replication will pull subscriber.
> Some tables are filtered, in the publication properties, if I go to FILTER
> ROWS, then FILTER CLAUSE column, some tables are filtered like so
> depot_system_id = 'xxxxx-xxxxx-xxxxx-xxxxxx'
> where x makes up the guid.
> is that enough information? what else do you need?
> Many thanks
|||Hi,
Publisher:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
Subscriber:
MSDE
Kind Regards
Warren
"Kestutis Adomavicius" <kicker.lt@.noospamm-tut.by> wrote in message
news:um0A7yvKFHA.3960@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Also would be good to know which exact SQL Server version you are runing
> SELECT @.@.VERSION
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:%23nEFf6uKFHA.1280@.TK2MSFTNGP09.phx.gbl...
FILTER
>
|||Anyone have any ideas?
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:uZZOIA4KFHA.2860@.TK2MSFTNGP10.phx.gbl...
> Hi,
>
> Publisher:
> --
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: )
> Subscriber:
> --
> MSDE
>
> Kind Regards
> Warren
>
> "Kestutis Adomavicius" <kicker.lt@.noospamm-tut.by> wrote in message
> news:um0A7yvKFHA.3960@.TK2MSFTNGP09.phx.gbl...
> FILTER
>
|||can you post your schema and publication script here?
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
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:uj$i292LFHA.4080@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Anyone have any ideas?
>
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:uZZOIA4KFHA.2860@.TK2MSFTNGP10.phx.gbl...
runing
>
|||Hi Hilary,
By publication script, I assume you mean the script to create the
publication (right-click publication --> Generate SQL Script)?
And the schema? Do you want the published databases schema?
can you advise,
thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%2317MDe6LFHA.3988@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> can you post your schema and publication script here?
> --
> 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
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:uj$i292LFHA.4080@.TK2MSFTNGP10.phx.gbl...
> runing
to
>
|||Anyone able to help on this?
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:OGkKj9FMFHA.3420@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi Hilary,
> By publication script, I assume you mean the script to create the
> publication (right-click publication --> Generate SQL Script)?
> And the schema? Do you want the published databases schema?
> can you advise,
> thanks
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%2317MDe6LFHA.3988@.tk2msftngp13.phx.gbl...
> to
so
>

Merge Replication Error

i am having the following error with merge replication:
failed to enumerate changes in the filtered article (source: Merge replication Provider(Agent);error number -2143200925)
does anyone have a resolution for this please.
thanks.Make sure the accounts used have sa privileges on both subscriber server.

Saturday, February 25, 2012

Merge Replication between MSDE doesn't work!

Hello!

I have a problem with creating Merge replication between two instances of MSDE 2000. In the article http://support.microsoft.com/kb/324992/, published by Microsoft, described, that it is possible.

I am creating Merge Publication at MSDE 2000, with Distributor and Publisher configured at same machine, with options "Allow Pull Subscriptions" and "Allow Anonymous Subscriptions". There is no problem with creating of Publication. Even snapshot generation finishes successfully. Also I create a login with SQL Server Authentication and "System Administrators" database role at the publisher in order to connect to it from Subscriber.

The problem occurs, when creating anonymous pull subscription to this publication at another instance of MSDE. During initial synchronization an error occurs:

The process could not connect to Distributor '<publisher_server_name>'. Login failed for user '<login_created_at_publisher>'. Reason: Not associated with a trusted SQL Server connection. The step failed.

Although I didn't use Windows Authentication at all, so Subscriber doesn't need to connect to Distributor using trusted SQL Server connection.

What is a problem and how can I workaround?

Note: The same works correctly, if MS SQL Server used as a Publisher instead of MSDE.

Please, help!

I can provide publication and subscription creation scripts, if required.

Guys, don't care anymore: I found the reason!!!

Probably, the problem was in using an old version of MSDE, and installing MSDE with SP4 solved it!

However, there are still some problems with connecting to MSDE from a remote workstation via named pipes, but I think I can research it independently.

Thanks for consideration!

Merge Replication between MSDE doesn't work!

Hello!

I have a problem with creating Merge replication between two instances of MSDE 2000. In the article http://support.microsoft.com/kb/324992/, published by Microsoft, described, that it is possible.

I am creating Merge Publication at MSDE 2000, with Distributor and Publisher configured at same machine, with options "Allow Pull Subscriptions" and "Allow Anonymous Subscriptions". There is no problem with creating of Publication. Even snapshot generation finishes successfully. Also I create a login with SQL Server Authentication and "System Administrators" database role at the publisher in order to connect to it from Subscriber.

The problem occurs, when creating anonymous pull subscription to this publication at another instance of MSDE. During initial synchronization an error occurs:

The process could not connect to Distributor '<publisher_server_name>'. Login failed for user '<login_created_at_publisher>'. Reason: Not associated with a trusted SQL Server connection. The step failed.

Although I didn't use Windows Authentication at all, so Subscriber doesn't need to connect to Distributor using trusted SQL Server connection.

What is a problem and how can I workaround?

Note: The same works correctly, if MS SQL Server used as a Publisher instead of MSDE.

Please, help!

I can provide publication and subscription creation scripts, if required.

Guys, don't care anymore: I found the reason!!!

Probably, the problem was in using an old version of MSDE, and installing MSDE with SP4 solved it!

However, there are still some problems with connecting to MSDE from a remote workstation via named pipes, but I think I can research it independently.

Thanks for consideration!

Monday, February 20, 2012

Merge Replication Alther Column in Place

Hi all i have a merge replication am im trying to alter a column in place,
ive got an article from sql server cental, that describes creating a temp
column copy the data over drop and recrete the column, copy the data over
and drop the temp column,
Step 1
exec sp_repladdcolumn @.source_object = 'authors'
,@.column = 'TempTest'
,@.typetext = 'varchar(100) NULL'
,@.publication_to_add = 'pubs'
This works ok
Step 2
update authors set TempTest = Test
This works ok
Step 3
exec sp_repldropcolumn @.source_object = 'authors'
,@.column = 'Test'
But this gives an error
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'pubs' because schema replication is performed.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__authors__test__186C9245' is dependent on column 'Test'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN Test failed because one or more objects access this
column.
Can anyone help please, how can i drop this object, to let me drop the
column.
Thanks Andrew
You first have to drop default contraint which is causing the problem
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"andrew bourne" <andrewbourne@.vardells.com> wrote in message news:eZuBH8UVFHA.3584@.TK2MSFTNGP14.phx.gbl...
Hi all i have a merge replication am im trying to alter a column in place,
ive got an article from sql server cental, that describes creating a temp
column copy the data over drop and recrete the column, copy the data over
and drop the temp column,
Step 1
exec sp_repladdcolumn @.source_object = 'authors'
,@.column = 'TempTest'
,@.typetext = 'varchar(100) NULL'
,@.publication_to_add = 'pubs'
This works ok
Step 2
update authors set TempTest = Test
This works ok
Step 3
exec sp_repldropcolumn @.source_object = 'authors'
,@.column = 'Test'
But this gives an error
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'pubs' because schema replication is performed.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__authors__test__186C9245' is dependent on column 'Test'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN Test failed because one or more objects access this
column.
Can anyone help please, how can i drop this object, to let me drop the
column.
Thanks Andrew