MAPI_E_LOGON_FAILED

SQL Mail is failing. SQL Agent Mail works, but SQL Mail
does not.
The online help says: "When you look up the hex value
0x80040111, you see that this corresponds to the MAPI
error message MAPI_E_LOGON_FAILED, which indicates that
SQL Mail failed to logon with the mail profile provided.
You can then take corrective action based on the error
message."
OK, so what's the "corrective action"?Check the following article for troubleshooting the
problems:
INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
INF: Common SQL Mail Problems
http://support.microsoft.com/?id=315886
-Sue
On Mon, 13 Oct 2003 08:53:56 -0700, "Dennis Taylor"
<dennistaylor@.freightliner.com> wrote:
>SQL Mail is failing. SQL Agent Mail works, but SQL Mail
>does not.
>The online help says: "When you look up the hex value
>0x80040111, you see that this corresponds to the MAPI
>error message MAPI_E_LOGON_FAILED, which indicates that
>SQL Mail failed to logon with the mail profile provided.
>You can then take corrective action based on the error
>message."
>OK, so what's the "corrective action"?
>

Manually Insert a Primary Key Value

I have a colleague who mysteriously lost his record in our Employee table.
The "employee ID" field serves as the primary key on the table.
How do I manually insert his record, including the old primary key value,
back into the table? That is, how do I bypass the primary-key constraint?
Thanks in advance,
Mark HolahanWhat is the definition of the table?
AMB
"Mark Holahan" wrote:

> I have a colleague who mysteriously lost his record in our Employee table.
> The "employee ID" field serves as the primary key on the table.
> How do I manually insert his record, including the old primary key value,
> back into the table? That is, how do I bypass the primary-key constraint?
> Thanks in advance,
> Mark Holahan
>
>|||Is this an identity field? if so use:
SET IDENTITY_INSERT ON
--execute insert statement here
SET IDENTITY_INSERT OFF|||You can't "bypass" a primary key constraint unless you drop it. I
assume you are actually referring to the IDENTITY property on this
column. The IDENTITY property is quite distinct from a PRIMARY KEY
constraint. If you want to insert an explicit IDENTITY value then use
the SET IDENTITY_INSERT table_name ON option.
Why does it matter to you if the row gets inserted with a different
IDENTITY value to the one it originally had? It shouldn't have been
possible for the accidental delete to cause "orphan" rows in a
referencing table - That's assuming you have correctly declared foreign
key constraints against the employee ID column. If you don't have
foreign keys then that's something you really ought to fix.
David Portas
SQL Server MVP
--|||AMB,
The table definition follows:
CREATE TABLE [dbo].[Employee] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BranchId] [int] NULL ,
[SalesRepId] [int] NULL ,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NetworkId] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Deactivated] [datetime] NULL ,
[ResetPW] [bit] NOT NULL ,
[Tries] [tinyint] NULL ,
[LastLoginDtm] [datetime] NULL ,
[PendingInfoUpdate] [bit] NOT NULL ,
[IsSalesRep] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] ADD
CONSTRAINT [DF_Employee_ResetPW] DEFAULT (0) FOR [ResetPW],
CONSTRAINT [DF_Employee_PendingInfoUpdate] DEFAULT (0) FOR
[PendingInfoUpdate],
CONSTRAINT [DF_Employee_IsSalesRep] DEFAULT (0) FOR [IsSalesRep]
GO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3E956B3B-CF85-4FBA-B885-41BE4C9A96FD@.microsoft.com...
> What is the definition of the table?
>
> AMB
> "Mark Holahan" wrote:
>|||Read David's post.
AMB
"Mark Holahan" wrote:

