-1

I am trying to create a trigger that emails the results of a query on insert. The table requires me to create json_value columns and I am struggling to get the syntax correct in the query section of the executable.

This is how I would normally write a query for json_value columns:

SELECT
json_value(property_dict,'$.level_1.level_2.description')
FROM [database].[dbo].[table]

When writing the query as above in the executable I get "Incorrect syntax near '$.'.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MSSQLAlert',
@recipients = 'email@domain.com',
@subject = 'Test Email',
@query = 'SELECT TOP 1 json_value(jsoncolumn,'$.level_1.level_2.description')
FROM [database].[dbo].[table]',
@attach_query_result_as_file = 0

If I do not include the quotes outside of the $ and description I get "Failed to initialize sqlcmd library with error number -2147467259."

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MSSQLAlert',
@recipients = 'email@domain.com',
@subject = 'Test Email',
@query = 'SELECT TOP 1 json_value(jsoncolumn,$.level_1.level_2.description)
FROM [database].[dbo].[table]',
@attach_query_result_as_file = 0

Is what I am trying to do possible? Thanks for any help!

charles1051
  • 7
  • 2
  • `FROM database`? You don't select from a database... You select from a table, View, etc, that resides inside a database. – Thom A Oct 19 '21 at 19:57
  • If your *table* called `database` really does exist (a poor name for a table for multiple reasons) then firstly, does it exist on the database `msdb`? If not, then why are you not using 3 part naming or providing the database parameter? Also, you need to delimit identify said table's name as it's a reserved keyword. (Though, again, I strongly suggest you rethink the name.) – Thom A Oct 19 '21 at 20:03
  • Right that's my bad I did mean table. I was trying to use a generic term in my post but it would be FROM [database].[dbo].[table]. I am able to send emails with this executable if I use a different column from the same table that is not a json_value. – charles1051 Oct 19 '21 at 20:11
  • 1
    [Edit] your question to not be confusing, and representative of what you are *actually* doing. – Thom A Oct 19 '21 at 20:14
  • Well as my question states I was trying to send an email. Not sure how my mistake changes that but I am very sorry that confused you. The post has been updated. – charles1051 Oct 20 '21 at 11:37
  • It's confusing because it leads us to believe you think you can `SELECT` from a `DATABASE`; which is completely not true. Also, as I mentioned in my [earlier comment](https://stackoverflow.com/questions/69636923/can-i-include-json-value-columns-in-an-executable-email?noredirect=1#comment123087771_69636923) As you used 1 part naming, along with an object called `database` it implied that the problem was the lack of 3 part naming and delimit identifying. This is why providing representative and sensible examples is important. – Thom A Oct 20 '21 at 11:40

1 Answers1

1

Since your query is contained within a string value being passed as a parameter value you just need to escape the single quotes in your query. Try this:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MSSQLAlert',
@recipients = 'email@domain.com',
@subject = 'Test Email',
@query = 'SELECT TOP 1 json_value(jsoncolumn,''$.level_1.level_2.description'')
          FROM SomeTable',
@attach_query_result_as_file = 0;
squillman
  • 13,363
  • 3
  • 41
  • 60