Feeds:
Posts
Comments

Archive for May, 2014

In my earlier article, I have explained that creating a directory / sub directory in any file table is same like creating a directory / sub directory in windows itself. However, sometimes you need to create these directories and sub directories via T-SQL. I came across a case recently where I had to create a series of sub directories inside a directly using T-SQL.

Prerequisite :
I strongly recommend that you read the given below articles to have a clear understanding about FileTables.

  1. FileTables – Prerequisites
  2. FileTables – Data Definition Language (DDL)
  3. FileTables – Data Manipulation Language (DML)

Let me explain the solution step by step.

Step 1 :
First of all you need to create a sequence (A new object shipped with SQL Server 2012) to generate a series of IDs. However you can use any other techniques to create these series.

--This script is compatible with SQL Server 2012 and above.
--DROP SEQUENCE [dbo].[NewID]
--GO
CREATE SEQUENCE [dbo].[NewID]
 AS [bigint]
 START WITH 100000000
 INCREMENT BY 1
 CACHE
GO

Step 2 :
In this step, you need to create the given below procedure that can generate a new ID for your sub directory. Remember that filetable maintains directory and sub directory IDs in a hierarchy ID datatype. So you must get the parent folder ID (directory hierarchy ID) in order to create a child folder (sub directory). You can get further detail about hierarchy ID here. This stored procedure is self explanatory.

--This script is compatible with SQL Server 2012 and above.
--DROP PROCEDURE dbo.GetNewPathLocator
--GO
CREATE PROCEDURE dbo.GetNewPathLocator
@MainFolderID HIERARCHYID,
@SubDirectoryPath VARCHAR(MAX) OUTPUT

AS
BEGIN       

	DECLARE @FirstSeqNum sql_variant,
	        @LastSeqNum  sql_variant

     EXEC sys.sp_sequence_get_range
     @sequence_name = N'dbo.NewID'
   , @range_size = 3
   , @range_first_value = @FirstSeqNum OUTPUT
   , @range_last_value = @LastSeqNum OUTPUT 

    SELECT @SubDirectoryPath = CONCAT(COALESCE(@MainFolderID.ToString(),'/'),
	CONVERT(VARCHAR(20),@FirstSeqNum) ,'.',
	CONVERT(VARCHAR(20),Convert(BIGINT,@FirstSeqNum)+1) ,'.',
	CONVERT(VARCHAR(20),@LastSeqNum) ,'/')

END
GO

Step 3 :
Now, it is time to create a sub directory in any directory using T-SQL. I already created a directory inside a filetable as shown below.

creating sub directory in filetable

Let us create a sub directory inside that directory.

--This script is compatible with SQL Server 2012 and above.
DECLARE @MainFolderPath AS HIERARCHYID
--You must have the given below file table (dbo.DataBank)
--and directory (IT) inside file table in the existing database.
SELECT @MainFolderPath=path_locator FROM dbo.DataBank
WHERE [name]='IT'

DECLARE @SubDirectoryPath varchar(max)
EXEC dbo.GetNewPathLocator
  @MainFolderID=@MainFolderPath
, @SubDirectoryPath = @SubDirectoryPath OUTPUT 

--SELECT @SubDirectoryPath

INSERT INTO dbo.DataBank (name,path_locator,is_directory,is_archive)
VALUES ('sub directory', @SubDirectoryPath, 1, 0);
GO

Given below is the new sub directory created inside IT folder via T-SQL.

creating sub directory in filetable 2

Conclusion :
Remember, filetable keeps directory and sub directory IDs in Hierarchy ID datatype. So you must go through this concept. In addition, the whole process is self explanatory.

Let me know if you came across this situation and how you handled it.

Read Full Post »

Change Data Capture (CDC) has been discussed in detail in my earlier articles. In this article, I will discuss an error message that I came across while disabling the CDC for a table due to insufficient parameters.

error

Let me explain this error in detail :

Message Number: 22960

Severity : 16

Error Message: Change data capture instance ‘%s’ has not been enabled for the source table ‘%s.%s’. Use sys.sp_cdc_help_change_data_capture to verify the capture instance name and retry the operation.

Error Generation:

Let me DISABLE CDC feature on a particular table.

USE AdventureWorks2012
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name   = N'Department',
@capture_instance = NULL
GO

Msg 22960, Level 16, State 1, Procedure sp_cdc_disable_table_internal, Line 75
Change data capture instance ‘(null)’ has not been enabled for the source table ‘HumanResources.Department’. Use sys.sp_cdc_help_change_data_capture to verify the capture instance name and retry the operation.

Ooopps…… I am unable to disable CDC on this table. How to fix it ?

Resolution:
The resolution is very simple because partially it is explained in the error message itself. Let me fix this error step by step.

Step 1 :
First of all, you need to execute sys.sp_cdc_help_change_data_capture (A system stored procedure) in order to find the capture instance name of that particular table name as shown below.

USE AdventureWorks2012
GO
sys.sp_cdc_help_change_data_capture
GO
--OUTPUT

error message 22960.1

Step 2 :
The next step is to get the capture instance name of the particular table from the above result set and pass it in the sys.sp_cdc_disable_table (A system stored procedure) to disable the CDC feature from that table as shown below.

