November 1, 2019 at 8:52 pm
Please assist.... i am pretty close and not sure where it keeps going wrong... I have the job writing to a file on the D:\ drive and that part is working fine and trying to pick it up and send it ...
Thank you for your time and assistance as its greatly appreciated.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile_Name',
@recipients = 'dheath@exxxxxxx.com',
@subject = ' Moveit Results of Success/Failure',
--@attach_query_result_as_file = 1,
@file_attachment = 'D:\Test\results.txt',
@query = ''Declare @Prior_Days int = 0; -- last 24 hours
With
cte_tasks_with_failure_and_no_success As
(
SELECT
'Task_Name' = Taskname,
'Min_TimeStarted' = Min(Convert(datetime, TimeStarted)),
'Max_TimeEnded' = Max(Convert(datetime, TimeEnded)),
'Bytes_Transferred' = Sum([TotalBytesSent]),
'Iteration_Cnt_Total' = Count(Success),
'Iteration_Cnt_No_xfers' = Sum(IIF(Success = 'No xfers', 1, 0)),
'Iteration_Cnt_Failure' = Sum(IIF(Success = 'Failure', 1, 0)),
'Iteration_Cnt_Success' = Sum(IIF(Success = 'Success', 1, 0))
FROM
[tablename].[dbo].[taskruns]
GROUP BY
taskname, Format(Convert(datetime, TimeStarted), 'yyyyMMdd')
HAVING
Sum(IIF(Success = 'Failure', 1, 0)) > 1 AND
Sum(IIF(Success = 'Success', 1, 0)) = 0
)
SELECT
Task_Name,
'Days_Prior' = DateDiff(dd, [Max_TimeEnded], GetDate()),
Min_TimeStarted,
Max_TimeEnded,
Bytes_Transferred,
Iteration_Cnt_Total,
Iteration_Cnt_No_xfers,
Iteration_Cnt_Failure,
Iteration_Cnt_Success
FROM
cte_tasks_with_failure_and_no_success
WHERE
DateDiff(dd, [Max_TimeEnded], GetDate()) <= @Prior_Days
ORDER BY
1,2
DHeath
November 1, 2019 at 9:10 pm
so there's two things here you have to check.
the D:\ drive here must be on the SQL server.
@file_attachment = 'D:\Test\results.txt',
if the drive is not on the server, you'll need to change it to a UncPath, like \\AppsErver\ShareName\Result.txt
next, you need to resolve folder level permissions.
the service account running SQL Database needs read permissions and list folder contents for that folder if it is to attach it. if this is an SSIS package, then the SQL Agent account would also need access, most likely.
select * from sys.dm_server_services
typically you would just right click on the folder...go to the security tab, and add that account by name.
Lowell
November 5, 2019 at 2:18 pm
Thanks Lowell as your assistance is appreciated...Yet i am still dead in the water... Your suggestion about permission were excellent and spot on. yet now i am getting different messages with things such as :
Executed as user: Domain\sqlservices. Failed to open attachment file 'D:\Test\Moveit_results.txt'. Executing API 'CreateFile' failed with error number 32. [SQLSTATE 42000] (Error 22051) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153). The step failed
This seems to let me know its actually trying to get to the file now as before it was not..ALSO i have check and rechecked that the service account has FULL CONTROL on the folder and files on this server.
I know i am not doing something correct in this aspect here.. just dont know where and not having much luck with online examples when it comes to attaching a file
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile_Name',
@recipients = 'dheath@xxxxxxh.com',
@subject = ' Moveit Results of Success/Failure',
--@attach_query_result_as_file = 1, **** what should this be when i use it says it should be declared???
@execute_query_database = 'automation',
@file_attachments = N'D:\Test\Moveit_results.txt',
Ideas???
DHeath
November 5, 2019 at 5:46 pm
Error number 32 is ERROR_SHARING_VIOLATION so something else is accessing the file when you go to attach it.
Sue
November 5, 2019 at 6:00 pm
Sue,
Thank you for your reply...greatly appreciated.
There is nothing trying to touch the file.. i created it specifically for this. I am thinking my coce is wrong in the way i attach or grab the file. Also i just noticed the only thing in my file is the error message but not the results from the query. Could this be where i am going wrong?
DHeath
DHeath
November 5, 2019 at 6:50 pm
I've hit it where the process that created the file didn't finish up completely when I went to send the mail so that can happen. It could also be related to antivirus. But that is that error for error number 32.
In terms of what's in the file, yeah...that's not right. That almost sounds like a file output from the job rather than the query results. Or maybe you have the output file for the query and the job using the same file name. Not necessarily the case but If I read your first post, sounds like maybe the two are mixed up - job output file and query output file. Those are two different things. If you used @attach_query_result_as_file and got an error about needing to declare it then I would suspect there was a typo. Usually for what you do, you would just need to specify @attach_query_result_as_file = 1 and provide just a file name for the attachment with @query_attachment_filename = 'SomeFileName.txt'. I just did the following as a quick test and it was fine with sending the attachment:
DECLARE @qry varchar(200)
SELECT @qry='set nocount on;
select top 10 logical_name, physical_name, state_desc
from msdb.dbo.backupfile'
EXEC msdb.dbo.sp_send_dbmail
@recipients='Someone@address.com',
@subject='Some File',
@profile_name = 'YourProfileName',
@body='Refer to attachment',
@query =@qry,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'WhateverFileName.txt'
Sue
November 5, 2019 at 6:51 pm
How are you creating the file? That is most likely the problem here - as that process (and SQL Server) would then have a lock on the file.
Are you aware that sp_send_dbmail can create and attach the file for you? The parameter @attach_query_result_as_file = 1 will do just that - using the source query to generate the file. The file generated will be a delimited file with each field from the query being delimited by the separator chosen (@query_result_separator).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 5, 2019 at 7:50 pm
Sue, I am wondering if its the code because i am doing some file manipulation and not simply a select statement as those seem okay...and i know the code is pulling back what i need. And for that i say thank you for the suggestion. I still get the error about declaring @attach_query_result_as_file = 1 as even when i cut and paste..it wants me to declare this...not sure why... LoL
Jeffery...I have went thru the job and ask the job to do the attachment... Would this cause the problem if done at both places? In job and in script? i dont think it would but thats all i can think... will double check and removed the script attachments commands...Also i am hoping that my query just runs and the job writes to text file.
Thanks for the input as i think i am making progress as my errors are different...sooo who knows.. 🙂
New error is this but i do believe its progress
Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147467259
DHeath
November 5, 2019 at 8:43 pm
On the send mail, you have a syntax error in your original post that I just noticed as the parameter is @file_attachments (plural) rather than singular. Don't think that would cause the error with using @attach_query_result_as_file though and it's correct in your second post. And in your response to Jeffrey, that's what I was saying about Job output file vs query file. Two different things and that's likely why you got the error 32. But you want to send a query file not the job output file. It would cause a problem when you use the same filename for both and try to send that file in the job.
And then I noticed something else in your original post where the CTE is built using: FROM [tablename].[dbo].[taskruns] Normally that would be databasename.schemaname.tablename. So I wonder if the query itself has some problems. You may want to start by breaking things down to first the query and making sure that's correct. And then testing some basic send mail procedures before trying the attachment. I have no idea why you get the error with @attach_query_result_as_file but it's likely related to syntax issues and/or how you wrote the process, file manipulation you are using to send the results. Not the fact you are doing the file manipulation but how it's been coded. Try a basic query that you can post that sends the file as an attachment using @attach_query_result_as_file = 1. I'm not sure we can help you figure out the issues without seeing the real code. But trying one you can post - if you get the error again, we can more likely help you out. And if it doesn't fail, you have a template to use and build on in trying to get your "real" one to work.
Sue
November 5, 2019 at 9:48 pm
I am not sure what file you are trying to attach - or how that file is generated. It seems to me you want to generate the file from a query - and attach the results of that query. If that is so...then try this:
Use Works;
Go
Declare @query nvarchar(max) = ''
, @recipients varchar(max) = 'someuser@somedomain.com'
, @cc_recipients varchar(max) = 'otheruser@somedomain.com';
Set @query = '
Set Nocount On;
Declare @Prior_Days int = 0; -- last 24 hours
With cte_tasks_with_failure_and_no_success
As (
Select tr.TaskName
, Min_TimeStarted = min(convert(datetime, tr.TimeStarted))
, Max_TimeEnded = max(convert(datetime, tr.TimeEnded))
, Bytes_Transferred = sum(tr.TotalBytesSent)
, Iteration_Cnt_Total = count(tr.Success)
, Iteration_Cnt_No_xfers = sum(iif(tr.Success = ''No xfers'', 1, 0))
, Iteration_Cnt_Failure = sum(iif(tr.Success = ''Failure'', 1, 0))
, Iteration_Cnt_Success = sum(iif(tr.Success = ''Success'', 1, 0))
From dbo.TaskRuns tr
Group By
tr.TaskName
, convert(datetime, tr.TimeStarted)
Having sum(iif(tr.Success = ''Failure'', 1, 0)) > 1
And sum(iif(tr.Success = ''Success'', 1, 0)) = 0
)
Select quotename(c.TaskName, char(34)) As [Sep=,' + char(13) + char(10) + 'Task_Name]
, Days_Prior = datediff(dd, c.Max_TimeEnded, getdate())
, c.Min_TimeStarted
, c.Max_TimeEnded
, c.Bytes_Transferred
, c.Iteration_Cnt_Total
, c.Iteration_Cnt_No_xfers
, c.Iteration_Cnt_Failure
, c.Iteration_Cnt_Success
From cte_tasks_with_failure_and_no_success c
Where datediff(dd, c.Max_TimeEnded, getdate()) <= @Prior_Days
Order By
Task_Name
, Days_Prior;'
Execute msdb.dbo.sp_send_dbmail
@profile_name = 'Profile_Name'
, @query = @query
, @subject = 'Moveit Results of Success/Failure'
, @body = 'Attached is a list of success/failures for Moveit Results'
, @recipients = @recipients
, @copy_recipients = @cc_recipients
, @execute_query_database = '{your user database here}'
, @attach_query_result_as_file = 1
, @query_result_width = 8000
, @query_attachment_filename = 'Moveit_results.csv'
, @query_result_header = 1
, @query_result_separator = ','
, @query_result_no_padding = 1;
This is a basic template I use to send an attachment as a CSV file that will be opened by Excel. The key to making sure the file can be opened correctly is the first column defining the type of data.
There is no need to attach a file - as it will be generated as needed, unless you have some other process generating a different file to be attached.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 5, 2019 at 11:18 pm
Sue Thank you so much for your time and everything .... you are appreciated and wanted to say thanks
Jeffery... the code you sent seems to work much better than what i had as far as what i am trying to chase and errors. the only message i am getting now is this...
Msg 22050, Level 16, State 1, Line 2
Failed to initialize sqlcmd library with error number -2147467259. And i think that has to do with permissions but not sure even this is not much to go on
DHeath
November 6, 2019 at 10:43 pm
I did a quick search for that error - it is most likely a permissions issue, but without more information it isn't possible to identify. It could be a password failure, or a file size limit has been reached - or something else.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 6, 2019 at 10:59 pm
Jeffery....Thanks so much for the assistance... i too been searching all over for that error and came to the conclusion of permissions as well. Now will give try to give sysadmin rights at both windows level and sql level to the account running sql agent and see if that fixes the issue. I do believe thats the account trying to make all the calls as i place me as job owner and still same same.
Thanks again for everything super appreciated.
DHeath
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply