How to Create Condition and Policy in SQL Server 2012 / Create a Policy for Naming Convention of Stored Procedure

This article will cover most salient feature of SQL Server, Policy based administrative management of SQL Server to create a condition at server level.
In almost all of the projects we follow some standards for naming convention, so to avoid redundant naming of objects like stored procedure we can create a policy for naming convention.
I have been working with SQL Server from past one year and we always follow naming convention, so it will be better if admin create one policy for naming convention so that no developer can miss it.

Policies

Policy-Based Management is a new management feature introduced in SQL Server 2008. Policy-Based Management allows Database Administrators to define a set of policies that can control many different aspects of SQL Server. Policies can be applied to a single server or to groups of servers. For example, a Database Administrators could define a policy that specifies how a particular configuration option should be set on all the servers in the enterprise.
Before creating policy, first we will create one condition that will be used in policy

How to create Condition

Following are the steps to create condition in SQL Server

1. Go to Management -> Conditions as show below

2. Right click on Condition and click on New Condition, a new window will appear as show below

3. Fill all the fields

1. Write any name in the Name field (for example: StoredProcNamingConvention)
2. Select ‘Stored Procedure’ in the Facet drop down list
3. Keep AndOr blank because this single condition
4. Select Operator LIKE in drop down list
5. Write rule in the value column enclosed with single quotes (for example: ‘usp%’)

Note: This rule is to start stored procedures with ‘usp’, if we want stored procedure to start with ‘usp_’ the value should be ‘usp[_]%’

4. After clicking on OK button condition will be created

How to create Policy

5. Now right click on Policy and click on New Policy

6. New Policy window will open, fill all the fields

1. Enter name of policy
2. Enable it by checking Enabled checkbox
3. Select condition created for naming convention of stored procedure
4. Select Evaluation Mode as On change: prevent
5. Server restriction as None

7. After Clicking on OK button policy will be created

8. Now if we execute following script to create stored procedure ‘sp_MyStoredProc’

CREATE PROCEDURE [dbo].[spMyStoredproc]
AS
BEGIN
  SELECT *
  FROM [dbo].[APIConfiguration]
END

Message from SQL Server for violating policy:

Policy 'StoredProcNamingConventionPolicy' has been violated by 'SQLSERVER:\SQL\ABCServer\DEFAULT\Databases\JPDB\StoredProcedures\dbo.spMyStoredproc'.
This transaction will be rolled back.
Policy condition: '@Name LIKE 'usp%''
Policy description: ''
Additional help: '' : ''
Statement: 'CREATE PROCEDURE [dbo].[spMyStoredproc]
AS
BEGIN
  SELECT *
  FROM [dbo].[APIConfiguration]
END'.
Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65
The transaction ended in the trigger. The batch has been aborted.

9. But if we write stored procedure name which start with ‘usp’, then script will be executed successfully

CREATE PROCEDURE [dbo].[uspMyStoredproc]
AS
BEGIN
  SELECT *
  FROM [dbo].[APIConfiguration]
END

Message

How to Convert Hexadecimal String to Byte Array in C#

We can convert a character to byte in C#, we can also convert a string to byte array using Encoding.ASCII.GetBytes

But if we want to convert a hexadecimal string to byte array then there is no inbuilt method in C#.

We can achieve this in C# by reading characters in the hexadecimal string

Below is a method "HexToByte" which take string as input and return byte array

private static byte[] HexToByte(string hexString)
        {
            int lengthCount = 0;

            // offset value is 2 for removing first two characters '0x' from hexadecimal string
            int offset = 2;
            int byteLength = 0;

            // byte array length will be half of hexadecimal string length
            byte[] bytes = new byte[(hexString.Length - offset) / 2];
            byteLength = bytes.Length;
            for (lengthCount = 0; lengthCount < byteLength; lengthCount++)
            {
                // Adding two nybble from hexadecimal string to create one byte
                bytes[lengthCount] = (byte)((int.Parse(hexString[offset].ToString(), System.Globalization.NumberStyles.HexNumber, CultureInfo.InvariantCulture) << 4) | int.Parse(hexString[offset + 1].ToString(), System.Globalization.NumberStyles.HexNumber, CultureInfo.InvariantCulture));
                offset += 2;
            }
            
            return bytes;
        }

Basic concept of above code is read two characters from hex string and create one byte, because one hexadecimal character is nibble, so to create a byte we can add two nibble

It is useful when you store a file in SQL table and column type is varchar as we have seen in post: How To Store Any File into SQL Database

Using above method "HexToByte" we can convert file stored in sql table of varchar type column in byte array, which can be used to read stored file

How to generate Insert Script of a table in SQL Server

Many times we need to generate SQL Script of table not only schema but with data also. SQL server provide this functionality inbuilt. TO generate SQL script of any object in a DB of SQL server follow below steps Generate Script for an object

Follow below steps to generate script of table with data

Right click on DB -> Task -> GenerateScripts

A new Window will open as shown below, then click on 'Next' button

After clicking on Next button, a new wizard will open for selecting object for which you want to create script, select second radio button for selecting specific database object and then select the check-box of your table as shown below

After clicking on 'Next' button, Click on 'Advance' button

In the advance option change general settings of 'Types of data to script' -> Schema and data and then click on Ok button

After clicking on Ok button, you can change directory path where you want to store your script and then click on 'Next' button

Click on 'Next' button

Click finish button, your script will be stored in the given location

Open the script in SQL Server