Working with Merge Replication between SQL Server 2000 and SQL Server
CE 2.0.
I was doing some filtering on the Publication with "host_name()"
function. I was comparing host_name() function with a nvarchar column,
so all was working pretty well.
In order to filter all appointments for a specific patient, I was
trying to assign the HostName property of the replication object with
patientId. This patientId is a uniqueidentifier column, therefore, when
building the where clause condition in the Publication's properties
(patientId = HOST_NAME()), I got an error saying that cannot compare
varchar with uniqueidentifier.
Any solution to this? I think I should compare the uniqueidentifier
column rather than adding another nvarchar column
(patientId2)containing same value as patientId original column?
Besides: I need a patient only can see his/her appointments, but that a
doctor can see all appointments from all his patients. Appointment
table contains doctorId and a patientId fields, which both have
uniqueidentifier datatype.
What should be the best filter for this case? Would I need another
publication or a gool filter would done it?
Thanks in advance.
have a look at fn_varbintohexstr.
For the second one it sounds like you need a second publication where you
filter on the doctorID.
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
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1128333296.020256.272540@.g44g2000cwa.googlegr oups.com...
> Working with Merge Replication between SQL Server 2000 and SQL Server
> CE 2.0.
> I was doing some filtering on the Publication with "host_name()"
> function. I was comparing host_name() function with a nvarchar column,
> so all was working pretty well.
> In order to filter all appointments for a specific patient, I was
> trying to assign the HostName property of the replication object with
> patientId. This patientId is a uniqueidentifier column, therefore, when
> building the where clause condition in the Publication's properties
> (patientId = HOST_NAME()), I got an error saying that cannot compare
> varchar with uniqueidentifier.
> Any solution to this? I think I should compare the uniqueidentifier
> column rather than adding another nvarchar column
> (patientId2)containing same value as patientId original column?
> Besides: I need a patient only can see his/her appointments, but that a
> doctor can see all appointments from all his patients. Appointment
> table contains doctorId and a patientId fields, which both have
> uniqueidentifier datatype.
> What should be the best filter for this case? Would I need another
> publication or a gool filter would done it?
> Thanks in advance.
>
|||Thanks Hilary.
I'll take a look at that function.
Respecting to my second request, my initial idea was to only have one
publication for all the application instead of having several and
having to mantain them. Does it worth or is the normal approach to have
more than one publication? Isn't there another way of achieving what
I'm looking for? I would prefer to have only one publication, but if
there is not another way.......
Regards.
|||I would go for multiple publications. There is more administrative effort,
but slight.
You might be able to get away with a more complex filter, is something along
the line of
<> where PatientID in (select patientID from patients where something = case
when HOST_NAME() > 'M' then (select doctorID from doctors) else
host_name()end )
So you could build an elaborate case statement and do existence checks with
subqueries, or even use UDF's, but you are limited by what you can do on the
host end which is CE, and then there will be a performance hit while this
where condition is evaluated.
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
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1128341002.180658.194500@.g43g2000cwa.googlegr oups.com...
> Thanks Hilary.
> I'll take a look at that function.
> Respecting to my second request, my initial idea was to only have one
> publication for all the application instead of having several and
> having to mantain them. Does it worth or is the normal approach to have
> more than one publication? Isn't there another way of achieving what
> I'm looking for? I would prefer to have only one publication, but if
> there is not another way.......
> Regards.
>
|||I see your example too complex to build. Besides, I'm not sure I
understand your "where clause" example. What about when HOST_NAME() >
'M' ? Sorry but I don't understand it at all. Could you give me a
little more detailed example, with pseudo-code or something similar
please?
You mean you can elaborate case statements in the Publication's where
clause? And can I include subqueries and so on?
I understand UDF's are difficult when working against SQL Server CE,
aren't they?
I cannot achieve to convert host_name() to uniqueidentifier.
Publication's property page tells me cannot do that. Any example?
The alternative of RDA would be useful in this case?
Sorry to ask you for all kind of examples Hilary but I've never
experienced with Merge Replication before?
|||Hi again Hilary, I've achieved what I was looking for with the row
filtering I show you below. Don't know if it's good for performance or
not, but works great and I think meets my requirements. Anyway, any
opinion or new idea will be greatly appreciated:
SELECT <columnas_publicadas> FROM [dbo].[Appointment] WHERE
patientId in
(select patientId from Patient where patientFirstName = HOST_NAME())
or doctorId in
(select doctorId from Doctor where doctorFirstName = HOST_NAME())
This is a non-real "where clause", because it's not very useful to
filter rows based on the first name of the patient or doctor. Anyway,
in this case, I fill the Replication object's HostName property with
patient's or doctor's first name and the "OR" clause works great for
me! I understand that when patientId is not found, doctorId is checked.
If patientId is found, doctorId is not checked anymore.
I now would like to achieve the same with uniqueidentifiers but I'm not
able.
Is it better to have two publications for this case, or with this "OR
where clause" would be enough?
Thanks very much. I'm willing for any reply or advice.
|||basically its something bogus, but something I use every once and a while.
Host_name() >'m' compares this first value of host_name and checks to see if
its ascii value is greater than the ascii value of m.
so a host_name of 'alpha' would not meet this filtering criteria, whereas a
host_name of 'nancy' would. You can override the host_name() value by using
the -HostName parameter in your command string.
I can't really elaborate on the possible where clause or a case statement as
I don't know your schema. It looks like you are filtering on patients by
patientID. Note that you could issue a subquery if you have a doctor's table
of if the patients table is related to the doctor table via dri and you
extend the join filter it should work automatically.
I was hoping you would look at what I had written and go ah ha!
Can you post the schema of the doctors/patients tables?
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
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1128343662.941267.220180@.f14g2000cwb.googlegr oups.com...
> I see your example too complex to build. Besides, I'm not sure I
> understand your "where clause" example. What about when HOST_NAME() >
> 'M' ? Sorry but I don't understand it at all. Could you give me a
> little more detailed example, with pseudo-code or something similar
> please?
> You mean you can elaborate case statements in the Publication's where
> clause? And can I include subqueries and so on?
> I understand UDF's are difficult when working against SQL Server CE,
> aren't they?
> I cannot achieve to convert host_name() to uniqueidentifier.
> Publication's property page tells me cannot do that. Any example?
> The alternative of RDA would be useful in this case?
> Sorry to ask you for all kind of examples Hilary but I've never
> experienced with Merge Replication before?
>
|||I don't know if you've read my last post. I suppose yes. I've achieved
this way, with a where clause containing an OR:
SELECT <columnas_publicadas> FROM [dbo].[Appointment] WHERE
patientId in
(select patientId from Patient where patientFirstName = HOST_NAME())
or doctorId in
(select doctorId from Doctor where doctorFirstName = HOST_NAME())
I understand you don't agree with this approach at all
but as I've
already told you, I want to avoid having more tha one publication. How
do you see it?
My database schema goes as follows:
Table Patient: patientId (uniqueidentifier -PK), patientFirstName, etc.
Table Doctor: doctorId (uniqueidentifier -PK), doctorFirstName, etc.
Relation table - Table DoctorPatient: patientId (part of PK), doctorId
(part of PK), diseaseId(part of PK), rowguidcol (generated by the
Publication) and so on.
Note that a patient can have same doctor for different diseases. For
example: Patient A is attended by Doctor B for disease C, and Patient A
is also attented by Doctor B, this time for disease D.
Anyway, I cannot filter directly by GUID, that is, I cannot convert
host_name() to GUID!
Help me please!
I hope to have explained myself correctly enough. Really thanks for
your time Hilary.Regards.
|||Hi again Hilary,
My "final" where clause would be as follows:
SELECT <columnas_publicadas> FROM [dbo].[Appointment] WHERE
HOST_NAME() = convert(nvarchar(100), patientId) or HOST_NAME()
= convert(nvarchar(100), doctorId) and isClosed = 0
You can notice that I am now able to do the comparison between
uniqueidentifier and nvarchar. I thougth this comparison had to be done
in the other direction, but found this and worked. Would be better to
convert host_name() to uniqueidentifier or left it this way?
I've also noticed that this where clause consumes additional time. I
didn't expect, but this way the first replication delays in some
seconds respect an initial replication wihout where clauses. Is this
normal? Could be because I now haven't got many rows while this
attempts are being done?
Give me some feedback please. Regards.
|||This looks ok. I think you would be better off adding an int column to the
doctor and patient tables. Add the identity property to these columns, put
an index on them and then use these in your join condition. It will be more
efficient this way.
Other than that try to put an index on the unique identifier column to speed
up the queries.
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
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1128433459.393363.244450@.g14g2000cwa.googlegr oups.com...
> Hi again Hilary,
> My "final" where clause would be as follows:
> SELECT <columnas_publicadas> FROM [dbo].[Appointment] WHERE
> HOST_NAME() = convert(nvarchar(100), patientId) or HOST_NAME()
> = convert(nvarchar(100), doctorId) and isClosed = 0
> You can notice that I am now able to do the comparison between
> uniqueidentifier and nvarchar. I thougth this comparison had to be done
> in the other direction, but found this and worked. Would be better to
> convert host_name() to uniqueidentifier or left it this way?
> I've also noticed that this where clause consumes additional time. I
> didn't expect, but this way the first replication delays in some
> seconds respect an initial replication wihout where clauses. Is this
> normal? Could be because I now haven't got many rows while this
> attempts are being done?
> Give me some feedback please. Regards.
>
