Alerts passing info to a job

I'm not sure if this is the right place to post this, but here's my question
.
I have a trigger on a table where if a particular field is updated, I need
to create a file containing some info relevant to the row that was updated.
My initial thought was to raiserror in my trigger and then fire a job from
the alert which will run a select statement to a file, but I don't think it'
s
possible to pass information from my alert to my job to specify which row I
need to select against.
Am I going about this the entirely wrong way? Or is this a possibility?You can just start the job from trigger with sp_start_job. sp_start_job
works asynchronously, so your trigger won't have to wait for the job to
finish before it can complete itself.
Jacco Schalkwijk
SQL Server MVP
"MattBell" <MattBell@.discussions.microsoft.com> wrote in message
news:645BC25A-52AB-4E5F-A74C-7F6232D2C97C@.microsoft.com...
> I'm not sure if this is the right place to post this, but here's my
> question.
> I have a trigger on a table where if a particular field is updated, I need
> to create a file containing some info relevant to the row that was
> updated.
> My initial thought was to raiserror in my trigger and then fire a job from
> the alert which will run a select statement to a file, but I don't think
> it's
> possible to pass information from my alert to my job to specify which row
> I
> need to select against.
> Am I going about this the entirely wrong way? Or is this a possibility?|||Why not call a stored procedure from the trigger? You can pass the data
there and create your file when the trigger is fired..
Regards,
Brad Feaker
Ex nihilo, nihil fit
"MattBell" wrote:

> I'm not sure if this is the right place to post this, but here's my questi
on.
> I have a trigger on a table where if a particular field is updated, I need
> to create a file containing some info relevant to the row that was updated
.
> My initial thought was to raiserror in my trigger and then fire a job from
> the alert which will run a select statement to a file, but I don't think i
t's
> possible to pass information from my alert to my job to specify which row
I
> need to select against.
> Am I going about this the entirely wrong way? Or is this a possibility?|||Hey thanks for the info, works great.
One quick question though, is there anyway to squelch the "Job Started"
output from the sp_start_job?
"Jacco Schalkwijk" wrote:

> You can just start the job from trigger with sp_start_job. sp_start_job
> works asynchronously, so your trigger won't have to wait for the job to
> finish before it can complete itself.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "MattBell" <MattBell@.discussions.microsoft.com> wrote in message
> news:645BC25A-52AB-4E5F-A74C-7F6232D2C97C@.microsoft.com...
>
>|||No, unfortunately not. But it is only an informational message, so it won't
be picked up by most clients, for example ADO.
Jacco Schalkwijk
SQL Server MVP
"MattBell" <MattBell@.discussions.microsoft.com> wrote in message
news:047C5095-0253-40B8-BBFA-542603136CBD@.microsoft.com...
> Hey thanks for the info, works great.
> One quick question though, is there anyway to squelch the "Job Started"
> output from the sp_start_job?
>
> "Jacco Schalkwijk" wrote:
>

Alerting on SQL Server events in SQL Server 2005

In SQL Server 2000 I have several Alerts set to respond to SQL Server events. One in particular is 3738 which lets me know a database is deleted. In SQL Server 2005, this event is not set to log, and therefore I am not getting the alert to fire. I tried to run sp_altermessage, but it will not let you modify system messages. I configured a WMI alert to trigger off of SELECT * FROM DROP_DATABASE, and this tells me that a database has been deleted. However, the error based alert in SQL Server 2000 would also tell me which database was deleted, and the WMI alert does not. This information is important for determining the priority of my response. Does anyone know how I can get an alert configured in SQL Server 2005 which will also include the name of the database being deleted?

Note, I have similar issues with failed login and about a dozen other alerts. WMI does not appear to provide the detail that the error based alerting used to. But I am pretty new to WMI, so maybe there is something that can be done to get WMI to include the desired level of detail.

Thanks

John

I now have failed login alerts working using error numbers. (I fat fingered the error number.) But I continue to have trouble getting SQL Server Agent Alerts to fire for errors associated with database creation and deletion. It appears to be due to errors not being set to log, and no way to change that. Any ideas on how I can get meaningful information about these events?

