We have some SQL jobs those run on a schedule basis. These jobs are setup to
notify us via email in case of job fails. However, Some time we get this
error "Failed to notify 'DBA' via email." Initially I thought it may be b/c
of mail server was not available but our exchnage server admin has looked at
exchange logs and not found any thing at that time. I also looked at the SQL
server logs and did not find any issues. My question is: is there any thing
can go wrong in the SQL server that can cause this probelm?
Help is appreciated.
Thanks,
MohMoh,
FWIW - SQL Server MAPI will only wait a certain amount of time to deliver a
message. If it cannot get out, then the mail will 'fail' even though
Exchange is up and running and healthy. It might be Exchange, networking,
domain issues, etc. that cause the problem.
If you Google on "sql mail Failed to notify" you will see a few comments on
this, without any real help that I could find.
(Unless xp_smtp_sendmail would work for you.)
RLF
"Moh Abb" <mabbas@.aligntech.com> wrote in message
news:eiv3swh3EHA.3376@.TK2MSFTNGP12.phx.gbl...
> We have some SQL jobs those run on a schedule basis. These jobs are setup
to
> notify us via email in case of job fails. However, Some time we get this
> error "Failed to notify 'DBA' via email." Initially I thought it may be
b/c
> of mail server was not available but our exchnage server admin has looked
at
> exchange logs and not found any thing at that time. I also looked at the
SQL
> server logs and did not find any issues. My question is: is there any
thing
> can go wrong in the SQL server that can cause this probelm?
> Help is appreciated.
> Thanks,
> Moh
>|||"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:ePA64pj3EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Moh,
> FWIW - SQL Server MAPI will only wait a certain amount of time to deliver
a
> message. If it cannot get out, then the mail will 'fail' even though
> Exchange is up and running and healthy. It might be Exchange, networking,
> domain issues, etc. that cause the problem.
> If you Google on "sql mail Failed to notify" you will see a few comments
on
> this, without any real help that I could find.
Generally I find if you don't absolutely need to use Exchange, setup a local
SMTP server right on teh SQL box and send email through that directly.
Eliminates timeouts, etc.
> (Unless xp_smtp_sendmail would work for you.)
> RLF
> "Moh Abb" <mabbas@.aligntech.com> wrote in message
> news:eiv3swh3EHA.3376@.TK2MSFTNGP12.phx.gbl...
> > We have some SQL jobs those run on a schedule basis. These jobs are
setup
> to
> > notify us via email in case of job fails. However, Some time we get this
> > error "Failed to notify 'DBA' via email." Initially I thought it may be
> b/c
> > of mail server was not available but our exchnage server admin has
looked
> at
> > exchange logs and not found any thing at that time. I also looked at the
> SQL
> > server logs and did not find any issues. My question is: is there any
> thing
> > can go wrong in the SQL server that can cause this probelm?
> >
> > Help is appreciated.
> >
> > Thanks,
> > Moh
> >
> >
>
>
Showing posts with label basis. Show all posts
Showing posts with label basis. Show all posts
Tuesday, March 27, 2012
Wednesday, March 7, 2012
Fact Snapshot Table Question
Hello All,
I have a Snapshot Fact table that shows data on a monthly basis. Each person has a row for each month.
But now I am adding bonus information. The problem is, for each month a person may have received more than one bonus amount and type in a month. For example:
ID Date Bonus Bonus Type
1000 1/1/2000 $5,000 Team
1000 1/5/2000 $ 500 GoodJob
1000 1/7/2000 $ 250 Incentive
What is the best way to have multiple bonuses and their amounts in the fact table, (when the fact table currently has a single row per employee, per month)?
Thank you for the help.
-Gumbatman
Assuming that you are using SSAS 2005, Just keep this information in it's own fact table and add that to the cube as another measure group.|||Darren,
Thank you for the reply, but I am not 100% clear on what you mean (my issue, not yours).
I have my main fact table, that is populated each month, for example:
If I now have the bonus information as a separate table, how would I link them in order to add the bonus information as a Measure Group?
Do I need to have the JobKey and GeographicKeys in the Bonus fact table to do my slicing by dimensions (seems redundant)?
Taking a guess, I assume my Bonus fact table should it look something like this:
My assumption is to link the two fact tables by DateKey and EmployeeID.
Thank you for the help. I've been trying to get my brain around this for a while.
|||Having different granularities can make things a bit harder to get your head around. If you have a look at the Adventure Works cube you will see that the sales targets measure group is probably similar to bonus fact table we are talking about here.
The answer to your question hinges around how you want to see the data come out like the following, with the bonus essentially repeated for each unrelated attribute (ie Job and Geography).
Then simply adding the bonus fact table to the cube and setting up the dimension relationships tab so that only the date and employee dimensions are related to the bonus amounts should be enough. You can also change a setting on the measure group so that if someone drills down by an unrelated dimension then the bonus amount returns null.
Those are the two default options, the only other option I can think of would be to devise an allocation mechanism to divide the bonus between the unrelated attributes like Job and Geography which would involve either extra work in the ETL phase or calculations in the MDX script.
|||Darren,
Thank you so much for the answers and explanations. I still need to get my head around how the Sales Targets measure group is related, but it certainly seems to be what I was looking for.
I really appreciate you taking the time to answer this for me. You've saved me lots of time and work trying to figure it out on my own (which probably wouldn't have happened).
-Gumbatman
|||Darren,
I've tried implementing this, but I am not doing something correctly.
I have my Fact table:
And my Bonus Fact Table, which references the RowKey:
As you can see, Employee 500 got two bonuses in October and Employee 700 got two bonuses in November.
I linked the two Fact tables in the Data Source View (using RowKey) and added a new Measure Group. In the Dimensions tab, I can only connect the Date to the Time Dimension. I don't know how to connect the tables other than that since the Bonus Fact table doesn't have those dimensions.
When I Browse the cube, the Bonus amounts do show over time, but the total populates all the rows. For example,
How do I connect the two fact tables so I can use all the Dimensions that are already connected to the main Fact table? I did try the many-to-many connection but that didn't help either.
Thank you for the help.
-Gumbatman
Friday, February 24, 2012
extracting the specified record no of records from database table
hi all,
I need to select the no of records on the basis of specified range of records.
In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..
I have used temporary table,map the table primary key to the next table with identity
but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...
Is there some other best way to extract the specified data
here is the type of query.
select * from customers where rownum between 1000 and 10000
this is in oracle
i am in need to do this in the sql server
waiting for the response...............................
There is no such thing like row number in MS SQL. You will need to create an identity column in your table, or maintain the number column manually.|||If you are using SQL Server 2005 then you can use the ROW_NUMBER() function instead:
select *
from (select *, row_number() over(order by CustomerId) as rownum
from customers
) as c
where c.rownum between 1000 and 10000;
If you are using SQL Server 2000 then the identity column approach using temporary table is the best way to go (I guess you are doing this now based on the information in your post).
Also, it seems like you are trying to batch some DML operation. If so you can use SET ROWCOUNT or TOP clause in DML (SQL Server 2005). See below for example:
declare @.n int
set @.n = 1000 -- set number of rows you want to insert at a time
set rowcount @.n
while(1=1)
begin
insert into MasterCustomers
select ...
from Customers as c1
where not exists(
select * from MasterCustomers as c2
where c2.CustomerName = c1.CustomerName)
if @.@.rowcount = 0 break
end
set rowcount 0
-- using TOP
declare @.n int
set @.n = 1000 -- set number of rows you want to insert at a time
while(1=1)
begin
insert top(@.n) into MasterCustomers
select ...
from Customers as c1
where not exists(
select * from MasterCustomers as c2
where c2.CustomerName = c1.CustomerName)
if @.@.rowcount = 0 break
end
|||SQL 2000 does not have the limit keyord. You can use double TOP instead of, but you must have a primary key on that tableselect * from
(select top 1000 * from (
select top 11000 *
from customers
order by customer_id asc
) as tmp1
order by cutomer_id desc
) as tmp2
order by ...
for extract customers betweeen 10000 and 11000 based on customer_id
|||
if you use sql2005
you also can use the function ROW_NUMBER() to
select rownum between 1000 and 10000 like oracle
example:
with customers _temp as
(SELECT ROW_NUMBER() OVER (order by customer) as RowNumber,*
from customers)
select *
from customers _temp
where RowNumber between 1000 and 10000
extracting the specified record no of records from database table
hi all,
I need to select the no of records on the basis of specified range of records.
In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..
I have used temporary table,map the table primary key to the next table with identity
but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...
Is there some other best way to extract the specified data
here is the type of query.
select * from customers where rownum between 1000 and 10000
this is in oracle
i am in need to do this in the sql server
waiting for the response...............................
There is no such thing like row number in MS SQL. You will need to create an identity column in your table, or maintain the number column manually.|||If you are using SQL Server 2005 then you can use the ROW_NUMBER() function instead:
select *
from (select *, row_number() over(order by CustomerId) as rownum
from customers
) as c
where c.rownum between 1000 and 10000;
If you are using SQL Server 2000 then the identity column approach using temporary table is the best way to go (I guess you are doing this now based on the information in your post).
Also, it seems like you are trying to batch some DML operation. If so you can use SET ROWCOUNT or TOP clause in DML (SQL Server 2005). See below for example:
declare @.n int
set @.n = 1000 -- set number of rows you want to insert at a time
set rowcount @.n
while(1=1)
begin
insert into MasterCustomers
select ...
from Customers as c1
where not exists(
select * from MasterCustomers as c2
where c2.CustomerName = c1.CustomerName)
if @.@.rowcount = 0 break
end
set rowcount 0
-- using TOP
declare @.n int
set @.n = 1000 -- set number of rows you want to insert at a time
while(1=1)
begin
insert top(@.n) into MasterCustomers
select ...
from Customers as c1
where not exists(
select * from MasterCustomers as c2
where c2.CustomerName = c1.CustomerName)
if @.@.rowcount = 0 break
end
|||SQL 2000 does not have the limit keyord. You can use double TOP instead of, but you must have a primary key on that table
select * from
(select top 1000 * from (
select top 11000 *
from customers
order by customer_id asc
) as tmp1
order by cutomer_id desc
) as tmp2
order by ...
for extract customers betweeen 10000 and 11000 based on customer_id|||
if you use sql2005
you also can use the function ROW_NUMBER() to
select rownum between 1000 and 10000 like oracle
example:
with customers _temp as
(SELECT ROW_NUMBER() OVER (order by customer) as RowNumber,*
from customers)
select *
from customers _temp
where RowNumber between 1000 and 10000
Subscribe to:
Posts (Atom)