DMO Anonymous Merge Pull

I adapted Hilary's DMO Example, and have tried ReplCtrl\VB\ReplSamp.vbp ...
WHAT CODE DO I NEED to .Initialize .Run .Terminate the SQLMerge?
Subscription was created using Windows Synchronization Manager,
Administrator Account on WinXPPro Laptop. VB is running in MS Access XP
Runtime .ade
CODE looks good, but is still missing 'something'
DMO Example -- last line is
objMergePullSubscriptions.Add objMergPullSubscription
Obviously FAILS because 'The subscription already exists.'
AIRCODE; Run via VPN to namedserver
Option Compare Database
Option Explicit
Private Sub cmdMergePublication_Click()
'On Error GoTo Err_cmdMergePublication_Click
Const SQLDMOSubscription_Anonymous = 2
Const SQLDMOReplSecurity_Normal = 0
Const SQLDMOReplSecurity_Integrated = 1
Const SQLDMOSubscription_All = 3
Const SQLDMOMergeSubscriber_Default = 2
Dim objServer, objReplication, objReplicationDatabases, objReplicationDatabase
Dim objMergePullSubscription, objMergePullSubscriptions,
objReplicationSecurity
Set objServer = CreateObject("SQLDMO.SQLServer")
objServer.Connect ".", "sa", "nyrv%fa"
Set objReplication = objServer.Replication
Set objReplicationDatabases = objReplication.ReplicationDatabases
Set objReplicationDatabase = objReplicationDatabases("CareSQL50207")
Set objMergePullSubscription = CreateObject("SQLDMO.MergePullSubscription2")
Set objReplicationSecurity = objMergePullSubscription.DistributorSecurity
objReplicationSecurity.SecurityMode = SQLDMOReplSecurity_Normal
objReplicationSecurity.StandardLogin = "sa"
objReplicationSecurity.StandardPassword = "d23&tmv"
With objMergePullSubscription
.AltSnapshotFolder = "C:\temp\"
.Distributor = "GSHSBS2000"
.DistributorSecurity.SecurityMode = SQLDMOReplSecurity_Normal
.DistributorSecurity.StandardLogin = "sa"
.DistributorSecurity.StandardPassword = "nyrv%fa"
.Publisher = "GSHSBS2000"
.PublicationDB = "CareSQL50207"
.Publication = "CareSQL50207"
.PublisherSecurity.SecurityMode = SQLDMOReplSecurity_Normal
.PublisherSecurity.StandardLogin = "sa"
.PublisherSecurity.StandardPassword = "nyrv%fa"
.SubscriberType = SQLDMOMergeSubscriber_Default
.SubscriptionType = SQLDMOSubscription_Anonymous
.SubscriberSecurityMode = SQLDMOReplSecurity_Normal
.SubscriberLogin = "sa"
.SubscriberPassword = "d23&tmv"
.UseFTP = False
End With
Set objMergePullSubscriptions = objReplicationDatabase.MergePullSubscriptions
'$$$$$ STOPS here: [MS][ODBC][SQLServer] The subscription already exists.
objMergePullSubscriptions.Add objMergePullSubscription
Set objMergePullSubscription = Nothing
Set objMergePullSubscriptions = Nothing
Set objReplicationDatabases = Nothing
Set objReplication = Nothing
Set objServer = Nothing
Exit_cmdMergePublication_Click:
Exit Sub
Err_cmdMergePublication_Click:
MsgBox Err.Description
Resume Exit_cmdMergePublication_Click
End Sub
TIA
Aubrey Kelley
this is to create the job, not to start it. I've been puzzling for a good
long while on trying to figure out how to start it.
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
"Aubrey" <Aubrey@.discussions.microsoft.com> wrote in message
news:9D3A8AF9-7A89-4BCA-B3A4-0D564D00C217@.microsoft.com...
> I adapted Hilary's DMO Example, and have tried ReplCtrl\VB\ReplSamp.vbp
...
> WHAT CODE DO I NEED to .Initialize .Run .Terminate the SQLMerge?
>
> Subscription was created using Windows Synchronization Manager,
> Administrator Account on WinXPPro Laptop. VB is running in MS Access XP
> Runtime .ade
> CODE looks good, but is still missing 'something'
> DMO Example -- last line is
> objMergePullSubscriptions.Add objMergPullSubscription
> Obviously FAILS because 'The subscription already exists.'
>
> AIRCODE; Run via VPN to namedserver
> Option Compare Database
> Option Explicit
> Private Sub cmdMergePublication_Click()
> 'On Error GoTo Err_cmdMergePublication_Click
> Const SQLDMOSubscription_Anonymous = 2
> Const SQLDMOReplSecurity_Normal = 0
> Const SQLDMOReplSecurity_Integrated = 1
> Const SQLDMOSubscription_All = 3
> Const SQLDMOMergeSubscriber_Default = 2
> Dim objServer, objReplication, objReplicationDatabases,
objReplicationDatabase
> Dim objMergePullSubscription, objMergePullSubscriptions,
> objReplicationSecurity
> Set objServer = CreateObject("SQLDMO.SQLServer")
> objServer.Connect ".", "sa", "nyrv%fa"
> Set objReplication = objServer.Replication
> Set objReplicationDatabases = objReplication.ReplicationDatabases
> Set objReplicationDatabase = objReplicationDatabases("CareSQL50207")
> Set objMergePullSubscription =
CreateObject("SQLDMO.MergePullSubscription2")
> Set objReplicationSecurity = objMergePullSubscription.DistributorSecurity
> objReplicationSecurity.SecurityMode = SQLDMOReplSecurity_Normal
> objReplicationSecurity.StandardLogin = "sa"
> objReplicationSecurity.StandardPassword = "d23&tmv"
> With objMergePullSubscription
> .AltSnapshotFolder = "C:\temp\"
> .Distributor = "GSHSBS2000"
> .DistributorSecurity.SecurityMode = SQLDMOReplSecurity_Normal
> .DistributorSecurity.StandardLogin = "sa"
> .DistributorSecurity.StandardPassword = "nyrv%fa"
> .Publisher = "GSHSBS2000"
> .PublicationDB = "CareSQL50207"
> .Publication = "CareSQL50207"
> .PublisherSecurity.SecurityMode = SQLDMOReplSecurity_Normal
> .PublisherSecurity.StandardLogin = "sa"
> .PublisherSecurity.StandardPassword = "nyrv%fa"
> .SubscriberType = SQLDMOMergeSubscriber_Default
> .SubscriptionType = SQLDMOSubscription_Anonymous
> .SubscriberSecurityMode = SQLDMOReplSecurity_Normal
> .SubscriberLogin = "sa"
> .SubscriberPassword = "d23&tmv"
> .UseFTP = False
> End With
> Set objMergePullSubscriptions =
objReplicationDatabase.MergePullSubscriptions
> '$$$$$ STOPS here: [MS][ODBC][SQLServer] The subscription already exists.
> objMergePullSubscriptions.Add objMergePullSubscription
> Set objMergePullSubscription = Nothing
> Set objMergePullSubscriptions = Nothing
> Set objReplicationDatabases = Nothing
> Set objReplication = Nothing
> Set objServer = Nothing
> Exit_cmdMergePublication_Click:
> Exit Sub
> Err_cmdMergePublication_Click:
> MsgBox Err.Description
> Resume Exit_cmdMergePublication_Click
> End Sub
> --
> TIA
> Aubrey Kelley
|||Ouch! At least now I do not feel 'so dumb'. Maybe PSS will be helpful now
that we are so far. Nothing but dead ends in the past ...
Aubrey
"Hilary Cotter" wrote:

