Access Crashes with CONTAINS in ServerFilter

Please excuse the crosspost from the Access forms group.
We are trying to implement a keyword search functionality in an Access 2002
project. We have set up SQL Server 2000 for full-text searching. Testing
SQL Server through Query Analyzer produces the expected results.
In a search tab on a form in an Access project, we dynamically construct a
WHERE clause for a subform. A keyword search is implemented by adding the
CONTAINS predicate to the ServerFilter string.
Once constructed, the ServerFilter is then applied to the subform and the
subform requeried to display the search's results. In testing, when we enter
criteria into the keyword field, Access crashes with no preceeding error
message. The crash occurs on the Requery statement for the subform. On the
screen, the subform fields all go blank, one gets the #Name error appearing
in it, and then the "Access has encountered . . . " message appears. An
example of an exact ServerFilter string producing the error is
"CONTAINS(*,'data')".
The documentation with Access says that the ServerFilter property is a WHERE
clause (without the WHERE). Is there an undocumented limitation that
predicates like CONTAINS cannot be used in the ServerFilter string, or am I
missing something? Any help appreciated.
can you run profiler on SQL Server to see what the sql command access is
running on SQL Server is.
if you can run this command in query analyzer, the problem is not with SQL
Server FTS, but rather with Access.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Smith" <PGCS@.online.nospam> wrote in message
news:AF65C9BB-3C00-4A95-B71B-95C6542CB6A1@.microsoft.com...
> Please excuse the crosspost from the Access forms group.
> We are trying to implement a keyword search functionality in an Access
2002
> project. We have set up SQL Server 2000 for full-text searching.
Testing
> SQL Server through Query Analyzer produces the expected results.
> In a search tab on a form in an Access project, we dynamically construct a
> WHERE clause for a subform. A keyword search is implemented by adding the
> CONTAINS predicate to the ServerFilter string.
> Once constructed, the ServerFilter is then applied to the subform and the
> subform requeried to display the search's results. In testing, when we
enter
> criteria into the keyword field, Access crashes with no preceeding error
> message. The crash occurs on the Requery statement for the subform. On
the
> screen, the subform fields all go blank, one gets the #Name error
appearing
> in it, and then the "Access has encountered . . . " message appears. An
> example of an exact ServerFilter string producing the error is
> "CONTAINS(*,'data')".
> The documentation with Access says that the ServerFilter property is a
WHERE
> clause (without the WHERE). Is there an undocumented limitation that
> predicates like CONTAINS cannot be used in the ServerFilter string, or am
I
> missing something? Any help appreciated.
|||The culprit is definitely Access. About that there was really no question.
I am hoping someone who uses SQL Server full text searching may have
encountered this problem with Access and know how to resolve it. Access is
producing a string of SQL SELECT statements (54 to be exact) which are in
the form SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*, 'string')).
With each successive call by Access, an additional set of parentheses is
added around the CONTAINS predicate.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:O47ozRtiEHA.2012@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> can you run profiler on SQL Server to see what the sql command access is
> running on SQL Server is.
> if you can run this command in query analyzer, the problem is not with SQL
> Server FTS, but rather with Access.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Paul Smith" <PGCS@.online.nospam> wrote in message
> news:AF65C9BB-3C00-4A95-B71B-95C6542CB6A1@.microsoft.com...
> 2002
> Testing
a[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
> enter
> the
> appearing
> WHERE
am
> I
>
|||Paul,
To the best of my knowledge, no one has reported a similar problem via this
newsgroup and I've been replying and monitoring it since it's inception in
late 2000. I'd recommend that you open a support case with Microsoft PSS SQL
Server &/or Access support to see if they have had past customers who have
encountered this possible bug. If you do, could you post back here what the
result was?
Other than the multiple additional set of parentheses that Access is adding,
can you issue a SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*,
'string') via Access and if so do you get the same error? Is your goal to
issue SQL FTS queries to SQL Server as the backend to an Access front-end?
Thanks,
John
"Paul Smith" <paulgcsmith@.hotmail.com> wrote in message
news:u7pEKwtiEHA.2696@.TK2MSFTNGP11.phx.gbl...
> The culprit is definitely Access. About that there was really no
question.
> I am hoping someone who uses SQL Server full text searching may have
> encountered this problem with Access and know how to resolve it. Access
is[vbcol=seagreen]
> producing a string of SQL SELECT statements (54 to be exact) which are in
> the form SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*, 'string')).
> With each successive call by Access, an additional set of parentheses is
> added around the CONTAINS predicate.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:O47ozRtiEHA.2012@.TK2MSFTNGP09.phx.gbl...
SQL[vbcol=seagreen]
construct[vbcol=seagreen]
> a
> the
> the
we[vbcol=seagreen]
error[vbcol=seagreen]
On[vbcol=seagreen]
An
> am
>
|||For anyone else who might encounter this problem: we seem to have resolved
the problem here. One of our developers noticed that we had gotten angled
quotation mark characters rather than the typical straight apostrophe as a
string delimiter. We aren't quite sure how this happened, but once this was
corrected, things seemed to start working correctly.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23Ic$xQxiEHA.2812@.tk2msftngp13.phx.gbl...
> Paul,
> To the best of my knowledge, no one has reported a similar problem via
this
> newsgroup and I've been replying and monitoring it since it's inception in
> late 2000. I'd recommend that you open a support case with Microsoft PSS
SQL
> Server &/or Access support to see if they have had past customers who have
> encountered this possible bug. If you do, could you post back here what
the
> result was?
> Other than the multiple additional set of parentheses that Access is
adding,[vbcol=seagreen]
> can you issue a SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*,
> 'string') via Access and if so do you get the same error? Is your goal to
> issue SQL FTS queries to SQL Server as the backend to an Access front-end?
> Thanks,
> John
>
> "Paul Smith" <paulgcsmith@.hotmail.com> wrote in message
> news:u7pEKwtiEHA.2696@.TK2MSFTNGP11.phx.gbl...
> question.
> is
in[vbcol=seagreen]
is[vbcol=seagreen]
> SQL
Access[vbcol=seagreen]
> construct
adding[vbcol=seagreen]
and[vbcol=seagreen]
> we
> error
> On
> An
a[vbcol=seagreen]
that[vbcol=seagreen]
or
>

