Big Problem on Merge Replication

Hi, I'm working on a project taht involve SQLSever 2K and SQLSevre CE.
We have about 50 remote terminal that daily sincronize with one server but
each day something wrong happend.
The last one in order of time is this error
Error number 80004005
Native number 29006
Error descripcion: A call to SQL Server reconcilier failed. [,,,,,]
SSCE_M_SQLRECONCILIERFAILED
Followed by:
Source: Merge replication provider
Error number 80045063
Error descripcion: Failed to enumerate changes in the filtered articles
and then by this:
Source: srtv
Error number : 0
Native number: 0
Error descripcion: Call sp_MSsetupbelongs (?,?,?,?,?,0,?,?,1,?,?,?,?,?,?)
Usually at this time the only possibility is to delete the sdf database and
to resincronize the remote terminal.
I have already found an article in KB 328314 - FIX: Cannot Upload Null
Values in Sql_variant Data Type by Using SQL Server CE Merge Replication
but without possible solution and the strange thing is that I have no
Sql_variant Data on my Db.
Please help me because the project is at high risk of crash!!!
Thanx in advance
Best Regards
Gianluca Padovani
what happens if you restart your application that runs the merge pull?
Normally this will clear this error.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Gianluca Padovani" <Gianluca Padovani@.discussions.microsoft.com> wrote in
message news:2F7758AC-A8C6-40B9-8EE1-3C2B0ACEBD1F@.microsoft.com...
> Hi, I'm working on a project taht involve SQLSever 2K and SQLSevre CE.
> We have about 50 remote terminal that daily sincronize with one server but
> each day something wrong happend.
> The last one in order of time is this error
> Error number 80004005
> Native number 29006
> Error descripcion: A call to SQL Server reconcilier failed. [,,,,,]
> SSCE_M_SQLRECONCILIERFAILED
> Followed by:
> Source: Merge replication provider
> Error number 80045063
> Error descripcion: Failed to enumerate changes in the filtered articles
> and then by this:
> Source: srtv
> Error number : 0
> Native number: 0
> Error descripcion: Call sp_MSsetupbelongs (?,?,?,?,?,0,?,?,1,?,?,?,?,?,?)
> Usually at this time the only possibility is to delete the sdf database
and
> to resincronize the remote terminal.
> I have already found an article in KB 328314 - FIX: Cannot Upload Null
> Values in Sql_variant Data Type by Using SQL Server CE Merge Replication
> but without possible solution and the strange thing is that I have no
> Sql_variant Data on my Db.
> Please help me because the project is at high risk of crash!!!
> Thanx in advance
> Best Regards
> Gianluca Padovani
|||Hi Hilary,
thanks for your help, I'm waiting for the response to your test request and
asap i'll give you the answer.
In the meanwhile I can tell you another thing. In My previous post I put the
PocjePC error but at the same time I have a corresponding one on SQLServer
Merge Agent.
The erorr is :
Last Command: {call sp_MSsetupbelongs(?,?,?,?,?,0,?,?,1,?,?,?,?,?,?)}
Failed to enumerate changes in the filtered articles.
(Source: Merge Replication Provider (Agent); Error number: -2147200925)
The merge process timed out while executing a query. Reconfigure the
QueryTimeout parameter and retry the operation.
(Source: srvtv (Data source); Error number: 0)
I made a quick search into the NG and I have found another couple of article
regarding this error.
I one of those article Paul Ibison suggest one FIX
(http://support.microsoft.com/?id=814916 ) to correct this error. May I use
it too?
Again thanks a lot for your help
Best Regards
Gianluca Padovani
|||Hi Hilary
I have got the confirm that if I reset the application and run it again the
error is cleared. This is a goal but is notthe solution because two session
of sincronization means an overhead of time not acceptable for our situation.
I hope in your answer about my previus message regarding the hotfix
Thanx again
Gianluca PAdovani
"Hilary Cotter" wrote:

> what happens if you restart your application that runs the merge pull?
> Normally this will clear this error.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Gianluca Padovani" <Gianluca Padovani@.discussions.microsoft.com> wrote in
> message news:2F7758AC-A8C6-40B9-8EE1-3C2B0ACEBD1F@.microsoft.com...
> and
>
>

Bidirectional transactional replication and remote distributors

I want to be able to setup the following configuration using bidirectional transactional replication on SQL 2005

instance A lives on machine 1

instance B lives on machine 2

Instance A publishes to a transactional subscription on Instance B

Instance B does the reverse and publishes to a transactional subscription on Instance A

Instance A pushes to a distribution database on machine 2

Instance B pushes to a distribution database on machine 1

Problems Implementing Configuration

I can setup each instance as a distributor and create separate distribution databases using

sp_adddistributor and sp_adddistributiondb

I can then enable each publisher to use the correct distribution database using sp_adddistpublisher

However, when I try and run sp_replicationdboption to setup a database for publication, I get the error:

Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 55

The Distributor has not been installed correctly. Could not enable database for publishing.

If I try and configure a publication with the wizard, it says that instance A must be enabled as a publisher before you can create a publication - and then presents the dialog to add instance A to be handled by the local distribution database - which I don't want.

It appears that I need to run sp_adddistributor on the publisher as well as the distributor for the appropriate instance, but if I do this I get the error:

Msg 14099, Level 16, State 1, Procedure sp_adddistributor, Line 109

The server 'instance A' is already defined as a Distributor.

It seems that you can't select a remote distributor if you already have a local distributor configured.

Is there a way round this limitation?

Note that configuring the environment with a single distribution database works fine

Try calling sp_adddistpublisher on machine 1 to add machine 2 as publisher and do the same on machine 2 to add machine 1 as publisher.

Regards,

Gary

|||Check out the new Peer-Peer replication in 2005 which does exactly what you want.|||

Gary - I have tried what you suggested and got the following error as per my original post

Msg 14099, Level 16, State 1, Procedure sp_adddistributor, Line 109

The server 'instance A' is already defined as a Distributor.

=====================

Dinakar - thanks for the suggestion - I have been checking out peer to peer, but I am not sure if I can live with the restrictions

=====================

I will probably go with a single distribution database located on the publisher with the lowest load. This should be satisfactory for the requirements

Thanks

aero1

Best way to synch data without admin rights?

I have a local copy of SQL Server 2000 installed with full admin
rights. I am working with a database on a remote server and I only have
permissions to that DB. I cannot setup
backup/synchro/replication/push/pull/etc due to not having the proper
permissions.
I have been using DTS to transfer the data to my local DB. But, is there
a good way to automate this process? Basically I just want to copy the
data locally everyday as a backup in case the remote server dies. Would
it be possible to create local job that transfers the data or am I stuck
doing this manually?
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot comSchedule your DTS package to run every day.
I believe you just right-click on the package to scedule it.|||Thanks. I will settle with doing that. I was also considering a 3rd
party tool if this does not do the job.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 4/24/2005 3:13 PM, Greg Gilman wrote:
> Schedule your DTS package to run every day.
> I believe you just right-click on the package to scedule it.
>|||So far it is working fine. But, I remember the reason I did not use the
schedule was because sometimes tables would be added and skipped by the
DTS since I am not copying all objects. I find that copying tables is
more reliable then objects/data. If only DTS could also add any new
table created. But, to make a long story short, this not seems to be the
best option considering it is the only option I have to transfer data.
Thanks
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 4/24/2005 3:13 PM, Greg Gilman wrote:
> Schedule your DTS package to run every day.
> I believe you just right-click on the package to scedule it.
>|||Well I have to copy objects anyway, just realized copying table data
fails when the table object already exists in the source database...
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 4/24/2005 3:13 PM, Greg Gilman wrote:
> Schedule your DTS package to run every day.
> I believe you just right-click on the package to scedule it.
>|||You may consider using DB Ghost (http://www.dbghost.com) which can be
scheduled and as it does a comparison - it will only get the data that it
needs where as DTS will have to get all the data. In fact I syncrhonize my
local database with the production system every day using this script:
http://www.innovartis.co.uk/downloa...J
ob.zip
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Steve Lewis - Website Nation" wrote:

> Thanks. I will settle with doing that. I was also considering a 3rd
> party tool if this does not do the job.
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>
> On 4/24/2005 3:13 PM, Greg Gilman wrote:
>|||Thanks for the script Mark. I will also checkout your software. So far
the DTS object/data transfer is working fine.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 4/25/2005 3:58 AM, mark baekdal wrote:
> You may consider using DB Ghost (http://www.dbghost.com) which can be
> scheduled and as it does a comparison - it will only get the data that it
> needs where as DTS will have to get all the data. In fact I syncrhonize my
> local database with the production system every day using this script:
> http://www.innovartis.co.uk/downloa...br />
rJob.zip
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Build, Comparison and Synchronization from Source Control = Database chang
e
> management for SQL Server
>
> "Steve Lewis - Website Nation" wrote:
>

Best way to install a database at a remote site

Greetings,

I am in the process of installing a SQL database at a customer
location. I have determined that there are 3 ways to do this, and I
wanted to know which is the best of the 3.

1 Install From Script.
In this method I create the database and its objects in scripts that
are run via osql utility on the SQL server machine. For loading any
initial data that I need in the database I also run bcp commands.

2 Install from a backup
In this method I created an empty database on the SQL server, and then
restore over it the database from a backup of the database that I need
to deploy. Then I add or re-attach the users for the database. I
perform all of these operations using osql as well.

3 Install by attaching the data files.
In this method I created an empty database on the SQL server, and then
I attach the data files to the database using the sp_attach procedure.
Then I add or re-attach the users for the database. I perform all of
these operations using osql as well.

Although it is no problem for me to use any of these methods, I wanted
to know from you veterans out there what the best practices are. And
also if there are any unseen hazards for each method above. Or if I
am totally off-the-target, and there is another method that is the
preferred way.

Thanks in Advance,
roger"great_googley_moogley" <pow_pow1476@.yahoo.com> wrote in message
news:dda0ee86.0404021123.46fd0e4e@.posting.google.c om...
> Greetings,
> I am in the process of installing a SQL database at a customer
> location. I have determined that there are 3 ways to do this, and I
> wanted to know which is the best of the 3.
> 1 Install From Script.
> In this method I create the database and its objects in scripts that
> are run via osql utility on the SQL server machine. For loading any
> initial data that I need in the database I also run bcp commands.
> 2 Install from a backup
> In this method I created an empty database on the SQL server, and then
> restore over it the database from a backup of the database that I need
> to deploy. Then I add or re-attach the users for the database. I
> perform all of these operations using osql as well.
> 3 Install by attaching the data files.
> In this method I created an empty database on the SQL server, and then
> I attach the data files to the database using the sp_attach procedure.
> Then I add or re-attach the users for the database. I perform all of
> these operations using osql as well.
> Although it is no problem for me to use any of these methods, I wanted
> to know from you veterans out there what the best practices are. And
> also if there are any unseen hazards for each method above. Or if I
> am totally off-the-target, and there is another method that is the
> preferred way.
> Thanks in Advance,
> roger

All three options can give the same results, as you say, but there are some
differences in functionality.

Option 1 is good if you need to load different data for different clients -
with other methods you'd need a copy of the database for every possible data
set you need to provide, but with scripts the object scripts are the same,
and only the data (BCP) files change. Or from the database object
perspective, you can provide different subsets and/or versions of stored
procedures etc. to different clients. In addition, this option doesn't
require you to have any access to the filesystem, which can be useful in
environments with limited bandwidth or tight security requirements. The
downside - if you consider it one - is that you need to manage the scripts,
but presumably you're using some sort of source control and deployment
scripting already.

Options 2 and 3 are essentially the same, in that you're creating the
database from files which you need to copy to a filesystem first. The major
difference is that you can restore from a network drive, but you can't
attach a database on a network drive (usually). So if you have no access to
the server's local filesystem, but you do have access to a network share
accessible to the server, you could only use the restore option. The big
advantage of these options is obviously simplicity..

Personally, I would consider option 1 the best in the sense that it's the
most flexible - you have complete control over which objects and data are
loaded, and the layout of the files and filegroups can be decided together
with the DBA before you create the database. But if you're always installing
identical databases in very similar environments (eg. inside a large
company), then the simplicity of one of the other options is likely to be
better for you - less time and effort required.

Simon|||On Fri, 2 Apr 2004 22:07:49 +0200, Simon Hayes <sql@.hayes.ch> wrote:

> "great_googley_moogley" <pow_pow1476@.yahoo.com> wrote in message
> news:dda0ee86.0404021123.46fd0e4e@.posting.google.c om...
>> Greetings,
>>
>> I am in the process of installing a SQL database at a customer
>> location. I have determined that there are 3 ways to do this, and I
>> wanted to know which is the best of the 3.
>>
>> 1 Install From Script.
>> In this method I create the database and its objects in scripts that
>> are run via osql utility on the SQL server machine. For loading any
>> initial data that I need in the database I also run bcp commands.
>>
>> 2 Install from a backup
>> In this method I created an empty database on the SQL server, and then
>> restore over it the database from a backup of the database that I need
>> to deploy. Then I add or re-attach the users for the database. I
>> perform all of these operations using osql as well.
>>
>> 3 Install by attaching the data files.
>> In this method I created an empty database on the SQL server, and then
>> I attach the data files to the database using the sp_attach procedure.
>> Then I add or re-attach the users for the database. I perform all of
>> these operations using osql as well.
>>
>> Although it is no problem for me to use any of these methods, I wanted
>> to know from you veterans out there what the best practices are. And
>> also if there are any unseen hazards for each method above. Or if I
>> am totally off-the-target, and there is another method that is the
>> preferred way.
>>
>> Thanks in Advance,
>> roger
> All three options can give the same results, as you say, but there are
> some
> differences in functionality.
> Option 1 is good if you need to load different data for different
> clients -
> with other methods you'd need a copy of the database for every possible
> data
> set you need to provide, but with scripts the object scripts are the
> same,
> and only the data (BCP) files change. Or from the database object
> perspective, you can provide different subsets and/or versions of stored
> procedures etc. to different clients. In addition, this option doesn't
> require you to have any access to the filesystem, which can be useful in
> environments with limited bandwidth or tight security requirements. The
> downside - if you consider it one - is that you need to manage the
> scripts,
> but presumably you're using some sort of source control and deployment
> scripting already.
> Options 2 and 3 are essentially the same, in that you're creating the
> database from files which you need to copy to a filesystem first. The
> major
> difference is that you can restore from a network drive, but you can't
> attach a database on a network drive (usually). So if you have no access
> to
> the server's local filesystem, but you do have access to a network share
> accessible to the server, you could only use the restore option. The big
> advantage of these options is obviously simplicity..
> Personally, I would consider option 1 the best in the sense that it's the
> most flexible - you have complete control over which objects and data are
> loaded, and the layout of the files and filegroups can be decided
> together
> with the DBA before you create the database. But if you're always
> installing
> identical databases in very similar environments (eg. inside a large
> company), then the simplicity of one of the other options is likely to be
> better for you - less time and effort required.
> Simon

I am using option 1 too. But there is one thing I cannot do with it. This
thing is installing custom dll implementing extended stored procedure into
remote computer. Do you know any solution for this problem?

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||great_googley_moogley (pow_pow1476@.yahoo.com) writes:
> 1 Install From Script.
> In this method I create the database and its objects in scripts that
> are run via osql utility on the SQL server machine. For loading any
> initial data that I need in the database I also run bcp commands.

Be careful to use the -I option to enable the option QUOTED_IDENTIFIERS.
This is paricularly important if you are using indexed views.

> 2 Install from a backup
> In this method I created an empty database on the SQL server, and then
> restore over it the database from a backup of the database that I need
> to deploy. Then I add or re-attach the users for the database. I
> perform all of these operations using osql as well.
> 3 Install by attaching the data files.
> In this method I created an empty database on the SQL server, and then
> I attach the data files to the database using the sp_attach procedure.
> Then I add or re-attach the users for the database. I perform all of
> these operations using osql as well.

This far, all methods may appear to be the same. But then comes the next
step: you need to install fixes, updates and changes. Suddenly option
1 is the only one possible.

In our shop we ship install kits both for installation of new databases,
and upgrades. We have a toolset that includes a tool building an empty
database from SourceSafe. You can do that do step, so that you first
extract the files, and then build from the disk at the remote script.
The toolset also includes a tool that can build update scripts with
changed files, and the same applies to them that you can run in two steps.
If you're curious, it's available at http://www.abaris.se/abaperls/ as
freeware.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Igor Solodovnikov (igor@.helpco.kiev) writes:
> I am using option 1 too. But there is one thing I cannot do with it. This
> thing is installing custom dll implementing extended stored procedure into
> remote computer. Do you know any solution for this problem?

See my reply for the original poster, about the toolset we use.

When our installation staff build new databases or run update scripts at
customer sites, they rarely run them directly. They package everything
in Windows Installer kits. I don't know too much about that part of the
process, but I guess this is the way to package it. Of course, learning
Wise and all that may be too much - packaing in a BAT file could work too.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 2 Apr 2004 22:15:15 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

> Igor Solodovnikov (igor@.helpco.kiev) writes:
>> I am using option 1 too. But there is one thing I cannot do with it.
>> This
>> thing is installing custom dll implementing extended stored procedure
>> into
>> remote computer. Do you know any solution for this problem?
> See my reply for the original poster, about the toolset we use.
> When our installation staff build new databases or run update scripts at
> customer sites, they rarely run them directly. They package everything
> in Windows Installer kits. I don't know too much about that part of the
> process, but I guess this is the way to package it. Of course, learning
> Wise and all that may be too much - packaing in a BAT file could work
> too.

Thank you for answer. I must say that I also using Wise and Windows
Installer. My installer copies all required files to local computer and
then executes another utility (written by me) which can install new
instance of msde or create database through SQL script on existing
instance of SQL server/msde. When existing instance is on local computer I
can simply copy dll into sql server directory and my extended stored proc
will work. But I cant do that when existing instance is on another
computer.
Using SQL script to create database is very convenient but it has this
problem which complicates creating extended stored procs.
To solve this problem I must create one more Windows Installer package and
run it on remote computer. This is ugly.

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

Best way to create a remote database and data within it?

Is there any tool that will allow me to create a script for creating a database with all its objects like triggers, indexes, relationships etc. and also populating the tables with data ? So far I haven't found any.

Enterprise Manager will easily let you script all the objects. Right click on an object and select All Tasks and then Generate SQL Script. You can then elect to generate scripts for all objects.

You can use a third party tool to script the data (I have usedRed Gate's SQL Data Compare). Or you could generate scripts using a SELECT statement if you really wanted to.

Best way to connect to a remote SQL server using the internet.

Can any one suggest me the best way to connect to a remote SQL server using
the internet.
The option that I know is: Use of remote IP address to connect. Is there any
other options availabe to access the SQL server like using ASP pages or some
thing else. ?
I want to ensure that the connection is stable and the speed is good.
If you can suggest me any site where I can look for optimising SQL Server on
the internet, it would be useful.
FYI - I am using VB as the Front End.
Thanks and Regards,
PeriHey Peri,
I have used SQL Server 2000 with Visual Basic 6.0, Visual Basic.Net and ASP.
Net. All you really have to do is when you use for example an ADO connection
, pass the server's ip instead of its name. If it is located with your netwo
rk you can use the servers
name. You don't really need any special settings on the server. Just make su
re it has a public ip and no firewall is blocking any ports. There is a port
that SQL uses but I am not totally sure which one it is. ]
Danny Cabrera
dcabrera@.medstarholdings.com
System Programmer