Help with xp_sendmail needed!

Hello,
I am using xp_sendmail SP to send emails to myself, not to a distribution li
st. I receive the error: Server: Msg 18031, Level 16, State 1, Line 0
xp_sendmail: Could not resolve recipient
The server is a MS Windows 2003 running SQL 2000 and Office XP SP3. The OS,
SQL and Office are fully updated. I do not think that this problem is relate
d to the bug described in Article ID:315666.
May I ask you for some help?
Thank you,
Ovidiu TanjalaHi Ovidiu,
Are you able to execute xp_stopmail and xp_startmail successfully? Also
please post your xp_sendmail statement, which resulted in this error.
Thanks
Yogish

Help with xp_sendmail needed!

Hello,
I am using xp_sendmail SP to send emails to myself, not to a distribution list. I receive the error: Server: Msg 18031, Level 16, State 1, Line 0
xp_sendmail: Could not resolve recipient
The server is a MS Windows 2003 running SQL 2000 and Office XP SP3. The OS, SQL and Office are fully updated. I do not think that this problem is related to the bug described in Article ID:315666.
May I ask you for some help?
Thank you,
Ovidiu Tanjala
Hi Ovidiu,
Are you able to execute xp_stopmail and xp_startmail successfully? Also
please post your xp_sendmail statement, which resulted in this error.
Thanks
Yogish

Help with xp..sendmail needed!