Access controlon line level

Is it possible to implement control access on the line level usning standard
features?
I had to implement several tables User, groupe, article ..., map users to
groups and articles as well as some Sps in order that users or group of users
acces articles they are allowed to diplay.
I would highly appreciate any help
Thanks
"SalamElias" <eliassal@.online.nospam> wrote in message
news:644C3FAF-8D72-4EF4-847F-82E96B13ADD8@.microsoft.com...
> Is it possible to implement control access on the line level usning
> standard
> features?
> I had to implement several tables User, groupe, article ..., map users to
> groups and articles as well as some Sps in order that users or group of
> users
> acces articles they are allowed to diplay.
> I would highly appreciate any help
> Thanks
>
Not sure what you mean by "line level". If you mean on a per-row level,
then yes it is.
Especially if you use stored procs.
I'd keep a table of users and values. Each row would have a corresponding
value.
Do all DML work through a stored proc. So if you want a user to view data,
the stored proc would look at their credentials and as part of the WHERE
clause would select only rows that meet that set of credentials (plus any
others required for that specific query.)
It's also possible to do this via views if you want.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||"SalamElias" <eliassal@.online.nospam> wrote in message
news:644C3FAF-8D72-4EF4-847F-82E96B13ADD8@.microsoft.com...
> Is it possible to implement control access on the line level usning
> standard
> features?
> I had to implement several tables User, groupe, article ..., map users to
> groups and articles as well as some Sps in order that users or group of
> users
> acces articles they are allowed to diplay.
> I would highly appreciate any help
> Thanks
>
I think your question is about row-level security. Use stored procedures and
views to limit the rows that individual users can access.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Access controlon line level

