SQL Server – Error While Enabling CDC on a Table – The specified ‘@server’ is invalid (valid values are returned by sp_helpserver)

Every environment is different and it is no doubt some of these environment changes can give you results that are not expected. Recently one of my blog reader followed below blogs:

and they reported that when he is trying to enable CDC for a table, he was getting below error:

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 623
Could not update the metadata that indicates table [HumanResources].[Shift] is enabled for Change Data Capture. The failure occurred when executing the command ‘[sys].[sp_cdc_add_job] @job_type = N’capture”. The error returned was 22836: ‘Could not update the metadata for database AdventureWorks2014 to indicate that a Change Data Capture job has been added. The failure occurred when executing the command ‘sp_add_jobstep_internal’. The error returned was 14234: ‘The specified ‘@server’ is invalid (valid values are returned by sp_helpserver).’. Use the action and error to determine the cause of the failure and resubmit the request.’. Use the action and error to determine the cause of the failure and resubmit the request.

So the best way to learn these new things is by exploring the events how it happened. Below is the script he was using (taken from my blog)

-- You can run this stored procedure in the context of each database to enable CDC at database level.
-- (The following script will enable CDC in AdventureWorks2014 database)
USE AdventureWorks2014
GO
EXEC sys.sp_cdc_enable_db
GO
-- Following script will enable CDC on HumanResources.Shift table.
USE AdventureWorks2014
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'HumanResources',
@source_name   = N'Shift',
@role_name     = NULL
GO

First command was working fine. Error was raised by second command. I asked him to capture the profiler and share with me. I found that first SQL Server get instance name using below query:

SELECT @server = CONVERT(SYSNAME,SERVERPROPERTY('ServerName'))

The value is then passed to create the job for CDC. In procedure sp_verify_jobstep, below is the condition which was failing.

IF (@server IS NOT NULL) AND (NOT EXISTS (SELECT *
FROM MASTER.dbo.sysservers
WHERE (UPPER(srvname) = UPPER(@server))))
RAISERROR(14234, -1, -1, '@server', 'sp_helpserver')

Notice that this is the same error in error message (which I have highlighted) I asked him to check and he verified that SQL Server name was changed but sp_dropserver and sp_addserver was not executed. Here is the command to fix the issue. Please change the parameter values as per your SQL Instance.

sp_dropserver 'HostName\InstanceName_incorrect'
GO
sp_addserver 'HostName\InstanceName', 'local'
GO

In short, error is caused due to mismatch in value between SERVERPROPERTY(‘ServerName’)) and master.dbo.sysservers

Have you ever done similar troubleshooting in your environments using Profiler? Do let me know what you did and what was the learning. I am sure a lot of readers are going to benefit from your stories and scenarios.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Excellent Event – TechEd Sri Lanka – Feb 8, 2010

TechEd Sri Lanka was held at Waters Edge, Colombo between Feb 8 and Feb 10, 2010. It was one of the largest successful technical event in Sri Lanka. I was extremely surprised to how technically sound this event was and how excited the TechEd attendees were.

techsl SQLAuthority News   Excellent Event   TechEd Sri Lanka   Feb 8, 2010

I presented there on two different subject. They were very enthusiastic and had so many interesting questions during the session. One of my session received rating of 8.9. I must thank you to all the attendees for sending their feedback and appreciating my session. Both of my session have received feedback above average.

The Other Side of SQL Server Index : Advanced Solutions to Ancient Problem (Rating 8.9)
SQL Server Index is very powerful tool and when in hand of the less skilled expert, the same tool can be very dangerous for performance and can kill server. This session touches the root base of the how incorrect usage of Index can reduce performance and what is the correct way to implement indexing solution. A problem addressed in the session may be age old but the solutions are looked into with the focus of latest versions of SQL Server.

The history of the Log: Change Data Capture (CDC) (Rating 7.8)
Learn to capture the history of data using CDC. An age old method of writing queries and triggers to capture change in database table is replaced with much powerful asynchronous method of change data capture (CDC). All attendees will learn how to configure CDC in less than 60 seconds.

