Problem with xp_sendmail and @query parameter

I am having a bit of trouble with a new SQLServer 2000 installation. I am
in the process of swapping out a server (sql sp3) and replacing it with a
new server (sql sp3a). Everything went smoothly until I hit a brick wall
with xp_sendmail.
The problem is when xp_sendmail tries to attach a query (with the @.query
parameter), and the SQL for the query runs on a linked server. Under the
old server, this was never a problem. But now I receive the following error
message:
ODBC error 7410 (42000) Remote access not allowed for Windows NT user
activated by SETUSER.
Also, here is the actual sql stmt which is running:
exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
test',@.subject='Test 1', @.attach_results=true,@.query='select * from
server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
The links to the other server are identical on both the old and new
machines. Any assistance would be greatly appreciated.
Thanks,
BobMost probably the job is owned by someone who isn't sysadmin, and some stuff
aren't allowed to do when the jobs owner isn't sysadmin (stuff at the server
level, as Agent uses SETUSER to emulate the jobowner's user context *in that
particular database*).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bob R." <bobr2@.earthlink.net> wrote in message
news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> I am having a bit of trouble with a new SQLServer 2000 installation. I am
> in the process of swapping out a server (sql sp3) and replacing it with a
> new server (sql sp3a). Everything went smoothly until I hit a brick wall
> with xp_sendmail.
> The problem is when xp_sendmail tries to attach a query (with the @.query
> parameter), and the SQL for the query runs on a linked server. Under the
> old server, this was never a problem. But now I receive the following
error
> message:
> ODBC error 7410 (42000) Remote access not allowed for Windows NT user
> activated by SETUSER.
> Also, here is the actual sql stmt which is running:
> exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> The links to the other server are identical on both the old and new
> machines. Any assistance would be greatly appreciated.
> Thanks,
> Bob
>|||Thanks, but this is not a job. The problem happens in Query Analyzer. If I
run the same SQL Stmt against the old server in Query Analyzer, it works
fine.
-Bob
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eg$aOOVrDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Most probably the job is owned by someone who isn't sysadmin, and some
stuff
> aren't allowed to do when the jobs owner isn't sysadmin (stuff at the
server
> level, as Agent uses SETUSER to emulate the jobowner's user context *in
that
> particular database*).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Bob R." <bobr2@.earthlink.net> wrote in message
> news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > I am having a bit of trouble with a new SQLServer 2000 installation. I
am
> > in the process of swapping out a server (sql sp3) and replacing it with
a
> > new server (sql sp3a). Everything went smoothly until I hit a brick
wall
> > with xp_sendmail.
> >
> > The problem is when xp_sendmail tries to attach a query (with the @.query
> > parameter), and the SQL for the query runs on a linked server. Under
the
> > old server, this was never a problem. But now I receive the following
> error
> > message:
> >
> > ODBC error 7410 (42000) Remote access not allowed for Windows NT user
> > activated by SETUSER.
> >
> > Also, here is the actual sql stmt which is running:
> >
> > exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> > test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> > server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> >
> > The links to the other server are identical on both the old and new
> > machines. Any assistance would be greatly appreciated.
> >
> > Thanks,
> >
> > Bob
> >
> >
>|||Perhaps one is executed to text and the other to grid. Grid is faster, and possibly also the text
handling can have been improved in some sp of QA (not sure about this, though). Also, does SET
NOCOUNT ON help.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bob R." <bobr2@.earthlink.net> wrote in message news:uL3%23d3drDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Thanks, but this is not a job. The problem happens in Query Analyzer. If I
> run the same SQL Stmt against the old server in Query Analyzer, it works
> fine.
> -Bob
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eg$aOOVrDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > Most probably the job is owned by someone who isn't sysadmin, and some
> stuff
> > aren't allowed to do when the jobs owner isn't sysadmin (stuff at the
> server
> > level, as Agent uses SETUSER to emulate the jobowner's user context *in
> that
> > particular database*).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Bob R." <bobr2@.earthlink.net> wrote in message
> > news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > > I am having a bit of trouble with a new SQLServer 2000 installation. I
> am
> > > in the process of swapping out a server (sql sp3) and replacing it with
> a
> > > new server (sql sp3a). Everything went smoothly until I hit a brick
> wall
> > > with xp_sendmail.
> > >
> > > The problem is when xp_sendmail tries to attach a query (with the @.query
> > > parameter), and the SQL for the query runs on a linked server. Under
> the
> > > old server, this was never a problem. But now I receive the following
> > error
> > > message:
> > >
> > > ODBC error 7410 (42000) Remote access not allowed for Windows NT user
> > > activated by SETUSER.
> > >
> > > Also, here is the actual sql stmt which is running:
> > >
> > > exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> > > test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> > > server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> > >
> > > The links to the other server are identical on both the old and new
> > > machines. Any assistance would be greatly appreciated.
> > >
> > > Thanks,
> > >
> > > Bob
> > >
> > >
> >
> >
>|||I ran a trace on both versions, and I can see that the new version (sp3a) is
running "sp_setuserbylogid" before executing the query. The older machine
is on sp3, and does not do this. I looked at the dll for xp_sendmail
between both machines, and it looks like sp3a updated sqlmap70.dll. (This
is not mentioned anywhere in the release notes) I swapped out the dll for
the one from sp3, and everything works fine. However, since I could find no
documentation concerning changes to xp_sendmail in sp3a, I am not sure if
this is a good idea.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%236bGUserDHA.372@.TK2MSFTNGP11.phx.gbl...
> Perhaps one is executed to text and the other to grid. Grid is faster, and
possibly also the text
> handling can have been improved in some sp of QA (not sure about this,
though). Also, does SET
> NOCOUNT ON help.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Bob R." <bobr2@.earthlink.net> wrote in message
news:uL3%23d3drDHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Thanks, but this is not a job. The problem happens in Query Analyzer.
If I
> > run the same SQL Stmt against the old server in Query Analyzer, it works
> > fine.
> >
> > -Bob
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:eg$aOOVrDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > > Most probably the job is owned by someone who isn't sysadmin, and some
> > stuff
> > > aren't allowed to do when the jobs owner isn't sysadmin (stuff at the
> > server
> > > level, as Agent uses SETUSER to emulate the jobowner's user context
*in
> > that
> > > particular database*).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Bob R." <bobr2@.earthlink.net> wrote in message
> > > news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > > > I am having a bit of trouble with a new SQLServer 2000 installation.
I
> > am
> > > > in the process of swapping out a server (sql sp3) and replacing it
with
> > a
> > > > new server (sql sp3a). Everything went smoothly until I hit a brick
> > wall
> > > > with xp_sendmail.
> > > >
> > > > The problem is when xp_sendmail tries to attach a query (with the
@.query
> > > > parameter), and the SQL for the query runs on a linked server.
Under
> > the
> > > > old server, this was never a problem. But now I receive the
following
> > > error
> > > > message:
> > > >
> > > > ODBC error 7410 (42000) Remote access not allowed for Windows NT
user
> > > > activated by SETUSER.
> > > >
> > > > Also, here is the actual sql stmt which is running:
> > > >
> > > > exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> > > > test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> > > > server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> > > >
> > > > The links to the other server are identical on both the old and new
> > > > machines. Any assistance would be greatly appreciated.
> > > >
> > > > Thanks,
> > > >
> > > > Bob
> > > >
> > > >
> > >
> > >
> >
> >
>