Thanks

John

|||

Hey John. The properties exposed by each given WMI event type/group should be able to give you everything you need. For example, in the DROP_DATABASE event type will provide you the DatabaseName event property that should give you the name of the database that was dropped. To see a list of all the properties exposed by the DROP_DATABASE event type, see the following link:

http://msdn2.microsoft.com/en-us/library/ms179622(SQL.90).aspx

Within the SQL Server agent job you create to respond to this event, you can create T-SQL similar to the following to get the name of the database that was dropped:

select '$(WMI(DatabaseName))'

Within the given SQL Agent job you configure, you should then be able to do whatever you like with the DatabaseName, including setting it to a local variable or inserting into a table, etc.

NOTE: You may have to enable a registry key in order to allow for run-time replacement of tokens within the SQL Agent. If you receive an error to the effect of "reason: Variable WMI(DatabaseName) not found", open your registry editor and navigate to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent hive and enable the "AlertReplaceRuntimeTokens" key. Once you do that, restart your service and you should be good to go.

HTH

|||

Chad,

Thanks for the answer.For clarification, no matter what you put in the query in the definition of the SQL Server Agent Alert on a WMI event that information will not be included in the email response?I have currently a query in the WMI query for the alert which should return the information I want, but still the information is not included in the email. So instead we need to create a SQL Server Agent job that queries the WMI data we want to see and email it to us, and then have the SQL Server Agent alert call that job as the response.Just want to make sure I am following what needs to be done.

What was so bad about allowing sp_altermessage change the logging property of system messages?It seems that it could have coded to not allow the changing of any property except logging for system messages, and then none of this would be an issue.

Sorry, just a little frustration coming out.But I am curious to know if I am understanding what needs to be done.

Thanks

John

|||

That's correct...to get the information from the WMI query, you'll need to create a job that then formats and sends an email based on the information you get from the WMI query.

I can't really speak to why you shouldn't be able to allow logging of a system message, that would have been a decision by the project team, though I'll be happy to put in a request to have it reviewed and possibly changed back...though I can't ensure anything will actually come of it.

HTH,

|||

Chad,

Thanks for all your help. That would be great if you could put in a request. I think that would make life easier on a lot of people. As for now, I'll work on creating the jobs and alerts.

Thanks

John

Alert fails to notify Operator (SQL Server 2005)