Selected feedback from both of the sessions

  • I got massive knowledge from there Superb
  • Brilliant effort taken to educate us~! surely gonna make my life easier at office!!
  • The best presenter is this person
  • Presentation was very interesting and would be able to gather the points he talk easily
  • Great demo and presentation, cool stuffs.

Along with me there will be other two Solid Quality Mentors – Rushabh Mehta and Joy Ratnayake presenting on various topics. During my visit to Sri Lanka I had received the opportunity to meet MVP from Sri Lanka and I was able to attend the Sri Lanka SQL Server User Group Meeting as well.

Sri Lanka is wonderful place and I had great time to visit the country as well attend TechED Event. I am eagerly waiting for TechEd to come again in 2011.

TechEd SriLanka (1) SQLAuthority News   Excellent Event   TechEd Sri Lanka   Feb 8, 2010

TechEd SriLanka

Pinal at TechEd SriLanka

Pinal at TechEd SriLanka

Keynote at TechEd Sri Lanka

Keynote at TechEd Sri Lanka

Pinal Presenting at TechEd Sri Lanka

Pinal Presenting at TechEd Sri Lanka

Pinal Presenting at TechEd Sri Lanka

Pinal Presenting at TechEd Sri Lanka

Rushabh Mehta at TechEd Sri Lanka

Rushabh Mehta at TechEd Sri Lanka

Pinal Presenting at TechEd Sri Lanka

Pinal Presenting at TechEd Sri Lanka

UG Leaders and MVPs of Sri Lanka - Joy, Chandana, Gogula, Dinesh

UG Leaders and MVPs of Sri Lanka - Joy, Chandana, Gogula, Dinesh

Sri Lanka SQL Server User Group

Sri Lanka SQL Server User Group

Reference: Pinal Dave (http://blog.SQLAuthority.com)



SQL SERVER – CDC and TRUNCATE – Cannot truncate table because it is published for replication or enabled for Change Data Capture

Few days ago, I got the great opportunity to visit Bangalore Infosys. Please read the complete details for the event here: SQLAuthority News – Notes from TechDays 2009 at Infosys, Bangalore. I mentioned during the session that CDC is asynchronous and it reads the log file to populate its data. I had received a very interesting question during the session.

The question is as follows: does CDC feature capture the data during the truncate operation? Answer: It is not possible or not applicable. Truncate is operation that is not logged in the log file, and if one tries to truncate the table that is enabled for CDC, it will right away throw the following error.

Msg 4711, Level 16, State 1, Line 1
Cannot truncate table because it is published for replication or enabled for Change Data Capture.

Let us create the scenario that will generate the above error.

/***** Set up TestTable *****/
USE AdventureWorks
GO
-- Create Table
CREATE TABLE dbo.TestTable (ID INT)
GO
-- Insert One Hundred Records
-- INSERT 1
INSERT INTO dbo.TestTable (ID)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
/***** Enable CDC *****/
--The following script will enable CDC in AdventureWorks database.
USE AdventureWorks
GO
EXEC sys.sp_cdc_enable_db
GO
--Following script will enable CDC on dbo.TestTable table.
USE AdventureWorks
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'TestTable',
@role_name = NULL
GO
/* Attempt to Truncate Table will thrown following error
Msg 4711, Level 16, State 1, Line 1
Cannot truncate table 'TestTable' because it is published for replication or enabled for Change Data Capture.

*/
TRUNCATE TABLE TestTable
GO
/***** Clean up *****/
--Disabling Change Data Capture on a table
USE AdventureWorks;
GO
EXECUTE sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'TestTable',
@capture_instance = N'dbo_TestTable';
GO
--Disable Change Data Capture Feature on Database
USE AdventureWorks
GO
EXEC sys.sp_cdc_disable_db
GO
-- Drop Table
DROP TABLE TestTable
GO

The workaround for this is to either use DELETE statement instead of TRUNCATE or to disable CDC first and then enable the CDC on the table after the TRUNCATE operation is completed.

I am interested to know if you have any interesting fact of above CDC. I will publish the information on this blog with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)