> this is to create the job, not to start it. I've been puzzling for a good
> long while on trying to figure out how to start it.
> --
> 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
> "Aubrey" <Aubrey@.discussions.microsoft.com> wrote in message
> news:9D3A8AF9-7A89-4BCA-B3A4-0D564D00C217@.microsoft.com...
> ...
> objReplicationDatabase
> CreateObject("SQLDMO.MergePullSubscription2")
> objReplicationDatabase.MergePullSubscriptions
>
>
|||here is what I have so far - this will spill the anmes of the anonymous pull
subscriptions in the database Northwindsub.
Can't seem to get it to resolve to the job_id's for the merge pull though.
set objSQLServer=CreateObject("SQLDMO.SQLServer")
objSQLServer.loginSecure=True
objSQLServer.Connect "Publisher"
set objReplication=objSQLServer.Replication
wscript.echo objReplication.Distributor.DistributionServer
set objReplicationDatabase =
objReplication.ReplicationDatabases("Northwindsub" )
for each objMergepullSubscription in
objReplicationDatabase.MergepullSubscriptions
wscript.echo "objMergepullSubscription.Name"
wscript.echo objMergepullSubscription.Name
wscript.echo "objMergepullSubscription.MergeJObID"
wscript.echo objMergepullSubscription.MergeJObID
set QueryResults=objMergepullSubscription.EnumJobInfo
for b=1 to QueryResults.Rows
for a=1 to QueryResults.Columns
wscript.echo QueryResults.ColumnName(A)
wscript.echo QueryResults.GetColumnString(b,A)
next
next
wscript.echo objReplication.Distributor.DistributionServer
set QueryResults=objReplication.Distributor.EnumMergeA gentViews()
for b=1 to QueryResults.Rows
for a=1 to QueryResults.Columns
wscript.echo QueryResults.ColumnName(A)
wscript.echo QueryResults.GetColumnString(b,A)
next
next
set objDistributor=Nothing
next
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
"Aubrey" <Aubrey@.discussions.microsoft.com> wrote in message
news:2F4F23EF-3F58-44CD-8620-4BBA107F649F@.microsoft.com...[vbcol=seagreen]
> Ouch! At least now I do not feel 'so dumb'. Maybe PSS will be helpful now
> that we are so far. Nothing but dead ends in the past ...
> Aubrey
> "Hilary Cotter" wrote:
good[vbcol=seagreen]
ReplCtrl\VB\ReplSamp.vbp[vbcol=seagreen]
XP[vbcol=seagreen]
objMergePullSubscription.DistributorSecurity[vbcol=seagreen]
exists.[vbcol=seagreen]

