Hi all,
I'm still stuck on this issue. Please find a re-cap below. I'd be really grateful if anyone
could shed some light on this.Thanks a million :o)
I am performing merge replication between SQL CE/Server 2000.
I would like to select the stores specific to each rep
unless the repid = 6, in which case I would like to select
all stores. Is this possible in merge replication?
I have been attempting to set the host_name() to my repID and
passing this Host_Name() into a table-valued UDF. The aim
being that the UDF will return a table of stores. My problem
is that I'm receiving an error and I would like to know if
UDF's accept Host_Name() as a parameter ?
My Dynamic Filter ...
SELECT <published_columns> FROM [dbo].[Stores] WHERE
Stores.RepID IN (SELECT * FROM SELECT_Stores_RepID(Host_Name()))
The Error I'm receiving ...
Error 170: Line 1: Incorrect Syntax near '('.
Cube,this is my previous reply:"I have only been able to use UDFs before
when they are scalar, eg SELECT <published_columns> FROM [dbo].[Region]
WHERE region.regionid = dbo.fn_TaxRate(host_name())works fine. In your case
can you restructure your query to return a scalar value and then use this
value combined with another table in a join + where clause perhaps?"You
mentioned that you would give it a try and post back your results - how did
it go?Regards,Paul Ibison
|||Hi Paul,
I gave this a go but I didn't have much joy, maybe I'm just confused as to how to
go about things. As you mentioned I changed my Dynamic Filter so that I returned
the RepID ...
SELECT <published_columns> FROM [dbo].[tbl_Usr] WHERE
CAST(FK_Rep_ID AS char(4)) = Host_Name()
Next I attempted to use this in an Extended Filter (which is where I ran into problems) ...
SELECT <published_columns> FROM [dbo].[tbl_Usr] INNER JOIN
[dbo].[Stores] ON Stores.RepID IN (SELECT * FROM
SELECT_Stores_RepID(tbl_Usr.TKT_RepID))
This product the syntax error ...
Error 170: Line 1: Incorrect Syntax near '.'.
This will work if I pass in a constant instead of tbl_Usr.TKT_RepID, which of
course I can't do. (At this stage you're probably saying to yourself...this guy just
tried the same thing he did in the Dynamic Filter...and is chasing his tail ;o)
I'm very open to trying alternative queries/approaches. At this stage I've looked at this
so many times I need some devine inspiration ;o)
Thanks in advance,
|||OK - I'll give it a try and post up an example later this afternoon (5pm UK
time).
Regards,
Paul
|||OK, have tested it and it works. Here is how I did it:
I had 2 tables: MainTable and LookupTable
MainTable
ID,Description
1,aaa
2,bbb
3,ccc
LookupTable
ID,Description,Host
1,aaa,Host1
2,bbb,Host1
3,ccc,Host999
My filter is LookupTable.Host = Host_Name()
The tables are joined on LookupTbale.ID = MainTable.ID
I set the -HOSTNAME parameter on the merge agent to be Host1 and only 2
records from each table are replicated. This successfully mimics what you
are doing with the subquery.
Any questions, please post back.
Paul Ibison
|||Hi Paul,
This is what I've been doing to date. But what I'd like is to select ALL records
if Host_Name() = some Rep ID set by the Merge Agent. Pseudocode...
IF (Host_Name() = 6)
SELECT * FROM Stores
ELSE
SELECT * FROM Stores WHERE RepID = Host_Name()
Maybe your filters do this, and I'm just missing the point ?
Thanks for the time you're giving me :o)
-- Paul Ibison wrote: --
OK, have tested it and it works. Here is how I did it:
I had 2 tables: MainTable and LookupTable
MainTable
ID,Description
1,aaa
2,bbb
3,ccc
LookupTable
ID,Description,Host
1,aaa,Host1
2,bbb,Host1
3,ccc,Host999
My filter is LookupTable.Host = Host_Name()
The tables are joined on LookupTbale.ID = MainTable.ID
I set the -HOSTNAME parameter on the merge agent to be Host1 and only 2
records from each table are replicated. This successfully mimics what you
are doing with the subquery.
Any questions, please post back.
Paul Ibison
|||Cube,
such logic is part of the linking table. If you had 3 stores, then the
linking table looks like this:
LookupTable
StoreID,Host
1,6
2,6
3,6
For the other hostnames there are records representing the corresponding
links:
LookupTable
StoreID,Host
1,6
2,6
3,6
2,999
HTH,
Paul Ibison
Showing posts with label shed. Show all posts
Showing posts with label shed. Show all posts
Saturday, February 25, 2012
Subscribe to:
Posts (Atom)