英文:
SQL Server Management Studio: Policy - ExecuteSql throws error if the statement string contains an empty field
问题
I want a SQL Server Management Policy to check the values in a delimited list returned by FOR XML. The problem is that "FOR XML ('')" will throw a SQL error.
I may not post screenshots or site-specific details so I will demonstrate the error using a simple example:
-
In SQL Server Management Studio, open Object Explorer, expand Management, expand Policy Management, expand Policies to see the existing policies.
-
Right click Policies and choose New Policy...
-
Give it a name: List Databases
-
Click Check Condition and then New Condition
-
Name the condition: List Databases
-
Click the ellipsis button to the right of the Expression Field
-
Type in this statement:
ExecuteSql('String','SELECT name + '', '' FROM sys.databases FOR XML PATH')
-
Click OK
-
In the Value field, enter the database names separated by a comma and space: 'master, tempdb, model, msdb, database1, database2, '
-
Operator is '='
-
Click OK
-
Click OK
Now right-click and evaluate the policy. It's not true because the XML has delimiters:
'<row>master, </row><row>tempdb, </row><row>model, </row><row>msdb, </row><row>database1, </row><row>database2, </row>'
I want to get rid of the </row><row> to reduce the string length to a manageable length. I get the error when I modify the query string to this:
ExecuteSql('String','SELECT name + '', '' FROM sys.databases FOR XML PATH('' '')')
The error is
Exception encountered while executing policy 'List Databases'. Unclosed quotation mark after the character string ')'. Incorrect syntax near ')'. Microsoft SQL Server, Error: 105)
Why does it detect a quotation mark mismatch?
For the short lists, I added the </row><row> delimiters to the comparison string. However, I have a long list that is too long and truncates the return string with delimiters.
This is on SQL Server 2019
英文:
I want a SQL Server Management Policy to check the values in a delimited list returned by FOR XML. The problem is that "FOR XML ('')" will throw a SQL error.
I may not post screenshots or site-specific details so I will demonstrate the error using a simple example:
-
In SQL Server Management Studio, open Object Explorer, expand Management, expand Policy Management, expand Policies to see the existing policies.
-
Right click Policies and choose New Policy...
-
Give it a name: List Databases
-
Click Check Condition and then New Condition
-
Name the condition: List Databases
-
Click the ellipsis button to the right of the Expression Field
-
Type in this statement:
ExecuteSql('String','SELECT name + '', '' FROM sys.databases FOR XML PATH')
-
Click OK
-
In the Value field, enter the database names separated by a comma and space: 'master, tempdb, model, msdb, database1, database2, '
-
Operator is '='
-
Click OK
-
Click OK
Now right-click and evaluate the policy. It's not true because the XML has delimiters:
'<row>master, </row><row>tempdb, </row><row>model, </row><row>msdb, </row><row>database1, </row><row>database2, </row>'
I want to get rid of the </row><row>
to reduce the string length to a manageable length. I get the error when I modify the query string to this:
ExecuteSql('String','SELECT name + '', '' FROM sys.databases FOR XML PATH('''')')
The error is
> Exception encountered while executing policy 'List Databases'. Unclosed quotation mark after the character string ')'. Incorrect syntax near ')'. Microsoft SQL Server, Error: 105)
Why does it detect a quotation mark mismatch?
For the short lists, I added the </row><row>
delimiters to the comparison string. However, I have a long list that is too long and truncates the return string with delimiters.
This is on SQL Server 2019
答案1
得分: 1
You can use FOR XML PATH('')
to get text without XML tags.
Note the extra use of a subquery and , TYPE).value
to de-escape the XML.
ExecuteSql('String','SELECT (SELECT name + '', '' FROM sys.databases FOR XML PATH(''), TYPE).value(''text()[1]''))'
But since you are on SQL Server 2019, you can just use STRING_AGG
ExecuteSql('String','SELECT STRING_AGG(name, '', '') FROM sys.databases')
Note that this version does not have a trailing ,
英文:
You can use FOR XML PATH('')
to get text without XML tags.
Note the extra use of a subquery and , TYPE).value
to de-escape the XML.
ExecuteSql('String','SELECT (SELECT name + '', '' FROM sys.databases FOR XML PATH(''''), TYPE).value(''text()[1]'')')
But since you are on SQL Server 2019, you can just use STRING_AGG
ExecuteSql('String','SELECT STRING_AGG(name, '', '') FROM sys.databases')
Note that this version does not have a trailing ,
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论