Create Subscription Without Administrator Priveleges

Hi,
I have an application that attempts to create a subscription through the
Reporting Services API. I'm currently getting an error that states the
"'System.Web.Services.Protocols.SoapException: A subscription delivery error
has occurred. --> A subscription delivery error has occurred. --> The value
of parameter ''extensionSettings'' is not valid. Check the documentation for
information about valid values. --> The account you are using does not have
administrator privileges. A subscription cannot be created for
testuser@.company.com"
I did a bit more poking around and found that if I added TestUser, the
currently logged in account, to the administrator group of the machine that
has the Reporting Services webserver, I no longer had this problem and I was
able to create the subscription through the API. My domain users are all set
to Browsers through the Report Manager web interface, but I doubt this
matters since it is separate from the API.
I'm devising a work around for this problem now that doesn't use the default
credentials for the Reporting Service web service, but uses credentials of a
user who is an administrator of that machine. I believe this will work, but
I was wondering how else I might give users the privelege of creating
subscriptions without being administrators of the machine. Also, what other
methods (i.e. delete subscription) are priveleged to only administrators of
the machine?
Thanks, JoelTurns out they just can't create subscriptions for anybody else but
themselves.
Bugger! And I built a great little address book tool too!
-Joel
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:e$OO684xEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have an application that attempts to create a subscription through the
> Reporting Services API. I'm currently getting an error that states the
> "'System.Web.Services.Protocols.SoapException: A subscription delivery
> error has occurred. --> A subscription delivery error has occurred. -->
> The value of parameter ''extensionSettings'' is not valid. Check the
> documentation for information about valid values. --> The account you are
> using does not have administrator privileges. A subscription cannot be
> created for testuser@.company.com"
> I did a bit more poking around and found that if I added TestUser, the
> currently logged in account, to the administrator group of the machine
> that has the Reporting Services webserver, I no longer had this problem
> and I was able to create the subscription through the API. My domain users
> are all set to Browsers through the Report Manager web interface, but I
> doubt this matters since it is separate from the API.
> I'm devising a work around for this problem now that doesn't use the
> default credentials for the Reporting Service web service, but uses
> credentials of a user who is an administrator of that machine. I believe
> this will work, but I was wondering how else I might give users the
> privelege of creating subscriptions without being administrators of the
> machine. Also, what other methods (i.e. delete subscription) are
> priveleged to only administrators of the machine?
> Thanks, Joel
>|||Hi Joel,
I am looking into this issue and will update you as soon as possible when I
find any valueable things to add.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||All,
Following a lead, I checked the output of a GetPermissions call and this is
what I, an adminstrator of the webserver and a content manager Reporting
Services received for output.
Create Folder
Delete
Read Properties
Update Properties
Create Report
Create Resource
Create data source
Read Security Policies
Update Security Policies
The example in RS BOL states that I should see more permissions :
Delete
Execute and View
Read Properties
Update Properties
Update Parameters
Read Data Sources
Update Data Sources
Read Report Definition
Update Report Definition
Create Subscription
Delete Subscription
Read Subscription
Delete Report History
Update Subscription
Create Any Subscription
Delete Any Subscription
Read Any Subscription
Read Security Policies
Update Security Policies
Update Any Subscription
Read Policy
Update Policy
List Report History
Create Report History
Execute
Create Link
I ran it again with a user that had Browser permissions only and who wasn't
an administrator and it returned
Read Properties
My main concern is where are the
Create Subscription
Delete Subscription
Read Subscription
permissions'? If only for me, but also for TestUser.
Thx, Joel
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:puNmpb$xEHA.3956@.cpmsftngxa10.phx.gbl...
> Hi Joel,
> I am looking into this issue and will update you as soon as possible when
> I
> find any valueable things to add.
>
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||So it turns out that I had my path set to the root directory in the sample
application
Dim permissions As [String]() = rs.GetPermissions("/")
If change it to point directly to a report I get back the expected
permissions.
Dim permissions As [String]() = rs.GetPermissions("/Billing/Billing Group
Activity Detail")
Joel
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:eq0fb2AyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> All,
> Following a lead, I checked the output of a GetPermissions call and this
> is what I, an adminstrator of the webserver and a content manager
> Reporting Services received for output.
> Create Folder
> Delete
> Read Properties
> Update Properties
> Create Report
> Create Resource
> Create data source
> Read Security Policies
> Update Security Policies
> The example in RS BOL states that I should see more permissions :
> Delete
> Execute and View
> Read Properties
> Update Properties
> Update Parameters
> Read Data Sources
> Update Data Sources
> Read Report Definition
> Update Report Definition
> Create Subscription
> Delete Subscription
> Read Subscription
> Delete Report History
> Update Subscription
> Create Any Subscription
> Delete Any Subscription
> Read Any Subscription
> Read Security Policies
> Update Security Policies
> Update Any Subscription
> Read Policy
> Update Policy
> List Report History
> Create Report History
> Execute
> Create Link
> I ran it again with a user that had Browser permissions only and who
> wasn't an administrator and it returned
> Read Properties
> My main concern is where are the
> Create Subscription
> Delete Subscription
> Read Subscription
>
> permissions'? If only for me, but also for TestUser.
> Thx, Joel
>
> ""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
> news:puNmpb$xEHA.3956@.cpmsftngxa10.phx.gbl...
>> Hi Joel,
>> I am looking into this issue and will update you as soon as possible when
>> I
>> find any valueable things to add.
>>
>> Thank you for your patience and corporation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Online Partner Support Specialist
>> Partner Support Group
>> Microsoft Global Technical Support Center
>> ---
>> Get Secure! - http://www.microsoft.com/security
>> This posting is provided "as is" with no warranties and confers no
>> rights.
>> Please reply to newsgroups only, many thanks!
>|||For all to learn from ...
The permissions on subscriptions don't make sense. It seems the user is
blocked from creating a subscription for anybody only at the Report Manager
web site level, not at the API level if SendEmailToUserAlias=False. This is
evident by the CC and BCC text boxes not being visible when the user is not
an administrator of the machine RS is running on, and by RS NOT throwing an
error when the CC and BCC fields are used in the extensionParameters array
through the API call to CreateSubscription. However, the comments field is
not allowed through an API call or shown on the website and throws an error
if its use is attempted. It seems MSFT has implemented most security only at
the presentation tier (the web site), not the middle-tier. (However,
documentation on the all of the security is lacking.)
Thx, Joel
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:eZRrC7AyEHA.2348@.TK2MSFTNGP12.phx.gbl...
> So it turns out that I had my path set to the root directory in the sample
> application
> Dim permissions As [String]() = rs.GetPermissions("/")
> If change it to point directly to a report I get back the expected
> permissions.
> Dim permissions As [String]() = rs.GetPermissions("/Billing/Billing Group
> Activity Detail")
> Joel
> "Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
> news:eq0fb2AyEHA.3120@.TK2MSFTNGP12.phx.gbl...
>> All,
>> Following a lead, I checked the output of a GetPermissions call and this
>> is what I, an adminstrator of the webserver and a content manager
>> Reporting Services received for output.
>> Create Folder
>> Delete
>> Read Properties
>> Update Properties
>> Create Report
>> Create Resource
>> Create data source
>> Read Security Policies
>> Update Security Policies
>> The example in RS BOL states that I should see more permissions :
>> Delete
>> Execute and View
>> Read Properties
>> Update Properties
>> Update Parameters
>> Read Data Sources
>> Update Data Sources
>> Read Report Definition
>> Update Report Definition
>> Create Subscription
>> Delete Subscription
>> Read Subscription
>> Delete Report History
>> Update Subscription
>> Create Any Subscription
>> Delete Any Subscription
>> Read Any Subscription
>> Read Security Policies
>> Update Security Policies
>> Update Any Subscription
>> Read Policy
>> Update Policy
>> List Report History
>> Create Report History
>> Execute
>> Create Link
>> I ran it again with a user that had Browser permissions only and who
>> wasn't an administrator and it returned
>> Read Properties
>> My main concern is where are the
>> Create Subscription
>> Delete Subscription
>> Read Subscription
>>
>> permissions'? If only for me, but also for TestUser.
>> Thx, Joel
>>
>> ""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
>> news:puNmpb$xEHA.3956@.cpmsftngxa10.phx.gbl...
>> Hi Joel,
>> I am looking into this issue and will update you as soon as possible
>> when I
>> find any valueable things to add.
>>
>> Thank you for your patience and corporation. If you have any questions
>> or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Online Partner Support Specialist
>> Partner Support Group
>> Microsoft Global Technical Support Center
>> ---
>> Get Secure! - http://www.microsoft.com/security
>> This posting is provided "as is" with no warranties and confers no
>> rights.
>> Please reply to newsgroups only, many thanks!
>>
>

create procedure for 'search agent'

Hey all,
Just when I thought I was starting to get the hang of T-SQL, I try a
project that now (after several failed attempts) has me thoroughly
. I'm trying to create a procedure for a 'search agent' that will
execute users' saved searches and generate email notifications via
xp_smtp_sendmail when matches are found. I want to schedule the procedure
to run as a job; and the resulting brief email to users should be in html
format as it will need to contain a hyperlink to the appropriate search
results. I think I have the basic pieces in place that I need but I could
sure use some help.
I (think) that I need to follow this basic concept to accomplish what I'm
after, but after working on this for several hours to no avail I've
reverted back to square one to try a fresh look. As a beginner this is
sometimes helpful for me in identifying the specific SQL tasks that need to
be carried out, though not yet in this case <g>. So the (very) beginnings
of the procedure, with several commented lines:
CREATE PROCEDURE dbo.usp_SearchAgent
AS
-- Need to do this for each t1.SearchID
-- in order to get the parameters to search but
-- need to then execute
SELECT t2.ParameterID, t2.FieldName, t2.SearchFor, t2.SearchOption,
t2.SearchFor2
FROM dbo.tblSavedSearch t1 INNER JOIN
dbo.tblSavedSearchParameters t2 ON t1.SearchID = t2.SearchID INNER
JOIN
dbo.tblSearchNotifications t3 ON t1.NotifyID = t3.NotifyID
WHERE (t1.NotifyUser = 1) AND (t1.NotifyExpires >= GETDATE()) AND
(DATEDIFF(d, t1.LastNotification, GETDATE())) >= t3.NotifyValue)
-- After we've executed each search
-- are there any matches?
IF ( SELECT COUNT(*)
FROM testVehicle t
WHERE ? ) > 0 -- Use the search parameters here
-- Or a better way to test..?
-- If we have matches then we need to generate an email via
xp_smtp_sendmail
-- Beyond a generic 'matches found' message the email needs to
include a link to the results (similar to, but with proper syntax):
-- '<A HREF=http://www.domain.com/searchpage.asp?
Action=AdvancedSearch&SearchID=' + SearchID + ">" + SearchName
-- Generate just a single email to the user for each SearchID with
matches
DECLARE @.TO varchar(100)
SET @.TO = ''
EXEC @.R = dbo.xp_smtp_sendmail
@.FROM = N'searchagent@.domain.com'
@.TO = N''
@.SUBJECT = N'Search Results'
@.MESSAGE = N'Matches found. Click the link below to view results.'
@.TYPE = N''
@.SERVER = N'mail.domain.com'
-- If we've sent an email then we'll need to update the
LastNotification in t1
Users' saved searches are stored in two tables (search, parameters);
another table contains the 'notification elements'; and I've included a
snipped version of the User and Vehicle tables:
CREATE TABLE [tblSavedSearch] (
[SearchID] [int] IDENTITY (1, 1) NOT NULL ,
[SearchName] [varchar] (100) NOT NULL ,
[UserID] [int] NOT NULL ,
[DateSaved] [datetime] NOT NULL CONSTRAINT [DF_tblSavedSearch_DateSaved]
DEFAULT (getdate()),
[NotifyUser] [bit] NULL ,
[NotifyID] [int] NULL ,
[NotifyExpires] [datetime] NULL CONSTRAINT
[DF_tblSavedSearch_NotifyExpires] DEFAULT (dateadd(day,7,getdate())),
[LastNotification] [datetime] NULL ,
CONSTRAINT [PK_tblSavedSearch] PRIMARY KEY CLUSTERED
(
[SearchID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, SampleSearch1, 1, 5/24/2005 00:00:00, 1, 1, NULL
2, SampleSearch2, 1, 5/23/2005 00:00:00, 1, 2, NULL
3, SampleSearch3, 2, 5/22/2005 00:00:00, 1, 3, NULL
#####
CREATE TABLE [tblSavedSearchParameters] (
[ParameterID] [int] IDENTITY (1, 1) NOT NULL ,
[SearchID] [int] NOT NULL ,
[FieldName] [varchar] (100) NULL ,
[SearchFor] [varchar] (100) NULL ,
[SearchOption] [varchar] (100) NULL ,
[SearchFor2] [varchar] (100) NULL ,
[DateSaved2] [datetime] NULL CONSTRAINT
[DF_tblSavedSearchParameters_DateSaved2]
DEFAULT (getdate()),
CONSTRAINT [PK_tblSavedSearchParameters] PRIMARY KEY CLUSTERED
(
[ParameterID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, 1, Make, Honda, Equals, NULL, 5/24/2005 00:00:00
2, 2, Model, Accord, Equals, NULL, 5/23/2005 00:00:00
3, 2, Year, 2003, Equals, NULL, 5/24/2005 00:00:00
4, 3, Make, Chev, Contains, NULL, 5/24/2005 00:00:00
#####
CREATE TABLE [tblSearchNotifications] (
[NotifyID] [int] IDENTITY (1, 1) NOT NULL ,
[NotifyFrequency] [varchar] (25) NOT NULL ,
[NotifyValue] [varchar] (3) NULL ,
CONSTRAINT [PK_tblSearchNotifications] PRIMARY KEY CLUSTERED
(
[NotifyID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, Daily, 1
2, Wly, 7
3, Monthly, 30
4, Yearly, 365
#####
CREATE TABLE [testUsers] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [varchar] (100) NOT NULL ,
CONSTRAINT [PK_testUsers] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, any@.where.com
2, any@.where.com
3, any@.where.com
#####
CREATE TABLE [testVehicle] (
[VehicleID] [int] IDENTITY (1, 1) NOT NULL ,
[Year] [int] NOT NULL ,
[Make] [varchar] (50) NOT NULL ,
[Model] [varchar] (100) NOT NULL ,
[VIN] [varchar] (17) NOT NULL
CONSTRAINT [PK_testVehicle] PRIMARY KEY CLUSTERED
(
[VehicleID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, 2003, Honda, Accord, 12345678901234567
2, 2002, Honda, Civic, 23456789012345678
3, 2005, Ford, Mustang, 34567890123456789
4, 2002, Chevrolet, Malibu, 45678901234567890
#####
Thanks in advance for any and all assistance.
Message posted via http://www.webservertalk.comHi
Look at using sp_executesql, the following will also give you the basics to
implement a solution:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
John
"The Gekkster via webservertalk.com" wrote:

> Hey all,
> Just when I thought I was starting to get the hang of T-SQL, I try a
> project that now (after several failed attempts) has me thoroughly
> . I'm trying to create a procedure for a 'search agent' that will
> execute users' saved searches and generate email notifications via
> xp_smtp_sendmail when matches are found. I want to schedule the procedure
> to run as a job; and the resulting brief email to users should be in html
> format as it will need to contain a hyperlink to the appropriate search
> results. I think I have the basic pieces in place that I need but I could
> sure use some help.
> I (think) that I need to follow this basic concept to accomplish what I'm
> after, but after working on this for several hours to no avail I've
> reverted back to square one to try a fresh look. As a beginner this is
> sometimes helpful for me in identifying the specific SQL tasks that need t
o
> be carried out, though not yet in this case <g>. So the (very) beginnings
> of the procedure, with several commented lines:
>
> CREATE PROCEDURE dbo.usp_SearchAgent
> AS
> -- Need to do this for each t1.SearchID
> -- in order to get the parameters to search but
> -- need to then execute
> SELECT t2.ParameterID, t2.FieldName, t2.SearchFor, t2.SearchOption,
> t2.SearchFor2
> FROM dbo.tblSavedSearch t1 INNER JOIN
> dbo.tblSavedSearchParameters t2 ON t1.SearchID = t2.SearchID INNE
R
> JOIN
> dbo.tblSearchNotifications t3 ON t1.NotifyID = t3.NotifyID
> WHERE (t1.NotifyUser = 1) AND (t1.NotifyExpires >= GETDATE()) AND
> (DATEDIFF(d, t1.LastNotification, GETDATE())) >= t3.NotifyValue)
> -- After we've executed each search
> -- are there any matches?
> IF ( SELECT COUNT(*)
> FROM testVehicle t
> WHERE ? ) > 0 -- Use the search parameters here
> -- Or a better way to test..?
> -- If we have matches then we need to generate an email via
> xp_smtp_sendmail
> -- Beyond a generic 'matches found' message the email needs to
> include a link to the results (similar to, but with proper syntax):
> -- '<A HREF=http://www.domain.com/searchpage.asp?
> Action=AdvancedSearch&SearchID=' + SearchID + ">" + SearchName
> -- Generate just a single email to the user for each SearchID with
> matches
> DECLARE @.TO varchar(100)
> SET @.TO = ''
> EXEC @.R = dbo.xp_smtp_sendmail
> @.FROM = N'searchagent@.domain.com'
> @.TO = N''
> @.SUBJECT = N'Search Results'
> @.MESSAGE = N'Matches found. Click the link below to view results.'
> @.TYPE = N''
> @.SERVER = N'mail.domain.com'
> -- If we've sent an email then we'll need to update the
> LastNotification in t1
>
> Users' saved searches are stored in two tables (search, parameters);
> another table contains the 'notification elements'; and I've included a
> snipped version of the User and Vehicle tables:
>
> CREATE TABLE [tblSavedSearch] (
> [SearchID] [int] IDENTITY (1, 1) NOT NULL ,
> [SearchName] [varchar] (100) NOT NULL ,
> [UserID] [int] NOT NULL ,
> [DateSaved] [datetime] NOT NULL CONSTRAINT [DF_tblSavedSearch_DateSaved]
> DEFAULT (getdate()),
> [NotifyUser] [bit] NULL ,
> [NotifyID] [int] NULL ,
> [NotifyExpires] [datetime] NULL CONSTRAINT
> [DF_tblSavedSearch_NotifyExpires] DEFAULT (dateadd(day,7,getdate())),
> [LastNotification] [datetime] NULL ,
> CONSTRAINT [PK_tblSavedSearch] PRIMARY KEY CLUSTERED
> (
> [SearchID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, SampleSearch1, 1, 5/24/2005 00:00:00, 1, 1, NULL
> 2, SampleSearch2, 1, 5/23/2005 00:00:00, 1, 2, NULL
> 3, SampleSearch3, 2, 5/22/2005 00:00:00, 1, 3, NULL
> #####
>
> CREATE TABLE [tblSavedSearchParameters] (
> [ParameterID] [int] IDENTITY (1, 1) NOT NULL ,
> [SearchID] [int] NOT NULL ,
> [FieldName] [varchar] (100) NULL ,
> [SearchFor] [varchar] (100) NULL ,
> [SearchOption] [varchar] (100) NULL ,
> [SearchFor2] [varchar] (100) NULL ,
> [DateSaved2] [datetime] NULL CONSTRAINT
> [DF_tblSavedSearchParameters_DateSaved2]
DEFAULT (getdate()),
> CONSTRAINT [PK_tblSavedSearchParameters] PRIMARY KEY CLUSTERED
> (
> [ParameterID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, 1, Make, Honda, Equals, NULL, 5/24/2005 00:00:00
> 2, 2, Model, Accord, Equals, NULL, 5/23/2005 00:00:
00
> 3, 2, Year, 2003, Equals, NULL, 5/24/2005 00:00:00
> 4, 3, Make, Chev, Contains, NULL, 5/24/2005 00:00:0
0
> #####
>
> CREATE TABLE [tblSearchNotifications] (
> [NotifyID] [int] IDENTITY (1, 1) NOT NULL ,
> [NotifyFrequency] [varchar] (25) NOT NULL ,
> [NotifyValue] [varchar] (3) NULL ,
> CONSTRAINT [PK_tblSearchNotifications] PRIMARY KEY CLUSTERED
> (
> [NotifyID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, Daily, 1
> 2, Wly, 7
> 3, Monthly, 30
> 4, Yearly, 365
> #####
>
> CREATE TABLE [testUsers] (
> [UserID] [int] IDENTITY (1, 1) NOT NULL ,
> [EmailAddress] [varchar] (100) NOT NULL ,
> CONSTRAINT [PK_testUsers] PRIMARY KEY CLUSTERED
> (
> [UserID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, any@.where.com
> 2, any@.where.com
> 3, any@.where.com
> #####
>
> CREATE TABLE [testVehicle] (
> [VehicleID] [int] IDENTITY (1, 1) NOT NULL ,
> [Year] [int] NOT NULL ,
> [Make] [varchar] (50) NOT NULL ,
> [Model] [varchar] (100) NOT NULL ,
> [VIN] [varchar] (17) NOT NULL
> CONSTRAINT [PK_testVehicle] PRIMARY KEY CLUSTERED
> (
> [VehicleID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, 2003, Honda, Accord, 12345678901234567
> 2, 2002, Honda, Civic, 23456789012345678
> 3, 2005, Ford, Mustang, 34567890123456789
> 4, 2002, Chevrolet, Malibu, 45678901234567890
> #####
>
> Thanks in advance for any and all assistance.
> --
> Message posted via http://www.webservertalk.com
>|||Still struggling quite a bit with this one. For some reason things just
aren't clicking with me.
Beyond the DDL from the OP, I tried to create a view that retrieves the
correct rows (i.e. those saved searches that require a notification) which
is working properly:
#######
CREATE VIEW dbo.vw_SearchAgent
AS
SELECT t2.UserID, t2.SearchID, t3.ParameterID, t3.FieldName,
t3.SearchFor, t3.SearchOption, t3.SearchFor2, t2.LastNotification
FROM dbo.tblSavedSearchParameters t3 INNER JOIN
dbo.tblSavedSearch t2 ON t3.SearchID = t2.SearchID
LEFT OUTER JOIN
dbo.tblSearchNotifications t1 ON t2.NotifyID =
t1.NotifyID AND DATEDIFF(d, t2.LastNotification, GETDATE()) > t1.NotifyValue
WHERE (t2.NotifyUser = 1) AND (t2.NotifyExpires >= GETDATE())
GO
#######
I'm also trying to utilize a CASE expression when building the dynamic SQL,
like in this snippet:
CASE @.SearchOption
WHEN 'Contains' THEN ' AND FieldName LIKE ''%'' @.SearchFor + ''%'''
WHEN 'Starts with ...' THEN ' AND FieldName LIKE @.SearchFor + ''%'''
WHEN 'More than ...' THEN ' AND FieldName > @.SearchFor'
WHEN 'Less than ...' THEN ' AND FieldName < @.SearchFor'
WHEN 'Equal or more than ...' THEN ' AND FieldName >= @.SearchFor'
WHEN 'Equal or less than ...' THEN ' AND FieldName <= @.SearchFor'
WHEN 'Between' THEN ' AND FieldName >= @.SearchFor AND FieldName <=
@.SearchFor2'
ELSE ' AND FieldName = @.SearchFor'
END
This seems so simple: Execute each saved search against Table1 using the
appropriate saved search parameters; count the matches (if any); and
generate an email (if needed). But I can't seem to get it quite right. My
problem is with generating the correct SQL string when more than one search
parameter exists for a given SearchID.
I'm really stuck on this one, could anyone help out? Thanks.