September 29, 2015 at 1:19 am
Hello
I have written a stored procedure to send the results of a query to me when a particular condition is met. This is the syntax I am attempting to run:
SET NOCOUNT ON;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'KCSIS Visitor',
@recipients = [<my email address>],
@subject = 'KCSIS - Visitor with unsupervised access to pupils',
@query = 'SELECT dbo.TblSafeguardVisitors.DateOfAct, dbo.TblSafeguardVisitors.Name_Of_Speaker, dbo.TblSafeguardVisitors.Member_Of_Staff_Hosting,
dbo.TblSafeguardVisitors.Dept_Act, dbo.TblSafeguardVisitors.Unsupervised, dbo.TblSafeguardVisitors.Known, dbo.TblSafeguardVisitors.ReferenceTaken,
dbo.TblSafeguardVisitors.InternetResearched, dbo.TblSafeguardVisitors.OtherCheck, dbo.TblSafeguardVisitors.Notes, dbo.TblSafeguardVisitors.Entered_By
FROM dbo.CurrYearT INNER JOIN
dbo.TblSafeguardVisitors ON dbo.CurrYearT.DateOfKCSIS < dbo.TblSafeguardVisitors.DateOfAct
WHERE (dbo.TblSafeguardVisitors.Unsupervised = 1) WITH (NOLOCK)',
@sensitivity = 'Confidential',
@body = 'An entry has been made for a new speaker with unsupervised access to children.
Below is the last entry made.
'
END
Whenever I attempt to run it, I get the error message
Msg 22050, Level 16, State 1, Line 16
Failed to initialize sqlcmd library with error number -2147467259.
However, if I REM out the query, the SP works fine and I receive the email. Being paranoid about the syntax of the underlying query, I even went into creating a view and got it to write the SQL syntax for me - but still to no avail. Checking the error log for
September 29, 2015 at 1:35 am
try specifying the database prefix in the sql query
SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
September 29, 2015 at 3:19 am
Hi Kenny
Thanks for the suggestion. I have now put the full database names in for each reference to a table within the query, but I still get the same error, alas.
Regards
Dave
:crying:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply