May 4, 2016 at 7:52 am
I am running the following script through SSMS and it works fine but while i run through job i am receiving failed to initialize sqlcmd library error. I have search on web and made few changes i.e change @query_result_header = 1 and @query_no_truncate = 1 but somehow it's not wroking for me.
Can anyone please advice?
declare @sub as nvarchar(128)
set @sub = 'Error Logs From ' + convert(varchar(50), getdate() - 1, 121)
EXEC [msdb].dbo.sp_send_dbmail
@profile_name ='SQLSRVMON',
@recipients ='XXXX',
@subject = @sub,
@from_address= 'XXXX.com',
@reply_to = 'XXX.com',
@body ='Everyone, The following are a list of errors from the previous daY ',
@body_format='TEXT',
@execute_query_database ='db_NAME',
@attach_query_result_as_file = '1',
@query_attachment_filename = 'output.txt',
--@query_result_width = 2000,
@query_result_header = 1,
@query_no_truncate = 1,
@query_result_no_padding = 0,
--@query_no_truncate = 1,
@query_result_separator = '',
@query = '
select '' ID: '' + cast(L.Id as nvarchar(20))+ char(13) + char(10)
, ''DATE: '' + cast(L.[Date] as nvarchar(20))+ char(13) + char(10)
, ''THREAD: '' +L.[Thread]+ char(13) + char(10)
, ''LEVEL: '' + L.[Level] + char(13) + char(10)
, ''LOGGER: '' + L.[Logger]+ char(13) + char(10)
, ''METHOD: '' + L.[Method] + char(13) + char(10)
, ''MESSAGE: '' + L.[Message] + char(13) + char(10) + char(13) + char(10)
, ''EXCEPTION: '' + L.[Exception] + char(13) + char(10) + char(13) + char(10) + char(13) + char(10) + ''-------------------------------------------------------''
from [db_name].[dbo].[Log] L with (nolock)
where L.[Level] = ''ERROR''
and logger <> ''TextLogging''
and logger not like ''WebForms%''
and [date] >= GETDATE() - 1 order by id asc
'
Edit:
This works fine if i remove the @query. Not sure what is the exact problem with this query?
I have included db_name.schema.table_name.
December 12, 2016 at 9:45 am
i am having this same truncation issue, have you solved this?
December 14, 2016 at 9:34 pm
Take a look at this link, hope this will help you out: http://www.sqlservercentral.com/articles/SQLCMD/123681/%5B/url%5D
November 1, 2018 at 2:59 pm
I found this info to help me with the same error:
original post was failing because SQL didn’t know where to run SELECT * FROM TestTable. The fix is to provide sp_send_dbmail with database context by either fully qualifying your table in the @query parameter:
@query = 'SELECT id FROM testDB.dbo.TestTable'
or by providing the optional @execute_query_database parameter:
@execute_query_database = 'testDB'
I chose to add the @execute_query_database and it worked!
Source: Found Here
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy