October 26, 2017 at 2:07 pm
I have a stored procedure that is used for a report. I am being asked to have an email go to one person every Monday. I wrote a stored procedure that I will run as a job in SQL Server Agent.
I am getting the following error when I try to execute the procedure
Failed to initialize sqlcmd library with error number -2147467259.
Here is my procedure
CREATE PROCEDURE [dbo].[BobReport]
AS
SET NOCOUNT ON
DECLARE @now varchar(50), @mailBody varchar(max)
SET @now = CONVERT(varchar(20), GETDATE(),100)
SET @mailBody = 'Deactivated positions as of ' + @now +char(13)+char(10)+char(13)+char(10) + 'Automated Email - Do Not Reply'
DECLARE @Temp Table
(
Position varchar(100),
StoreNumber int,
Manager varchar(75),
StatusDate datetime
)
INSERT @Temp
EXECUTE ActiveRetailJobsReport 2,null,null,4
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='myProfile',
@recipients='Bobs email',
@query = 'SELECT * FROM @Temp',
@subject = 'Deactivated Position Report' ,
@body = @mailBody,
@attach_query_result_as_file = 1 ;
I have tried using a temporary table instead of the table variable and still receive the same error
Here is the ActiveRetailJobsReport procedure in case that is needed
ALTER PROCEDURE [dbo].[ActiveRetailJobsReport]
@ReportType int,
@Store int = NULL,
@District int = NULL,
@Zone int = NULL
AS
SET NOCOUNT ON
--This report will work with 2 table variables that we will loop through when set to all stores, a district or a zone, an individual store will only loop through the @Jobs table variable.
--Insert the jobs that are controlled by the stores. Only the jobs that are marked as Retail (JobType 1) and those that do not allow resumes are controlled by the store
DECLARE @Jobs table
(
JobID int,
JobTitle varchar(100),
tid int identity
)
INSERT @Jobs
SELECT
JobID,
JobTitle
FROM
dbo.Jobs
WHERE
AllowResumes = 0
AND
JobType = 1
DECLARE @Locations table
(
LocationID int,
StoreNumber int,
tid int identity
)
DECLARE @Temp table
(
JobTitle varchar(100),
StoreNumber int,
MgrName varchar(75),
StatusDate datetime
)
DECLARE @StartL int, @StopL int, @StartJ int, @StopJ int, @LocationID int, @JobID int, @JobTitle varchar(100), @StoreNumber int, @MgrName varchar(75), @StatusDate datetime
SET @StartJ = 1
SELECT @StopJ = COUNT(*) + 1 FROM @Jobs
IF @ReportType = 1 --- All Stores
BEGIN
INSERT @Locations
SELECT LocationID, StoreNumber FROM dbo.Locations WHERE Deleted = 0 AND NOT StoreNumber IN (0,888,995,996,997,998,999)
SET @StartL = 1
SELECT @StopL = Count(*) + 1 FROM @Locations
WHILE @StartJ < @StopJ
BEGIN
SELECT @JobID = JobID, @JobTitle = JobTitle FROM @Jobs WHERE tid = @StartJ
WHILE @StartL < @StopL
BEGIN
SELECT @LocationID = LocationID, @StoreNumber = StoreNumber FROM @Locations WHERE tid = @StartL
-- Check to see if the job is active if it does not exist then we know it is not active
IF NOT EXISTS(SELECT JobID FROM dbo.JobLocations WHERE JobID = @JobID AND LocationID = @LocationID)
BEGIN
--Look to see if there is a record in the JobStatusLog table. This table is new as of the date this procedure was created so there are no records right now
IF EXISTS(SELECT TOP 1 AdminUser FROM dbo.JobStatusLog WHERE JobID = @JobID AND LocationID = @LocationID AND Active = 0 ORDER BY StatusDate DESC)
BEGIN
--There is a record so assign the variable names for Admin user and date that the job was deactivated
SELECT TOP 1 @MgrName = AdminUser, @StatusDate = StatusDate FROM dbo.JobStatusLog WHERE JobID = @JobID AND LocationID = @LocationID AND Active = 0 ORDER BY StatusDate DESC
END
ELSE
BEGIN
-- no records found so set variables to null
SET @MgrName = NULL
SET @StatusDate = NULL
END
INSERT @Temp
VALUES
(
@JobTitle,
@StoreNumber,
@MgrName,
@StatusDate
)
END
SET @StartL = @StartL + 1
END
SET @StartJ = @StartJ + 1
--Reset The inner loop to 1
SET @StartL = 1
END
END
IF @ReportType = 2 -- Zone
BEGIN
INSERT @Locations
SELECT LocationID, StoreNumber FROM dbo.Locations WHERE ZoneID = @Zone AND Deleted = 0
SET @StartL = 1
SELECT @StopL = Count(*) + 1 FROM @Locations
WHILE @StartJ < @StopJ
BEGIN
SELECT @JobID = JobID, @JobTitle = JobTitle FROM @Jobs WHERE tid = @StartJ
WHILE @StartL < @StopL
BEGIN
SELECT @LocationID = LocationID, @StoreNumber = StoreNumber FROM @Locations WHERE tid = @StartL
-- Check to see if the job is active if it does not exist then we know it is not active
IF NOT EXISTS(SELECT JobID FROM dbo.JobLocations WHERE JobID = @JobID AND LocationID = @LocationID)
BEGIN
--Look to see if there is a record in the JobStatusLog table. This table is new as of the date this procedure was created so there are no records right now
IF EXISTS(SELECT TOP 1 AdminUser FROM dbo.JobStatusLog WHERE JobID = @JobID AND LocationID = @LocationID AND Active = 0 ORDER BY StatusDate DESC)
BEGIN
--There is a record so assign the variable names for Admin user and date that the job was deactivated
SELECT TOP 1 @MgrName = AdminUser, @StatusDate = StatusDate FROM dbo.JobStatusLog WHERE JobID = @JobID AND LocationID = @LocationID AND Active = 0 ORDER BY StatusDate DESC
END
ELSE
BEGIN
-- no records found so set variables to null
SET @MgrName = NULL
SET @StatusDate = NULL
END
INSERT @Temp
VALUES
(
@JobTitle,
@StoreNumber,
@MgrName,
@StatusDate
)
END
SET @StartL = @StartL + 1
END
SET @StartJ = @StartJ + 1
--Reset The inner loop to 1
SET @StartL = 1
END
END
IF @ReportType = 3 -- District
BEGIN
INSERT @Locations
SELECT LocationID, StoreNumber FROM dbo.Locations WHERE DistrictID = @District AND Deleted = 0
SET @StartL = 1
SELECT @StopL = Count(*) + 1 FROM @Locations
WHILE @StartJ < @StopJ
BEGIN
SELECT @JobID = JobID, @JobTitle = JobTitle FROM @Jobs WHERE tid = @StartJ
WHILE @StartL < @StopL
BEGIN
SELECT @LocationID = LocationID, @StoreNumber = StoreNumber FROM @Locations WHERE tid = @StartL
-- Check to see if the job is active if it does not exist then we know it is not active
IF NOT EXISTS(SELECT JobID FROM dbo.JobLocations WHERE JobID = @JobID AND LocationID = @LocationID)
BEGIN
--Look to see if there is a record in the JobStatusLog table. This table is new as of the date this procedure was created so there are no records right now
IF EXISTS(SELECT TOP 1 AdminUser FROM dbo.JobStatusLog WHERE JobID = @JobID AND LocationID = @LocationID AND Active = 0 ORDER BY StatusDate DESC)
BEGIN
--There is a record so assign the variable names for Admin user and date that the job was deactivated
SELECT TOP 1 @MgrName = AdminUser, @StatusDate = StatusDate FROM dbo.JobStatusLog WHERE JobID = @JobID AND LocationID = @LocationID AND Active = 0 ORDER BY StatusDate DESC
END
ELSE
BEGIN
-- no records found so set variables to null
SET @MgrName = NULL
SET @StatusDate = NULL
END
INSERT @Temp
VALUES
(
@JobTitle,
@StoreNumber,
@MgrName,
@StatusDate
)
END
SET @StartL = @StartL + 1
END
SET @StartJ = @StartJ + 1
--Reset The inner loop to 1
SET @StartL = 1
END
END
IF @ReportType = 4 --- Individual Store
BEGIN
WHILE @StartJ < @StopJ
BEGIN
SELECT @StoreNumber = StoreNumber FROM dbo.Locations WHERE LocationID = @Store
SELECT @JobID = JobID, @JobTitle = JobTitle FROM @Jobs WHERE tid = @StartJ
IF NOT EXISTS(SELECT JobID FROM dbo.JobLocations WHERE JobID = @JobID AND LocationID = @Store)
BEGIN
--Look to see if there is a record in the JobStatusLog table. This table is new as of the date this procedure was created so there are no records right now
IF EXISTS(SELECT TOP 1 AdminUser FROM dbo.JobStatusLog WHERE JobID = @JobID AND LocationID = @Store AND Active = 0 ORDER BY StatusDate DESC)
BEGIN
--There is a record so assign the variable names for Admin user and date that the job was deactivated
SELECT TOP 1 @MgrName = AdminUser, @StatusDate = StatusDate FROM dbo.JobStatusLog WHERE JobID = @JobID AND LocationID = @Store AND Active = 0 ORDER BY StatusDate DESC
END
ELSE
BEGIN
-- no records found so set variables to null
SET @MgrName = NULL
SET @StatusDate = NULL
END
INSERT @Temp
VALUES
(
@JobTitle,
@StoreNumber,
@MgrName,
@StatusDate
)
END
SET @StartJ = @StartJ + 1
END
END
SELECT
JobTitle,
StoreNumber,
MgrName,
StatusDate
FROM
@Temp
ORDER BY
JobTitle,
StoreNumber
October 26, 2017 at 2:26 pm
You can't do any of that because your temp tables, table variables etc do not exist in the same scope when sp_send_dbmail executes.
Somehow, you need to engineer things such that the whole query can be executed as part of the @query = parameter.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 26, 2017 at 2:26 pm
http://caspian.dotconf.net/menu/Software/SendEmail/
I use SendMail...works like a charm and super easy to setup.
October 26, 2017 at 2:28 pm
chef423 - Thursday, October 26, 2017 2:26 PMhttp://caspian.dotconf.net/menu/Software/SendEmail/I use SendMail...works like a charm and super easy to setup.
Would it solve this particular problem, or are you spamming?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 26, 2017 at 2:33 pm
Phil Parkin - Thursday, October 26, 2017 2:28 PMchef423 - Thursday, October 26, 2017 2:26 PMhttp://caspian.dotconf.net/menu/Software/SendEmail/I use SendMail...works like a charm and super easy to setup.
Would it solve this particular problem, or are you spamming?
No, I am trying to help ?
He was asking how to send a file output from SQL...maybe I didn't read correctly?
SendMail is 100% free...so nothing to spam. But my apologies if I read that incorrectly.
October 26, 2017 at 2:42 pm
Well crap... I was afraid of that. It is not a simple query which is why I showed the 2nd sproc. This Manager is in charge of a number of stores and when the jobs are active( when they are hiring for the position) they are held in a many to many relationship table. But this report is based on when they are NOT active (not in the table) so there is no way to do a join
October 26, 2017 at 2:44 pm
Thanks chef but command line wont cut it either. btw she not he.
October 26, 2017 at 3:18 pm
mjohnson 71479 - Thursday, October 26, 2017 2:42 PMWell crap... I was afraid of that. It is not a simple query which is why I showed the 2nd sproc. This Manager is in charge of a number of stores and when the jobs are active( when they are hiring for the position) they are held in a many to many relationship table. But this report is based on when they are NOT active (not in the table) so there is no way to do a join
However, there is nothing stopping you from populating a persisted physical table with the results and then selecting from that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 26, 2017 at 3:36 pm
I just needed to think outside of the T-SQL box and use a non-ANSI join. I have it working now
ALTER PROCEDURE [dbo].[BobReport]
AS
SET NOCOUNT ON
DECLARE @now varchar(50), @mailBody varchar(max)
SET @now = CONVERT(varchar(20), GETDATE(),100)
SET @mailBody = 'Deactivated positions as of ' + @now + char(13)+char(10)+char(13)+char(10) + 'Automated Email - Do Not Reply'
IF EXISTS(SELECT J.JobTitle, L.StoreNumber FROM dbo.Jobs J, dbo.Locations L WHERE J.AllowResumes = 0 AND J.JobType = 1 AND J.Deleted = 0 AND L.ZoneID = 4 AND L.LocationID <> 191
AND L.Deleted = 0 AND L.LocationID NOT IN (SELECT LocationID FROM dbo.JobLocations WHERE JobID IN (SELECT JobID FROM dbo.Jobs WHERE AllowResumes = 0 AND JobType = 1)))
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='my profile',
@recipients='Bobs Email',
@query = 'SELECT J.JobTitle, L.StoreNumber FROM dbo.Jobs J, dbo.Locations L WHERE J.AllowResumes = 0 AND J.JobType = 1 AND J.Deleted = 0 AND L.ZoneID = 4 AND L.LocationID <> 191
AND L.Deleted = 0 AND L.LocationID NOT IN (SELECT LocationID FROM dbo.JobLocations WHERE JobID IN (SELECT JobID FROM dbo.Jobs WHERE AllowResumes = 0 AND JobType = 1))',
@subject = 'Deactivated Position Report' ,
@body = @mailBody,
@attach_query_result_as_file = 1 ;
END
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='my profile',
@recipients='Bobs email',
@query = 'SELECT ''No Positions have been deactivated across the zone.''',
@subject = 'Deactivated Position Report - No Deactivations' ,
@body = 'Automated Email - Do Not Reply',
@attach_query_result_as_file = 0 ;
END
October 26, 2017 at 8:22 pm
mjohnson 71479 - Thursday, October 26, 2017 3:36 PMI just needed to think outside of the T-SQL box and use a non-ANSI join. I have it working now
ALTER PROCEDURE [dbo].[BobReport]AS
SET NOCOUNT ON
DECLARE @now varchar(50), @mailBody varchar(max)
SET @now = CONVERT(varchar(20), GETDATE(),100)
SET @mailBody = 'Deactivated positions as of ' + @now + char(13)+char(10)+char(13)+char(10) + 'Automated Email - Do Not Reply'IF EXISTS(SELECT J.JobTitle, L.StoreNumber FROM dbo.Jobs J, dbo.Locations L WHERE J.AllowResumes = 0 AND J.JobType = 1 AND J.Deleted = 0 AND L.ZoneID = 4 AND L.LocationID <> 191
AND L.Deleted = 0 AND L.LocationID NOT IN (SELECT LocationID FROM dbo.JobLocations WHERE JobID IN (SELECT JobID FROM dbo.Jobs WHERE AllowResumes = 0 AND JobType = 1)))
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='my profile',
@recipients='Bobs Email',
@query = 'SELECT J.JobTitle, L.StoreNumber FROM dbo.Jobs J, dbo.Locations L WHERE J.AllowResumes = 0 AND J.JobType = 1 AND J.Deleted = 0 AND L.ZoneID = 4 AND L.LocationID <> 191
AND L.Deleted = 0 AND L.LocationID NOT IN (SELECT LocationID FROM dbo.JobLocations WHERE JobID IN (SELECT JobID FROM dbo.Jobs WHERE AllowResumes = 0 AND JobType = 1))',
@subject = 'Deactivated Position Report' ,
@body = @mailBody,
@attach_query_result_as_file = 1 ;END
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='my profile',
@recipients='Bobs email',
@query = 'SELECT ''No Positions have been deactivated across the zone.''',
@subject = 'Deactivated Position Report - No Deactivations' ,
@body = 'Automated Email - Do Not Reply',
@attach_query_result_as_file = 0 ;
END
Just a thought... we don't allow non-maintenance procs to send emails where I work because, as you have done, the email address(es) would frequently be hard coded. When some would copy the proc from prod to the Dev or Test environments, they would forget to change the email address and, during the testing, the recipient's hair would catch fire because they would catch all of the emails that said stuff was either broken or the test data was reflected instead of real data and the recipient(s) would spend the day bugging everyone as to what went wrong.
Since your already using SQL Agent to schedule the creation and emailing of this report, I recommend a 2 step process for the job... the first step would be to create the report using the stored procedure and the second step would be to email it. That way, the proc is only doing one thing (which should generally be the case for most procs) and it can be copied to other environments without risk of false alarms and the resulting need for cranial fire-fighting apparatus. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2017 at 5:51 am
Jeff Moden - Thursday, October 26, 2017 8:22 PMmjohnson 71479 - Thursday, October 26, 2017 3:36 PMI just needed to think outside of the T-SQL box and use a non-ANSI join. I have it working now
ALTER PROCEDURE [dbo].[BobReport]AS
SET NOCOUNT ON
DECLARE @now varchar(50), @mailBody varchar(max)
SET @now = CONVERT(varchar(20), GETDATE(),100)
SET @mailBody = 'Deactivated positions as of ' + @now + char(13)+char(10)+char(13)+char(10) + 'Automated Email - Do Not Reply'IF EXISTS(SELECT J.JobTitle, L.StoreNumber FROM dbo.Jobs J, dbo.Locations L WHERE J.AllowResumes = 0 AND J.JobType = 1 AND J.Deleted = 0 AND L.ZoneID = 4 AND L.LocationID <> 191
AND L.Deleted = 0 AND L.LocationID NOT IN (SELECT LocationID FROM dbo.JobLocations WHERE JobID IN (SELECT JobID FROM dbo.Jobs WHERE AllowResumes = 0 AND JobType = 1)))
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='my profile',
@recipients='Bobs Email',
@query = 'SELECT J.JobTitle, L.StoreNumber FROM dbo.Jobs J, dbo.Locations L WHERE J.AllowResumes = 0 AND J.JobType = 1 AND J.Deleted = 0 AND L.ZoneID = 4 AND L.LocationID <> 191
AND L.Deleted = 0 AND L.LocationID NOT IN (SELECT LocationID FROM dbo.JobLocations WHERE JobID IN (SELECT JobID FROM dbo.Jobs WHERE AllowResumes = 0 AND JobType = 1))',
@subject = 'Deactivated Position Report' ,
@body = @mailBody,
@attach_query_result_as_file = 1 ;END
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='my profile',
@recipients='Bobs email',
@query = 'SELECT ''No Positions have been deactivated across the zone.''',
@subject = 'Deactivated Position Report - No Deactivations' ,
@body = 'Automated Email - Do Not Reply',
@attach_query_result_as_file = 0 ;
ENDJust a thought... we don't allow non-maintenance procs to send emails where I work because, as you have done, the email address(es) would frequently be hard coded. When some would copy the proc from prod to the Dev or Test environments, they would forget to change the email address and, during the testing, the recipient's hair would catch fire because they would catch all of the emails that said stuff was either broken or the test data was reflected instead of real data and the recipient(s) would spend the day bugging everyone as to what went wrong.
Since your already using SQL Agent to schedule the creation and emailing of this report, I recommend a 2 step process for the job... the first step would be to create the report using the stored procedure and the second step would be to email it. That way, the proc is only doing one thing (which should generally be the case for most procs) and it can be copied to other environments without risk of false alarms and the resulting need for cranial fire-fighting apparatus. 😉
Another method is to set up a Configuration Settings table that holds different values for Prod and Test (and Dev too, if you need that flexibility).
Columns like
Job Name ("Deactivation email report")
Setting name ("EmailTo")
Value ("Bob's email address")
Environment ("Prod")
When you put in values for the test environment, you'd use "Developer's email address" or "Tester's email address" instead.
Then when you run the job,
SELECT @SendTo = Value
FROM ConfigSettings
WHERE JobName = 'Deactivation email report' AND SetName = 'EmailTo' AND Environment = 'Prod'; -- or 'Test' as applicable
And then when Bob moves on to a bigger and better position, you just change the value in the ConfigSettings table to "New-Bob's email address".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply