Can't e-mail in SQL 7

[DougP]

I created a DTS package that sends and e-mail just fine.
But I would like it to send the results too in the message.
So I found this T-SQL code but I get an error.

Server: Msg17966, Level 16, State 1, Line 1
SQL Mail session not started

Any Ideas??

TIA
DougP, MCP

 

[dbtech]

Is the SQL Mail started? you can check this under
support services when u expand the server list using MMC.

To send emails with SQL Server check the links below.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q263556

http://www.mssqlserver.com/articles/sqlmail_p1.asp

Nice collection of links, u need to register to the site
before you can access the URL

http://www.sqlservercentral.com/columnists/sjones/sqlmailresources.asp

dbtech

 

[tlbroadbent]

What is the code you are using? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[DougP]

here is the code.
EXEC xp_startmail @user = 'Administrator', @password = 'rfrlhtn4s'
EXEC xp_sendmail @recipients = 'douglasp'

@query = 'SELECT FROM INFORMATION_SCHEMA.TABLES',
@subject = 'SQL Server Report',
@message = 'The contents of INFORMATION_SCHEMA.TABLES:',
@attach_results = 'TRUE', @width = 250

But I was able to get the Start mail procedure to run based on dbtech's info.
I have another error though on line 4.

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@query'.

Any ideas on that??
It is a sample I copied out of Book-On-Line

DougP, MCP

 

[tlbroadbent]

Add a comma after the recipient name.

EXEC xp_sendmail @recipients = 'douglasp', Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[DougP]

Fantastic !!!!!
There was a comma there and I deleted it messing around with trying to get the Mail going.

Now instead of getting and attachment can I just have the information in the message.
All I need to see is the record count
I also need to see it for 2 tables one is [Copy-PartMaster Lite] the other is [Copy-PartMaster Lite-OLD]

This is my real code:

EXEC xp_startmail @user = 'Administrator', @password = 'rfrlhtn4s'
EXEC xp_sendmail @recipients = 'douglasp',

@query = 'SELECT Count(*) From [Copy-PartMaster Lite]',
@subject = 'SQL Server Report',
@message = 'Record count for table [Copy-PartMaster Lite]',
@attach_results = 'TRUE', @width = 250
DougP, MCP

 

[tlbroadbent]

Change @attach_results to 'False' and the query results will appear in the message body. Change the query as follows to return two rows.


@query =
'SELECT TblName=''Copy-PartMaster Lite'',
RowCnt=Count(*)
From [Copy-PartMaster Lite] Union All
SELECT TblName=''Copy-PartMaster Lite-OLD'',
RowCnt=Count(*)
From [Copy-PartMaster Lite-OLD]',
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[DougP]

Getting this error now

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ' '.
-------------- here is my code -----------------
EXEC xp_startmail @user = 'Administrator', @password = 'rfrlhtn4s'
EXEC xp_sendmail @recipients = 'douglasp',

@query =
'SELECT TblName=''Copy-PartMaster Lite'',
RowCnt=Count(*)
From [Copy-PartMaster Lite] Union All
SELECT TblName=''Copy-PartMaster Lite-OLD'',
RowCnt=Count(*)
From [Copy-PartMaster Lite-OLD]',
@subject = 'SQL Server Report',
@message = 'The contents of INFORMATION_SCHEMA.TABLES:',
@attach_results = 'FALSE', @width = 250

DougP, MCP

 

[tlbroadbent]

I thought about this when posting the last answer but opted to be lazy. Declare a variable for the query statement. Load the variable and pass the variable contents to xp_sendmail.

Declare @sql varchar(1000)
Select @sql=
'SELECT TblName=''Copy-PartMaster Lite'','+
'RowCnt=Count(*) ' +
'From [Copy-PartMaster Lite] Union All ' +
'SELECT TblName=''Copy-PartMaster Lite-OLD'','+
'RowCnt=Count(*) ' +
'From [Copy-PartMaster Lite-OLD]'