I am trying to set up a new alert which will notify me when a particular
error is raised. I have configured the SQL Server Agent Alert System to use
Database Mail, and confirmed that this works with Job notifications (I have
successfully notified myself by email on completion of a job).
I have added a new user-defined error with sp_addmessage and have created an
alert to notify me when this error is raised. I have then generated the error
with the raiserror statement. The error message is returned in the query
result window, but I do not get the email.Hi Jon
"Jon Ley" wrote:
> I am trying to set up a new alert which will notify me when a particular
> error is raised. I have configured the SQL Server Agent Alert System to use
> Database Mail, and confirmed that this works with Job notifications (I have
> successfully notified myself by email on completion of a job).
> I have added a new user-defined error with sp_addmessage and have created an
> alert to notify me when this error is raised. I have then generated the error
> with the raiserror statement. The error message is returned in the query
> result window, but I do not get the email.
Is there anything in the SQL Server error log?
If you have email blocking as part of the antivirus suite you may have
exceptions logged there.
John|||How did you define the alert notification? By error number?
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jon Ley" <JonLey@.discussions.microsoft.com> wrote in message
news:29B92338-9259-489C-BFBA-C3D75B72B901@.microsoft.com...
>I am trying to set up a new alert which will notify me when a particular
> error is raised. I have configured the SQL Server Agent Alert System to
> use
> Database Mail, and confirmed that this works with Job notifications (I
> have
> successfully notified myself by email on completion of a job).
> I have added a new user-defined error with sp_addmessage and have created
> an
> alert to notify me when this error is raised. I have then generated the
> error
> with the raiserror statement. The error message is returned in the query
> result window, but I do not get the email.|||On May 19, 1:18 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> How did you define the alert notification? By error number?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Jon Ley" <Jon...@.discussions.microsoft.com> wrote in message
> news:29B92338-9259-489C-BFBA-C3D75B72B901@.microsoft.com...
>
> >I am trying to set up a new alert which will notify me when a particular
> > error is raised. I have configured the SQL Server Agent Alert System to
> > use
> > Database Mail, and confirmed that this works with Job notifications (I
> > have
> > successfully notified myself by email on completion of a job).
> > I have added a new user-defined error with sp_addmessage and have created
> > an
> > alert to notify me when this error is raised. I have then generated the
> > error
> > with the raiserror statement. The error message is returned in the query
> > result window, but I do not get the email.- Hide quoted text -
> - Show quoted text -
Are you on service pak 2? There are problems with database mail that
are corrected with service SP2.
Worth a shot anyway.
Kristina|||John,
Thanks for your response. There does not appear to be anything in the error
log.
Jon.
"John Bell" wrote:
> Hi Jon
> "Jon Ley" wrote:
> > I am trying to set up a new alert which will notify me when a particular
> > error is raised. I have configured the SQL Server Agent Alert System to use
> > Database Mail, and confirmed that this works with Job notifications (I have
> > successfully notified myself by email on completion of a job).
> >
> > I have added a new user-defined error with sp_addmessage and have created an
> > alert to notify me when this error is raised. I have then generated the error
> > with the raiserror statement. The error message is returned in the query
> > result window, but I do not get the email.
> Is there anything in the SQL Server error log?
> If you have email blocking as part of the antivirus suite you may have
> exceptions logged there.
> John|||TheSQLGuru: Yes, I defined the alert notification by error number (50001)
Kristina: I have just applied sp2 and retried. Still not working.
Regards,
Jon.
"Kristina" wrote:
> On May 19, 1:18 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> > How did you define the alert notification? By error number?
> >
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> >
> > "Jon Ley" <Jon...@.discussions.microsoft.com> wrote in message
> >
> > news:29B92338-9259-489C-BFBA-C3D75B72B901@.microsoft.com...
> >
> >
> >
> > >I am trying to set up a new alert which will notify me when a particular
> > > error is raised. I have configured the SQL Server Agent Alert System to
> > > use
> > > Database Mail, and confirmed that this works with Job notifications (I
> > > have
> > > successfully notified myself by email on completion of a job).
> >
> > > I have added a new user-defined error with sp_addmessage and have created
> > > an
> > > alert to notify me when this error is raised. I have then generated the
> > > error
> > > with the raiserror statement. The error message is returned in the query
> > > result window, but I do not get the email.- Hide quoted text -
> >
> > - Show quoted text -
> Are you on service pak 2? There are problems with database mail that
> are corrected with service SP2.
> Worth a shot anyway.
> Kristina
>|||Hi Jon
"Jon Ley" wrote:
> John,
> Thanks for your response. There does not appear to be anything in the error
> log.
> Jon.
>
Under the Alert Properties, there is a history dialog which should show you
if the alert has been triggered. If this is not updated when you cause the
error then you are not satisfying the criteria described in the remarks
section of http://msdn2.microsoft.com/en-us/library/ms189531.aspx. Check that
the errors appears in the windows event log.
John|||Thanks, John, I have got it working now using the WITH LOG option on the
RAISERROR statement. This appears to be a change from SQL Server 2000 - I had
exactly the same alert configured with a severity level of 10 and without it
being logged, and it would quite happily notify me by email when this error
was raised.
Regards,
Jon Ley.
"John Bell" wrote:
> Hi Jon
> "Jon Ley" wrote:
> > John,
> >
> > Thanks for your response. There does not appear to be anything in the error
> > log.
> >
> > Jon.
> >
> Under the Alert Properties, there is a history dialog which should show you
> if the alert has been triggered. If this is not updated when you cause the
> error then you are not satisfying the criteria described in the remarks
> section of http://msdn2.microsoft.com/en-us/library/ms189531.aspx. Check that
> the errors appears in the windows event log.
> John|||Did you configure the alert to always log to windows eventlog? You can check this in sys.messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jon Ley" <JonLey@.discussions.microsoft.com> wrote in message
news:54D9247D-1915-41DB-AA98-09D1E1E7B4DF@.microsoft.com...
> Thanks, John, I have got it working now using the WITH LOG option on the
> RAISERROR statement. This appears to be a change from SQL Server 2000 - I had
> exactly the same alert configured with a severity level of 10 and without it
> being logged, and it would quite happily notify me by email when this error
> was raised.
> Regards,
> Jon Ley.
> "John Bell" wrote:
>> Hi Jon
>> "Jon Ley" wrote:
>> > John,
>> >
>> > Thanks for your response. There does not appear to be anything in the error
>> > log.
>> >
>> > Jon.
>> >
>> Under the Alert Properties, there is a history dialog which should show you
>> if the alert has been triggered. If this is not updated when you cause the
>> error then you are not satisfying the criteria described in the remarks
>> section of http://msdn2.microsoft.com/en-us/library/ms189531.aspx. Check that
>> the errors appears in the windows event log.
>> Johnsql