DML against remote tables (MSSQL to DB2)

I created a linked server to a DB2 database and I can pull data fine, but when I try to insert/update/delete it tells me "SQL0471N Invocation of routine "SYSIBM.SQLTABLES" failed due to reason "00E7900C"" when trying: DELETE FROM DB2LinkedServer..SPACENAME.TABLENAME

I believe I need to send a clear string to DB2 that doesn't get compiled on the sql server side. Is there something like openquery that I can use for DML statements in SQL Server?Upon further review I found this site: http://support.microsoft.com/kb/270119/EN-US/

It shows how to use openquery to execute DML statements. nifty.

Distribution Agent TimeOut

I have setup pull transactional replication with multiple publications and
distribution agents.
One of my distribution agent always says the following error--
Timeout expired
(Source: ODBC SQL Server Driver (ODBC); Error number: S1T00)
while other agents are executed just fine..
any suggestions..?
Regards,
bump up LoginTimeout and QueryTimeout to 120.
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
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:65DC7C0C-6149-4DB0-8C28-06DAC9EAAA59@.microsoft.com...
> I have setup pull transactional replication with multiple publications and
> distribution agents.
> One of my distribution agent always says the following error--
> --
> Timeout expired
> (Source: ODBC SQL Server Driver (ODBC); Error number: S1T00)
> --
> while other agents are executed just fine..
> any suggestions..?
> Regards,

Distribution Agent Fails on Pull subscriptions

Environment:
SQLServer 2000 Enterprise w/SP3
Win 2000 Server w/SP3
In testing replication against a copy of our production database, when I run
the snapshot agent, it completes with success. When I then run the
distribution agent, I get the following error returned:
The name ' ' is not permitted in this context. Only constants, expressions,
or variables allowed here. Column names are not permitted.
(Source: STAGING2 (Data source); Error number: 128)
As near as I can tell, the script being processed when the error is
generated is:
================================================== ==
SET QUOTED_IDENTIFIER ON
GO
drop procedure [spBDCInsertAddressRecord]
GO
CREATE PROCEDURE [spBDCInsertAddressRecord] @.pAddress_id_out int OUT
AS
/************************************************** ************************************************** ******
INSERTS A BLANK ADDRESS RECORD AND RETURNS THE ADDRESS_ID VALUE
************************************************** ************************************************** *******/
INSERT INTO ADDRESS (LINE1) VALUES(" ")
SELECT @.pAddress_id_out = @.@.IDENTITY
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
================================================== =
This is a Transactional replication and the subscription is a Pull
subscription. The address_id column is the primary key for the table and is
an automatically generated INT data type.
Any thoughts?
TIA,
Ken
Try using INSERT INTO ADDRESS (LINE1) VALUES(' ') -- ie single quotes
Rgds,
Paul Ibison
"kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
news:3C35C8AF-8CD0-44E7-898C-BBF2C0978F5E@.microsoft.com...
> Environment:
> SQLServer 2000 Enterprise w/SP3
> Win 2000 Server w/SP3
> In testing replication against a copy of our production database, when I
run
> the snapshot agent, it completes with success. When I then run the
> distribution agent, I get the following error returned:
> The name ' ' is not permitted in this context. Only constants,
expressions,
> or variables allowed here. Column names are not permitted.
> (Source: STAGING2 (Data source); Error number: 128)
> As near as I can tell, the script being processed when the error is
> generated is:
> ================================================== ==
> SET QUOTED_IDENTIFIER ON
> GO
> drop procedure [spBDCInsertAddressRecord]
> GO
> CREATE PROCEDURE [spBDCInsertAddressRecord] @.pAddress_id_out int OUT
> AS
>
/************************************************** *************************
*******************************
> INSERTS A BLANK ADDRESS RECORD AND RETURNS THE ADDRESS_ID VALUE
>
************************************************** **************************
*******************************/
> INSERT INTO ADDRESS (LINE1) VALUES(" ")
> SELECT @.pAddress_id_out = @.@.IDENTITY
> RETURN 0
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> ================================================== =
> This is a Transactional replication and the subscription is a Pull
> subscription. The address_id column is the primary key for the table and
is
> an automatically generated INT data type.
> Any thoughts?
> TIA,
> Ken