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.

Create cube file with Analysis Service Command?

Hi all experts,

Is it possible to create local cube file in SQL job agent with Analysis Service Command?

And how?

Thanks in advance.

Hi,

There is a similar thread about creating local cube files here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=380407&SiteID=1

|||

In an earlier post I mentioned you can use the ASCMD utility to submit MDX statements to an OLAP cube via the command line. You can use that for what follows.

To get the MDX statement script, I opened Profiler on my SSAS instance and then started up Excel 2007. In Excel, I connected to the OLAP cube and then in the OLAP Tools portion of the Data ribbon I selected to create a local cube. I used the wizard to define the cube and submitted the request. Profiler caught the submitted statement. Here it is (with some data removed to keep the size of this post down:

Code Snippet

CREATE GLOBAL CUBE [Adventure Works] STORAGE 'C:\Documents and Settings\bcsmith\My Documents\Adventure Works.cub' FROM [Adventure Works]
(
MEASURE [Adventure Works].[Internet Sales Amount],
MEASURE [Adventure Works].[Internet Order Quantity],
MEASURE [Adventure Works].[Internet Extended Amount],
MEASURE [Adventure Works].[Internet Tax Amount],
DIMENSION [Adventure Works].[Account].[Accounts]
,
DIMENSION [Adventure Works].[Department].[Departments]
,
DIMENSION [Adventure Works].[Destination Currency].[Destination Currency]
(
LEVEL [Destination Currency],
MEMBER [Destination Currency].[Destination Currency].&[US Dollar]
),
DIMENSION [Adventure Works].[Employee].[Employees]
,
DIMENSION [Adventure Works].[Organization].[Organizations]
,
DIMENSION [Adventure Works].[Scenario].[Scenario]
(
LEVEL [Scenario],
MEMBER [Scenario].[Scenario].&[1]
)

)

Hope that helps you get started.

Bryan

|||Thanks Adrian and Bryan for the kindly advice. I am going to try it.

Thanks again.

CREATE Credential Secret Storage

I am hoping to use SQL Server Agent 2k5 to run jobs in the security context of another account. I have successfully done this, so I know it works.

My question is: what mechanism does SQL 2k5 use to encrypt the secret that I enter when setting up the credentials for the job account using the CREATE CREDENTIAL statement? Is the secret protected using DPAPI? If so, what precautions must I take if I when managing the SQL Server Agent service account?

Many thanks, Kevin

The secret part of the credential is encrypted by the service master key (SMK). The SMK itself is protected by DPAPI using the SQL Server service account credentials. Changing the Agent service account shouldn't have any impact on the credentials, but changing the SQL Server service account will have an impact. If you change the service account manually, you will end up with a SMK that cannot be decrypted (in newer SQL Server builds we are mitigating this problem to some extent). My strong advice is to always have available an up-to-date backup of the SMK. This way, you can always restore the SMK if it becomes undecryptable. Losing the SMK is equivalent with losing all your encrypted data, so you should be extra careful about keeping backups of the SMK.

As a side note, SMK backups store the SMK encrypted with a password using 3DES and on Windows 2003 we enforce the password policy strength settings as we do for SQL Server logins.

For some additional information on the SMK, you can also look at the following:

http://blogs.msdn.com/lcris/archive/2005/07/08/437048.aspx

Thanks
Laurentiu

|||The credential secret is protected by service master key (see CREATE CREDENTIAL in BOL for details) which is in turm protected by DPAPI.

However encrypted credential secred as well as encrypted service master key is persisted in master database. Thus anybody who runs under the same windows account as SQLServer (i.e. NETWORK SERVICE by default) have an access to it. There is no published or unpublished interface to read these secrets, but this data can be eventually obtained from master.mdf file or even by implementing xp on a live server.

In short your job account credentials are protected by one or more ACLs granted to account under which SQLServer runs. It will be a good practice to run SQLServer under a unique account, so that no other machine task or service is using it. In that case only NT box admin has access to it. But this is normal, since any secret on local machine is available to NTBox admin.