Aggregations Hits

Dear all,

I have a problem with aggregations.

I have created aggregations on my different partitions; on a particular partition the wizard is announcing a performance gain of 44%.

Despite my reports use the measure from that partition when I trace the MDX query execution using SQL Server Profiler I have no aggregations hits.

As I have modified the MDX queries many times I was thinking my MDX queries were poorly written and that would be the cause of the aggregations not being hit.

I have then created a blank report in BIDS and using the Query Builder I have created a simple query using the measure for which the aggregation designer wizard was announcing 44% of perf gain but just as in my reports the SQL Server Profiler shows that there is no aggregation hit at all.

What am I doing wrong? My cube is quite simple and very light, I have one main measure with about 1.3 million rows and the dimensions are quite small but performance is very poor.

Any help will be appreciated.

It could be a number of things. First of all, it sounds like the aggregations you've built aren't useful for the queries you're running - that's not the fault of the queries themselves, it's just you need to build the right aggregations. The term 'performance gain' is a bit misleading in BIDS because it doesn't mean that all of your queries will automatically run x% faster; only queries which can use the aggregations you've built to derive their data will run faster.

Have you read the following white papers?

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

http://www.microsoft.com/downloads/details.aspx?FamilyId=975C5BB2-8207-4B4E-BE7C-06AC86E24C13&displaylang=en

They should give you an idea of what you need to do. It's also possible (especially given the fact that your cube is so small) that the performance problems you've got won't be solved with aggregations. Have you got any calculations defined on your cube?

Chris

|||Ensure that youre aggregations are built, you have been running ProcessIndex?|||Thanks for your answer Chris I'll check these White papers.

Aggregation Optimization Issue

Okay, so I want to set particular levels to be aggregated and not others because I think I'm smarter than the 'random optimizer' and have a good idea of what dims are used more than others. And I want to deploy good aggs before the customer uses it and we can't know exactly what they are going to do, so usage based aggs are out for a month or two - until the customer gets onto the system.

So I can see there is the AllMemberAggregationUsage, which I want used for all dims, then there is the option AggregationUsage for each level. I am choosing various levels and setting it to Full, and others to None.

It is my understanding that the number of aggregations possible is equal to (Num of levels + All level) for each dim times eachother. So lets say you have 3 dims of 1 level, 2 levels, and 2 levels. That would be (1+1) * (2+1) * (2+1). So, if I put one level in each of the 2-level dims to None rather than Full then this should be 2*2*2 = 8, and if I put them all to Full this should be 2*3*3 = 18.