Hi,
I keep getting the following error in my SP which I can't seem to resolve:-
Server: Msg 18031, Level 16, State 1, Line 0
xp_sendmail: Could not resolve recipient
If anyone has any ideas why this is happening it would be greatly
appreciated. My SP is below for reference.
DECLARE @.REG_NO as varchar(10)
DECLARE @.URN as varchar(10)
DECLARE @.OFFICER as varchar(10)
DECLARE @.RENEWAL_DATE as varchar(10)
DECLARE @.OFFICER_EMAIL as varchar(15)
DECLARE @.sbj as varchar(50)
DECLARE @.msg as varchar(50)
DECLARE @.recipient as varchar(50)
DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
SELECT
REG_NO = s.s_id,
URN = d.sd_urn,
OFFICER = d.sd_reviewing_officer,
RENEWAL_DATE = CONVERT(VARCHAR,d.sd_renewal_date, 103),
OFFICER_EMAIL = e.e_officer_email
FROM
dbo.tbl_surveillance s
INNER JOIN
dbo.tbl_surveillance_dates d
ON
s.s_id = d.sd_s_id
LEFT OUTER JOIN
dbo.tbl_email e
ON
d.sd_e_id = e.e_id
WHERE
d.sd_renewal_date < GETDATE()+7;
OPEN surveillance_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables should be in the same order as the columns in the
--SELECT statement.
FETCH NEXT FROM surveillance_cursor
INTO @.REG_NO, @.URN, @.OFFICER, @.RENEWAL_DATE, @.OFFICER_EMAIL
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.recipient = @.OFFICER_EMAIL
select @.sbj = 'List of Renewal Dates'
select @.msg = @.REG_NO + ', ' + @.URN + ', ' + @.OFFICER + ',' + @.RENEWAL_DATE
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM surveillance_cursor
INTO @.REG_NO, @.URN, @.OFFICER, @.RENEWAL_DATE, @.OFFICER_EMAIL
END
CLOSE surveillance_cursor
DEALLOCATE surveillance_cursor
GO
Really appreciate any help on this.
Thanks
DamonDoes xp_sendmail work if you call it manually (e.g. not populating the
elements with variables)? If you can't get xp_sendmail to work at all, you
might save time by reading http://www.aspfaq.com/2403 and using an
alternative. If you are using SQL Server 2005, you should consider database
mail instead (sorry, the article hasn't been updated for that).
Did you try wrapping the call in
IF @.OFFICER_EMAIL IS NOT NULL
BEGIN
..
END
Or better yet, have an e-mail format checking function,
IF dbo.IsEmail(@.OFFICER_EMAIL)
BEGIN
..
END
xp_sendmail also seems to be fussy about trailing spaces, and while they
shouldn't be there because it is varchar, you could try:
SET @.OFFICER_EMAIL = LTRIM(RTRIM(@.OFFICER_EMAIL))
Finally, if @.OFFICER_EMAIL is a distribution list, see
http://support.microsoft.com/kb/315666
"Damon" <nonsense@.nononsense.com> wrote in message
news:2oQsf.18319$mn1.4396@.newsfe7-win.ntli.net...
> Hi,
> I keep getting the following error in my SP which I can't seem to
> resolve:-
> Server: Msg 18031, Level 16, State 1, Line 0
> xp_sendmail: Could not resolve recipient
> If anyone has any ideas why this is happening it would be greatly
> appreciated. My SP is below for reference.
> DECLARE @.REG_NO as varchar(10)
> DECLARE @.URN as varchar(10)
> DECLARE @.OFFICER as varchar(10)
> DECLARE @.RENEWAL_DATE as varchar(10)
> DECLARE @.OFFICER_EMAIL as varchar(15)
> DECLARE @.sbj as varchar(50)
> DECLARE @.msg as varchar(50)
> DECLARE @.recipient as varchar(50)
> DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
> SELECT
> REG_NO = s.s_id,
> URN = d.sd_urn,
> OFFICER = d.sd_reviewing_officer,
> RENEWAL_DATE = CONVERT(VARCHAR,d.sd_renewal_date, 103),
> OFFICER_EMAIL = e.e_officer_email
> FROM
> dbo.tbl_surveillance s
> INNER JOIN
> dbo.tbl_surveillance_dates d
> ON
> s.s_id = d.sd_s_id
> LEFT OUTER JOIN
> dbo.tbl_email e
> ON
> d.sd_e_id = e.e_id
> WHERE
> d.sd_renewal_date < GETDATE()+7;
> OPEN surveillance_cursor
> -- Perform the first fetch and store the values in variables.
> -- Note: The variables should be in the same order as the columns in the
> --SELECT statement.
> FETCH NEXT FROM surveillance_cursor
> INTO @.REG_NO, @.URN, @.OFFICER, @.RENEWAL_DATE, @.OFFICER_EMAIL
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.recipient = @.OFFICER_EMAIL
> select @.sbj = 'List of Renewal Dates'
> select @.msg = @.REG_NO + ', ' + @.URN + ', ' + @.OFFICER + ',' +
> @.RENEWAL_DATE
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM surveillance_cursor
> INTO @.REG_NO, @.URN, @.OFFICER, @.RENEWAL_DATE, @.OFFICER_EMAIL
> END
> CLOSE surveillance_cursor
> DEALLOCATE surveillance_cursor
> GO
>
>
> Really appreciate any help on this.
> Thanks
> Damon
>|||hi,
Thank you very much for your response. I have tried xp_sendmail manually by
typing it into Query Analyzer and it works fine. It doesn't seem to like it
in my SP.
I have also added the IF @.OFFICER_EMAIL IS NOT NULL and SET @.OFFICER_EMAIL =
LTRIM(RTRIM(@.OFFICER_EMAIL)) into my Stored Procedure but I still get the
same error.
Any more advice is greatly appreciated.
Thanks
Damon
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e2vgJxHDGHA.528@.TK2MSFTNGP09.phx.gbl...
> Does xp_sendmail work if you call it manually (e.g. not populating the
> elements with variables)? If you can't get xp_sendmail to work at all,
> you might save time by reading http://www.aspfaq.com/2403 and using an
> alternative. If you are using SQL Server 2005, you should consider
> database mail instead (sorry, the article hasn't been updated for that).
> Did you try wrapping the call in
> IF @.OFFICER_EMAIL IS NOT NULL
> BEGIN
> ...
> END
> Or better yet, have an e-mail format checking function,
> IF dbo.IsEmail(@.OFFICER_EMAIL)
> BEGIN
> ...
> END
> xp_sendmail also seems to be fussy about trailing spaces, and while they
> shouldn't be there because it is varchar, you could try:
> SET @.OFFICER_EMAIL = LTRIM(RTRIM(@.OFFICER_EMAIL))
> Finally, if @.OFFICER_EMAIL is a distribution list, see
> http://support.microsoft.com/kb/315666
>
>
> "Damon" <nonsense@.nononsense.com> wrote in message
> news:2oQsf.18319$mn1.4396@.newsfe7-win.ntli.net...
>|||> Any more advice is greatly appreciated.

> DECLARE @.OFFICER_EMAIL as varchar(15)
Well, is it remotely possible that any of your e-mail addresses are longer
than 15 characters? What is the table structure of tbl_email? What is
the datatype of e_officer_email?|||Do you know what; I think I just made a complete rookie mistake. It was the
DECLARE @.OFFICER_EMAIL as varchar(15). It should have been 30.
I cannot thank you enough.
Damon
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eFP6phIDGHA.1312@.TK2MSFTNGP09.phx.gbl...
>
> Well, is it remotely possible that any of your e-mail addresses are longer
> than 15 characters? What is the table structure of tbl_email? What is
> the datatype of e_officer_email?
>|||> Do you know what; I think I just made a complete rookie mistake. It was
> the DECLARE @.OFFICER_EMAIL as varchar(15). It should have been 30.
Our systems use VARCHAR(128). Even I have e-mail addresses that are > 30
characters, and I don't have an abnormally long name or domain names...|||Good idea, I think I will chane it to that.
Thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXyqv1IDGHA.208@.TK2MSFTNGP10.phx.gbl...
> Our systems use VARCHAR(128). Even I have e-mail addresses that are > 30
> characters, and I don't have an abnormally long name or domain names...
>

Help with user accounts

Is there a way to filter at the server level what data is returned to
clients by user? I know that I can create a user account and assign what
columns/tables the user account has access to, but what if I want them to
have full access to a table, but only certain records in that table. Any
ideas?To add more clarification:
Lets assume I have the following tables:
CUSTOMER:
ID CUSTOMER_NAME COMPANY
========================================
=============================
1 Customer 1 1
2 Customer 2 1
3 Customer 3 1
4 Customer 4 2
5 Customer 5 2
6 Customer 6 2
7 Customer 7 1
8 Customer 8 2
9 Customer 9 2
10 Customer 10 2
USERS:
ID USER_NAME
=======================================
1 User 1
2 User 2
3 User 3
4 User 4
COMPANY:
ID COMPANY_NAME
========================================
==
1 Company 1
2 Company 2
SECURITY:
USERID COMPANY_ID
========================================
1 1
1 2
2 1
3 2
4 1
4 2
Ok the application would know that if User 4 is pulling a list from the
CUSTOMER table to show the customers for both companies. Likewise if User 2
preformed the same action they should only see customers from Company 1.
My question is how to I get this same functionality from say Query Analyzer?
"John Harbison" <JohnH@.desertmicro.net> wrote in message
news:eZH9VUcwEHA.3528@.tk2msftngp13.phx.gbl...
> Is there a way to filter at the server level what data is returned to
> clients by user? I know that I can create a user account and assign what
> columns/tables the user account has access to, but what if I want them to
> have full access to a table, but only certain records in that table. Any
> ideas?
>|||Check
http://vyaskn.tripod.com/ row_level...s
es.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"John Harbison" <JohnH@.desertmicro.net> wrote in message
news:%231TwA$cwEHA.2908@.tk2msftngp13.phx.gbl...
> To add more clarification:
> Lets assume I have the following tables:
> CUSTOMER:
> ID CUSTOMER_NAME COMPANY
> ========================================
=============================
> 1 Customer 1 1
> 2 Customer 2 1
> 3 Customer 3 1
> 4 Customer 4 2
> 5 Customer 5 2
> 6 Customer 6 2
> 7 Customer 7 1
> 8 Customer 8 2
> 9 Customer 9 2
> 10 Customer 10 2
>
> USERS:
> ID USER_NAME
> =======================================
> 1 User 1
> 2 User 2
> 3 User 3
> 4 User 4
>
> COMPANY:
> ID COMPANY_NAME
> ========================================
==
> 1 Company 1
> 2 Company 2
>
> SECURITY:
> USERID COMPANY_ID
> ========================================
> 1 1
> 1 2
> 2 1
> 3 2
> 4 1
> 4 2
>
> Ok the application would know that if User 4 is pulling a list from the
> CUSTOMER table to show the customers for both companies. Likewise if User
2
> preformed the same action they should only see customers from Company 1.
> My question is how to I get this same functionality from say Query
Analyzer?
>
> "John Harbison" <JohnH@.desertmicro.net> wrote in message
> news:eZH9VUcwEHA.3528@.tk2msftngp13.phx.gbl...
what[vbcol=seagreen]
to[vbcol=seagreen]
Any[vbcol=seagreen]
>