> AMB,
> The table definition follows:
> CREATE TABLE [dbo].[Employee] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BranchId] [int] NULL ,
> [SalesRepId] [int] NULL ,
> [Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NetworkId] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Deactivated] [datetime] NULL ,
> [ResetPW] [bit] NOT NULL ,
> [Tries] [tinyint] NULL ,
> [LastLoginDtm] [datetime] NULL ,
> [PendingInfoUpdate] [bit] NOT NULL ,
> [IsSalesRep] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
> CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Employee] ADD
> CONSTRAINT [DF_Employee_ResetPW] DEFAULT (0) FOR [ResetPW],
> CONSTRAINT [DF_Employee_PendingInfoUpdate] DEFAULT (0) FOR
> [PendingInfoUpdate],
> CONSTRAINT [DF_Employee_IsSalesRep] DEFAULT (0) FOR [IsSalesRep]
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:3E956B3B-CF85-4FBA-B885-41BE4C9A96FD@.microsoft.com...
>
>|||Distinction noted.
CIO of company claims RI puts unneeded burden on SQL Server. Therefore we
handle RI on the front end. I don't necessarily agree, especially when I
read in BOL that, "The query optimizer also uses constraint definitions to
build high-performance query execution plans." But I've never done the
homework to disprove his theory. So I abide.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107450710.189817.206210@.g14g2000cwa.googlegroups.com...
> You can't "bypass" a primary key constraint unless you drop it. I
> assume you are actually referring to the IDENTITY property on this
> column. The IDENTITY property is quite distinct from a PRIMARY KEY
> constraint. If you want to insert an explicit IDENTITY value then use
> the SET IDENTITY_INSERT table_name ON option.
> Why does it matter to you if the row gets inserted with a different
> IDENTITY value to the one it originally had? It shouldn't have been
> possible for the accidental delete to cause "orphan" rows in a
> referencing table - That's assuming you have correctly declared foreign
> key constraints against the employee ID column. If you don't have
> foreign keys then that's something you really ought to fix.
> --
> David Portas
> SQL Server MVP
> --
>|||The CIO is wrong. If he wants to design databases he should take a course
first ;-)
Obviously handling RI on the front end isn't working otherwise you wouldn't
have this problem. No surprises there.
David Portas
SQL Server MVP
--

Manipulating dates in an expression

Does anyone know a way to subtract a date from a date
parameter (e.g. = Parameters!Date.Value - 1, which of
course does not work)
thanksYou might want to read the MSDN documentation for the DateTime class:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
E.g. subtracting one day would be =Parameters!Date.Value.AddDays(-1)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nick Caramello" <nick@.podconsulting.com> wrote in message
news:2ebc01c49fde$a16edd50$a501280a@.phx.gbl...
> Does anyone know a way to subtract a date from a date
> parameter (e.g. = Parameters!Date.Value - 1, which of
> course does not work)
> thanks|||Here is how I subtracted one date from another to get a number of days
between them:
=Fields!DESIRED_SHIP_DATE.Value.ToOADate() -
Fields!ORDER_DATE.Value.ToOADate()
This information below is not a direct solution for your problem, but the
information may help you with other things data/time related.
Here is how I get the current hour and manipate it:
The DateAndTime.Hour(Now) return a 24 hour clock output for the hour, so
this code turns hour 13 to 1 (like 1pm).
Dim cHour As Integer = DateAndTime.Hour(Now)
If cHour > 12 Then
StartHourBox.Text = cHour - 12
Else
StartHourBox.Text = cHour
End If
Look at all the different options available to you with "DateAndTime",
including Day, Month, and year.
Later,
Ed Hammond
--
"Nick Caramello" <nick@.podconsulting.com> wrote in message
news:2ebc01c49fde$a16edd50$a501280a@.phx.gbl...
> Does anyone know a way to subtract a date from a date
> parameter (e.g. = Parameters!Date.Value - 1, which of
> course does not work)
> thanks

Manipulating Data

Hello all...

I have a table that inlcudes a field that represents a percentage value (like 1.23), but the data is presented as 000123 (hope that makes sense). I get the data from a location outside of my office and I have no say in how the data is formatted.

I need to run a query that multiplies one field by this above mentioned field.

Is there a way to convert the value to the way I need it programmatically (in a query) without putting it in a temporary table?

Example:

Table 1 - fields - ID, Amount
Table 2 - fields - Type, Percentage

Table 1 (one row of data) - 1 234
Table 2 (one row of data) - A 000123

I want to multiply the Amount from Table 1 (234) by the Percentage from Table 2 (000123) where the Type from Table 2 is equal to A.

This should be worked out as 234 * 1.23

Thanks for any help.

Quote:

Originally Posted by narpet

Hello all...