Is it possible to implement control access on the line level usning standard
features?
I had to implement several tables User, groupe, article ..., map users to
groups and articles as well as some Sps in order that users or group of users
acces articles they are allowed to diplay.
I would highly appreciate any help
Thanks"SalamElias" <eliassal@.online.nospam> wrote in message
news:644C3FAF-8D72-4EF4-847F-82E96B13ADD8@.microsoft.com...
> Is it possible to implement control access on the line level usning
> standard
> features?
> I had to implement several tables User, groupe, article ..., map users to
> groups and articles as well as some Sps in order that users or group of
> users
> acces articles they are allowed to diplay.
> I would highly appreciate any help
> Thanks
>
Not sure what you mean by "line level". If you mean on a per-row level,
then yes it is.
Especially if you use stored procs.
I'd keep a table of users and values. Each row would have a corresponding
value.
Do all DML work through a stored proc. So if you want a user to view data,
the stored proc would look at their credentials and as part of the WHERE
clause would select only rows that meet that set of credentials (plus any
others required for that specific query.)
It's also possible to do this via views if you want.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||"SalamElias" <eliassal@.online.nospam> wrote in message
news:644C3FAF-8D72-4EF4-847F-82E96B13ADD8@.microsoft.com...
> Is it possible to implement control access on the line level usning
> standard
> features?
> I had to implement several tables User, groupe, article ..., map users to
> groups and articles as well as some Sps in order that users or group of
> users
> acces articles they are allowed to diplay.
> I would highly appreciate any help
> Thanks
>
I think your question is about row-level security. Use stored procedures and
views to limit the rows that individual users can access.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Access controlon line level

Is it possible to implement control access on the line level usning standard
features?
I had to implement several tables User, groupe, article ..., map users to
groups and articles as well as some Sps in order that users or group of user
s
acces articles they are allowed to diplay.
I would highly appreciate any help
Thanks"SalamElias" <eliassal@.online.nospam> wrote in message
news:644C3FAF-8D72-4EF4-847F-82E96B13ADD8@.microsoft.com...
> Is it possible to implement control access on the line level usning
> standard
> features?
> I had to implement several tables User, groupe, article ..., map users to
> groups and articles as well as some Sps in order that users or group of
> users
> acces articles they are allowed to diplay.
> I would highly appreciate any help
> Thanks
>
Not sure what you mean by "line level". If you mean on a per-row level,
then yes it is.
Especially if you use stored procs.
I'd keep a table of users and values. Each row would have a corresponding
value.
Do all DML work through a stored proc. So if you want a user to view data,
the stored proc would look at their credentials and as part of the WHERE
clause would select only rows that meet that set of credentials (plus any
others required for that specific query.)
It's also possible to do this via views if you want.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||"SalamElias" <eliassal@.online.nospam> wrote in message
news:644C3FAF-8D72-4EF4-847F-82E96B13ADD8@.microsoft.com...
> Is it possible to implement control access on the line level usning
> standard
> features?
> I had to implement several tables User, groupe, article ..., map users to
> groups and articles as well as some Sps in order that users or group of
> users
> acces articles they are allowed to diplay.
> I would highly appreciate any help
> Thanks
>
I think your question is about row-level security. Use stored procedures and
views to limit the rows that individual users can access.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

About plug-in algorithms in SQL Server 2005

Hi, all here,

I am having a question about plug-in algorithms in SQL Server 2005. Since we are able to implement our own algorithms in SQL Server 2005 analysis services architecture, so my question is: what benefits can to a great extent be achieved? Like say, we are going to implement a plug-in algorithm, so what considerations should be concerned?

Thanks a lot in advance for any guidance and help.

The plug-in architecture allows you to implement any algorithm that consumes data in attribute value pairs. Analysis Services takes care of many infrastructure needs so that your development can focus on what's important to you - the details of the algorithm.

For example, AS takes care of

data access, pulling the data from a variety of sources|||Hi, Jamie, thanks a lot.|||This white paper provides an overview of the benefits of the plug-in architecture , along with links to more technical resources: Add Custom Data Mining Algorithms to SQL Server 2005.|||

Hi, Raman, Thanks a lot.

In addition, would you please make sure that what languages are supported in data mining plug-in framework for implementation? Thanks a lot.

|||The plug-in infrastructure passes on client locale information as appropriate in the contextservices object but it's up to you as the algorithm implementor to return the appropriate localized strings (and numbers, as the case may be). Ideally, plug-in algorithms should support all 10 languages supported by SQL Server 2005 Analysis Services.|||Hi, Raman, thanks a lot.