Replication Error in Visual Basic

Hi Group,
I have an SQL Server with a database called support for which I have setup a
merge replication subscription called "support".
On my laptop, I have a support program using MSDE locally to access an exact
copy of the data as the main SQL server has.
Whenenver I try to synchronise using the SQLDistribution activeX control, I
get the following message:
"Cannot create transactional subscription to merge publication "support".
The publication type should either be transactional(0) or snapshot(1) for
this operation."
The trouble is, how I see it, I have to used merged because several clients
would have disconnected data sources and any or all could effectively change
the data.
Any help on this appreciated.
Jim
I think your script is wrong. Could you post it 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
|||Hi Hilary,
Thanks for the reply. I figured out I actually needed to use the "merge"
activeX control and not the distribution one. My code is below but I am
still receiving this error:
"The merge process was unable to add the pull subscription from
'JBHOME':'Support:'Support'."
JBHOME being my SQL server, support being the database name and also the
publication name. I have tried various variations on the below code but to
no avail.
-- CODE: --
With SQLMerge
.Publisher = "JBHOME"
.PublisherDatabase = "Support"
.Publication = "Support"
.PublisherSecurityMode = NT_AUTHENTICATION
.Distributor = "JBHOME"
.DistributorSecurityMode = NT_AUTHENTICATION
.Subscriber = "(Local)"
.SubscriberDatabase = "Support"
.SubscriberDatasourceType = SQL_SERVER
.SubscriberSecurityMode = NT_AUTHENTICATION
.SubscriberLogin = "sa"
.SubscriberPassword = "mypassword"
.SubscriptionType = ANONYMOUS
.SynchronizationType = AUTOMATIC
.AddSubscription EXISTING_DATABASE, NONE
.Initialize
.Run
.Terminate
End With
-- END CODE --
Kind Regards
Jim
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:1113405033.118118.312480@.g14g2000cwa.googlegr oups.com...
>I think your script is wrong. Could you post it 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
>
|||FYI,
What I am attempting to do is have a server with a database on it and be
able for a vb written client program to "merge" update the remote server.
The clients will use MSDE as their disconnected database, and all this will
work over the internet (hopefully!!!)
Jim
"Reapes" <reapesuk-@.hotmail.com> wrote in message
news:xvb7e.85688$Nr5.22780@.fe2.news.blueyonder.co. uk...
> Hi Hilary,
> Thanks for the reply. I figured out I actually needed to use the "merge"
> activeX control and not the distribution one. My code is below but I am
> still receiving this error:
> "The merge process was unable to add the pull subscription from
> 'JBHOME':'Support:'Support'."
> JBHOME being my SQL server, support being the database name and also the
> publication name. I have tried various variations on the below code but to
> no avail.
> -- CODE: --
> With SQLMerge
> .Publisher = "JBHOME"
> .PublisherDatabase = "Support"
> .Publication = "Support"
> .PublisherSecurityMode = NT_AUTHENTICATION
> .Distributor = "JBHOME"
> .DistributorSecurityMode = NT_AUTHENTICATION
> .Subscriber = "(Local)"
> .SubscriberDatabase = "Support"
> .SubscriberDatasourceType = SQL_SERVER
> .SubscriberSecurityMode = NT_AUTHENTICATION
> .SubscriberLogin = "sa"
> .SubscriberPassword = "mypassword"
> .SubscriptionType = ANONYMOUS
> .SynchronizationType = AUTOMATIC
> .AddSubscription EXISTING_DATABASE, NONE
>
> .Initialize
> .Run
> .Terminate
> End With
>
> -- END CODE --
> Kind Regards
> Jim
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:1113405033.118118.312480@.g14g2000cwa.googlegr oups.com...
>

replication and log shrinking

Since I created a replication (snapshot basic - push) when I'm running the backup log ... with truncate_ only....the size of the transaction log don't lessens. why? (sql2005 without sp)I set to simple the recovery mode and back to full....nothing
the usedPages value is very large...estimatedpages is very slow....
but not shrinking....|||

check of any open transaction . DBCC Opentran

Madhu

|||thnx
I run and and this is the result:

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (4990:4944:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What can i do with this?|||

From BOL

If neither database_name nor database_id is specified, the default is the current database.

Use DBCC OPENTRAN to determine whether an open transaction exists within the log. When using the BACKUP LOG statement, only the inactive portion of the log can be truncated; an open transaction can cause the log to not truncate completely. In earlier versions of Microsoft? SQL Server?, either all users had to log off or the server had to be shut down and restarted to clear uncommitted transactions from the log. With DBCC OPENTRAN, an open transaction can be identified (by obtaining the system process ID from the sp_who output) and terminated, if necessary.

you need to termintate this open transaction

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

http://www.sql-server-performance.com/dbcc_commands.asp

google it you will fine many

Madhu

|||I stopped the replication (snapshot) and after I could shrink the log....
So this is a bug? ( the db running without any sp)

I tried....I re-set the replication and the symptom is occurred again....
so when I set a replication on this db, I can't shrink the transaction log anymore...

Replicating structure only not data

I have a SQL 2005 database that I am using with a website. This basic website will be sold to other companies and ran on their servers with different URLs. Since, All of these databases will store different data, I'm not sure how I can make updates to original database and replicate those structure changes to the other DBs without changing the data also. Is there a way to automate the replication of structural DB changes without replicating the data along with it?

Thanks,

Kirk

You can enable @.replicate_dll at publication level, which will push the schema change to your subscriber. And you can set all the ins/upd/del commands to NONE for all of your articles, which will ignore all the data changes that took places on the publisher.

Gary

|||

Thanks Gary,

I am not new to SQL 2005 but I have never done any replication. Could you lead me to a good reference that would explain how to do some of the things that you wrote about? Any help is greatly appreciated.

Kirk

|||

I think the following would be a good start

General info on transactional replication

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

Creating create publication - by default @.replicate_ddl is on, so it will replcate ddl (aka - schema changes)

http://msdn2.microsoft.com/en-gb/library/ms147855.aspx
http://msdn2.microsoft.com/en-gb/library/ms188738.aspx

Create article - you want to set @.ins_cmd='NONE', @.upd_cmd='NONE', @.del_cmd='NONE'

http://msdn2.microsoft.com/en-gb/library/ms173857.aspx

Regards,

Gary

|||Thanks Gary, I will take a look at those links.