problem with xp_sendmail

Hi
I had the xp_sendmail working the other day, but when I now try to send an
email using the xp_sendmail proc, I get the following error message:
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
What does that mean? I can't even see that there's a @.user parameter to set
for the xp_sendmail proc. Is it just me that are missing something here?
Regards
Steen
Hi
You may want to check the mail profiles, by logging in as the service
account and making sure it exists and is working.
John
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ux9r1XCZEHA.2432@.tk2msftngp13.phx.gbl...
> Hi
> I had the xp_sendmail working the other day, but when I now try to send an
> email using the xp_sendmail proc, I get the following error message:
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> What does that mean? I can't even see that there's a @.user parameter to
set
> for the xp_sendmail proc. Is it just me that are missing something here?
> Regards
> Steen
>
|||arghhh...stupid me... The account that runs the SQLServer service needs to
be the one that has the mailbox setup for it to work from TSql. In my case
it was still the local system account on some servers but the SQLServer
Agent service is all set to the mailbox user - that was why it worked when
testing an operator setup but not when running the xp_sendmail.
Steen
"John Bell" <jbellnewsposts@.hotmail.com> skrev i en meddelelse
news:%23ofKcfCZEHA.1152@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi
> You may want to check the mail profiles, by logging in as the service
> account and making sure it exists and is working.
> John
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ux9r1XCZEHA.2432@.tk2msftngp13.phx.gbl...
an
> set
>

problem with xp_sendmail

