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
> > > >
> > > >
> > >
> > >
> >
> >
>