I have a table that inlcudes a field that represents a percentage value (like 1.23), but the data is presented as 000123 (hope that makes sense). I get the data from a location outside of my office and I have no say in how the data is formatted.

I need to run a query that multiplies one field by this above mentioned field.

Is there a way to convert the value to the way I need it programmatically (in a query) without putting it in a temporary table?

Example:

Table 1 - fields - ID, Amount
Table 2 - fields - Type, Percentage

Table 1 (one row of data) - 1 234
Table 2 (one row of data) - A 000123

I want to multiply the Amount from Table 1 (234) by the Percentage from Table 2 (000123) where the Type from Table 2 is equal to A.

This should be worked out as 234 * 1.23

Thanks for any help.

first, how are the two tables related? although these two tables may still be joined even if they are not related, it's a rare situation that you will join two unrelated tables.

try this:
select 1, amount, type, percentage, amount * cast(percentage as float)/100.00
from table1
full outer join table2 on cast(id as varchar(2)) = cast(type as varchar(2)).
and type = 'A'

since "full outer join" joins the two table wheather there matched records or not, it will always return the values on the right

i did not test this query, is this right?|||

Quote:

Originally Posted by ck9663

first, how are the two tables related? although these two tables may still be joined even if they are not related, it's a rare situation that you will join two unrelated tables.

try this:
select 1, amount, type, percentage, amount * cast(percentage as float)/100.00
from table1
full outer join table2 on cast(id as varchar(2)) = cast(type as varchar(2)).
and type = 'A'

since "full outer join" joins the two table wheather there matched records or not, it will always return the values on the right

i did not test this query, is this right?

I'm not at work right now. I will test this when I get in on Monday morning. Thanks for the info... I will post and let you know how this works. As an answer to your question... the two tables will be joined by a common account number.|||

Quote:

Originally Posted by narpet

I'm not at work right now. I will test this when I get in on Monday morning. Thanks for the info... I will post and let you know how this works. As an answer to your question... the two tables will be joined by a common account number.

then you use account number as the join key. whether it'll be an outer, left, right or inner join will be up to your requirement|||That worked perfectly. Thanks very much for the help!

manipulate matrix/group column heading for subtotal column

Hello all!

I'm using a matrix with a subtotal. The subtotal shows prior to the detail (first column). For the heading I have

= Fields!category.Value + " HeadCounts"

(where category is the grouping for the matrix)

Which is fine for the detail columns. But the subtotal column repeats the value for the first detail column heading and it is inappropriate. How do I identify this column and replace the heading when it is the subtotal column?

I hope I stated that clearly.

You can use the InScope() function to find out if you are in a subtotal and display different content. http://msdn2.microsoft.com/en-us/library/ms156490.aspx|||

I had two fields under the group/category. Each calculating an aggregate on different fields. I had placed the field/column headings at that level which included the category 'marker'. I found that if I put the category ('marker') at the group level on the heading and removed it at this point that it filled it appropriately. (beginner error... my apologies)

I tried using 'InScope' as you suggested and found that at that level it reported all heading at the same 'level'... I had to ask level as I couldn't manage to ask the appropriate InScope question.

I do appreciate your assitance. Thank you.

b

manipulate field value from select statement

Hi all,

any assistance will be much appreciated on this one .... a bit clueless at the mo!

I've been trying to execute the code below in which part of my select statement is a calculated value i.e. Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE

code:

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) & "/" & SUBSTRING([SD],5,2) & "/" & Left([SD],4) AS STARTDATE,
Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE, vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

however this error message keeps coming up at runtime:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ENDDATE'.

My guess is it's happening when I try to get the date difference (DATEDIFF)....

help!!

Try this..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

|||

Sh... should have seen that one.

Cheers mate .. however I'm still having an error from that code:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_spend'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_dates'.

Is there some sort of restriction on selecting from a view in sql server?

|||

Bolugbe wrote:

My guess is it's happening when I try to get the date difference (DATEDIFF)....

You're right, the problem is in the DATEDIFF statement.
You cannot use just assigned aliases in calculations, so you should either copy/paste the formula for getting ENDDATE into DATEDIFF function or use nested select statements|||

Try this one..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), Convert(datetime,Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4))) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]