USE AdventureWorks2012
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name   = N'Department',
@capture_instance = N'HumanResources_Department'
GO

Conclusion :

Remember, whenever you need to disable CDC feature from any table, find out the capture instance name of the table and then disable it using sys.sp_cdc_disable_table in order to avoid this error.

Read Full Post »

In my earlier articles, I discussed & demonstrated all basic activities in Change Data Capture (CDC) in any SQL Server database. However, I did not discuss about the retention period of CDC data in the database. In most cases, we need to modify the retention period due to our business requirements. I came across this query many times, if we can modify retention period, if Yes, how ?

BY DEFAULT, CDC configure the data retention period of 3 days. In other words, CDC keeps all the data changes history for 3 days ONLY and the rest will be cleaned (deleted). In most of my clients, I configured CDC for few tables with the retention period of 10 days and few of them leave it as a default (3 days).

Let me explain how to view / modify the retention period of CDC from in few easy steps.

Step 1 – View the existing RETENTION period in CDC:
First of all, you should check the existing retention period of CDC. It is also important to know that CDC keeps the retention period in minutes. Given below script will show the retention period in minutes as well as days by using dbo.cdc_jobs (a change data capture system table).

--This script is compatible with SQL Server 2008 and above.
USE msdb
GO
SELECT [retention] As [Retention period in minutes]
,[retention]/60/24 As [Retention period in days]
FROM
dbo.cdc_jobs
WHERE job_type ='cleanup'
GO
--OUTPUT

Change data capture.4.4_part1

Step 2 – Modify the RETENTION period in CDC :
As you can see in the above result that the retention period is 3 days (4320 minutes). Lets modify it to 10 days by using sys.sp_cdc_change_job (a system stored procedure of CDC).

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
DECLARE @New_retention_period_in_minutes AS SMALLINT
DECLARE @New_retention_period_in_days AS TINYINT

--Set the retention period for 10 days
SET @New_retention_period_in_days = 10

--Convert 10 days into minutes
SET @New_retention_period_in_minutes= @New_retention_period_in_days*60*24

--Select the total number of minutes in 10 days to check.
SELECT @New_retention_period_in_minutes
As [Retention period in minutes]

--Update minutes in the CDC job
EXECUTE sys.sp_cdc_change_job
    @job_type = N'cleanup',
    @retention = @New_retention_period_in_minutes;
GO
--OUTPUT

Change data capture.4.4_part2

Step 3 – Verify the RETENTION period in CDC:
You need to execute the same script as Step 1 but the output would be different this time, as we have successfully updated the retention period to 10 days in Step 2.

--This script is compatible with SQL Server 2008 and above.
USE msdb
GO
SELECT [retention] As [Retention period in minutes]
,[retention]/60/24 As [Retention period in days]
FROM
dbo.cdc_jobs
WHERE job_type ='cleanup'
GO
--OUTPUT

Change data capture.4.4_part33

An update of my blog is available at my twitter or you can like my Facebook page or subscribe via email by mentioning your email address in the ‘follow blog’ section.

Read Full Post »

In my earlier two articles, I demonstrated how to enable and utilize the Change Data Capture (CDC) in any SQL Server database with few simple steps. However if you enable this feature for testing purpose in the test server, or sometimes you enable it on the wrong database / table  by mistake, so you need to disable it by following few steps as shown below. Before proceeding with the disability of CDC feature in the database, you must make sure that you DO NOT need the changes recorded by CDC because once you disable it, the CDC data will no longer be available.

Let me explain how to disable the CDC from any respective table & database in few easy steps.

Step 1 – Validating SQL Server Agent:
First of all you must make sure that your SQL Server Agent is UP and RUNNING as shown below.

Change data capture.3.1_part3

Step 2 – Find capture instance name :
The next step is to find the name of the capture instance (audit table) of any particular table that you need to disable for CDC. In order to achieve it, you need to execute sys.sp_cdc_help_change_data_capture (system stored procedure) to get all the list of CDC enabled objects along with its capture instance name as shown below.

Please note, if you need to disable CDC for database ONLY kindly skip this step.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
sys.sp_cdc_help_change_data_capture
GO
--OUTPUT

Change data capture.3.2_part3

Step 3 – Disable CDC for tables:
Once you have the list, you need to note the capture instance name and execute the given below script with schema, table & capture instance name.
This script will disable the CDC feature for the particular table and you will lose all the CDC data for the given below table.

Please note, if you need to disable CDC for database ONLY kindly skip this step.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO

EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name   = N'Department',
@capture_instance = N'HumanResources_Department'
GO
--OUTPUT

Change data capture.3.3_part3

Step 4 – Disable CDC for database:
In this step, we will disable the CDC feature from any database. Given below is the script that will NOT only disable CDC for any database but also will disable the CDC for all tables in that database. So be careful while executing this statement.

--This script is compatible with SQL Server 2008 and above.

USE AdventureWorks2012
GO

EXEC sys.sp_cdc_disable_db
GO
--OUTPUT

Change data capture.3.4_part3

Let me know if you enabled CDC in your SQL Server and its feedback.

An update of my blog is available at my twitter or you can like my Facebook page or subscribe via email by mentioning your email address in the ‘follow blog’ section.

Read Full Post »