Is there a way to set a job to send notification to 2 seperate Operators.
We currently have one person set up as an operator to get notifications, but
there are a couple jobs that we need 2, 3 or even 4 people notified if the
job fails. I would prefer not to have to create multiple operators one for
this job, one for that job, etc. Is there any way for a job to notify more
that one operator?
Thanks
create distribution list containing operator's email accounts. and have sql
mail send notifications to the dl.
richard
"Connie" <cfelt@.ga.wa.gov> wrote in message
news:%23JltkBnHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Is there a way to set a job to send notification to 2 seperate Operators.
> We currently have one person set up as an operator to get notifications,
but
> there are a couple jobs that we need 2, 3 or even 4 people notified if the
> job fails. I would prefer not to have to create multiple operators one
for
> this job, one for that job, etc. Is there any way for a job to notify
more
> that one operator?
> Thanks
>
Showing posts with label operator. Show all posts
Showing posts with label operator. Show all posts
Friday, March 9, 2012
Operators
Create a new operator called User1&User2. In the EmailAddress of this user
put User1@.email.com;User2@.email.com
HTH. Ryan
"Susanna" <Susanna@.discussions.microsoft.com> wrote in message
news:CA4D4AC0-B3D5-4B26-9D06-62F9E55C418A@.microsoft.com...
> Hi,
> Can a job be assigned to notify 2 operators?
> If i click on the drop down box of E-mail operator in Jobs,
> i will see the 2nd operator.
> But why can't the 2nd operator receive the notification?
> If i press on Test to send an email at the Operators,
> it can send a test email.
> What should i do to let the other operator receive emails
> when jobs are successful?
> --
> Thanks in advance,
> SusannaHi,
Can a job be assigned to notify 2 operators?
If i click on the drop down box of E-mail operator in Jobs,
i will see the 2nd operator.
But why can't the 2nd operator receive the notification?
If i press on Test to send an email at the Operators,
it can send a test email.
What should i do to let the other operator receive emails
when jobs are successful?
Thanks in advance,
Susanna|||Create a new operator called User1&User2. In the EmailAddress of this user
put User1@.email.com;User2@.email.com
HTH. Ryan
"Susanna" <Susanna@.discussions.microsoft.com> wrote in message
news:CA4D4AC0-B3D5-4B26-9D06-62F9E55C418A@.microsoft.com...
> Hi,
> Can a job be assigned to notify 2 operators?
> If i click on the drop down box of E-mail operator in Jobs,
> i will see the 2nd operator.
> But why can't the 2nd operator receive the notification?
> If i press on Test to send an email at the Operators,
> it can send a test email.
> What should i do to let the other operator receive emails
> when jobs are successful?
> --
> Thanks in advance,
> Susanna|||Hi Ryan,
your example works!
Thanks,
Susanna
"Ryan" wrote:
> Create a new operator called User1&User2. In the EmailAddress of this user
> put User1@.email.com;User2@.email.com
> --
> HTH. Ryan
>
> "Susanna" <Susanna@.discussions.microsoft.com> wrote in message
> news:CA4D4AC0-B3D5-4B26-9D06-62F9E55C418A@.microsoft.com...
>
>
put User1@.email.com;User2@.email.com
HTH. Ryan
"Susanna" <Susanna@.discussions.microsoft.com> wrote in message
news:CA4D4AC0-B3D5-4B26-9D06-62F9E55C418A@.microsoft.com...
> Hi,
> Can a job be assigned to notify 2 operators?
> If i click on the drop down box of E-mail operator in Jobs,
> i will see the 2nd operator.
> But why can't the 2nd operator receive the notification?
> If i press on Test to send an email at the Operators,
> it can send a test email.
> What should i do to let the other operator receive emails
> when jobs are successful?
> --
> Thanks in advance,
> SusannaHi,
Can a job be assigned to notify 2 operators?
If i click on the drop down box of E-mail operator in Jobs,
i will see the 2nd operator.
But why can't the 2nd operator receive the notification?
If i press on Test to send an email at the Operators,
it can send a test email.
What should i do to let the other operator receive emails
when jobs are successful?
Thanks in advance,
Susanna|||Create a new operator called User1&User2. In the EmailAddress of this user
put User1@.email.com;User2@.email.com
HTH. Ryan
"Susanna" <Susanna@.discussions.microsoft.com> wrote in message
news:CA4D4AC0-B3D5-4B26-9D06-62F9E55C418A@.microsoft.com...
> Hi,
> Can a job be assigned to notify 2 operators?
> If i click on the drop down box of E-mail operator in Jobs,
> i will see the 2nd operator.
> But why can't the 2nd operator receive the notification?
> If i press on Test to send an email at the Operators,
> it can send a test email.
> What should i do to let the other operator receive emails
> when jobs are successful?
> --
> Thanks in advance,
> Susanna|||Hi Ryan,
your example works!
Thanks,
Susanna
"Ryan" wrote:
> Create a new operator called User1&User2. In the EmailAddress of this user
> put User1@.email.com;User2@.email.com
> --
> HTH. Ryan
>
> "Susanna" <Susanna@.discussions.microsoft.com> wrote in message
> news:CA4D4AC0-B3D5-4B26-9D06-62F9E55C418A@.microsoft.com...
>
>
Wednesday, March 7, 2012
Operators
Is there a way to set a job to send notification to 2 seperate Operators.
We currently have one person set up as an operator to get notifications, but
there are a couple jobs that we need 2, 3 or even 4 people notified if the
job fails. I would prefer not to have to create multiple operators one for
this job, one for that job, etc. Is there any way for a job to notify more
that one operator?
Thankscreate distribution list containing operator's email accounts. and have sql
mail send notifications to the dl.
richard
"Connie" <cfelt@.ga.wa.gov> wrote in message
news:%23JltkBnHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Is there a way to set a job to send notification to 2 seperate Operators.
> We currently have one person set up as an operator to get notifications,
but
> there are a couple jobs that we need 2, 3 or even 4 people notified if the
> job fails. I would prefer not to have to create multiple operators one
for
> this job, one for that job, etc. Is there any way for a job to notify
more
> that one operator?
> Thanks
>
We currently have one person set up as an operator to get notifications, but
there are a couple jobs that we need 2, 3 or even 4 people notified if the
job fails. I would prefer not to have to create multiple operators one for
this job, one for that job, etc. Is there any way for a job to notify more
that one operator?
Thankscreate distribution list containing operator's email accounts. and have sql
mail send notifications to the dl.
richard
"Connie" <cfelt@.ga.wa.gov> wrote in message
news:%23JltkBnHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Is there a way to set a job to send notification to 2 seperate Operators.
> We currently have one person set up as an operator to get notifications,
but
> there are a couple jobs that we need 2, 3 or even 4 people notified if the
> job fails. I would prefer not to have to create multiple operators one
for
> this job, one for that job, etc. Is there any way for a job to notify
more
> that one operator?
> Thanks
>
Operators
Hi,
Can a job be assigned to notify 2 operators?
If i click on the drop down box of E-mail operator in Jobs,
i will see the 2nd operator.
But why can't the 2nd operator receive the notification?
If i press on Test to send an email at the Operators,
it can send a test email.
What should i do to let the other operator receive emails
when jobs are successful?
--
Thanks in advance,
SusannaCreate a new operator called User1&User2. In the EmailAddress of this user
put User1@.email.com;User2@.email.com
--
HTH. Ryan
"Susanna" <Susanna@.discussions.microsoft.com> wrote in message
news:CA4D4AC0-B3D5-4B26-9D06-62F9E55C418A@.microsoft.com...
> Hi,
> Can a job be assigned to notify 2 operators?
> If i click on the drop down box of E-mail operator in Jobs,
> i will see the 2nd operator.
> But why can't the 2nd operator receive the notification?
> If i press on Test to send an email at the Operators,
> it can send a test email.
> What should i do to let the other operator receive emails
> when jobs are successful?
> --
> Thanks in advance,
> Susanna|||Hi Ryan,
your example works!
--
Thanks,
Susanna
"Ryan" wrote:
> Create a new operator called User1&User2. In the EmailAddress of this user
> put User1@.email.com;User2@.email.com
> --
> HTH. Ryan
>
> "Susanna" <Susanna@.discussions.microsoft.com> wrote in message
> news:CA4D4AC0-B3D5-4B26-9D06-62F9E55C418A@.microsoft.com...
> > Hi,
> > Can a job be assigned to notify 2 operators?
> > If i click on the drop down box of E-mail operator in Jobs,
> > i will see the 2nd operator.
> > But why can't the 2nd operator receive the notification?
> > If i press on Test to send an email at the Operators,
> > it can send a test email.
> > What should i do to let the other operator receive emails
> > when jobs are successful?
> >
> > --
> > Thanks in advance,
> > Susanna
>
>
Can a job be assigned to notify 2 operators?
If i click on the drop down box of E-mail operator in Jobs,
i will see the 2nd operator.
But why can't the 2nd operator receive the notification?
If i press on Test to send an email at the Operators,
it can send a test email.
What should i do to let the other operator receive emails
when jobs are successful?
--
Thanks in advance,
SusannaCreate a new operator called User1&User2. In the EmailAddress of this user
put User1@.email.com;User2@.email.com
--
HTH. Ryan
"Susanna" <Susanna@.discussions.microsoft.com> wrote in message
news:CA4D4AC0-B3D5-4B26-9D06-62F9E55C418A@.microsoft.com...
> Hi,
> Can a job be assigned to notify 2 operators?
> If i click on the drop down box of E-mail operator in Jobs,
> i will see the 2nd operator.
> But why can't the 2nd operator receive the notification?
> If i press on Test to send an email at the Operators,
> it can send a test email.
> What should i do to let the other operator receive emails
> when jobs are successful?
> --
> Thanks in advance,
> Susanna|||Hi Ryan,
your example works!
--
Thanks,
Susanna
"Ryan" wrote:
> Create a new operator called User1&User2. In the EmailAddress of this user
> put User1@.email.com;User2@.email.com
> --
> HTH. Ryan
>
> "Susanna" <Susanna@.discussions.microsoft.com> wrote in message
> news:CA4D4AC0-B3D5-4B26-9D06-62F9E55C418A@.microsoft.com...
> > Hi,
> > Can a job be assigned to notify 2 operators?
> > If i click on the drop down box of E-mail operator in Jobs,
> > i will see the 2nd operator.
> > But why can't the 2nd operator receive the notification?
> > If i press on Test to send an email at the Operators,
> > it can send a test email.
> > What should i do to let the other operator receive emails
> > when jobs are successful?
> >
> > --
> > Thanks in advance,
> > Susanna
>
>
Operators
Is there a way to set a job to send notification to 2 seperate Operators.
We currently have one person set up as an operator to get notifications, but
there are a couple jobs that we need 2, 3 or even 4 people notified if the
job fails. I would prefer not to have to create multiple operators one for
this job, one for that job, etc. Is there any way for a job to notify more
that one operator?
Thankscreate distribution list containing operator's email accounts. and have sql
mail send notifications to the dl.
richard
"Connie" <cfelt@.ga.wa.gov> wrote in message
news:%23JltkBnHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Is there a way to set a job to send notification to 2 seperate Operators.
> We currently have one person set up as an operator to get notifications,
but
> there are a couple jobs that we need 2, 3 or even 4 people notified if the
> job fails. I would prefer not to have to create multiple operators one
for
> this job, one for that job, etc. Is there any way for a job to notify
more
> that one operator?
> Thanks
>
We currently have one person set up as an operator to get notifications, but
there are a couple jobs that we need 2, 3 or even 4 people notified if the
job fails. I would prefer not to have to create multiple operators one for
this job, one for that job, etc. Is there any way for a job to notify more
that one operator?
Thankscreate distribution list containing operator's email accounts. and have sql
mail send notifications to the dl.
richard
"Connie" <cfelt@.ga.wa.gov> wrote in message
news:%23JltkBnHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Is there a way to set a job to send notification to 2 seperate Operators.
> We currently have one person set up as an operator to get notifications,
but
> there are a couple jobs that we need 2, 3 or even 4 people notified if the
> job fails. I would prefer not to have to create multiple operators one
for
> this job, one for that job, etc. Is there any way for a job to notify
more
> that one operator?
> Thanks
>
operator precedence challenge
I'm having problems with the query below. It works fine until the "NOT IN" part and I'm not sure why. Basically, I'm getting records where their educ_audio field is set to "no". I've messed with the parens to try to force SQL to process the NOT IN part before the other clauses without avail. Can someone shed some light?
TIA
SELECT distinct contacts.fname, contacts.lname, contacts.company, contacts.contact_id, contacts.business_phone, contacts.emailAddress, contacts.dateLastContact FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON journal.product_code = products.product_code WHERE ( journal.product_code IN ('ABLE') ) OR (( journal.product_code IN ('JOBS') ) AND ( journal.product_status IN ('12','14','15') )) OR (( products.prod_design IN ('audio') ) AND ( products.library_code IN ('hrss') )) AND (journal.journal_id NOT IN (SELECT journal.journal_id FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON products.product_code = journal.product_code where ( contacts.educ_audio IN ('no') ) )) ORDER BY contacts.lname asc, contacts.fnameI think you can get the results you want by enclosing all your OR clauses in one set of parenthesis. If I understand correctly, your records must satisfy at least one of the OR clauses, and the AND clause:
SELECT distinct
contacts.fname,
contacts.lname,
contacts.company,
contacts.contact_id,
contacts.business_phone,
contacts.emailAddress,
contacts.dateLastContact
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON journal.product_code = products.product_code
WHERE ((journal.product_code IN ('ABLE'))
OR ((journal.product_code IN ('JOBS')) AND (journal.product_status IN ('12','14','15')))
OR ((products.prod_design IN ('audio')) AND (products.library_code IN ('hrss'))))
AND (journal.journal_id NOT IN
(SELECT journal.journal_id
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON products.product_code = journal.product_code
WHERE (contacts.educ_audio IN ('no'))))
ORDER BY contacts.lname asc,
contacts.fname
But you can clean this up a lot more
First, IN ('ABLE') is equivalent to ='ABLE', so don't muddy the waters with more parentheses than you need.
Second, many of your Parenthesis pairs are superfluous, in that they enclose only one clause. Eliminate the clutter.
SELECT distinct
contacts.fname,
contacts.lname,
contacts.company,
contacts.contact_id,
contacts.business_phone,
contacts.emailAddress,
contacts.dateLastContact
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON journal.product_code = products.product_code
WHERE (journal.product_code = 'ABLE'
OR (journal.product_code = 'JOBS' AND journal.product_status IN ('12','14','15'))
OR (products.prod_design = 'audio' AND products.library_code IN ('hrss')))
AND journal.journal_id NOT IN
(SELECT journal.journal_id
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON products.product_code = journal.product_code
WHERE contacts.educ_audio = 'no')
ORDER BY contacts.lname asc,
contacts.fname
--Lastly, consider converting you NOT IN clause to a LEFT OUTER JOIN subquery:
SELECT distinct
contacts.fname,
contacts.lname,
contacts.company,
contacts.contact_id,
contacts.business_phone,
contacts.emailAddress,
contacts.dateLastContact
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON journal.product_code = products.product_code
LEFT OUTER JOIN
(SELECT journal.journal_id
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON products.product_code = journal.product_code
WHERE contacts.educ_audio = 'no') ExcludeRecords
on journal.journal_id = ExludeRecords.journal_id
WHERE (journal.product_code = 'ABLE'
OR (journal.product_code = 'JOBS' AND journal.product_status IN ('12','14','15'))
OR (products.prod_design = 'audio' AND products.library_code IN ('hrss')))
AND ExcludeRecords.journal_id is null
ORDER BY contacts.lname asc,
contacts.fname
If you take the time to develop a coding style that includes neat and consistent indenting and formatting, you will be rewarded with much clearer and more bug-free code.|||Thanks for your help.
Reason the SQL looks like it does (not indented, etc) is because that was a paste from my application. This is for an ad-hoc query tool I'm building.
As to your suggestions, they all seem to work except the last one.
Error: The column prefix 'ExludeRecords' does not match with a table name or alias name used in the query.|||Just a typo.
on journal.journal_id = ExludeRecords.journal_id
...should have been:
on journal.journal_id = ExcludeRecords.journal_id
TIA
SELECT distinct contacts.fname, contacts.lname, contacts.company, contacts.contact_id, contacts.business_phone, contacts.emailAddress, contacts.dateLastContact FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON journal.product_code = products.product_code WHERE ( journal.product_code IN ('ABLE') ) OR (( journal.product_code IN ('JOBS') ) AND ( journal.product_status IN ('12','14','15') )) OR (( products.prod_design IN ('audio') ) AND ( products.library_code IN ('hrss') )) AND (journal.journal_id NOT IN (SELECT journal.journal_id FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON products.product_code = journal.product_code where ( contacts.educ_audio IN ('no') ) )) ORDER BY contacts.lname asc, contacts.fnameI think you can get the results you want by enclosing all your OR clauses in one set of parenthesis. If I understand correctly, your records must satisfy at least one of the OR clauses, and the AND clause:
SELECT distinct
contacts.fname,
contacts.lname,
contacts.company,
contacts.contact_id,
contacts.business_phone,
contacts.emailAddress,
contacts.dateLastContact
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON journal.product_code = products.product_code
WHERE ((journal.product_code IN ('ABLE'))
OR ((journal.product_code IN ('JOBS')) AND (journal.product_status IN ('12','14','15')))
OR ((products.prod_design IN ('audio')) AND (products.library_code IN ('hrss'))))
AND (journal.journal_id NOT IN
(SELECT journal.journal_id
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON products.product_code = journal.product_code
WHERE (contacts.educ_audio IN ('no'))))
ORDER BY contacts.lname asc,
contacts.fname
But you can clean this up a lot more
First, IN ('ABLE') is equivalent to ='ABLE', so don't muddy the waters with more parentheses than you need.
Second, many of your Parenthesis pairs are superfluous, in that they enclose only one clause. Eliminate the clutter.
SELECT distinct
contacts.fname,
contacts.lname,
contacts.company,
contacts.contact_id,
contacts.business_phone,
contacts.emailAddress,
contacts.dateLastContact
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON journal.product_code = products.product_code
WHERE (journal.product_code = 'ABLE'
OR (journal.product_code = 'JOBS' AND journal.product_status IN ('12','14','15'))
OR (products.prod_design = 'audio' AND products.library_code IN ('hrss')))
AND journal.journal_id NOT IN
(SELECT journal.journal_id
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON products.product_code = journal.product_code
WHERE contacts.educ_audio = 'no')
ORDER BY contacts.lname asc,
contacts.fname
--Lastly, consider converting you NOT IN clause to a LEFT OUTER JOIN subquery:
SELECT distinct
contacts.fname,
contacts.lname,
contacts.company,
contacts.contact_id,
contacts.business_phone,
contacts.emailAddress,
contacts.dateLastContact
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON journal.product_code = products.product_code
LEFT OUTER JOIN
(SELECT journal.journal_id
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON products.product_code = journal.product_code
WHERE contacts.educ_audio = 'no') ExcludeRecords
on journal.journal_id = ExludeRecords.journal_id
WHERE (journal.product_code = 'ABLE'
OR (journal.product_code = 'JOBS' AND journal.product_status IN ('12','14','15'))
OR (products.prod_design = 'audio' AND products.library_code IN ('hrss')))
AND ExcludeRecords.journal_id is null
ORDER BY contacts.lname asc,
contacts.fname
If you take the time to develop a coding style that includes neat and consistent indenting and formatting, you will be rewarded with much clearer and more bug-free code.|||Thanks for your help.
Reason the SQL looks like it does (not indented, etc) is because that was a paste from my application. This is for an ad-hoc query tool I'm building.
As to your suggestions, they all seem to work except the last one.
Error: The column prefix 'ExludeRecords' does not match with a table name or alias name used in the query.|||Just a typo.
on journal.journal_id = ExludeRecords.journal_id
...should have been:
on journal.journal_id = ExcludeRecords.journal_id
Operator precedence bug in SQL Server 2000?
Consider the following expression:
SELECT 41 % 17 % 5
Returns: 2
This simply follows the rules of precedence:
1) Unary operators
2) Left to right per ranl
As all modulo operators are equal rank, it will be simply evaluated
left to right
--However, if we DO introduce unary operators, it doesn't work that way
SELECT 41 % +17 % 5
Returns: 1
As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
evaluated the modulo right to left.
As the + unary operator is basically void, it is a total mystery to me
why this happens.
-- Adding unary operators to the let or right elements as in SELECT +41
% 17 % +5 doesn't cause any trouble.
The return valus is still ok.
Adding a unary operator to the middle element changes the left-to-right
evaluation to right to left.
I found this under SQL Server 2000 Service Pack 3
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
I upgraded to Service Pack 4:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Still, the same thing happens.
After trying this under other operators, I found the same effect for
the divide (/) operator.
Consider:
SELECT 41 / 17 / 5
Will evaluate (correctly) as (4 / 17) / 5
However,
SELECT 41 /-+17 / 5
SELECT 41 / +17 / 5
Will both evaluate as 41 / (17 / 5) and 41 / (-17 / 5) respectively.
Anyone any idea? Bug? Documented ~feature~? What's up as I see no way
how this conforms to any operator precedence.
Cheers.
<martijn.vels@.gmail.com> wrote in message
news:1131753937.040613.203430@.g44g2000cwa.googlegr oups.com...
> Consider the following expression:
> SELECT 41 % 17 % 5
> Returns: 2
> This simply follows the rules of precedence:
> 1) Unary operators
> 2) Left to right per ranl
> As all modulo operators are equal rank, it will be simply evaluated
> left to right
> --However, if we DO introduce unary operators, it doesn't work that way
> SELECT 41 % +17 % 5
> Returns: 1
> As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
> evaluated the modulo right to left.
> As the + unary operator is basically void, it is a total mystery to me
> why this happens.
Unary + is still a valid operator and it has a lower precedence than %.
From BOL:
Operators have the precedence levels shown in the following table. An
operator on higher levels is evaluated before an operator on a lower level.
Level Operators
1 ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
(Bitwise AND)
.. . .
So the expression
41 % +17 % 5
is equivalent to as
41 % +(17 % 5)
David
|||David Browne wrote:
> <martijn.vels@.gmail.com> wrote in message
> news:1131753937.040613.203430@.g44g2000cwa.googlegr oups.com...
> Unary + is still a valid operator and it has a lower precedence than %.
> From BOL:
> Operators have the precedence levels shown in the following table. An
> operator on higher levels is evaluated before an operator on a lower level.
> Level Operators
> 1 ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
> (Bitwise AND)
> . . .
> So the expression
> 41 % +17 % 5
> is equivalent to as
> 41 % +(17 % 5)
> David
I don't know if there is an easy way to see the version of BOL, but my
BOL says this:
Operators have these precedence levels. An operator on higher levels is
evaluated before an operator on a lower level:
+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
etc.
In the BOL of SQL2K5 (CTP) it still has the same text:
Level Operators
1 + (Positive), - (Negative), ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
6 NOT
7 AND
8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
9 = (Assignment)
I usually use parentheses to make sure the desired evaluation order is
used. Having said that, it does seem to be a bug in either the
documentation or the implementation. One that has been around since at
least SQL7.0.
HTH,
Gert-Jan
|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43764A1B.E0EC13DE@.toomuchspamalready.nl...
> David Browne wrote:
> I don't know if there is an easy way to see the version of BOL, but my
> BOL says this:
> Operators have these precedence levels. An operator on higher levels is
> evaluated before an operator on a lower level:
> + (Positive), - (Negative), ~ (Bitwise NOT)
> * (Multiply), / (Division), % (Modulo)
> + (Add), (+ Concatenate), - (Subtract)
> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> etc.
> In the BOL of SQL2K5 (CTP) it still has the same text:
> Level Operators
> 1 + (Positive), - (Negative), ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
> 4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> 5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
> 6 NOT
> 7 AND
> 8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
> 9 = (Assignment)
>
> I usually use parentheses to make sure the desired evaluation order is
> used. Having said that, it does seem to be a bug in either the
> documentation or the implementation. One that has been around since at
> least SQL7.0.
>
The text was from the 2005 RTM BOL. I didn't bother to check SQL 2000 or
think that it changed. I guess this is a doc bug on SQL 2000 since it
clearly binds % before unary + in both SQL 2000 and 2005.
David
|||http://msdn.microsoft.com/library/de...oa-oz_3qpf.asp
[vbcol=seagreen]
[vbcol=seagreen]
is evaluated before the addition operator.
So yeah, clearly a bug.
My problem lies therein that I found this while working on my SQL
Server -> Oracle parser which automatically parses SQL expression to
Oracle format, parsing <Expr1> % <Expr2> in SQL as MOD(<Expr1>,
<Expr2>).
Oh well, guess I'll follow the BOL and leave any resulting chaos to
blame on crappy MS developers. (They can purchase my parser if they
want, heheheh)
Thanks for the input.
SELECT 41 % 17 % 5
Returns: 2
This simply follows the rules of precedence:
1) Unary operators
2) Left to right per ranl
As all modulo operators are equal rank, it will be simply evaluated
left to right
--However, if we DO introduce unary operators, it doesn't work that way
SELECT 41 % +17 % 5
Returns: 1
As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
evaluated the modulo right to left.
As the + unary operator is basically void, it is a total mystery to me
why this happens.
-- Adding unary operators to the let or right elements as in SELECT +41
% 17 % +5 doesn't cause any trouble.
The return valus is still ok.
Adding a unary operator to the middle element changes the left-to-right
evaluation to right to left.
I found this under SQL Server 2000 Service Pack 3
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
I upgraded to Service Pack 4:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Still, the same thing happens.
After trying this under other operators, I found the same effect for
the divide (/) operator.
Consider:
SELECT 41 / 17 / 5
Will evaluate (correctly) as (4 / 17) / 5
However,
SELECT 41 /-+17 / 5
SELECT 41 / +17 / 5
Will both evaluate as 41 / (17 / 5) and 41 / (-17 / 5) respectively.
Anyone any idea? Bug? Documented ~feature~? What's up as I see no way
how this conforms to any operator precedence.
Cheers.
<martijn.vels@.gmail.com> wrote in message
news:1131753937.040613.203430@.g44g2000cwa.googlegr oups.com...
> Consider the following expression:
> SELECT 41 % 17 % 5
> Returns: 2
> This simply follows the rules of precedence:
> 1) Unary operators
> 2) Left to right per ranl
> As all modulo operators are equal rank, it will be simply evaluated
> left to right
> --However, if we DO introduce unary operators, it doesn't work that way
> SELECT 41 % +17 % 5
> Returns: 1
> As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
> evaluated the modulo right to left.
> As the + unary operator is basically void, it is a total mystery to me
> why this happens.
Unary + is still a valid operator and it has a lower precedence than %.
From BOL:
Operators have the precedence levels shown in the following table. An
operator on higher levels is evaluated before an operator on a lower level.
Level Operators
1 ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
(Bitwise AND)
.. . .
So the expression
41 % +17 % 5
is equivalent to as
41 % +(17 % 5)
David
|||David Browne wrote:
> <martijn.vels@.gmail.com> wrote in message
> news:1131753937.040613.203430@.g44g2000cwa.googlegr oups.com...
> Unary + is still a valid operator and it has a lower precedence than %.
> From BOL:
> Operators have the precedence levels shown in the following table. An
> operator on higher levels is evaluated before an operator on a lower level.
> Level Operators
> 1 ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
> (Bitwise AND)
> . . .
> So the expression
> 41 % +17 % 5
> is equivalent to as
> 41 % +(17 % 5)
> David
I don't know if there is an easy way to see the version of BOL, but my
BOL says this:
Operators have these precedence levels. An operator on higher levels is
evaluated before an operator on a lower level:
+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
etc.
In the BOL of SQL2K5 (CTP) it still has the same text:
Level Operators
1 + (Positive), - (Negative), ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
6 NOT
7 AND
8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
9 = (Assignment)
I usually use parentheses to make sure the desired evaluation order is
used. Having said that, it does seem to be a bug in either the
documentation or the implementation. One that has been around since at
least SQL7.0.
HTH,
Gert-Jan
|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43764A1B.E0EC13DE@.toomuchspamalready.nl...
> David Browne wrote:
> I don't know if there is an easy way to see the version of BOL, but my
> BOL says this:
> Operators have these precedence levels. An operator on higher levels is
> evaluated before an operator on a lower level:
> + (Positive), - (Negative), ~ (Bitwise NOT)
> * (Multiply), / (Division), % (Modulo)
> + (Add), (+ Concatenate), - (Subtract)
> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> etc.
> In the BOL of SQL2K5 (CTP) it still has the same text:
> Level Operators
> 1 + (Positive), - (Negative), ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
> 4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> 5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
> 6 NOT
> 7 AND
> 8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
> 9 = (Assignment)
>
> I usually use parentheses to make sure the desired evaluation order is
> used. Having said that, it does seem to be a bug in either the
> documentation or the implementation. One that has been around since at
> least SQL7.0.
>
The text was from the 2005 RTM BOL. I didn't bother to check SQL 2000 or
think that it changed. I guess this is a doc bug on SQL 2000 since it
clearly binds % before unary + in both SQL 2000 and 2005.
David
|||http://msdn.microsoft.com/library/de...oa-oz_3qpf.asp
[vbcol=seagreen]
[vbcol=seagreen]
is evaluated before the addition operator.
So yeah, clearly a bug.
My problem lies therein that I found this while working on my SQL
Server -> Oracle parser which automatically parses SQL expression to
Oracle format, parsing <Expr1> % <Expr2> in SQL as MOD(<Expr1>,
<Expr2>).
Oh well, guess I'll follow the BOL and leave any resulting chaos to
blame on crappy MS developers. (They can purchase my parser if they
want, heheheh)
Thanks for the input.
Operator precedence bug in SQL Server 2000?
Consider the following expression:
SELECT 41 % 17 % 5
Returns: 2
This simply follows the rules of precedence:
1) Unary operators
2) Left to right per ranl
As all modulo operators are equal rank, it will be simply evaluated
left to right
--However, if we DO introduce unary operators, it doesn't work that way
SELECT 41 % +17 % 5
Returns: 1
As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
evaluated the modulo right to left.
As the + unary operator is basically void, it is a total mystery to me
why this happens.
-- Adding unary operators to the let or right elements as in SELECT +41
% 17 % +5 doesn't cause any trouble.
The return valus is still ok.
Adding a unary operator to the middle element changes the left-to-right
evaluation to right to left.
I found this under SQL Server 2000 Service Pack 3
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
I upgraded to Service Pack 4:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Still, the same thing happens.
After trying this under other operators, I found the same effect for
the divide (/) operator.
Consider:
SELECT 41 / 17 / 5
Will evaluate (correctly) as (4 / 17) / 5
However,
SELECT 41 /-+17 / 5
SELECT 41 / +17 / 5
Will both evaluate as 41 / (17 / 5) and 41 / (-17 / 5) respectively.
Anyone any idea? Bug? Documented ~feature~? What's up as I see no way
how this conforms to any operator precedence.
Cheers.<martijn.vels@.gmail.com> wrote in message
news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> Consider the following expression:
> SELECT 41 % 17 % 5
> Returns: 2
> This simply follows the rules of precedence:
> 1) Unary operators
> 2) Left to right per ranl
> As all modulo operators are equal rank, it will be simply evaluated
> left to right
> --However, if we DO introduce unary operators, it doesn't work that way
> SELECT 41 % +17 % 5
> Returns: 1
> As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
> evaluated the modulo right to left.
> As the + unary operator is basically void, it is a total mystery to me
> why this happens.
Unary + is still a valid operator and it has a lower precedence than %.
From BOL:
Operators have the precedence levels shown in the following table. An
operator on higher levels is evaluated before an operator on a lower level.
Level Operators
1 ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
(Bitwise AND)
. . .
So the expression
41 % +17 % 5
is equivalent to as
41 % +(17 % 5)
David|||David Browne wrote:
> <martijn.vels@.gmail.com> wrote in message
> news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> > Consider the following expression:
> > SELECT 41 % 17 % 5
> > Returns: 2
> >
> > This simply follows the rules of precedence:
> > 1) Unary operators
> > 2) Left to right per ranl
> > As all modulo operators are equal rank, it will be simply evaluated
> > left to right
> >
> > --However, if we DO introduce unary operators, it doesn't work that way
> > SELECT 41 % +17 % 5
> > Returns: 1
> > As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
> > evaluated the modulo right to left.
> > As the + unary operator is basically void, it is a total mystery to me
> > why this happens.
> Unary + is still a valid operator and it has a lower precedence than %.
> From BOL:
> Operators have the precedence levels shown in the following table. An
> operator on higher levels is evaluated before an operator on a lower level.
> Level Operators
> 1 ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
> (Bitwise AND)
> . . .
> So the expression
> 41 % +17 % 5
> is equivalent to as
> 41 % +(17 % 5)
> David
I don't know if there is an easy way to see the version of BOL, but my
BOL says this:
Operators have these precedence levels. An operator on higher levels is
evaluated before an operator on a lower level:
+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
etc.
In the BOL of SQL2K5 (CTP) it still has the same text:
Level Operators
1 + (Positive), - (Negative), ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
6 NOT
7 AND
8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
9 = (Assignment)
I usually use parentheses to make sure the desired evaluation order is
used. Having said that, it does seem to be a bug in either the
documentation or the implementation. One that has been around since at
least SQL7.0.
HTH,
Gert-Jan|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43764A1B.E0EC13DE@.toomuchspamalready.nl...
> David Browne wrote:
>> <martijn.vels@.gmail.com> wrote in message
>> news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
>> > Consider the following expression:
>> > SELECT 41 % 17 % 5
>> > Returns: 2
>> >
>> > This simply follows the rules of precedence:
>> > 1) Unary operators
>> > 2) Left to right per ranl
>> > As all modulo operators are equal rank, it will be simply evaluated
>> > left to right
>> >
>> > --However, if we DO introduce unary operators, it doesn't work that way
>> > SELECT 41 % +17 % 5
>> > Returns: 1
>> > As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
>> > evaluated the modulo right to left.
>> > As the + unary operator is basically void, it is a total mystery to me
>> > why this happens.
>> Unary + is still a valid operator and it has a lower precedence than %.
>> From BOL:
>> Operators have the precedence levels shown in the following table. An
>> operator on higher levels is evaluated before an operator on a lower
>> level.
>> Level Operators
>> 1 ~ (Bitwise NOT)
>> 2 * (Multiply), / (Division), % (Modulo)
>> 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
>> (Bitwise AND)
>> . . .
>> So the expression
>> 41 % +17 % 5
>> is equivalent to as
>> 41 % +(17 % 5)
>> David
> I don't know if there is an easy way to see the version of BOL, but my
> BOL says this:
> Operators have these precedence levels. An operator on higher levels is
> evaluated before an operator on a lower level:
> + (Positive), - (Negative), ~ (Bitwise NOT)
> * (Multiply), / (Division), % (Modulo)
> + (Add), (+ Concatenate), - (Subtract)
> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> etc.
> In the BOL of SQL2K5 (CTP) it still has the same text:
> Level Operators
> 1 + (Positive), - (Negative), ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
> 4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> 5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
> 6 NOT
> 7 AND
> 8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
> 9 = (Assignment)
>
> I usually use parentheses to make sure the desired evaluation order is
> used. Having said that, it does seem to be a bug in either the
> documentation or the implementation. One that has been around since at
> least SQL7.0.
>
The text was from the 2005 RTM BOL. I didn't bother to check SQL 2000 or
think that it changed. I guess this is a doc bug on SQL 2000 since it
clearly binds % before unary + in both SQL 2000 and 2005.
David|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_3qpf.asp
>> Operators have these precedence levels. An operator on higher levels is evaluated before an operator on a lower level:
>> + (Positive), - (Negative), ~ (Bitwise NOT)
>> * (Multiply), / (Division), % (Modulo)
>> + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
>> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
>> ^ (Bitwise Exlusive OR), | (Bitwise OR)
>> NOT
>> AND
>> ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
>> = (Assignment)
>> When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression. For example, in the expression used in the SET statement of this example, the subtraction operator is evaluated before the addition operator.
--
So yeah, clearly a bug.
My problem lies therein that I found this while working on my SQL
Server -> Oracle parser which automatically parses SQL expression to
Oracle format, parsing <Expr1> % <Expr2> in SQL as MOD(<Expr1>,
<Expr2>).
Oh well, guess I'll follow the BOL and leave any resulting chaos to
blame on crappy MS developers. (They can purchase my parser if they
want, heheheh)
Thanks for the input.
SELECT 41 % 17 % 5
Returns: 2
This simply follows the rules of precedence:
1) Unary operators
2) Left to right per ranl
As all modulo operators are equal rank, it will be simply evaluated
left to right
--However, if we DO introduce unary operators, it doesn't work that way
SELECT 41 % +17 % 5
Returns: 1
As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
evaluated the modulo right to left.
As the + unary operator is basically void, it is a total mystery to me
why this happens.
-- Adding unary operators to the let or right elements as in SELECT +41
% 17 % +5 doesn't cause any trouble.
The return valus is still ok.
Adding a unary operator to the middle element changes the left-to-right
evaluation to right to left.
I found this under SQL Server 2000 Service Pack 3
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
I upgraded to Service Pack 4:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Still, the same thing happens.
After trying this under other operators, I found the same effect for
the divide (/) operator.
Consider:
SELECT 41 / 17 / 5
Will evaluate (correctly) as (4 / 17) / 5
However,
SELECT 41 /-+17 / 5
SELECT 41 / +17 / 5
Will both evaluate as 41 / (17 / 5) and 41 / (-17 / 5) respectively.
Anyone any idea? Bug? Documented ~feature~? What's up as I see no way
how this conforms to any operator precedence.
Cheers.<martijn.vels@.gmail.com> wrote in message
news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> Consider the following expression:
> SELECT 41 % 17 % 5
> Returns: 2
> This simply follows the rules of precedence:
> 1) Unary operators
> 2) Left to right per ranl
> As all modulo operators are equal rank, it will be simply evaluated
> left to right
> --However, if we DO introduce unary operators, it doesn't work that way
> SELECT 41 % +17 % 5
> Returns: 1
> As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
> evaluated the modulo right to left.
> As the + unary operator is basically void, it is a total mystery to me
> why this happens.
Unary + is still a valid operator and it has a lower precedence than %.
From BOL:
Operators have the precedence levels shown in the following table. An
operator on higher levels is evaluated before an operator on a lower level.
Level Operators
1 ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
(Bitwise AND)
. . .
So the expression
41 % +17 % 5
is equivalent to as
41 % +(17 % 5)
David|||David Browne wrote:
> <martijn.vels@.gmail.com> wrote in message
> news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> > Consider the following expression:
> > SELECT 41 % 17 % 5
> > Returns: 2
> >
> > This simply follows the rules of precedence:
> > 1) Unary operators
> > 2) Left to right per ranl
> > As all modulo operators are equal rank, it will be simply evaluated
> > left to right
> >
> > --However, if we DO introduce unary operators, it doesn't work that way
> > SELECT 41 % +17 % 5
> > Returns: 1
> > As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
> > evaluated the modulo right to left.
> > As the + unary operator is basically void, it is a total mystery to me
> > why this happens.
> Unary + is still a valid operator and it has a lower precedence than %.
> From BOL:
> Operators have the precedence levels shown in the following table. An
> operator on higher levels is evaluated before an operator on a lower level.
> Level Operators
> 1 ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
> (Bitwise AND)
> . . .
> So the expression
> 41 % +17 % 5
> is equivalent to as
> 41 % +(17 % 5)
> David
I don't know if there is an easy way to see the version of BOL, but my
BOL says this:
Operators have these precedence levels. An operator on higher levels is
evaluated before an operator on a lower level:
+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
etc.
In the BOL of SQL2K5 (CTP) it still has the same text:
Level Operators
1 + (Positive), - (Negative), ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
6 NOT
7 AND
8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
9 = (Assignment)
I usually use parentheses to make sure the desired evaluation order is
used. Having said that, it does seem to be a bug in either the
documentation or the implementation. One that has been around since at
least SQL7.0.
HTH,
Gert-Jan|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43764A1B.E0EC13DE@.toomuchspamalready.nl...
> David Browne wrote:
>> <martijn.vels@.gmail.com> wrote in message
>> news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
>> > Consider the following expression:
>> > SELECT 41 % 17 % 5
>> > Returns: 2
>> >
>> > This simply follows the rules of precedence:
>> > 1) Unary operators
>> > 2) Left to right per ranl
>> > As all modulo operators are equal rank, it will be simply evaluated
>> > left to right
>> >
>> > --However, if we DO introduce unary operators, it doesn't work that way
>> > SELECT 41 % +17 % 5
>> > Returns: 1
>> > As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
>> > evaluated the modulo right to left.
>> > As the + unary operator is basically void, it is a total mystery to me
>> > why this happens.
>> Unary + is still a valid operator and it has a lower precedence than %.
>> From BOL:
>> Operators have the precedence levels shown in the following table. An
>> operator on higher levels is evaluated before an operator on a lower
>> level.
>> Level Operators
>> 1 ~ (Bitwise NOT)
>> 2 * (Multiply), / (Division), % (Modulo)
>> 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
>> (Bitwise AND)
>> . . .
>> So the expression
>> 41 % +17 % 5
>> is equivalent to as
>> 41 % +(17 % 5)
>> David
> I don't know if there is an easy way to see the version of BOL, but my
> BOL says this:
> Operators have these precedence levels. An operator on higher levels is
> evaluated before an operator on a lower level:
> + (Positive), - (Negative), ~ (Bitwise NOT)
> * (Multiply), / (Division), % (Modulo)
> + (Add), (+ Concatenate), - (Subtract)
> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> etc.
> In the BOL of SQL2K5 (CTP) it still has the same text:
> Level Operators
> 1 + (Positive), - (Negative), ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
> 4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> 5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
> 6 NOT
> 7 AND
> 8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
> 9 = (Assignment)
>
> I usually use parentheses to make sure the desired evaluation order is
> used. Having said that, it does seem to be a bug in either the
> documentation or the implementation. One that has been around since at
> least SQL7.0.
>
The text was from the 2005 RTM BOL. I didn't bother to check SQL 2000 or
think that it changed. I guess this is a doc bug on SQL 2000 since it
clearly binds % before unary + in both SQL 2000 and 2005.
David|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_3qpf.asp
>> Operators have these precedence levels. An operator on higher levels is evaluated before an operator on a lower level:
>> + (Positive), - (Negative), ~ (Bitwise NOT)
>> * (Multiply), / (Division), % (Modulo)
>> + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
>> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
>> ^ (Bitwise Exlusive OR), | (Bitwise OR)
>> NOT
>> AND
>> ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
>> = (Assignment)
>> When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression. For example, in the expression used in the SET statement of this example, the subtraction operator is evaluated before the addition operator.
--
So yeah, clearly a bug.
My problem lies therein that I found this while working on my SQL
Server -> Oracle parser which automatically parses SQL expression to
Oracle format, parsing <Expr1> % <Expr2> in SQL as MOD(<Expr1>,
<Expr2>).
Oh well, guess I'll follow the BOL and leave any resulting chaos to
blame on crappy MS developers. (They can purchase my parser if they
want, heheheh)
Thanks for the input.
Operator precedence bug in SQL Server 2000?
Consider the following expression:
SELECT 41 % 17 % 5
Returns: 2
This simply follows the rules of precedence:
1) Unary operators
2) Left to right per ranl
As all modulo operators are equal rank, it will be simply evaluated
left to right
--However, if we DO introduce unary operators, it doesn't work that way
SELECT 41 % +17 % 5
Returns: 1
As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
evaluated the modulo right to left.
As the + unary operator is basically void, it is a total mystery to me
why this happens.
-- Adding unary operators to the let or right elements as in SELECT +41
% 17 % +5 doesn't cause any trouble.
The return valus is still ok.
Adding a unary operator to the middle element changes the left-to-right
evaluation to right to left.
I found this under SQL Server 2000 Service Pack 3
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
I upgraded to Service Pack 4:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Still, the same thing happens.
After trying this under other operators, I found the same effect for
the divide (/) operator.
Consider:
SELECT 41 / 17 / 5
Will evaluate (correctly) as (4 / 17) / 5
However,
SELECT 41 /-+17 / 5
SELECT 41 / +17 / 5
Will both evaluate as 41 / (17 / 5) and 41 / (-17 / 5) respectively.
Anyone any idea? Bug? Documented ~feature~? What's up as I see no way
how this conforms to any operator precedence.
Cheers.<martijn.vels@.gmail.com> wrote in message
news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> Consider the following expression:
> SELECT 41 % 17 % 5
> Returns: 2
> This simply follows the rules of precedence:
> 1) Unary operators
> 2) Left to right per ranl
> As all modulo operators are equal rank, it will be simply evaluated
> left to right
> --However, if we DO introduce unary operators, it doesn't work that way
> SELECT 41 % +17 % 5
> Returns: 1
> As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
> evaluated the modulo right to left.
> As the + unary operator is basically void, it is a total mystery to me
> why this happens.
Unary + is still a valid operator and it has a lower precedence than %.
From BOL:
Operators have the precedence levels shown in the following table. An
operator on higher levels is evaluated before an operator on a lower level.
Level Operators
1 ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
(Bitwise AND)
. . .
So the expression
41 % +17 % 5
is equivalent to as
41 % +(17 % 5)
David|||David Browne wrote:
> <martijn.vels@.gmail.com> wrote in message
> news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> Unary + is still a valid operator and it has a lower precedence than %.
> From BOL:
> Operators have the precedence levels shown in the following table. An
> operator on higher levels is evaluated before an operator on a lower level
.
> Level Operators
> 1 ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
> (Bitwise AND)
> . . .
> So the expression
> 41 % +17 % 5
> is equivalent to as
> 41 % +(17 % 5)
> David
I don't know if there is an easy way to see the version of BOL, but my
BOL says this:
Operators have these precedence levels. An operator on higher levels is
evaluated before an operator on a lower level:
+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
etc.
In the BOL of SQL2K5 (CTP) it still has the same text:
Level Operators
1 + (Positive), - (Negative), ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
6 NOT
7 AND
8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
9 = (Assignment)
I usually use parentheses to make sure the desired evaluation order is
used. Having said that, it does seem to be a bug in either the
documentation or the implementation. One that has been around since at
least SQL7.0.
HTH,
Gert-Jan|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43764A1B.E0EC13DE@.toomuchspamalready.nl...
> David Browne wrote:
> I don't know if there is an easy way to see the version of BOL, but my
> BOL says this:
> Operators have these precedence levels. An operator on higher levels is
> evaluated before an operator on a lower level:
> + (Positive), - (Negative), ~ (Bitwise NOT)
> * (Multiply), / (Division), % (Modulo)
> + (Add), (+ Concatenate), - (Subtract)
> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> etc.
> In the BOL of SQL2K5 (CTP) it still has the same text:
> Level Operators
> 1 + (Positive), - (Negative), ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
> 4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> 5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
> 6 NOT
> 7 AND
> 8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
> 9 = (Assignment)
>
> I usually use parentheses to make sure the desired evaluation order is
> used. Having said that, it does seem to be a bug in either the
> documentation or the implementation. One that has been around since at
> least SQL7.0.
>
The text was from the 2005 RTM BOL. I didn't bother to check SQL 2000 or
think that it changed. I guess this is a doc bug on SQL 2000 since it
clearly binds % before unary + in both SQL 2000 and 2005.
David|||http://msdn.microsoft.com/library/d...br />
3qpf.asp
[vbcol=seagreen]
[vbcol=seagreen]
is evaluated before the addition operator.
So yeah, clearly a bug.
My problem lies therein that I found this while working on my SQL
Server -> Oracle parser which automatically parses SQL expression to
Oracle format, parsing <Expr1> % <Expr2> in SQL as MOD(<Expr1>,
<Expr2> ).
Oh well, guess I'll follow the BOL and leave any resulting chaos to
blame on crappy MS developers. (They can purchase my parser if they
want, heheheh)
Thanks for the input.
SELECT 41 % 17 % 5
Returns: 2
This simply follows the rules of precedence:
1) Unary operators
2) Left to right per ranl
As all modulo operators are equal rank, it will be simply evaluated
left to right
--However, if we DO introduce unary operators, it doesn't work that way
SELECT 41 % +17 % 5
Returns: 1
As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
evaluated the modulo right to left.
As the + unary operator is basically void, it is a total mystery to me
why this happens.
-- Adding unary operators to the let or right elements as in SELECT +41
% 17 % +5 doesn't cause any trouble.
The return valus is still ok.
Adding a unary operator to the middle element changes the left-to-right
evaluation to right to left.
I found this under SQL Server 2000 Service Pack 3
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
I upgraded to Service Pack 4:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Still, the same thing happens.
After trying this under other operators, I found the same effect for
the divide (/) operator.
Consider:
SELECT 41 / 17 / 5
Will evaluate (correctly) as (4 / 17) / 5
However,
SELECT 41 /-+17 / 5
SELECT 41 / +17 / 5
Will both evaluate as 41 / (17 / 5) and 41 / (-17 / 5) respectively.
Anyone any idea? Bug? Documented ~feature~? What's up as I see no way
how this conforms to any operator precedence.
Cheers.<martijn.vels@.gmail.com> wrote in message
news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> Consider the following expression:
> SELECT 41 % 17 % 5
> Returns: 2
> This simply follows the rules of precedence:
> 1) Unary operators
> 2) Left to right per ranl
> As all modulo operators are equal rank, it will be simply evaluated
> left to right
> --However, if we DO introduce unary operators, it doesn't work that way
> SELECT 41 % +17 % 5
> Returns: 1
> As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
> evaluated the modulo right to left.
> As the + unary operator is basically void, it is a total mystery to me
> why this happens.
Unary + is still a valid operator and it has a lower precedence than %.
From BOL:
Operators have the precedence levels shown in the following table. An
operator on higher levels is evaluated before an operator on a lower level.
Level Operators
1 ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
(Bitwise AND)
. . .
So the expression
41 % +17 % 5
is equivalent to as
41 % +(17 % 5)
David|||David Browne wrote:
> <martijn.vels@.gmail.com> wrote in message
> news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> Unary + is still a valid operator and it has a lower precedence than %.
> From BOL:
> Operators have the precedence levels shown in the following table. An
> operator on higher levels is evaluated before an operator on a lower level
.
> Level Operators
> 1 ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
> (Bitwise AND)
> . . .
> So the expression
> 41 % +17 % 5
> is equivalent to as
> 41 % +(17 % 5)
> David
I don't know if there is an easy way to see the version of BOL, but my
BOL says this:
Operators have these precedence levels. An operator on higher levels is
evaluated before an operator on a lower level:
+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
etc.
In the BOL of SQL2K5 (CTP) it still has the same text:
Level Operators
1 + (Positive), - (Negative), ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
6 NOT
7 AND
8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
9 = (Assignment)
I usually use parentheses to make sure the desired evaluation order is
used. Having said that, it does seem to be a bug in either the
documentation or the implementation. One that has been around since at
least SQL7.0.
HTH,
Gert-Jan|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43764A1B.E0EC13DE@.toomuchspamalready.nl...
> David Browne wrote:
> I don't know if there is an easy way to see the version of BOL, but my
> BOL says this:
> Operators have these precedence levels. An operator on higher levels is
> evaluated before an operator on a lower level:
> + (Positive), - (Negative), ~ (Bitwise NOT)
> * (Multiply), / (Division), % (Modulo)
> + (Add), (+ Concatenate), - (Subtract)
> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> etc.
> In the BOL of SQL2K5 (CTP) it still has the same text:
> Level Operators
> 1 + (Positive), - (Negative), ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
> 4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> 5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
> 6 NOT
> 7 AND
> 8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
> 9 = (Assignment)
>
> I usually use parentheses to make sure the desired evaluation order is
> used. Having said that, it does seem to be a bug in either the
> documentation or the implementation. One that has been around since at
> least SQL7.0.
>
The text was from the 2005 RTM BOL. I didn't bother to check SQL 2000 or
think that it changed. I guess this is a doc bug on SQL 2000 since it
clearly binds % before unary + in both SQL 2000 and 2005.
David|||http://msdn.microsoft.com/library/d...br />
3qpf.asp
[vbcol=seagreen]
[vbcol=seagreen]
is evaluated before the addition operator.
So yeah, clearly a bug.
My problem lies therein that I found this while working on my SQL
Server -> Oracle parser which automatically parses SQL expression to
Oracle format, parsing <Expr1> % <Expr2> in SQL as MOD(<Expr1>,
<Expr2> ).
Oh well, guess I'll follow the BOL and leave any resulting chaos to
blame on crappy MS developers. (They can purchase my parser if they
want, heheheh)
Thanks for the input.
Operator Mail Problems
Have you been through the following...
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;263556
&
http://support.microsoft.com/default.aspx?scid=kb;en-
us;315886
Peter
"Do not awake the sleeping dragon for you are crunchy and
taste good with ketchup".
Peter The Spate
>--Original Message--
>I have set up an operator under the SQL Server Agent,
added an email address
>and click Test, and I get the following error message:
>Error 22022: SQLServerAgent Error: The SQLServerAgent
mail session is not
>running; check the mail profile and/or the SQLServerAgent
service startup
>account int the SQLServerAgent Properties dialog.
>Now, This machine has outlook installed and under Support
Services -> SQL
>Mail -> Properties, the profile name (Outlook) is
selected and I can Test it
>(Successfully started (and stopped) a MAPI session with
this profile.)
>Under the SQL Server Agent settings I have the profile
set there (Outlook)
>and I can successfully test it (Successfully started (and
stopped) a mail
>session with this profile)
>Both the SQL Server and SQL Server Agent are run under
the same domain
>account. I can log on with this account and open Outlook
and send and
>recieve email messages with no problems. I can also use
xp_sendmail in
>Query Analyzer to send email messages as well.
>I have searched extensively through google and have yet
to find any settings
>that I am missing, however, I am unable to recieve alerts
to my operators,
>I'm not even able to get the test message to work. Does
anyone have any
>ideas as to what could be causing this problem.
>Thanks for the help,
>Matt
>
>.
>
> Have you been through the following...
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;263556
> &
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;315886
> Peter
Yes, I have been through both, but neither cover the problem I am having.
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;263556
&
http://support.microsoft.com/default.aspx?scid=kb;en-
us;315886
Peter
"Do not awake the sleeping dragon for you are crunchy and
taste good with ketchup".
Peter The Spate
>--Original Message--
>I have set up an operator under the SQL Server Agent,
added an email address
>and click Test, and I get the following error message:
>Error 22022: SQLServerAgent Error: The SQLServerAgent
mail session is not
>running; check the mail profile and/or the SQLServerAgent
service startup
>account int the SQLServerAgent Properties dialog.
>Now, This machine has outlook installed and under Support
Services -> SQL
>Mail -> Properties, the profile name (Outlook) is
selected and I can Test it
>(Successfully started (and stopped) a MAPI session with
this profile.)
>Under the SQL Server Agent settings I have the profile
set there (Outlook)
>and I can successfully test it (Successfully started (and
stopped) a mail
>session with this profile)
>Both the SQL Server and SQL Server Agent are run under
the same domain
>account. I can log on with this account and open Outlook
and send and
>recieve email messages with no problems. I can also use
xp_sendmail in
>Query Analyzer to send email messages as well.
>I have searched extensively through google and have yet
to find any settings
>that I am missing, however, I am unable to recieve alerts
to my operators,
>I'm not even able to get the test message to work. Does
anyone have any
>ideas as to what could be causing this problem.
>Thanks for the help,
>Matt
>
>.
>
> Have you been through the following...
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;263556
> &
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;315886
> Peter
Yes, I have been through both, but neither cover the problem I am having.
Operator Mail Problems
Have you been through the following...
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;263556
&
http://support.microsoft.com/default.aspx?scid=kb;en-
us;315886
Peter
"Do not awake the sleeping dragon for you are crunchy and
taste good with ketchup".
Peter The Spate
>--Original Message--
>I have set up an operator under the SQL Server Agent,
added an email address
>and click Test, and I get the following error message:
>Error 22022: SQLServerAgent Error: The SQLServerAgent
mail session is not
>running; check the mail profile and/or the SQLServerAgent
service startup
>account int the SQLServerAgent Properties dialog.
>Now, This machine has outlook installed and under Support
Services -> SQL
>Mail -> Properties, the profile name (Outlook) is
selected and I can Test it
>(Successfully started (and stopped) a MAPI session with
this profile.)
>Under the SQL Server Agent settings I have the profile
set there (Outlook)
>and I can successfully test it (Successfully started (and
stopped) a mail
>session with this profile)
>Both the SQL Server and SQL Server Agent are run under
the same domain
>account. I can log on with this account and open Outlook
and send and
>recieve email messages with no problems. I can also use
xp_sendmail in
>Query Analyzer to send email messages as well.
>I have searched extensively through google and have yet
to find any settings
>that I am missing, however, I am unable to recieve alerts
to my operators,
>I'm not even able to get the test message to work. Does
anyone have any
>ideas as to what could be causing this problem.
>Thanks for the help,
>Matt
>
>.
>> Have you been through the following...
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;263556
> &
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;315886
> Peter
Yes, I have been through both, but neither cover the problem I am having.
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;263556
&
http://support.microsoft.com/default.aspx?scid=kb;en-
us;315886
Peter
"Do not awake the sleeping dragon for you are crunchy and
taste good with ketchup".
Peter The Spate
>--Original Message--
>I have set up an operator under the SQL Server Agent,
added an email address
>and click Test, and I get the following error message:
>Error 22022: SQLServerAgent Error: The SQLServerAgent
mail session is not
>running; check the mail profile and/or the SQLServerAgent
service startup
>account int the SQLServerAgent Properties dialog.
>Now, This machine has outlook installed and under Support
Services -> SQL
>Mail -> Properties, the profile name (Outlook) is
selected and I can Test it
>(Successfully started (and stopped) a MAPI session with
this profile.)
>Under the SQL Server Agent settings I have the profile
set there (Outlook)
>and I can successfully test it (Successfully started (and
stopped) a mail
>session with this profile)
>Both the SQL Server and SQL Server Agent are run under
the same domain
>account. I can log on with this account and open Outlook
and send and
>recieve email messages with no problems. I can also use
xp_sendmail in
>Query Analyzer to send email messages as well.
>I have searched extensively through google and have yet
to find any settings
>that I am missing, however, I am unable to recieve alerts
to my operators,
>I'm not even able to get the test message to work. Does
anyone have any
>ideas as to what could be causing this problem.
>Thanks for the help,
>Matt
>
>.
>> Have you been through the following...
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;263556
> &
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;315886
> Peter
Yes, I have been through both, but neither cover the problem I am having.
Operator Mail Problems
I have set up an operator under the SQL Server Agent, added an email address
and click Test, and I get the following error message:
Error 22022: SQLServerAgent Error: The SQLServerAgent mail session is not
running; check the mail profile and/or the SQLServerAgent service startup
account int the SQLServerAgent Properties dialog.
Now, This machine has outlook installed and under Support Services -> SQL
Mail -> Properties, the profile name (Outlook) is selected and I can Test it
(Successfully started (and stopped) a MAPI session with this profile.)
Under the SQL Server Agent settings I have the profile set there (Outlook)
and I can successfully test it (Successfully started (and stopped) a mail
session with this profile)
Both the SQL Server and SQL Server Agent are run under the same domain
account. I can log on with this account and open Outlook and send and
recieve email messages with no problems. I can also use xp_sendmail in
Query Analyzer to send email messages as well.
I have searched extensively through google and have yet to find any settings
that I am missing, however, I am unable to recieve alerts to my operators,
I'm not even able to get the test message to work. Does anyone have any
ideas as to what could be causing this problem.
Thanks for the help,
MattHave you been through the following...
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;263556
&
http://support.microsoft.com/default.aspx?scid=kb;en-
us;315886
Peter
"Do not awake the sleeping dragon for you are crunchy and
taste good with ketchup".
Peter The Spate
>--Original Message--
>I have set up an operator under the SQL Server Agent,
added an email address
>and click Test, and I get the following error message:
>Error 22022: SQLServerAgent Error: The SQLServerAgent
mail session is not
>running; check the mail profile and/or the SQLServerAgent
service startup
>account int the SQLServerAgent Properties dialog.
>Now, This machine has outlook installed and under Support
Services -> SQL
>Mail -> Properties, the profile name (Outlook) is
selected and I can Test it
>(Successfully started (and stopped) a MAPI session with
this profile.)
>Under the SQL Server Agent settings I have the profile
set there (Outlook)
>and I can successfully test it (Successfully started (and
stopped) a mail
>session with this profile)
>Both the SQL Server and SQL Server Agent are run under
the same domain
>account. I can log on with this account and open Outlook
and send and
>recieve email messages with no problems. I can also use
xp_sendmail in
>Query Analyzer to send email messages as well.
>I have searched extensively through google and have yet
to find any settings
>that I am missing, however, I am unable to recieve alerts
to my operators,
>I'm not even able to get the test message to work. Does
anyone have any
>ideas as to what could be causing this problem.
>Thanks for the help,
>Matt
>
>.
>|||> Have you been through the following...
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;263556
> &
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;315886
> Peter
Yes, I have been through both, but neither cover the problem I am having.
and click Test, and I get the following error message:
Error 22022: SQLServerAgent Error: The SQLServerAgent mail session is not
running; check the mail profile and/or the SQLServerAgent service startup
account int the SQLServerAgent Properties dialog.
Now, This machine has outlook installed and under Support Services -> SQL
Mail -> Properties, the profile name (Outlook) is selected and I can Test it
(Successfully started (and stopped) a MAPI session with this profile.)
Under the SQL Server Agent settings I have the profile set there (Outlook)
and I can successfully test it (Successfully started (and stopped) a mail
session with this profile)
Both the SQL Server and SQL Server Agent are run under the same domain
account. I can log on with this account and open Outlook and send and
recieve email messages with no problems. I can also use xp_sendmail in
Query Analyzer to send email messages as well.
I have searched extensively through google and have yet to find any settings
that I am missing, however, I am unable to recieve alerts to my operators,
I'm not even able to get the test message to work. Does anyone have any
ideas as to what could be causing this problem.
Thanks for the help,
MattHave you been through the following...
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;263556
&
http://support.microsoft.com/default.aspx?scid=kb;en-
us;315886
Peter
"Do not awake the sleeping dragon for you are crunchy and
taste good with ketchup".
Peter The Spate
>--Original Message--
>I have set up an operator under the SQL Server Agent,
added an email address
>and click Test, and I get the following error message:
>Error 22022: SQLServerAgent Error: The SQLServerAgent
mail session is not
>running; check the mail profile and/or the SQLServerAgent
service startup
>account int the SQLServerAgent Properties dialog.
>Now, This machine has outlook installed and under Support
Services -> SQL
>Mail -> Properties, the profile name (Outlook) is
selected and I can Test it
>(Successfully started (and stopped) a MAPI session with
this profile.)
>Under the SQL Server Agent settings I have the profile
set there (Outlook)
>and I can successfully test it (Successfully started (and
stopped) a mail
>session with this profile)
>Both the SQL Server and SQL Server Agent are run under
the same domain
>account. I can log on with this account and open Outlook
and send and
>recieve email messages with no problems. I can also use
xp_sendmail in
>Query Analyzer to send email messages as well.
>I have searched extensively through google and have yet
to find any settings
>that I am missing, however, I am unable to recieve alerts
to my operators,
>I'm not even able to get the test message to work. Does
anyone have any
>ideas as to what could be causing this problem.
>Thanks for the help,
>Matt
>
>.
>|||> Have you been through the following...
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;263556
> &
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;315886
> Peter
Yes, I have been through both, but neither cover the problem I am having.
Operator is not valid for type 'Date' and type 'Date'
I was wondering if someone could assist me in writing an experession that
would subtract 2 DateTime's. I have 2 DateTime fields and want to display
both in a report and in a third column the difference between the 2 values.
However, I get an error:
"Operator is not valid for type 'Date' and type 'Date'"
Many thanks,
SimonYou can get the difference from SQL by trying something like this in your
store proc or query
SELECT date1, date2, DATEDIFF(d, date1, date2) AS date3
hope it helps.
- David
"Simon Dingley" wrote:
> I was wondering if someone could assist me in writing an experession that
> would subtract 2 DateTime's. I have 2 DateTime fields and want to display
> both in a report and in a third column the difference between the 2 values.
> However, I get an error:
> "Operator is not valid for type 'Date' and type 'Date'"
> Many thanks,
> Simon
>
>|||Assuming you have two DateTime fields and you want to determine the
difference in total seconds in the report rather than the query, you can use
an expression like this:
=Datediff("s", Fields!End_Time.Value, Fields!Start_Time.Value)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Nevarez" <DavidNevarez@.discussions.microsoft.com> wrote in message
news:33B5D244-F9D5-4ECB-99CB-F9B1B7D9E445@.microsoft.com...
> You can get the difference from SQL by trying something like this in your
> store proc or query
> SELECT date1, date2, DATEDIFF(d, date1, date2) AS date3
> hope it helps.
> - David
> "Simon Dingley" wrote:
> > I was wondering if someone could assist me in writing an experession
that
> > would subtract 2 DateTime's. I have 2 DateTime fields and want to
display
> > both in a report and in a third column the difference between the 2
values.
> > However, I get an error:
> >
> > "Operator is not valid for type 'Date' and type 'Date'"
> >
> > Many thanks,
> >
> > Simon
> >
> >
> >|||Thank You for the replies. I opted for the in-report solution as opposed to
the SQL solution but thanks for both. What I want to return is the
difference in days and hours but can seem to do it I changed the format
string to "d" instead of "s" to return the number of days but when I tried
to use "d h" i get "#error".
Thanks for the help.
Simon
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eVK4OnNsEHA.832@.TK2MSFTNGP10.phx.gbl...
> Assuming you have two DateTime fields and you want to determine the
> difference in total seconds in the report rather than the query, you can
use
> an expression like this:
> =Datediff("s", Fields!End_Time.Value, Fields!Start_Time.Value)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
would subtract 2 DateTime's. I have 2 DateTime fields and want to display
both in a report and in a third column the difference between the 2 values.
However, I get an error:
"Operator is not valid for type 'Date' and type 'Date'"
Many thanks,
SimonYou can get the difference from SQL by trying something like this in your
store proc or query
SELECT date1, date2, DATEDIFF(d, date1, date2) AS date3
hope it helps.
- David
"Simon Dingley" wrote:
> I was wondering if someone could assist me in writing an experession that
> would subtract 2 DateTime's. I have 2 DateTime fields and want to display
> both in a report and in a third column the difference between the 2 values.
> However, I get an error:
> "Operator is not valid for type 'Date' and type 'Date'"
> Many thanks,
> Simon
>
>|||Assuming you have two DateTime fields and you want to determine the
difference in total seconds in the report rather than the query, you can use
an expression like this:
=Datediff("s", Fields!End_Time.Value, Fields!Start_Time.Value)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Nevarez" <DavidNevarez@.discussions.microsoft.com> wrote in message
news:33B5D244-F9D5-4ECB-99CB-F9B1B7D9E445@.microsoft.com...
> You can get the difference from SQL by trying something like this in your
> store proc or query
> SELECT date1, date2, DATEDIFF(d, date1, date2) AS date3
> hope it helps.
> - David
> "Simon Dingley" wrote:
> > I was wondering if someone could assist me in writing an experession
that
> > would subtract 2 DateTime's. I have 2 DateTime fields and want to
display
> > both in a report and in a third column the difference between the 2
values.
> > However, I get an error:
> >
> > "Operator is not valid for type 'Date' and type 'Date'"
> >
> > Many thanks,
> >
> > Simon
> >
> >
> >|||Thank You for the replies. I opted for the in-report solution as opposed to
the SQL solution but thanks for both. What I want to return is the
difference in days and hours but can seem to do it I changed the format
string to "d" instead of "s" to return the number of days but when I tried
to use "d h" i get "#error".
Thanks for the help.
Simon
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eVK4OnNsEHA.832@.TK2MSFTNGP10.phx.gbl...
> Assuming you have two DateTime fields and you want to determine the
> difference in total seconds in the report rather than the query, you can
use
> an expression like this:
> =Datediff("s", Fields!End_Time.Value, Fields!Start_Time.Value)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
Operator failing
sql2k sp3
SQLMail and the Mail Profile are configured and
successfully test when hitting the "test" button in the
SQL Mail Configuration Properties box.
But when I try to do a test for an operator in the
Operator Properties it says "Error 22022: SQLServerAgent
mail session not running; check mail profile and/ or
SQLServerAgent service startup account in SQLServerAgent
properites dialog box".
The Agent and the Mail Profile(same account) are in the
Local Admins Group. I have already rebooted.
Any ideas?
TIA, ChrisR
Hi Chris
You have already asked this question once and got a reply...:-).
Have you tried to check the properties of the SQLSERVERAgent in EM. Here
you have to set a mailprofile for the mail session.
Regards
Steen
"ChrtisR" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:2901301c464fa$6779e5a0$a501280a@.phx.gbl...
> sql2k sp3
> SQLMail and the Mail Profile are configured and
> successfully test when hitting the "test" button in the
> SQL Mail Configuration Properties box.
> But when I try to do a test for an operator in the
> Operator Properties it says "Error 22022: SQLServerAgent
> mail session not running; check mail profile and/ or
> SQLServerAgent service startup account in SQLServerAgent
> properites dialog box".
> The Agent and the Mail Profile(same account) are in the
> Local Admins Group. I have already rebooted.
> Any ideas?
> TIA, ChrisR
>
SQLMail and the Mail Profile are configured and
successfully test when hitting the "test" button in the
SQL Mail Configuration Properties box.
But when I try to do a test for an operator in the
Operator Properties it says "Error 22022: SQLServerAgent
mail session not running; check mail profile and/ or
SQLServerAgent service startup account in SQLServerAgent
properites dialog box".
The Agent and the Mail Profile(same account) are in the
Local Admins Group. I have already rebooted.
Any ideas?
TIA, ChrisR
Hi Chris
You have already asked this question once and got a reply...:-).
Have you tried to check the properties of the SQLSERVERAgent in EM. Here
you have to set a mailprofile for the mail session.
Regards
Steen
"ChrtisR" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:2901301c464fa$6779e5a0$a501280a@.phx.gbl...
> sql2k sp3
> SQLMail and the Mail Profile are configured and
> successfully test when hitting the "test" button in the
> SQL Mail Configuration Properties box.
> But when I try to do a test for an operator in the
> Operator Properties it says "Error 22022: SQLServerAgent
> mail session not running; check mail profile and/ or
> SQLServerAgent service startup account in SQLServerAgent
> properites dialog box".
> The Agent and the Mail Profile(same account) are in the
> Local Admins Group. I have already rebooted.
> Any ideas?
> TIA, ChrisR
>
Operator failing
sql2k sp3
SQLMail and the Mail Profile are configured and
successfully test when hitting the "test" button in the
SQL Mail Configuration Properties box.
But when I try to do a test for an operator in the
Operator Properties it says "Error 22022: SQLServerAgent
mail session not running; check mail profile and/ or
SQLServerAgent service startup account in SQLServerAgent
properites dialog box".
The Agent and the Mail Profile(same account) are in the
Local Admins Group. I have already rebooted.
Any ideas?
TIA, ChrisRHi Chris
You have already asked this question once and got a reply...:-).
Have you tried to check the properties of the SQLSERVERAgent in EM. Here
you have to set a mailprofile for the mail session.
Regards
Steen
"ChrtisR" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:2901301c464fa$6779e5a0$a501280a@.phx
.gbl...
> sql2k sp3
> SQLMail and the Mail Profile are configured and
> successfully test when hitting the "test" button in the
> SQL Mail Configuration Properties box.
> But when I try to do a test for an operator in the
> Operator Properties it says "Error 22022: SQLServerAgent
> mail session not running; check mail profile and/ or
> SQLServerAgent service startup account in SQLServerAgent
> properites dialog box".
> The Agent and the Mail Profile(same account) are in the
> Local Admins Group. I have already rebooted.
> Any ideas?
> TIA, ChrisR
>
SQLMail and the Mail Profile are configured and
successfully test when hitting the "test" button in the
SQL Mail Configuration Properties box.
But when I try to do a test for an operator in the
Operator Properties it says "Error 22022: SQLServerAgent
mail session not running; check mail profile and/ or
SQLServerAgent service startup account in SQLServerAgent
properites dialog box".
The Agent and the Mail Profile(same account) are in the
Local Admins Group. I have already rebooted.
Any ideas?
TIA, ChrisRHi Chris
You have already asked this question once and got a reply...:-).
Have you tried to check the properties of the SQLSERVERAgent in EM. Here
you have to set a mailprofile for the mail session.
Regards
Steen
"ChrtisR" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:2901301c464fa$6779e5a0$a501280a@.phx
.gbl...
> sql2k sp3
> SQLMail and the Mail Profile are configured and
> successfully test when hitting the "test" button in the
> SQL Mail Configuration Properties box.
> But when I try to do a test for an operator in the
> Operator Properties it says "Error 22022: SQLServerAgent
> mail session not running; check mail profile and/ or
> SQLServerAgent service startup account in SQLServerAgent
> properites dialog box".
> The Agent and the Mail Profile(same account) are in the
> Local Admins Group. I have already rebooted.
> Any ideas?
> TIA, ChrisR
>
Operator failing
sql2k sp3
SQLMail and the Mail Profile are configured and
successfully test when hitting the "test" button in the
SQL Mail Configuration Properties box.
But when I try to do a test for an operator in the
Operator Properties it says "Error 22022: SQLServerAgent
mail session not running; check mail profile and/ or
SQLServerAgent service startup account in SQLServerAgent
properites dialog box".
The Agent and the Mail Profile(same account) are in the
Local Admins Group. I have already rebooted.
Any ideas?
TIA, ChrisRHi Chris
You have already asked this question once and got a reply...:-).
Have you tried to check the properties of the SQLSERVERAgent in EM. Here
you have to set a mailprofile for the mail session.
Regards
Steen
"ChrtisR" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:2901301c464fa$6779e5a0$a501280a@.phx.gbl...
> sql2k sp3
> SQLMail and the Mail Profile are configured and
> successfully test when hitting the "test" button in the
> SQL Mail Configuration Properties box.
> But when I try to do a test for an operator in the
> Operator Properties it says "Error 22022: SQLServerAgent
> mail session not running; check mail profile and/ or
> SQLServerAgent service startup account in SQLServerAgent
> properites dialog box".
> The Agent and the Mail Profile(same account) are in the
> Local Admins Group. I have already rebooted.
> Any ideas?
> TIA, ChrisR
>
SQLMail and the Mail Profile are configured and
successfully test when hitting the "test" button in the
SQL Mail Configuration Properties box.
But when I try to do a test for an operator in the
Operator Properties it says "Error 22022: SQLServerAgent
mail session not running; check mail profile and/ or
SQLServerAgent service startup account in SQLServerAgent
properites dialog box".
The Agent and the Mail Profile(same account) are in the
Local Admins Group. I have already rebooted.
Any ideas?
TIA, ChrisRHi Chris
You have already asked this question once and got a reply...:-).
Have you tried to check the properties of the SQLSERVERAgent in EM. Here
you have to set a mailprofile for the mail session.
Regards
Steen
"ChrtisR" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:2901301c464fa$6779e5a0$a501280a@.phx.gbl...
> sql2k sp3
> SQLMail and the Mail Profile are configured and
> successfully test when hitting the "test" button in the
> SQL Mail Configuration Properties box.
> But when I try to do a test for an operator in the
> Operator Properties it says "Error 22022: SQLServerAgent
> mail session not running; check mail profile and/ or
> SQLServerAgent service startup account in SQLServerAgent
> properites dialog box".
> The Agent and the Mail Profile(same account) are in the
> Local Admins Group. I have already rebooted.
> Any ideas?
> TIA, ChrisR
>
Subscribe to:
Posts (Atom)