I have a table JobDescription with the columns:
JobId
StartTime
EndTime
Priority
etc.
I want the Filter in that way that only the rows of the JobDescription of a
certain Email Adress will be synchronized.
Can anybody tell me how this work?
thx
mathon
But which rows from which email would be displayed is only clear when the
user logs in the application with his email-adress. So I think the static
filter is not the right. Should I use dynamic filter? - How do I have to
quote it in that way in the Publication Process and then in my code? -
Unfortunately I haven't found a good sample for that.
mathon
|||Yes - dynamic filters is the way to go. If this is per
user, you can have a column for the loginname (the data
returned from select suser_sname()) and use that in the
filter. The dynamic filter option is a subset of the
horizontal filters part of the wizard and will appear if
you have selected the advanced options (first checkbox in
the wizard). Your dynamic filter will look like this:
SELECT <published_columns> FROM [dbo].[JobDescription]
WHERE JobDescription.SuserSName = SUSer_SName().
If you don't want another column in the table, and want
to map logins to email addresses, you can publish the
mapping table and apply the dynamic filter to that table,
or alternatively encapsulate the whole thing in a UDF to
be used in the where clause.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Dear Mr. Ibison!
Let me reflect once to know If I'll be right:
The method in the Login-mask selects the the Email of the User (Engineer) ->
SELECT Email FROM Engineer WHERE UserId=? AND password=?.
So in the Publication Wizard:
1) First I check "Horizontally, by filtering the rows published data"
2) Then I check "Yes, enable dynamic filters"
3) Then I select the Table to filter - [dbo].[JobDescription]
4) Then the Select Statement looks as follows:
SELECT <published_columns> FROM [dbo].[JobDescription] WHERE
[dbo].[JobDescription].EmailEngineer = EngineerLogin()
But when I want to go further the following error occurs:
"The WHERE clause 'JobDescription.EmailEngineer = EngineerLogin()' could not
be executed on table '[dbo].[JobDescription]'.
Error 195. 'EngineerLogin() is not a recognized function name.
Do you know what went wrong?
(The function exists in my class-file.)Regards
mathon
"Paul Ibison" wrote:
> Yes - dynamic filters is the way to go. If this is per
> user, you can have a column for the loginname (the data
> returned from select suser_sname()) and use that in the
> filter. The dynamic filter option is a subset of the
> horizontal filters part of the wizard and will appear if
> you have selected the advanced options (first checkbox in
> the wizard). Your dynamic filter will look like this:
> SELECT <published_columns> FROM [dbo].[JobDescription]
> WHERE JobDescription.SuserSName = SUSer_SName().
> If you don't want another column in the table, and want
> to map logins to email addresses, you can publish the
> mapping table and apply the dynamic filter to that table,
> or alternatively encapsulate the whole thing in a UDF to
> be used in the where clause.
> HTH,
> Paul Ibison (SQL Server MVP)
>
>
|||Mathon,
firstly, I must clarify that the ability to filter data
on a per subscriber basis is part of merge replication
only (out of the box), so that is the scenario we are
looking at.
Your login mask may or may not be an issue. Is the
username and password used to log into sql server - ie is
this a SQL Server login? If it is, then filtering will be
simpler. In the wizard, ensure the advanced options are
enabled, and after selecting horizontal filters, select
the dynamic option. Your EngineerLogin() function as far
as I can see is a function you've created in your VB
code, so is not accessible to SQL Server (CLR integration
is a posibility in SQL 2005 but not SQL 2000). So, to map
the login name to the email address, you'll need a user-
defined function to be created in SQL Server. This
function will take suser_sname() as a parameter and will
return the email address as a return value.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
The password and userId is the log-in information for my mobile application
and not the login for the sql server.
Is this more complicated?regards
mat
"Paul Ibison" wrote:
> Mathon,
> firstly, I must clarify that the ability to filter data
> on a per subscriber basis is part of merge replication
> only (out of the box), so that is the scenario we are
> looking at.
> Your login mask may or may not be an issue. Is the
> username and password used to log into sql server - ie is
> this a SQL Server login? If it is, then filtering will be
> simpler. In the wizard, ensure the advanced options are
> enabled, and after selecting horizontal filters, select
> the dynamic option. Your EngineerLogin() function as far
> as I can see is a function you've created in your VB
> code, so is not accessible to SQL Server (CLR integration
> is a posibility in SQL 2005 but not SQL 2000). So, to map
> the login name to the email address, you'll need a user-
> defined function to be created in SQL Server. This
> function will take suser_sname() as a parameter and will
> return the email address as a return value.
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||OK - in that case there might be issues. Do you use the
same login to SQL Server for everyone? Is the mobile
device used by an individual user or multiple users?
In the easiest scenario, the device is used by the same
user each time. Typically you'll be using a sql server
login which doesn't differentiate between users, so we
can't rely on this in the filter. If this is true, you
could set the filter to use HOST_NAME(). In the merge
agent's job step for this subscriber, add -HOSTNAME
[emailaddress] to filter the data (replace emailaddress
with the individuals email address). The filter will be
as follows:
SELECT <published_columns> FROM [dbo].[JobDescription]
WHERE JobDescription.email = HOST_NAME().
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
Yes the device is always used by the same user and everybody has the same
login and password (sa,sa) to the SQL Server database. But i do not exactly
know what you mean with merge agents job step? Where should I add
-HOSTNAME[emailaddress]? And is it then still dynamically when I am not able
to filter according to the login a user makes in my mobile application?
regards
mat
"Paul Ibison" wrote:
> OK - in that case there might be issues. Do you use the
> same login to SQL Server for everyone? Is the mobile
> device used by an individual user or multiple users?
> In the easiest scenario, the device is used by the same
> user each time. Typically you'll be using a sql server
> login which doesn't differentiate between users, so we
> can't rely on this in the filter. If this is true, you
> could set the filter to use HOST_NAME(). In the merge
> agent's job step for this subscriber, add -HOSTNAME
> [emailaddress] to filter the data (replace emailaddress
> with the individuals email address). The filter will be
> as follows:
> SELECT <published_columns> FROM [dbo].[JobDescription]
> WHERE JobDescription.email = HOST_NAME().
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Mathon,
the hostname will be dynamic in the sense of each
subscription will be able to get different data. This
means that each mobile device can have different data. It
doesn't mean that if a different user logs onto the
device he'll see different data - the data is partitioned
according to device. If there is a 1:1 relationship
between users and devices then this is fine.
The merge agent is a job. The middle step runs the merge
executable. In this step there will be a number of
parameters , and at the end you can add -HOSTNAME with a
value. If you are doing this programatically, the
HOSTNAME is a property of the merge object.
I recommend that you set it up and have a play around
with the dynamic filtering in a test environment to see
if it meets your requirements.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
Unfortunately I am not so familiar with the Replication Mechanism. In my
application I realized the replication only in that way that all my tables
would be replicated and no filters were set. I do this in the Publication
Wizard in the SQL Server Enterprise Manager. In the code I only quote then
the properties for the Replication like login, publisher, etc. (The
Replication works, but as said I want to filter the one table
JobDescription.)
So I am not sure how I should execute your quoted steps to reach my aim. Can
you probably give me a simpler and/or more detailed explanation?:-/ sorry..
mathon
No comments:
Post a Comment