Error send query results via SQL mail

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Error send query results via SQL mail

Author

Topic

wiltech
Yak Posting Veteran

51 Posts

Posted - 2003-04-10 : 17:41:45

I'm running SQL2000 and Exchange 2000 (SBS2k actually), and I'm having problems sending query results using xp_sendmail.

If I run the following in query analyzer:

EXEC xp_sendmail 'twilhelm', 
     @subject = 'SQL Server Report #1'     

EXEC xp_sendmail 'twilhelm', 
     @query = 'sp_configure', 
     @subject = 'SQL Server Report #2',
     @width = 250

EXEC xp_sendmail 'twilhelm', 
     @query = 'sp_configure', 
     @subject = 'SQL Server Report #3',
     @width = 250,
     @attach_results = 'TRUE'

I get the following messages:

Mail sent.
ODBC error 4604 (42000) There is no such user or group 'sa4'.

ODBC error 4604 (42000) There is no such user or group 'sa4'.

Since the first message is sent I'm assuming that SQLMAIL is set up correctly.

Any thoughts ?

Thanks
Tony W

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-10 : 17:45:38

I have never seen those errors before and I run similar commands all the time. Do you have any idea what sa4 is? Is it a user on the SQL Server?

Tara

wiltech
Yak Posting Veteran

51 Posts

Posted - 2003-04-10 : 17:52:32

quote:

I have never seen those errors before and I run similar commands all the time. Do you have any idea what sa4 is? Is it a user on the SQL Server?

Tara

I have no idea what 'sa4' is. It's not a user or anyhting, the server only has 3 logins, besides SA.

Actually it should be 'sa#' since repeated running of those commands return different numbers.

Tony W

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-11 : 17:21:38

Hmmm, have you tried changing the subject? Try removing # from the subject line, maybe it's causing a problem for xp_sendmail. Other than that though, I have no clue. Let us know.

Tara

wiltech
Yak Posting Veteran

51 Posts

Posted - 2003-04-11 : 17:23:37

quote:

Hmmm, have you tried changing the subject? Try removing # from the subject line, maybe it's causing a problem for xp_sendmail. Other than that though, I have no clue. Let us know.

Tara

Yep, I've tried that, the only times I'm having problems is when I attempt to use the @Query argument.

Any other suggestions ?

Thanks
Tony W

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-11 : 17:27:55

Do any queries work when you pass it to @query? Such as: @query = 'SELECT name FROM sysobjects ORDER BY name'

If other queries work, then @query = 'EXEC sp_configure'

Tara

wiltech
Yak Posting Veteran

51 Posts

Posted - 2003-04-11 : 17:58:34

quote:

Do any queries work when you pass it to @query? Such as: @query = 'SELECT name FROM sysobjects ORDER BY name'

If other queries work, then @query = 'EXEC sp_configure'

Tara

Nope, no other queries work. I actually discovered this trying to write a SQL Agent job, so I tried to simplified it when I posted here.

Thanks
Tony W

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-11 : 18:02:13

Do you have service pack 2 or later for SQL Server installed?

Tara

wiltech
Yak Posting Veteran

51 Posts

Posted - 2003-04-11 : 18:04:51

quote:

Do you have service pack 2 or later for SQL Server installed?

Tara

It's supposed to have SQL SP3 on it. I'll verify this, and if I can on a dll file by file basis.

Tony W

cwightman
Starting Member

6 Posts

Posted - 2003-04-14 : 08:59:26

Does SA have a profile in outlook. If you are using another profile try running the command as that user.

wiltech
Yak Posting Veteran

51 Posts

Posted - 2003-04-14 : 09:04:32

quote:

Does SA have a profile in outlook. If you are using another profile try running the command as that user.

Yes it does. I can send mail, but not the results of a query.

I'd really hate to have to use a cursor to take the results of the query and put them in a variable, so I can send it.

Tony W

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-14 : 12:22:21

You will not have to use a cursor. These queries work fine in my environment.

Tara

wiltech
Yak Posting Veteran

51 Posts

Posted - 2003-04-14 : 12:31:49

quote:

You will not have to use a cursor. These queries work fine in my environment.

Tara

Tara,

The queries, by themselves, work fine in my environment as well, but I just can't email their results using xp_sendmail.

Unless I can come up with a solution to this problem I'll be forced to use a cursor to loop through the query to assign the results to a local variable, then email that variable as the body of the email.

Anyone know what all DLL files and version xp_sendmail uses so I can manually reregister them ?

Thanks
Tony W

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-14 : 12:36:29

Instead of working around the problem, try reinstalling whatever service pack that you are on (hopefully it is sp2 or sp3). If that doesn't work, are you able to call MS for support on this?

Tara

cwightman
Starting Member

6 Posts

Posted - 2003-04-15 : 05:31:43

Tony

We had a similar problem, the way we got around it was to add access rights to the TEMP directory in the Document and Settings folder on the account logged into the Server. As we had the NT Administrator account permanatley logged onto the server. If I tried to run a temp query from xp_sendmail it would fail, but if I used the administraor account it worked okay. The query writes the output file it needs to to the temp folder of which we had no access rights. Once I changed the access rights it worked okay. Did find this answer in one of the many SQL forums on the net. Hope this help you.

wiltech
Yak Posting Veteran

51 Posts

Posted - 2003-04-15 : 10:04:44

quote:

Tony

We had a similar problem, the way we got around it was to add access rights to the TEMP directory in the Document and Settings folder on the account logged into the Server. As we had the NT Administrator account permanatley logged onto the server. If I tried to run a temp query from xp_sendmail it would fail, but if I used the administraor account it worked okay. The query writes the output file it needs to to the temp folder of which we had no access rights. Once I changed the access rights it worked okay. Did find this answer in one of the many SQL forums on the net. Hope this help you.

Well, that user is a member of the "Administrators" group so I shouldn't need it. Anyways, I added that user to the directory security for "C:\winnt\temp", and ran the above, and it worked.

So to humor myself, I removed that user form the directory permissions, ran the above, and it worked.