In my actual cube I have 10 or so dims with varying levels and that calculation comes out to 1,500. In MSAS 2000 I did this all the time and the number of aggs would roughly equal (although never exctly, and never quite knew why not) this calc I have above. But when I do this in SSAS 2005 it comes out to '11 aggregations'. What is up with that?

Maybe its just a bug on the display of the total agg number, because when running the aggs on a small cube (fact table limited to 10 mil rows, full dimensions there, largest 4 million) it has been running for several hours which would make sense for the number of aggs I gave it. Also note that during the window where it 'prepares' aggs (with the graph on the right) it would go from 1 to 5 to 10 to 15 back to 7 up to 10 down to 8 and then end at 11. Maybe it just isn't updating that number right and I should assume that the number of aggs is in fact the number from the calc above? Not sure...

Setting the AttributeUsage only tells the aggregation designer that it should or should not consider including that particular level in aggregations. That doesn't guarantee that the wizard will actually create an aggregation with that column included. For good reason sometimes.

Take your 4 million member dimension; if for the sake of the argument the other 9 dimensions have only 10 members then an aggregation of all those columns would be 4E6*10^9 = 4E15 possible combinations or aggregationrows. But the maximum number of combinations that actually exist in your dataset is the number of factrows it has. Thus such an aggregation is completely useless - you could just as well read the base table and this aggregation will never be designed.

Of course the actual number of groupings is dependent on the datadistribution. If some columns are very dependent on each other there will be far fewer actual combinations then the theoretical number. However in our example above and many others that won't really matter - it will still be bigger then the number of factrows and thus useless waste of space.

To account for the more closerun cases one can assume that data is uniformly spread over the possibilities and use simple probability to estimate how big an aggregation will become. In addition to this AS cuts the allowed size of the aggregation a fair bit below the number of factrows and no doubt takes other performance issues as well as your AttributeUsage settings and size limits into account.

But the bottomline is that most of your 1500 possible aggregations above would be the same size as the facttable and thus will not be designed. Eleven was obviously the number of aggs that actually would be useful within the limits you put on it.

Aggregate() vs. A Set in the WHERE caluse

Hi,

I recently wrote some VBA code for Excel, which allowed a user to specify a list of members from a particular dimension and then produce a report which aggregated some measures with the members of a different dimension on the rows. It did this by creating an MDX query with the listed members fed into the aggregate function and stored in a calculated member which was then added to the WHERE clause.

eg

WITH MEMBER [Dimension].[Selected_Members] As Aggregate({[Dimension].[Member1], [Dimension].[Member2],.....})
SELECT [Measures].[Measure1] ON COLUMNS,
NON EMPTY [AnotherDimension].Members ON ROWS
FROM [Cube]
WHERE ([Dimension].[Selected_Members])

We found that as the number of members specified increased, the report slowed down dramatically. So, remembering that AS2005 allows you to specify sets in the WHERE clause of your MDX we decided to change it to something like this:

SELECT [Measures].[Measure1] ON COLUMNS,
NON EMPTY [AnotherDimension].Members ON ROWS
FROM [Cube]
WHERE ({[Dimension].[Member1], [Dimension].[Member2],.....})

We were quite surprised to see a very significant improvement in speed and the same results.

So, it would appear that the end result is exactly the same, but I would like to understand what the difference is and why one is so much faster than the other.

Many thanks,

Stuart

|||

I am surprised to see performance difference between these two examples. I would think that the execution plans should be exactly the same. Perhaps there is something going on in the cube.

Please note, that Aggregate cannot always be exchanged to set in WHERE clause, the results could become different. But if the set to Aggregate over is as static as in your example, it should usually be a safe rewrite.

Agent Job logs on using Default User Profile

Hi,

I get a strange problem whereby I have set up a Sql Server Agent job to run as a particular user. The job needs to access details from the users local profile.

The job fails to access the correct user profile when it logs in, and utilises the 'Default User' profile. This happens for scheduled jobs when the actual user used to run the job as is not logged into the machine where the Sql Server instance lives.

If, however, the user IS logged in at the time of job execution, the correct profile is loaded.