EXEC xp_sendmail @recipients = 'douglasp',
@query =@sql
@subject = 'SQL Server Report',
@message = 'The contents of INFORMATION_SCHEMA.TABLES:',
@attach_results = 'FALSE', @width = 250
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[DougP]

Nope
Same error at the same place

Cose as follows ---------------
EXEC xp_startmail @user = 'Administrator', @password = 'rfrlhtn4s'
Declare @sql varchar(1000)
Select @sql= 'SELECT TblName=''Copy-PartMaster Lite'' RowCnt=Count(*) From [Copy-PartMaster Lite] Union All SELECT TblName=''Copy-PartMaster Lite-OLD'' RowCnt=Count(*) From [Copy-PartMaster Lite-OLD] ,
EXEC xp_sendmail @recipients = 'douglasp',
@query =@sql
@subject = 'SQL Server Report',
@message = 'The contents of INFORMATION_SCHEMA.TABLES:',
@attach_results = 'FALSE', @width = 250

------------ Error message ---------------------
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ' '.
Server: Msg 105, Level 15, State 1, Line 8
Unclosed quotation mark before the character string ', @width = 250


DougP, MCP

 

[tlbroadbent]

You removed needed commas and replaced an ending quote with a comma. The statement should be...

Select @sql= 'SELECT TblName=''Copy-PartMaster Lite'', RowCnt=Count(*) From [Copy-PartMaster Lite] Union All SELECT TblName=''Copy-PartMaster Lite-OLD'', RowCnt=Count(*) From [Copy-PartMaster Lite-OLD]' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[sunila7]

Hi,

One more thing. sorry for jumping in late.
there should be a comma after @sql

EXEC xp_sendmail @recipients = 'douglasp',
@query =@sql,
@subject = 'SQL Server Report',
@message = 'The contents of INFORMATION_SCHEMA.TABLES:',
@attach_results = 'FALSE', @width = 250

Sunil

 

[DougP]

It does not like anything I do on that line.
After some exploration in Books-on-line I see that they refer to a stored procedure to run any thing other than the Schema stuff listed in the Original paost I had up top.

Like so
EXEC xp_sendmail 'robertk', @query = 'sp_configure'

So now I have to finagle a SP somehow

DougP, MCP

 

[DougP]

Well here it is
Don't know How I managed to figure it out but....

Use Master
EXEC xp_startmail @user = 'Administrator', @password = 'rfrlhtn4s'
EXEC xp_sendmail @recipients = 'douglasp', @query = 'USE Universal EXEC sp_CountIt'

The Stored Procedure sp_CountIt consists of

CREATE PROCEDURE [sp_CountIt] AS
Select Count(*) From [Copy-Partmaster Lite]

DougP, MCP

 

[tlbroadbent]

Good catch, Sunil.

Doug,

You don't need to create a stored proc. We send results of queries via xp_sendmail several times each day. You just need to get the syntax corrected.

Here is an actual xp_sendmail statement extracted from a SP that we run weekly via a job. @query1 contains a select statement that is built earlier in the SP.

exec master.dbo.xp_sendmail
@recipients = @Sendto1,
@query = @Query1,
@subject = 'Hours Report',
@message = 'Employees with less than 40 hours',
@attach_results='TRUE',
@width =250,
@copy_recipients= @Sendto2 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[DougP]

Ok, now can I see the Select statement from above.
also does this e-mail an attachemnt, or just the results in the message DougP, MCP

 

[tlbroadbent]

@attach_results='TRUE' says to send an attachment. Changing 'TRUE' to 'FALSE' changes the behavior as noted earlier. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[DougP]

I'm using SQL Server 7.0, does that make a difference? DougP, MCP

 

[tlbroadbent]

Most of the scripts I run were developed in SQL 7. The basic syntax is the same in SQL 2000 as it was in SQL 7. I don't believe that is the problem.

Do you want to mail the SP to me and perhaps I can analyze it offline? Use the HOTMAIL address below. Include TEK-TIPS in the subject line or my filters will toss your message.

tlbroadbentREMOVE@hotmail.com Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.