Sample Header Ad - 728x90

Issue with table naming policy with PBM - can't create it passed in the condition or not

1 vote
0 answers
72 views
I used PBM to create a condition to blocking create tables if nomenclature no haven't the prefix tbl: EXEC msdb.dbo.sp_syspolicy_update_condition @condition_id=24, @description=N'Necessário acrescentar o prefixo ''tbl''', @facet=N'IMultipartNameFacet', @expression=N' Bool LIKE 2 String Name String System.String tbl% ', @is_name_condition=2, @obj_name=N'tbl%' GO I applied this condition into my tables in the DB "cadastro" EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=65, @enabled=False EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=65, @type_skeleton=N'Server/Database/Sequence', @condition_name=N'' EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=65, @type_skeleton=N'Server/Database', @condition_name=N'' ... EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=72, @enabled=False EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=72, @type_skeleton=N'Server/Database/XmlSchemaCollection', @condition_name=N'' EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=72, @type_skeleton=N'Server/Database', @condition_name=N'' GO EXEC msdb.dbo.sp_syspolicy_update_policy @policy_id=37, @execution_mode=1, @schedule_uid=N'00000000-0000-0000-0000-000000000000' GO I setted **Evaluation Mode** like **On change: prevent**. If I try execute this script: create table tblteste( col1 int ); SSMS will return this error: Policy 'NomeDeTabela' has been violated by 'SQLSERVER:\SQL\XXX-XXX-XX\DEFAULT\Databases\cadastro\Tables\dbo.tblteste'. This transaction will be rolled back. Policy condition: '@Name LIKE 'tbl%'' Policy description: '' Additional help: 'Ausência de prefixo 'tbl' no nome da tabela' : '' Statement: 'create table tblteste( col1 int )'. Msg 515, Level 16, State 2, Procedure msdb.sys.sp_syspolicy_execute_policy, Line 69 [Batch Start Line 0] Cannot insert the value NULL into column 'target_query_expression', table 'msdb.dbo.syspolicy_policy_execution_history_details_internal'; column does not allow nulls. INSERT fails. The statement has been terminated. The nomenclature of table meets established policy. I followed step-by-step from Microsoft SQL Server documentation (https://learn.microsoft.com/en-us/sql/relational-databases/policy-based-management/lesson-2-create-and-apply-a-naming-standards-policy?view=sql-server-ver15) but the problem persist. Name and version information: Microsoft SQL Server Management Studio 14.0.17289.0 Microsoft Analysis Services Client Tools 14.0.1016.283 Microsoft Data Access Components (MDAC) 10.0.14393.0 Microsoft MSXML 3.0 6.0 Microsoft Internet Explorer 9.11.14393.0 Microsoft .NET Framework 4.0.30319.42000 Operating System 6.3.14393 Anywhone pass this problem before?
Asked by Leonardo Lacerda (324 rep)
Oct 17, 2019, 02:09 PM
Last activity: Oct 17, 2019, 04:55 PM