I can see this happening by simplying executing an OS command that kicks of a batch file with something like the following:

echo %USERPROFILE%
whoami

When the user is logged in I see the results as expected, eg:

UserProfile = C:\Documents and Settings\myUser
whoami=myDomain\myUser

However when the user is not logged on at the time of execution I get the following:

UserProfile = C:\Documents and Settings\Default User
whoami= myDomain\myUser

Any suggestions would be much appreciated....Its starting to do my head in...

Thanks.

What are associated privileges for that user to run as a SQLAgent account, in general if that job performs any copy or movement within network then the SQLAGent user account must need relevant privileges to complete that task.|||

Thanks for the reply..

At this stage the only thing my job is doing is outputing the results of whoami and %USERPROFILE% to a txt file. The job doesn't fail - which I would expect if there was a permissions problem. It just displays the %USERPROFILE% as Default User, when executing via a proxy account (not the SQL Agent Acc) - even though the whoami command shows the correct user is logged in.

Any ideas which privileges need to be applied to both the SQL Agent account, and the account which the proxy maps to?

|||Because of the Proxy usage this is the behaviour, I think. Refer to the books online about SQLAgent operator role

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/719ce56b-d6b2-414a-88a8-f43b725ebc79.htm

|||

The user that I am trying to get the step to log on as is a member of the sysadmin Server Role, so it should have access to all the Sql Server Agent functionality anyway - whether or not it has the SQLAgent operator role assigned..

The thing that has me really confused, is why can the Agent correctly logs the user in to execute the job (ie load the correct profile), ONLY when that same user is currently logged into the machine SQL Server is running on?

Is there maybe some way I could log the user in first, then execute the required step - ensuring that the user is logged out post execution - the thing is though I shouldn't need to do that...aaahhh..

thanks for our help...

Agent Job logs on using Default User Profile

Hi,

I get a strange problem whereby I have set up a Sql Server Agent job to run as a particular user. The job needs to access details from the users local profile.

The job fails to access the correct user profile when it logs in, and utilises the 'Default User' profile. This happens for scheduled jobs when the actual user used to run the job as is not logged into the machine where the Sql Server instance lives.

If, however, the user IS logged in at the time of job execution, the correct profile is loaded.

I can see this happening by simplying executing an OS command that kicks of a batch file with something like the following:

echo %USERPROFILE%
whoami

When the user is logged in I see the results as expected, eg:

UserProfile = C:\Documents and Settings\myUser
whoami=myDomain\myUser

However when the user is not logged on at the time of execution I get the following:

UserProfile = C:\Documents and Settings\Default User
whoami= myDomain\myUser

Any suggestions would be much appreciated....Its starting to do my head in...

Thanks.

What are associated privileges for that user to run as a SQLAgent account, in general if that job performs any copy or movement within network then the SQLAGent user account must need relevant privileges to complete that task.|||

Thanks for the reply..

At this stage the only thing my job is doing is outputing the results of whoami and %USERPROFILE% to a txt file. The job doesn't fail - which I would expect if there was a permissions problem. It just displays the %USERPROFILE% as Default User, when executing via a proxy account (not the SQL Agent Acc) - even though the whoami command shows the correct user is logged in.

Any ideas which privileges need to be applied to both the SQL Agent account, and the account which the proxy maps to?

|||Because of the Proxy usage this is the behaviour, I think. Refer to the books online about SQLAgent operator role

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/719ce56b-d6b2-414a-88a8-f43b725ebc79.htm

|||

The user that I am trying to get the step to log on as is a member of the sysadmin Server Role, so it should have access to all the Sql Server Agent functionality anyway - whether or not it has the SQLAgent operator role assigned..

The thing that has me really confused, is why can the Agent correctly logs the user in to execute the job (ie load the correct profile), ONLY when that same user is currently logged into the machine SQL Server is running on?

Is there maybe some way I could log the user in first, then execute the required step - ensuring that the user is logged out post execution - the thing is though I shouldn't need to do that...aaahhh..

thanks for our help...