I keep getting this error
Failed to initialize sqlcmd library with error number -2147467259
when I try to execute the following SQL code. This is a normal query and not through SQL Server Agent.
declare @SQL varchar(8000)
set @SQL = 'select a.b.value(''(../../@ID)'',''varchar(100)'') as [System-ID]
from [DB].[Schema].[Configuration] R
outer apply R.[Configuration].nodes(''root/System/Role/Authorization'') as a(b)
where R.[Report ID] = ''IT.00004'''
print @SQL
exec msdb.dbo.sp_send_dbmail
@profile_name = 'servername',
@recipients = '[email protected]',
@subject = 'Test B',
@query = @SQL
However, when I modify the query as followed everything works fine.
declare @SQL varchar(8000)
set @SQL = 'select *
from [DB].[Schema].[Configuration] R
where R.[Report ID] = ''IT.00004'''
print @SQL
exec msdb.dbo.sp_send_dbmail
@profile_name = 'servername',
@recipients = '[email protected]',
@subject = 'Test B',
@query = @SQL
So the problem must be with this part of the statement (I am referencing an XML column called "Configuration" in the table "Configuration" -> Column name and table name are the same):
outer apply R.[Configuration].nodes('root/System/Role/Authorization') as a(b)
When I run both queries outside of the msdb.dbo.sp_send_dbmail syntax they both run perfectly fine.
Does anyone know what is going on? I doubt that it is permission related since the part that is causing the problem is using the same table that doesn't cause any issues.
In order to resolve this issue I had to add the following to the query:
set @SQL = 'SET QUOTED_IDENTIFIER ON '
set @SQL = @SQL + char(13)
set @SQL = @SQL + 'select a.b.value(''(../../@ID)'',''varchar(100)'') as [System-ID]
from [DB].[Schema].[Configuration] R
outer apply R.[Configuration].nodes(''root/System/Role/Authorization'') as a(b)
where R.[Report ID] = ''IT.00004'''