Hello,
I have the following problem.
I use the xp_sendmail often in the SQL query analyser with the folowing
script:
USE MASTER
EXEC xp_sendmail
@.recipients = 'hans.de.korte@.verder.nl',
@.subject = 'Test mail',
@.message = 'Testmail1'
<<
This worked OK for months.
After rebooting the SQL server I get the following error:
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80040111
<<
I checked the following issues in the enterprise manager:
1. Support services - SQL Mail > With Profilename 'xyz' After clicking
the Test-Button I get the message: 'Succesfully started and stopped a
MAPI session with this profile'
2. Management - SQL server agent > Working OK (same test and message as
described under '1. Support services '.
3. Management - SQL server agent - Operater 'Hans', E-mail name
'hans.de.korte@.verder.nl' then Test-button, final message: 'The message
was sent successfully' (I get the e-mail at my adress).
How can I solve this problem?
*** Sent via Developersdex http://www.examnotes.net ***Hans
Have you checked these things. Most often xp_mail problems are not following
the steps in an order. As you told it ran for many months, most probably see
that the agent service and sql service *start* with the same domain
account(not local account)
Further Read this article
http://support.microsoft.com/kb/q299620/
Regards
R.D
"Hans" wrote:

> Hello,
> I have the following problem.
> I use the xp_sendmail often in the SQL query analyser with the folowing
> script:
> USE MASTER
> EXEC xp_sendmail
> @.recipients = 'hans.de.korte@.verder.nl',
> @.subject = 'Test mail',
> @.message = 'Testmail1'
> <<
> This worked OK for months.
> After rebooting the SQL server I get the following error:
> Server: Msg 18025, Level 16, State 1, Line 0
> xp_sendmail: failed with mail error 0x80040111
> <<
> I checked the following issues in the enterprise manager:
> 1. Support services - SQL Mail > With Profilename 'xyz' After clicking
> the Test-Button I get the message: 'Succesfully started and stopped a
> MAPI session with this profile'
> 2. Management - SQL server agent > Working OK (same test and message as
> described under '1. Support services '.
> 3. Management - SQL server agent - Operater 'Hans', E-mail name
> 'hans.de.korte@.verder.nl' then Test-button, final message: 'The message
> was sent successfully' (I get the e-mail at my adress).
> How can I solve this problem?
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

problem with xp_sendmail

Hi
I had the xp_sendmail working the other day, but when I now try to send an
email using the xp_sendmail proc, I get the following error message:
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
What does that mean? I can't even see that there's a @.user parameter to set
for the xp_sendmail proc. Is it just me that are missing something here?
Regards
SteenHi
You may want to check the mail profiles, by logging in as the service
account and making sure it exists and is working.
John
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ux9r1XCZEHA.2432@.tk2msftngp13.phx.gbl...
> Hi
> I had the xp_sendmail working the other day, but when I now try to send an
> email using the xp_sendmail proc, I get the following error message:
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> What does that mean? I can't even see that there's a @.user parameter to
set
> for the xp_sendmail proc. Is it just me that are missing something here?
> Regards
> Steen
>|||arghhh...stupid me... The account that runs the SQLServer service needs to
be the one that has the mailbox setup for it to work from TSql. In my case
it was still the local system account on some servers but the SQLServer
Agent service is all set to the mailbox user - that was why it worked when
testing an operator setup but not when running the xp_sendmail.
Steen
"John Bell" <jbellnewsposts@.hotmail.com> skrev i en meddelelse
news:%23ofKcfCZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Hi
> You may want to check the mail profiles, by logging in as the service
> account and making sure it exists and is working.
> John
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ux9r1XCZEHA.2432@.tk2msftngp13.phx.gbl...
> > Hi
> >
> > I had the xp_sendmail working the other day, but when I now try to send
an
> > email using the xp_sendmail proc, I get the following error message:
> >
> > xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> >
> > What does that mean? I can't even see that there's a @.user parameter to
> set
> > for the xp_sendmail proc. Is it just me that are missing something here?
> >
> > Regards
> > Steen
> >
> >
>

problem with xp_sendmail

Hi
I had the xp_sendmail working the other day, but when I now try to send an
email using the xp_sendmail proc, I get the following error message:
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
What does that mean? I can't even see that there's a @.user parameter to set
for the xp_sendmail proc. Is it just me that are missing something here?
Regards
SteenHi
You may want to check the mail profiles, by logging in as the service
account and making sure it exists and is working.
John
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ux9r1XCZEHA.2432@.tk2msftngp13.phx.gbl...
> Hi
> I had the xp_sendmail working the other day, but when I now try to send an
> email using the xp_sendmail proc, I get the following error message:
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> What does that mean? I can't even see that there's a @.user parameter to
set
> for the xp_sendmail proc. Is it just me that are missing something here?
> Regards
> Steen
>|||arghhh...stupid me... The account that runs the SQLServer service needs to
be the one that has the mailbox setup for it to work from TSql. In my case
it was still the local system account on some servers but the SQLServer
Agent service is all set to the mailbox user - that was why it worked when
testing an operator setup but not when running the xp_sendmail.
Steen
"John Bell" <jbellnewsposts@.hotmail.com> skrev i en meddelelse
news:%23ofKcfCZEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Hi
> You may want to check the mail profiles, by logging in as the service
> account and making sure it exists and is working.
> John
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ux9r1XCZEHA.2432@.tk2msftngp13.phx.gbl...
an[vbcol=seagreen]
> set
>