filter

We have salesmen with laptops and want to use merge replication that
they will start via a synchronize button (I will use the ActiveX merge
object). Each salesman should only have data for their own customers.
Can the filtering be done at the subscriber during the pull
subscription, or do I have to create different publications on the
server? If the subscriber can filter, where can I find the filter
information in the activex module?
TIA
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Darin,
there is a HOSTNAME property of the merge object which can be used for the
dynamic filter, or alternatively it could just pick up the NETBIOS name by
default if this is suitable for your data (ie if subscriber computer names
appear in the publication data). In the publication, the filter needs to use
the HOST_NAME() function in order for this to work.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'm not real sure on this filtering for pull subscriptions.
My original publication is setup on the server via SQLDMO, and the
laptop will use ActiveX to pull the subscription.
Would I specify on the server while creating the merge article the
filter (salesman=HOSTNAME() where hostname is the name of the laptop) OR
would I do some filter on the laptop side?
Kinda confused.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Darin,
yes - on the publisher specify salesman=HOST_NAME()
The function HOST_NAME() returns the subscriber NETBIOS name. If you don't
want the NETBIOS name, the property HOSTNAME of the merge object can be used
to override it with something else.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok, I am getting an error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near 'dbo'.
[microsoft][odbc sql server driver][sql server] A column used in filter
clause 'ccod_customer IN dbo.fRSCustomer(RTRIM(HOST_NAME()))' either
does not exist in the table 'ARContact' or cannot be exlcuded from the
current partition.
ccod_customer is in my table ARContact. I created the funcion
rRSCustomer to return a table which is a list of customer #'s this
salesman is tied to.
Any ideas?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Here is my code that creates the articles for publication (VB.NET)
objMergeArticle = New MergeArticle2
With objMergeArticle
.Name = junk
.SourceObjectName = junk
.SourceObjectOwner = "dbo"
.CreationScriptOptions =
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_Pr imaryObject + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_DR I_Defaults + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_DR I_ForeignKeys + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_Cl usteredIndexes + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_DR I_Checks + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_PK UKAsConstraints + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_UD DTsToBaseTypes + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_No nClusteredIndexes
.SubsetFilterClause = "ccod_customer IN
dbo.fRSCustomer(RTRIM(HOST_NAME()))"
End If
End With
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Darin,
can you try 'ARContact.ccod_customer IN
dbo.fRSCustomer(RTRIM(HOST_NAME()))'.
If this doesn't work, I'll have a go myself. I haven't used a table-level
function like you're doing here, but have used a join to a lookup table to
achieve the same aim. Anyway, if my suggestion doesn't work, I'll have a
look at your method (although I vaguely remember another poster also having
problems with table-level functions ).
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I am still having the same problems. In addition, I am having another
issue which I posted in another thread (ActiveX Pull error).
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Darin,
do you have access to EM on the laptop to confirm you can set it all up
manually - as a sort of proof-of-concept? My guess is that you are using
MSDE and this is not possible, but if it is, it might help with both these
issues.
Rgds,
Paul Ibison
|||The first error is occuring on the server while creating the filters.
The second is on the laptop. I will try to setup the pull subscription
directly on the laptop to see if that works.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
sql