Database Mail

[andyc209]

I have configured the Database Mail on SQL2005 to work from the MSDB database following some tutorials online. Now how can I get this to work from one of my databases - i want to use a database called MYDATA but when i [USE MYDATA] i get the stored procs are missing. Do i need to transfer some stored procs from MSDB to MYDATA?

 

[RiverGuy]

Why not just call the procedure in msdb? EXEC msdb.dbo.sp_send_dbmail....

 

[andyc209]

sorry bit of a noobie

I want to run a query from MYDATA that gets a list of addresses and details for an overnight mailing, so am i right in thinking I run the query in MYDATA and then for each result call the MSDB and post the data to the DBMail?

 

[RiverGuy]

You can be connected to your database called MYDATA. You just need to qualify the other database name when you reference an object outside of the database which you are connected to. So if you are connected to MYDATA, when you call EXEC msdb.dbo.sp_send_dbmail, the msdb.dbo part is telling SQL Server to execute this stored procedure in msdb, with the dbo schema.

As far as the rest, I really can't say without more information. You expect to get multiple results from your query? You want the query results attached to the email?

 

[andyc209]

sort of - basically i want to send weekly statements to clients on email rather than hand done as they are at the moment. So i as going to run a query in MYDATA that got all the clients account balances and mailed them overnight. So I have the query set to pull back the data for 50 different clinets, for exapmle, and then i want 50 emails going to each with their balances. My problem was how to get the 50 emails going from MYDATA when the DBmail was in the MSDB database. So i was thinking should i run the query and for each result call the MSDB databsea to send the mail, then loop to the next record in the MYDATA query.

 

[RiverGuy]

Here's an example. Run this in a test database

CREATE TABLE Clients (ClientID INT, ClientName VARCHAR(100))
INSERT INTO Clients SELECT 1, 'Client 1'
INSERT INTO Clients SELECT 2, 'Client 2'
INSERT INTO Clients SELECT 3, 'Client 3'
GO

CREATE TABLE Statements (ClientID INT, Amount DECIMAL(18,2))
INSERT INTO Statements SELECT 1, 100
INSERT INTO Statements SELECT 2, 200
INSERT INTO Statements SELECT 3, 300
GO

DECLARE @ClientID INT, @ClientName VARCHAR(100)

DECLARE ClientCursor CURSOR
FOR SELECT * FROM Clients
OPEN ClientCursor

FETCH NEXT FROM ClientCursor INTO @ClientID, @ClientName
WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @sql VARCHAR(200)
	SELECT @sql = 'SELECT * FROM YourTestDatabase.dbo.Statements WHERE ClientID =' + CONVERT(VARCHAR(10), @ClientID)
	DECLARE @sub VARCHAR(100)
	SELECT @sub = 'Statement for ' + @ClientName 
	EXEC msdb.dbo.sp_send_dbmail 
	@profile_name = 'YourProfileName',
	@subject = @sub,
	@recipients = 'YourEmailAddress',
	@query = @sql
	
	FETCH NEXT FROM ClientCursor INTO @ClientID, @ClientName
END

CLOSE ClientCursor
DEALLOCATE ClientCursor



	

DROP TABLE Clients
GO

DROP TABLE Statements
GO

 

[andyc209]

RIVERGUY - thanks for this, will give it a go. Thanks for your help