June 5, 2009 at 2:38 pm
I am hoping my experts at SQL ServerCentral.com can help me out.
I am writing a stored proc that will used to run a daily job send via sp_send_dbmail.
I am basically quering on views to a temp table where I will used the temp table as a loop. However, I cannot pass values to the EXEC sp_send_dbmail in the msdb DB. I have tried a synonym...
Below is the T-SQL
-- declare a temp table to be looped through
DECLARE @tblTempResults TABLE
(
RowID BIGINT IDENTITY(1,1),--- Identity in temp table to get 1, 2, 3 etc.
LastName VARCHAR(50),
FirstName VARCHAR(50),
Email VARCHAR(100)
)
-- INSERT all data into @tblTempResults to email message to let the end user know they have
-- metrics due
INSERT INTO @tblTempResults
--SELECT Distint to not duplicate email 1 email per user even if user has move than 1 metric
SELECT DISTINCT dbo.tblUsers.LastName, dbo.tblUsers.FirstName, dbo.tblUsers.Email
FROM dbo.tblUsers INNER JOIN
dbo.tblUserRoles_xref ON dbo.tblUsers.NBID = dbo.tblUserRoles_xref.NBID INNER JOIN
dbo.vwMetricInstanceDueDates ON dbo.tblUserRoles_xref.Identifier = dbo.vwMetricInstanceDueDates.MetricInstanceID
WHERE (dbo.tblUserRoles_xref.RoleID = 6) AND (dbo.vwMetricInstanceDueDates.DaysUntilDueDate = 3)
-- Used to ensure the correct data is being returned can be commented out later
--SELECT * FROM @tblTempResults
-- declare two variables to loop through each record
DECLARE @varMaxCount BIGINT
DECLARE @varCurrentCount BIGINT
SELECT @varMaxCount = max(RowID) -- Populate @varMax Count variable
FROM @tblTempResults
SET @varCurrentCount = 1 -- Set start varCurrentCount variable
-- Start the loop based on above two variables
WHILE(@varCurrentCount <= @varMaxCount)
BEGIN
DECLARE @varCurrentEmpID BIGINT
DECLARE @varCurrentFirstName VARCHAR(50)
DECLARE @varCurrentLastName VARCHAR(50)
DECLARE @varCurrentEmail VARCHAR(50)
-- GET THE RESULT FOR EACH RECORD BASED ON CURRENT ROW ID
SELECT @varCurrentFirstName = FirstName, @varCurrentLastName = LastName, @varCurrentEmail = Email FROM @tblTempResults WHERE RowID = @varCurrentCount
--SELECT @varCurrentFirstName
SELECT Email FROM @tblTempResults WHERE RowID = @varCurrentCount
--DECLARE @varCurrentEmail VARCHAR(50)
SET @varCurrentEmail = 'robert.dannelly@bankofamerica.com'
USE msdb
GO
EXEC sp_send_dbmail
--***************************************************************
-- Here is the issue I cannot pass variables from using SOLAR to msdb to perform loop to send email
-- I am unsure if we can add the msdb sp_send_dbmail to our db...
--*****************************************************************
EXEC synSendDBMail
-- Doing the above with a synonym results in:
-- Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 237
-- At least one of the following parameters must be specified. "@body, @query, @file_attachments, @subject".
@profile_name='CR2RCHMSQ47E',
@recipients='robert.dannelly@bankofamerica.com',
@subject='IRIS - Metric Values due in 3 days',
@body='The Interactive Risk Information System (IRIS) has sent you an email to inform you that you have metric
value(s) due in 3 days'
SET @varCurrentCount = @varCurrentCount+1
END
June 7, 2009 at 9:19 am
Hi
Your problem is not the different database. The problem is the "GO". It finishes the complete batch and starts a new one. GO within scripts is equal to using two (or more) different scripts.
Remove the GO and call the procedure like this:
EXEC sp_send_dbmail ...
Flo
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply