Transact SQL – Drop Temp Table if it it exists

Background

Reviewing some Transact SQL Code and saw a code block that works well in exception handling, but can have a bit of side effect in Transact SQL.

Code

Original Code

SQL


BEGIN TRY

    DROP TABLE #pollingData;
     
END TRY
BEGIN CATCH
END CATCH;

 

Explanation

When the temp table does not exist, an error is raised.

Because the drop table is enclosed in a try/catch block the error is gracefully handled by the system.

Noise

But, yet there is a bit of silent noises.

SQL Server Profiler

Image

Tabulated
  1. Event
    • Exception
      • Error :- 3701
      • Severity :- 11
      • State :- 5
    • User Error Message
      • Error :- 3701
      • Severity :- 11
      • State :- 5

Trace Events

Image

Explanation
  1. Events
    • objectName = error_reported
      • eventData
      • error Number :- 3701
      • severity :- 11
      • message :- Cannot drop the table ‘#pollingData’, because it does not exist or you do not have permission.
      • sqlStatement :- empty

 

Revised Code

Check If Table Exists, before attempt to drop

SQL

BEGIN TRY

	if object_id('tempdb..#pollingData') is not null
	begin
		DROP TABLE #pollingData;
    end 

END TRY

BEGIN CATCH

END CATCH;

Drop Table, If Exists

In MS SQL Server v2016 and later versions, we can use the new “drop object if exists” conditional statement…

SQL


drop table if exists #pollingData;

 

Other Errors

There are other errors that can be avoided with dropping an object only upon validation that it exists.

SET XACT_ABORT ON;

If you use the set xact_abort on directive, your code will abort upon running into the error mentioned above.

Error Message

Msg 3930, Level 16, State 1 ..
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

 

What datatype is the null value?

Background

A few weeks ago, I ran into a Transact SQL problem and I was forced to ask myself why two database objects were not compatible.

In the rest of this post, I will share the “foundational”  scenario and try to start answering the question, what datatype is Null.

 

Scenario

Our baseline scenario is to join data from two data-sources.  In our case, data from two sources have being brought into our databases and are now available as two tables.

 

Lab

Let us lay out our database objects.

 

DDL – Tables

 

Let us create our two tables, datatype4null.person and datatype4null.organization.

 

Table – datatype4Null.person

set noexec off
go

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop table datatype4Null.person
*/
if object_id('datatype4Null.person') is not null
begin
    set noexec on
end
go


create table [datatype4Null].[person]
(
      [id] int not null identity(1,1)

    , [name] varchar(255)

    , [employer]  varchar(255) null

    , [dateofBirth]  datetime not null

    , [age] as datediff(year, [dateofBirth], getdate())

    , [gender] bit null

    , [uniqueID]   uniqueIdentifier not null
            constraint defaultDatatype4NullPersonUniqueID default NEWID()

)

go

set noexec off
go

 

Table – datatype4Null.organization

set noexec off
go

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go

/*
    drop table datatype4Null.organization
*/
if object_id('datatype4Null.organization') is not null
begin
    set noexec on
end
go


create table [datatype4Null].[organization]
(
      [id] int not null identity(1,1)

    , [name] varchar(255)

    , [uniqueID]   uniqueIdentifier not null
            constraint defaultDatatype4NullOrganizationUniqueID default NEWID()


)
go

set noexec off
go

 

 

DML – Insert data

Let us insert data into our two tables.

Table – datatype4Null.person – DML

Insert data into the person table.

set nocount on;
go

use [tempdb]
go
 
truncate table [datatype4Null].[person]
go

insert into [datatype4Null].[person]
([name], [employer], [dateofBirth], gender)
values ('Mustapha Thompson', 'NBC News', '4/7/1990', 1)
;


insert into [datatype4Null].[person]
([name], [employer], [dateofBirth], gender)
values ('Stephanie Lo', 'Universal Studios', '3/10/1991', 0)
;

 

 

Table – datatype4Null.organization – DML

Insert data into the organization table.

set nocount on;
go

use [tempdb]
go
 
truncate table [datatype4Null].[organization]
go

insert into [datatype4Null].[organization]
([name])
values ('Staples')
;

 

DDL – Union View

As we will like to expose the data from our datasources as if they were from a single source, we will use a view to do so.

View – datatype4Null.v_entity_basedonTables

Here is the view that uses a union to merge the data from our two tables.

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_entity_basedOnTables]
*/
if object_id('[datatype4Null].[v_entity_basedOnTables]') is  null
begin
    exec('create view [datatype4Null].[v_entity_basedOnTables] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_entity_basedOnTables]
as

    select 
              1 as [originatorCode]
            , tblP.id
            , tblP.uniqueID
            , tblP.name
            , tblP.employer
            , tblP.dateofBirth
            , tblP.age
            , tblP.gender

    from    [datatype4Null].[person]  tblP
    
    union all

    select 
              2 as [originatorCode]
            , tblO.id
            , tblO.uniqueID
            , tblO.name
            , null as employer
            , null as dateofBirth
            , null as age
            , null as gender

    from    [datatype4Null].[organization]  tblO

go

 

 

 

DML – Fetch data from Union View

 

Let us fetch data from our union view:
SQL:

select *
from   [datatype4Null].[v_entity_basedOnTables]

 

Output:

datafromUnionOfTwoTables

 

 

So everything is good, we are able to fetch from our union.

 

DDL – View

 

But, what happens if we decide we will need to add a bit of logic and we will place some logic and thus use view instead of tables.

Let us create views rather than tables.

 

View datatype4Null.v_person

 

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_person]
*/
if object_id('[datatype4Null].[v_person]') is  null
begin
    exec('create view [datatype4Null].[v_person] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_person]
as

    select 
              1 as [originatorCode]
            , tblP.id
            , tblP.uniqueID
            , tblP.name
            , tblP.employer
            , tblP.dateofBirth
            , tblP.age
            , tblP.gender


    from    [datatype4Null].[person]  tblP

go

 

View datatype4Null.v_organization

 

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_organization]
*/
if object_id('[datatype4Null].[v_organization]') is  null
begin
    exec('create view [datatype4Null].[v_organization] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_organization]
as
    
    select 
              2 as [originatorCode]
            , tblO.id
            , tblO.uniqueID
            , tblO.name
            , null as employer
            , null as dateofBirth
            , null as age
            , null as gender

    from    [datatype4Null].[organization]  tblO

go

 

 

 

View datatype4Null.v_entity_basedonViews_DatatypeMismatched

 

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_entity_basedOnViews_DatatypeMismatched]
*/
if object_id('[datatype4Null].[v_entity_basedOnViews_DatatypeMismatched]') is  null
begin
    exec('create view [datatype4Null].[v_entity_basedOnViews_DatatypeMismatched] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_entity_basedOnViews_DatatypeMismatched]
as

    select 
              tblP.[originatorCode]
            , tblP.id
            , tblP.uniqueID
            , tblP.name
            , tblP.employer
            , tblP.dateofBirth
            , tblP.age
            , tblP.gender

    from    [datatype4Null].[v_person]  tblP
    
    union all

    select 
              tblO.[originatorCode]
            , tblO.id
            , tblO.uniqueID
            , tblO.name
            , tblO.employer
            , tblO.dateofBirth
            , tblO.age
            , tblO.gender

    from    [datatype4Null].[v_organization]  tblO

go

 

 

Query

Let us query our new Union View; the one that query’s the “Views Union“.

    select *
    from   [datatype4Null].[v_entity_basedOnViews_DatatypeMismatched]

 

Output:

But, we get an error message.

 

Image:

Msg 245, Level 16, State 1, Line 55
Conversion failed when converting the varchar value 'NBC News' to data type int.

 

Textual:

Msg 245, Level 16, State 1

 

 

Error Diagnosis

Our error is traced back to how SQL Server deduces and assigns datatypes to literal and computed columns.

Our view is defined as :

 

alter view [datatype4Null].[v_organization]
as
    
    select 
              2 as [originatorCode]
            , tblO.id
            , tblO.uniqueID
            , tblO.name
            , null as employer
            , null as dateofBirth
            , null as age
            , null as gender

    from    [datatype4Null].[organization]  tblO

go

 

The value null is defined as as int.

Now thinking forward, to get our datatypes we can issue ” sp_help <object-name>

 

Syntax:

exec sp_help '<object-name>'

 

Sample:

exec sp_help '[datatype4Null].[v_organization]'

 

Output :

datatypeforNullValue

 

Explanation:

From the screen shot above, we can see that SQL Server assigns our null value the int data type.

 

Solution

To fix, we will have to match our datatypes using cast or convert.

An example is pasted below:

 

use [tempdb]
go

if schema_id('datatype4Null') is null
begin
    exec('create schema datatype4Null');
end
go


/*
    drop view [datatype4Null].[v_entity_basedOnViews_DatatypeMatched]
*/
if object_id('[datatype4Null].[v_entity_basedOnViews_DatatypeMatched]') is  null
begin
    exec('create view [datatype4Null].[v_entity_basedOnViews_DatatypeMatched] as select 1/0 as [shell] ');
end
go


alter view [datatype4Null].[v_entity_basedOnViews_DatatypeMatched]
as

    select 
              tblP.[originatorCode]
            , tblP.id
            , tblP.uniqueID
            , tblP.name
            , tblP.employer
            , tblP.dateofBirth
            , tblP.age
            , tblP.gender

    from    [datatype4Null].[v_person]  tblP
    
    union all

    select 
              tblO.[originatorCode]
            , cast(tblO.id as int)
            , cast(tblO.uniqueID as uniqueidentifier)
            , cast(tblO.name as varchar)
            , cast(tblO.employer as varchar)
            , cast(tblO.dateofBirth as datetime)
            , cast(tblO.age as int)
            , cast(tblO.gender as bit)

    from    [datatype4Null].[v_organization]  tblO

go

 

Query :

select *
from   [datatype4Null].[v_entity_basedOnViews_DatatypeMatched]

 

Output:

datafromUnionOfTwoViewsUsingMatchedViews

 

Watching / Listening

 

Thanks to God for his Sabbath, my lazy Saturday.

Finally got around to watching Robert Redford’s “Horse Whisperer“.

The movie is almost done, but it is paused as I goggled for which songs goes “I was looking for a soft place to fall“.


A Soft Place To Fall” was written by Allison Moorer, Gwil Owen.

Daylight has found me here again
You can ask me anything, but where I’ve been
Things that used to matter seem so small
When you’re looking for a soft place to fall

Don’t misunderstand me, baby, please
I didn’t mean to bring back memories
You should know the reason why I called
I was looking for a soft place to fall

 

Summary

I think Allison Moorer &  Gwil Owen song’s lyrics makes for a good parting.

Incidentally, Demaryius Thomas story on ESPN ( http://espn.go.com/espn/feature/story/_/id/11830457/denver-broncos-demaryius-thomas-dreams-reuniting-estranged-family ) ended up along the same line.

Eli Saslow closed up his moving tribute by evoking Demaryius’s words :

“I’m making it my responsibility to give her a soft place to land,” he said.

Now that we have taking care of one pallid error message, let us return to things that matter.

 

 

 

Technical: Microsoft – SQL Server – Linked Server (LoopBack) – Errors – “Transaction context in use by another session” in InsteadofTrigger

Technical: Microsoft – SQL Server – Linked Server (LoopBack) – Errors – “Transaction context in use by another session” in InsteadofTrigger

Introduction

So basically we have a SQL Server Database Application and our topology involves a few DB SQL Server Instances.  During development, we are trying to constrain each Development\Developer environment to a single (lone) server.

Processing

Processing – Linked Server – Loopback (Create)



use master
go

/*
	select * from sys.servers
*/

declare @serverNameLocal sysname
declare @lsRemote        sysname
declare @tab		 sysname

set @lsRemote = 'LSREMOTE'
set @serverNameLocal = cast(serverproperty('servername') as sysname)
set @tab = char(9)

if exists
	(
	   select *
	   from   sys.servers tblServer
	   where  tblServer.name = @lsRemote
	)
begin

	print @tab + 'Dropping server ' + @lsRemote + ' ....'

	EXEC master.dbo.sp_dropserver @server=@lsRemote, @droplogins='droplogins'

end

print  @tab + 'Adding Self Server as Linked Server ... ' + @lsRemote

EXEC master.dbo.sp_addlinkedserver 
		  @server = @lsRemote
		, @srvproduct=@serverNameLocal
		, @provider=N'SQLOLEDB'
		, @datasrc=@serverNameLocal

EXEC master.dbo.sp_serveroption @server=@lsRemote
           , @optname=N'collation compatible', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'dist', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'pub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'rpc', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'rpc out', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'sub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'connect timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'collation name', @optvalue=null

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'lazy schema validation', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'query timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'use remote collation', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@lsRemote
       , @optname=N'remote proc transaction promotion'
       , @optvalue=N'false'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @lsRemote
       , @locallogin = NULL 
       , @useself = N'True'

print  @tab + 'Added Self Server as Linked Server ' + @lsRemote

Processing – Create Database (DBLab)


use [master]
go

if db_id('DBLab') is null
begin

	print 'Creating DB - DBLab .... '

	exec('create database [DBLab]')

	exec('ALTER DATABASE [DBLab] set recovery simple')

	print 'Creating DB - DBLab'

end
go

Processing – Table – dbo.whatisonyourmind (Create)

set noexec off
go

use [DBLab]
go

if object_id('dbo.whatisonyourmind') is not null
begin

	set noexec on
end
go

create table dbo.whatisonyourmind
(
	  [post] nvarchar(600)

	, [addedBy] sysname not null 
		constraint defaultWhatIsOnYourMindAddedBy default SYSTEM_USER

	, [addedOn] datetime not null 
		constraint defaultWhatIsOnYourMindAddedOn default getutcdate()

)

go

set noexec off
go

Processing – Table – dbo.whatisonyourmindArchive (Create)


set noexec off
go
use [DBLab]
go

if object_id('dbo.whatisonyourmindArchive') is not null
begin
	set noexec on
end
go

/*
	drop table dbo.whatisonyourmindArchive
*/
create table dbo.whatisonyourmindArchive
(

	  [id] bigint not null identity(1,1)

	, [post] nvarchar(600)

	, [addedBy] sysname not null 

	, [addedOn] datetime not null 

	, [modificationType] char (1) not null

	, [archivedBy] sysname not null 
		constraint defaultWhatIsOnYourMindArchivedBy default SYSTEM_USER

	, [dateArchived]  datetime not null 
		constraint defaultWhatIsOnYourMindArchivedOn default getutcdate()

)

go

set noexec off
go

Trigger

Here is a trigger the captures changes occurring on dbo.whatisonyourmind and propagates them unto dbo.whatisonyourmindArchive.


set noexec off
go

use [DBLab]
go

if object_id('dbo.TR_InsteadOf_Whatisonyourmind', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_Whatisonyourmind
end
go

if object_id('dbo.TR_InsteadOf_WhatisonyourmindLS', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_WhatisonyourmindLS
end
go

CREATE TRIGGER dbo.TR_InsteadOf_Whatisonyourmind
ON dbo.WhatIsOnYourMind
INSTEAD OF INSERT, UPDATE, DELETE
AS
begin

        --post changes unto dbo.whatisonyourmindArchive
	insert into dbo.whatisonyourmindArchive
	(
		  [post]

		, [addedBy]

		, [addedOn]

		, [modificationType]

	)
	select 
		  isNull(tblInsert.[post], tblDeleted.[post])

		, isNull(tblInsert.[addedBy], tblDeleted.[addedBy])

		, isNull(tblInsert.[addedOn], tblDeleted.[addedOn])

		, 
			case

				when ( 

				           (tblInsert.[post] is not null)
				        and (tblDeleted.[post] is null)

				      ) then  'I'

				when ( 

				               (tblInsert.[post] is null)
					   and (tblDeleted.[post] is not null)

				     ) then  'D'

				else 'U'

			end

	from   inserted tblInsert

		  full join deleted tblDeleted

		    on tblInsert.[post] = tblDeleted.[post]

        /* process insert/updates as 1 */
	MERGE INTO dbo.whatisonyourmind AS Target
	USING (
			select *
			from   inserted tblInsert
          )
			AS Source ([post], [addedBy], [addedOn] )
				ON Target.[addedBy] = Source.[addedBy]

	WHEN MATCHED THEN

		UPDATE 
		SET 
				  [post] = Source.[post]
				, [addedOn] = SOurce.[addedOn]

	WHEN NOT MATCHED BY TARGET THEN
		INSERT (post)
		VALUES (post)

	;

        /* remove records that are being deleted */
	delete tblTarget

	from   dbo.whatisonyourmind tblTarget

	where  tblTarget.[post] = 
			(

			   select  tblDeleted.[post]

			   from    deleted tblDeleted
											                           left outer join inserted tblInsert
													              on tblDeleted.[post] = tblInsert.[post]
										        			              and tblDeleted.[addedBy] = tblInsert.[addedBy]

			    where tblInsert.[post] is null

			)

end
go

Data Changes

Let us instigate data changes



use [DBLab]
go

/*

	truncate table [dbo].[whatisonyourmindArchive];
	truncate table [dbo].[whatisonyourmind];

*/

insert into [dbo].[whatisonyourmind]
([post])
values ('Dev1819')

insert into [dbo].[whatisonyourmind]
([post])
values ('DEV1819.2')

update [dbo].[whatisonyourmind]
set    [post] = 'DEV1819.3'
where  [addedBy] = SYSTEM_USER

delete
from   [dbo].[whatisonyourmind]
where  [post] = 'DEV1819.3'

select *
from   [dbo].[whatisonyourmind]

select *
from   [dbo].[whatisonyourmindArchive]

everything is good!

Trigger – Link Server

A few months later we get a request that we should decouple our OLTP (dbo.whatisonyourmind) and OLAP System (dbo.whatisonyourmindArchive).

And, we created the Linked Server mentioned earlier and change the trigger code a bit.

Here is our new trigger:



set noexec off
go

use [DBLab]
go

if object_id('dbo.TR_InsteadOf_Whatisonyourmind', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_Whatisonyourmind
end
go

if object_id('dbo.TR_InsteadOf_WhatisonyourmindLS', 'TR') is not null
begin
	drop trigger dbo.TR_InsteadOf_WhatisonyourmindLS
end
go

CREATE TRIGGER dbo.TR_InsteadOf_WhatisonyourmindLS
ON dbo.WhatIsOnYourMind
INSTEAD OF INSERT, UPDATE, DELETE
AS
begin

        /* insert into dbo.whatisonyourmind */
	insert into [LSREMOTE].[DBLab].dbo.whatisonyourmindArchive
	(
		  [post]

		, [addedBy]

		, [addedOn]

		, [modificationType]

	)
	select 
		  isNull(tblInsert.[post], tblDeleted.[post])

		, isNull(tblInsert.[addedBy], tblDeleted.[addedBy])

		, isNull(tblInsert.[addedOn], tblDeleted.[addedOn])

		, 
			case

				when ( 

				           (tblInsert.[post] is not null)
				        and (tblDeleted.[post] is null)

				      ) then  'I'

				when ( 

				               (tblInsert.[post] is null)
					   and (tblDeleted.[post] is not null)

				     ) then  'D'

				else 'U'

			end

	from   inserted tblInsert

		  full join deleted tblDeleted

		    on tblInsert.[post] = tblDeleted.[post]

	MERGE INTO dbo.whatisonyourmind AS Target
	USING (
			select *
			from   inserted tblInsert
          )
			AS Source ([post], [addedBy], [addedOn] )
				ON Target.[addedBy] = Source.[addedBy]

	WHEN MATCHED THEN

		UPDATE 
		SET 
				  [post] = Source.[post]
				, [addedOn] = SOurce.[addedOn]

	WHEN NOT MATCHED BY TARGET THEN
		INSERT (post)
		VALUES (post)

	;

	delete tblTarget

	from   dbo.whatisonyourmind tblTarget

	where  tblTarget.[post] = 
			(

			   select  tblDeleted.[post]

			   from    deleted tblDeleted
											                           left outer join inserted tblInsert
													              on tblDeleted.[post] = tblInsert.[post]
										        			              and tblDeleted.[addedBy] = tblInsert.[addedBy]

			    where tblInsert.[post] is null

			)

end
go

The basic changes between this new trigger and our earlier one is:

  • Changed “insert into dbo.whatisonyourmind” to “insert into [LSREMOTE].[DBLab].dbo.whatisonyourmindArchive”

Data Changes (Failed)

Let us instigate data changes



use [DBLab]
go

/*

	truncate table [dbo].[whatisonyourmindArchive];
	truncate table [dbo].[whatisonyourmind];

*/

insert into [dbo].[whatisonyourmind]
([post])
values ('DEV6834')

Upon, a quick test, nothing is good, as we get the error pasted below:


Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Code Repository

To facilitate quick and broad participation and review,  I have shared this code in github. And, it is available in https://github.com/DanielAdeniji/MSSQLServerLinkedServerLBTrigger

 

Lab Environment

This problem has been tested out in the following environments:

  • Microsoft SQL Server 2012 – SP1 {11.0.2100}
  • Microsoft SQL Server 2008/R2 – SP2 {10.50.4000}
  • Microsoft SQL Server 2000 – SP4 {8.0.2039}

 

Summary

This seems to be a problem and I have been able to reproduce on a couple of MS SQL Server Instances; both of them running MS SQL Server 2012.

And, thus I opened a Connect Item – “Transaction context in use by another session (when LoopBack LinkedServer is used in trigger” {https://connect.microsoft.com/SQLServer/feedback/details/814524/transaction-context-in-use-by-another-session-when-loopback-linkedserver-is-used-in-trigger} to track.

Addendum

2014-04-09 – Connect Feedback – Alexey Stepanov

Alexey commented on the Connect Item and pointed out two Technet Articles that discusses the ‘handicap’:

References

References – Linked Server – Loopback

SQL Server – v2000 – BCP – Error – FUNCTION SEQUENCE ERROR

Introduction

Trying to get data out of SQL Server and hoping to use the quickest path.  One traditional tool is bcp.

Code – Transact SQL

Code – Transact SQL – dbProduct

Create database dbProduct and create a lone table dbo.product.

set noexec off
go

use [master]
go

if db_id('dbproduct') is null
begin

	print 'Creating DB dbproduct ...' 	

	exec('create database [dbproduct]')
	exec('alter database [dbproduct] set recovery simple');

	print 'Created DB dbproduct' 	

end
go

use [dbproduct]
go

/*
	drop table [dbo].[product]
*/
if object_id('dbo.product') is not null
begin

	set noexec on

end
go

/*
	drop table dbo.product;
*/

create table dbo.product
(

 	  [id] bigint not null identity(1,1)
	, [productName] varchar(600) not null
	, [size] varchar(80) null
	, [price] money not null

	, constraint PK_product primary key
		([id])

	, constraint Unique_ProductName unique
		([productName])

	, [addedBy] sysname not null
		constraint defaultproductAddedBy default SYSTEM_USER

	, [addedOn] datetime not null
		constraint defaultproductAddedOn default getdate()

)

set noexec off
go

Code – Transact SQL – dbSales

Create database dbSales and create a lone table dbo.order and dbo.orderDetail.


set noexec off
go

use [master]
go

if db_id('dbSales') is null
begin

	print 'Creating DB dbSales ...' 	

	exec('create database [dbSales]')
	exec('alter database [dbSales] set recovery simple');

	print 'Created DB dbSales' 	

end
go

use [dbSales]
go

if object_id('dbo.[order]') is not null
begin

	set noexec on

end
go

/*

	drop table dbo.[orderDetail];
	drop table dbo.[order];

*/

/*

	exec sp_help 'dbo.Order'

	exec sp_help 'dbo.OrderDetail'
*/

create table dbo.[order]
(
 	  [id] bigint not null identity(1,1) 

	, constraint PK_Order primary key
		([id])

	, [addedBy] sysname not null
		constraint defaultOrderAddedBy default SYSTEM_USER

	, [addedOn] datetime not null
		constraint defaultOrderAddedOn default getdate()

)
go

set noexec off
go

if object_id('dbo.[orderDetail]') is not null
begin

	set noexec on

end
go

create table dbo.[orderDetail]
(
 	   [OrderID] bigint
 	,  [sequenceID] int not null

	,  [productID] int not null

	,  [NumberofItems] int not null

		  constraint defaultOrderDetailNumbreofItems default 1

	,  [addedBy] sysname not null

		  constraint defaultOrderDetailAddedBy default SYSTEM_USER

	, [addedOn] datetime not null

		  constraint defaultOrderDetailAddedOn default getdate()

	, constraint PK_OrderDetail primary key
		(
			  [OrderID]
			, [SequenceID]
               )

	, constraint FK_OrderDetail foreign key
		(
			  [OrderID]
                )
	        references dbo.[Order]
	        (
			  [id]
                )

)
go

set noexec off
go

Code – Transact SQL – dbSales – dbo.ufn_computedCost

Create Scaler function dbo.ufn_computedCost.


use [dbSales]
go

if object_id('dbo.ufn_computedCost') is not null
begin

	set noexec on

end
go

/*

	drop function dbo.ufn_computedCost

*/

create function dbo.ufn_computedCost
(
	  @productID   int
	, @NumberofItems  int
)
returns money
as
begin

	declare @cost money

	select @cost = 
			@NumberofItems * tblProduct.[price]
	from   [dbProduct].dbo.product tblProduct
	where  tblProduct.[id] = @productID

	return (@cost)

end
go

set noexec off
go

Code – Transact SQL – dbSales – dbo.OrderDetail.cost

On the dbo.OrderDetail table, create a new column (cost) and bind it to the user function dbo.ufn_computedCost.


use [dbSales]
go

/*
	alter table dbo.orderDetail
	  drop column [cost]
*/

if not exists
	(

	    select name
	    from   syscolumns tblColumn
	    where  tblColumn.id = object_id('dbo.orderDetail')
	    and    tblColumn.name = 'cost'

	)
begin

	print 'Adding new column dbo.orderDetail - cost ... '

		alter table [dbo].[orderDetail]
		   add [cost]
			as dbo.ufn_computedCost(
                                                    [productID]
                                                  , [NumberofItems]
                                               )

	print 'Added new column dbo.orderDetail - cost'

end
go

Populate Tables

Let us populate the tables

Populate tables – [dbProduct].dbo.Product


set nocount on;
go

delete from [dbproduct].dbo.product;
go

set identity_insert [dbproduct].dbo.product on
go

	insert into [dbproduct].dbo.product
	([id], [productName], [size], [price])
	values(1, 'Frosted Flakes Cereal', '15 oz',2.98)

	insert into [dbproduct].dbo.product
	([id],[productName], [size], [price])
	values(2, 'Kellogg''s Corn Flakes Cereal', '24 oz', 4.67)

	insert into [dbproduct].dbo.product
	([id],[productName],[size], [price])
	values(3, 'Milk - Vitamin D','1 Gallon', 3.65)

	insert into [dbproduct].dbo.product
	([id],[productName],[size], [price])
	values(31, 'Chobani Non-Fat Greek Yogurt','15/6 oz', 15.99)

	insert into [dbproduct].dbo.product
	([id],[productName],[size], [price])
	values(41, 'Donsuemor Madeleines French Cakes','28 oz', 8.13)

	insert into [dbproduct].dbo.product
	([id],[productName],[size], [price])
	values(51, 'Turon','15 pieces', 11.85)

        insert into [dbProduct].[dbo].product
	([id],[productName],[size], [price])
	values(52, 'empanada','7 pieces', 7.00)

go

set identity_insert [dbproduct].dbo.product off
go

Populate tables – [dbSales].dbo.Order and [dbSales].dbo.OrderDetail


set nocount on;
go

delete from [dbSales].dbo.[orderDetail];
delete from [dbSales].dbo.[order];
go

set identity_insert [dbSales].dbo.[order] on

	insert into [dbSales].dbo.[order]
	([id])
	values (1)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID]
	)
	values
	(
		1, 1, 1
	)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID]
	)
	values
	(
		1, 2, 3
	)

        --------------------------------------------------------
	insert into [dbSales].dbo.[order]
	([id])
	values (2)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID]
	)
	values
	(
		2, 1, 1
	)

       -----------------------------------------------------------

	insert into [dbSales].dbo.[order]
	([id])
	values (3)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID], [NumberofItems]
	)
	values
	(
		3, 1, 51,1
	)

	insert into [dbSales].dbo.[orderDetail]
	(
		[orderID], [sequenceID], [productID],[NumberofItems]
	)
	values
	(
		3, 2, 52,2
	)

go

set identity_insert [dbSales].dbo.[order] off
go

Bcp Data

Bcp Data – OrderDetail

Fetch data out of the dbo.OrderDetail table.

Script:

"C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\bcp"  "select * from [dbSales].dbo.[orderDetail]" queryout orderDetail.csv -c -S DBLAB\MSSQL2000  -T

Output:

bcp-good

Bcp Data – Failed

Let us imagine that a few weeks later or we are are in the process of moving to a new system and we try to bcp again, but upon issuing an identical BCP Command, we are now getting an error.

Bcp Data – OrderDetail

Fetch data out of the dbo.OrderDetail table.

Script:


"C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\bcp"  "select * from [dbSales].dbo.[orderDetail]" queryout orderDetail.csv -c -S DBLAB\MSSQL2000  -T

 

Error:

Error – Textual

SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error

Error – Image

bcp-failed

Error Diagnosis

An error that reads “Function sequence error” seems intimidating. But, no problem.

We issued a select statement against the target table for our bcp:

select * from dbsales.dbo.orderDetail

And, got back an helpful error message:

Server: Msg 208, Level 16, State 1, Procedure ufn_computedCost, Line 24
Invalid object name 'dbProduct.dbo.product'.

Fix

Our fix was to move the dbProduct database to our new system.

In Microsoft SQL Server it is sometimes difficult to find cross database dependencies;  partly because Transact SQL does not allow us to specify those relationships and constraints.

Using the code pasted below, we tried relating the dbo.OrderDetail.productID column in the dbSales database to the id column in the dbProduct.dbo.product table.

     use [dbSales]
     go

     alter table dbo.OrderDetail
	   add constraint FK_OrderDetail_Product foreign key
       (
   	   [productID]
       )
       references [dbProduct].dbo.[product]
       (
    	  [id]
       )

But, you will get an error stating “cross-database foreign key references are not supported.



Server: Msg 1763, Level 16, State 1, Line 2
Cross-database foreign key references are not supported. 
Foreign key 'dbProduct.dbo.product'.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.

Please keep in mind that if you try to reference an entirely missing database object, you will get a different error.

In the example below, we tried referencing a missing object (dbProduct.dbo.productMissing):


     use [dbSales]
     go

     alter table dbo.OrderDetail
	   add constraint FK_OrderDetail_Product foreign key
       (
   	   [productID]
       )
       references [dbProduct].dbo.[productMissing]
       (
    	  [id]
       )

And, get an error stating “references invalid table ….”


Server: Msg 1767, Level 16, State 1, Line 1
Foreign key 'FK_OrderDetail_Product' references invalid table 'dbProduct.dbo.product1'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

More research might reveal different root causes:

  • Someone might have renamed the dbProduct.dbo.product table
  • In our case, the dbProduct database had not yet been moved to our new system

Conclusion

Yes,  error messages are sometimes unpleasant.  And, quick Google searches might find problems that a bit worse and laiden with unrelated riddles.

But, steady and progressive remediation steps wins the day!

References

SQL Server – Linked Server /ODBC – Error – Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding (Msg 7320, Level 16, State 2, Line 39)

Error Message


OLE DB provider "MSDASQL" for linked server "DBLABJ_NC_10_ODBC" returned message
"[Microsoft][SQL Server Native Client 10.0]Datetime field overflow. Fractional second
precision exceeds the scale specified in the parameter binding.".

Msg 7320, Level 16, State 2, Line 39
Cannot execute the query "SELECT FROM "dbHR"."dbo"."employee" "Tbl1005" WHERE "Tbl1005"."hiredate"&gt;?" against OLE DB provider "MSDASQL" for linked server
"DBLABJ_NC_10_ODBC".

Solution

Original Code


declare @hireDataDestMax datetime
declare @hireDateDefaultMax datetime

set @hireDateDefaultMax = '1/1/1900'

set @hirDateDestMax =
			(
			   select max(hiredate)
			   from   dbo.employee tblSrc
			)

if @hireDataDestMax is null
begin
	set @hireDataDestMax = @hireDateDefaultMax
end

insert into [nxtaccess].dbo.employee
(
   [employeeID]
  ,[employeeName]
  ,[hireData]
)
select
   [employeeID]
  ,[employeeName]
  ,[hireData]
from  [DBLABJ_NC_10_ODBC].[dbHR].dbo.employee tblSrc
where [hiredate] > @hireDataDestMax

Revised Code


declare @hireDataDestMax smalldatetime
declare @hireDateDefaultMax smalldatetime

set @hireDateDefaultMax = '1/1/1900'

set @hirDateDestMax =
			(
			   select max(hiredate)
			   from   dbo.employee tblSrc
			)

if @hireDataDestMax is null
begin
	set @hireDataDestMax = @hireDateDefaultMax
end

insert into [nxtaccess].dbo.employee
(
   [employeeID]
  ,[employeeName]
  ,[hireData]
)
select
   [employeeID]
  ,[employeeName]
  ,[hireData]
from  [DBLABJ_NC_10_ODBC].[dbHR].dbo.employee tblSrc
where [hiredate] > @hireDataDestMax

Explanation

Changed the definition of our local datetime variable from datetime to smalldatetime.

It seems that the Linked Server ODBC layer is not able to handle the more expansive datetime variable; and that we will have to use smalldatetime.

Technical: Microsoft – SQL Server – Change Data Capture – Error – When FileGroup does not contain datafiles

Technical: Microsoft – SQL Server – Change Data Capture – Error – When FileGroup does not contain datafiles

Introduction

Not easy to recover from creating a Change Data Capture and targeting an empty file group.

Create Database

If Database DBLAB, does not exist please create it

        if db_id('DBLab') is null
        begin

            exec ('create database [DBLab]');
            exec ( 'alter database [DBLAB] set recovery simple');
        end
        go

Add File Group

If fileGroup fileGroup_MSSQLServer_CDC, does not exist then create one.

        use [DBLab]
        go
	/*
		Check if File group does not exist, and if so create it
	*/
	if not exists
	(
		select *
		from   sys.sysfilegroups tblFileGroup
		where  tblFileGroup.groupname = 'fileGroup_MSSQLServer_CDC'
	)
	begin

		print 'Creating file group DBLab-fileGroup_MSSQLServer_CDC ... '

		ALTER DATABASE [DBLab] 
			add filegroup fileGroup_MSSQLServer_CDC

		print 'Creating file group DBLab-fileGroup_MSSQLServer_CDC'

	end

Enable Database for CDC

Enable Database for CDC


use [DBLab]
go
if not exists
(

      SELECT *
      FROM   sys.databases
      WHERE  is_cdc_enabled = 1
      and name= db_name()

)
begin

     print 'Enable CDC ' + db_name() + ' ...'

         EXEC sys.sp_cdc_enable_db

     print 'Enabled CDC ' + db_name() + ''

end

Create Object (Table)

Create Object



set nocount on
go

use [DBLAB]
go

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

/*
	DROP TABLE [dbo].[customer];
*/

set noexec off
go

if object_id('[dbo].[customer]') is not null
begin

	set noexec on

end
go

CREATE TABLE [dbo].[customer]
(

	  [CustomerID]   [bigint] NOT NULL

	, [CustomerName] [nvarchar](255) NOT NULL

	, [inceptionDate] [datetime] NOT NULL

	, [expiryDate] [datetime] NULL

	, [active] bit not null 
		constraint defaultActive default 1

	, [dateAdded]    [datetime] NOT NULL
		 constraint defaultDateAdded default getutcdate()

	, [addedBy]      [sysname]  NOT NULL 
		constraint defaultAddedBy default SYSTEM_USER

	 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
		(
			[CustomerID] ASC
		)WITH 
		(
			  PAD_INDEX  = OFF
			, STATISTICS_NORECOMPUTE  = OFF
			, IGNORE_DUP_KEY = OFF
			, ALLOW_ROW_LOCKS  = ON
			, ALLOW_PAGE_LOCKS  = ON
		) 
		ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

set noexec off
go

Enable Object for CDC

Enable Object for CDC



set nocount on
go

use [DBLAB]
go

declare @objectSchema   sysname
declare @objectName     sysname
declare @objectNameFull sysname
declare @objectID       int
declare @filegroup_name sysname
declare @addCDC         bit

set @objectSchema = 'dbo'
set @objectName = 'customer'
set @objectNameFull = quoteName(@objectSchema) 
                                 + '.'
                                 + quoteName(@objectName)
set @objectID = object_id(@ObjectNameFull)
set @filegroup_name = N'fileGroup_MSSQLServer_CDC'
set @addCDC = 0
set @addCDC = 1

if (@addCDC = 0)
begin

	print 'CDC - Disable....' 

	if exists
	(

		SELECT *
		FROM   sys.tables 
		WHERE  is_tracked_by_cdc = 1
		and    schema_id = schema_id(@objectSchema)
		and    name= @objectName

	)
	begin

		EXEC sys.sp_cdc_disable_table
			  @source_schema = @objectSchema
			, @source_name   = @objectName
			, @capture_instance = 'dbo_customer'

	end

	print 'CDC - Disable' 

end

else if (@addCDC = 1)
begin

	if not exists
	(

		SELECT *
		FROM   sys.tables 
		WHERE  is_tracked_by_cdc = 1
		and    schema_id = schema_id(@objectSchema)
		and    name= @objectName

	)
	begin

	   print 'CDC - Enable....' 

	  EXEC sys.sp_cdc_enable_table
		  @source_schema = @objectSchema
		, @source_name   = @objectName
		, @role_name     = null -- N'roleMSQLServerCDC'

		/*
		  Msg 50000, Level 16, State 3, Line 129
		  Either a range parameter or the row filter option is not valid.

		   , @filegroup_name = N'fileGroup_MSSQLServer_CDC'
		*/

		--, @filegroup_name = N'PRIMARY'
		, @filegroup_name = @filegroup_name

	     select 
		  object_name(object_id) as objectName
		, capture_instance
		, supports_net_changes
		, index_name
		, filegroup_name
		, create_date
	     from   [cdc].[change_tables]
	     where  source_object_id =  @objectID

	     print 'CDC - Enabled' 

	end

end --addCDC =1

exec sys.sp_cdc_help_change_data_capture

Add Data

Add Data



USE [DBLab]
GO

set noexec off
set nocount off
go

/*
	Cannot truncate table 'dbo.customer' because it is published for replication or enabled for Change Data Capture.
*/
delete from [dbo].[customer]
go

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  1
   , 'Dell'
   , '2013-12-17'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  2
   , 'Intel'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  3
   , 'Bart'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  4
   , 'Amazon'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  5
   , 'Belkin'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  6
   , 'Toyota'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  7
   , 'Ford'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  8
   , 'Nizzan'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  9
   , 'Compaq'
   , '2013-12-18'
   , null
)

update [dbo].[customer]
set  active = 0
where  [CustomerID] % 3 = 0

select *
from   [dbo].[customer]

Retrieve CDC Data



use [DBLAB]
go

DECLARE @begin_time datetime, @end_time datetime
declare @from_lsn binary(10), @to_lsn binary(10)
declare @save_to_lsn binary(10)
declare @min_lsn binary(10)
declare @capture_instance sysname
declare  @objectID int

-- Obtain the beginning of the time interval.
SET @begin_time = GETDATE() -1;
 -- Obtain the end of the time interval.
SET @end_time = GETDATE();

select 
         @from_lsn = start_lsn
       , @capture_instance = capture_instance
from   [cdc].[change_tables]
where  source_object_id =  @objectID

set @objectID = object_id('dbo.customer')

-- Map the time interval to a change data capture query range.
SET @save_to_lsn = 0x000000000000000000;
SET @from_lsn = sys.fn_cdc_map_time_to_lsn
                   ('smallest greater than or equal', @begin_time);
SET @min_lsn = sys.fn_cdc_get_min_lsn (@capture_instance);
SET @from_lsn = sys.fn_cdc_increment_lsn(@save_to_lsn);
-- Tests to verify the low endpoint is valid for the current capture instance.
IF (@from_lsn < @min_lsn)
    BEGIN
        RAISERROR('Low endpoint of the request interval is invalid.', 16, -1);
    END

SET @from_lsn = sys.fn_cdc_get_min_lsn(@capture_instance)
SET @to_LSN =sys.fn_cdc_get_max_lsn()

if (@from_lsn = 0x00000000000000000000)
begin

	select @from_lsn = start_lsn
	from   [cdc].[change_tables]
	where  source_object_id =  @objectID

end

select *
from   [cdc].[change_tables]

select 
	   @from_lsn as [from_lsn]
         , sys.fn_cdc_map_lsn_to_time(@from_lsn) fromTime
	 , @min_lsn as [min_lsn]
	 , @to_lsn as [to_lsn]
         , sys.fn_cdc_map_lsn_to_time(@to_lsn) toTime

begin try

    SELECT
       CT.__$operation
     , CT.__$update_mask
     , CT.customerID
     , CT.customerName
     , LSN.tran_begin_time
     , LSN.tran_end_time
     , sys.fn_cdc_has_column_changed 
          ('dbo_Customer', 'customerID', __$update_mask) AS is_customerID_changed
     , sys.fn_cdc_has_column_changed
          ('dbo_Customer', 'CustomerName', __$update_mask) AS is_customerName_changed
    , sys.fn_cdc_has_column_changed ('dbo_Customer', 'expiryDate', __$update_mask) 
          AS id_ExpiryDate_changed
    , sys.fn_cdc_has_column_changed ('dbo_Customer', 'dateAdded', __$update_mask) 
        AS id_dateAdded_changed
   , sys.fn_cdc_has_column_changed ('dbo_Customer', 'active', __$update_mask) 
       AS id_Active_changed

  FROM cdc.fn_cdc_get_all_changes_dbo_Customer(@from_lsn, @to_lsn, N'all') CT
  --FROM cdc.fn_cdc_get_net_changes_dbo_Customer(@from_lsn, @to_lsn, N'all') CT

	inner join cdc.lsn_time_mapping AS LSN

		 ON CT.__$start_lsn = LSN.start_lsn

  WHERE __$operation in ( 3, 4)

end try

BEGIN CATCH

     DECLARE

        @ERROR_SEVERITY INT,

        @ERROR_STATE INT,

        @ERROR_NUMBER INT,

        @ERROR_LINE INT,

        @ERROR_MESSAGE VARCHAR(245)

    SELECT

        @ERROR_SEVERITY = ERROR_SEVERITY(),

        @ERROR_STATE = ERROR_STATE(),

        @ERROR_NUMBER = ERROR_NUMBER(),

        @ERROR_LINE = ERROR_LINE(),

        @ERROR_MESSAGE = ERROR_MESSAGE()

     IF @ERROR_NUMBER = 313

            RAISERROR(

                 'Either a range parameter or the row filter option is not valid.', 

                 @ERROR_SEVERITY, @ERROR_STATE)

     ELSE

     IF @ERROR_NUMBER = 229

            RAISERROR(

                 'The caller is not authorized to perform the query.', 

                 @ERROR_SEVERITY, @ERROR_STATE)

     ELSE

            RAISERROR('Msg %d, Line %d: %s',

                 @ERROR_SEVERITY, @ERROR_STATE, @ERROR_NUMBER,

                 @ERROR_LINE, @ERROR_MESSAGE) 

END CATCH 

Output – Grid:

CDCMetadata

Output – Text:

Text

Msg 50000, Level 16, State 3, Line 125

Either a range parameter or the row filter option is not valid.

GUI

CDCMetadata_Output_Text

Debugging Steps

The error message “Either a range parameter or the row filter option is not valid” is a bit generic.

Debugging Steps – Check Error Log

Debugging Steps – Check Dynamic Management View (DMV)

Debugging Steps – Check Dynamic Management View (DMV) – sys.dm_cdc_errors

The sys.dm_cdc_errors dmv has good error message.


select *
from  sys.dm_cdc_errors
order by session_id desc;

Output:

CDCMetadata_DMCDCErrors

Recovery Steps?

Use Dynamic Management Views

Friend Dynamic Management Views (dmv).  In this case:

Recovery Steps – Create File under file group

Let us go create a file for out designated file group.


declare @fileCreate bit

set @fileCreate = 1
if (@fileCreate = 1)
begin

	if not exists
	(
		select 
   		        tblFileGroup.groupid
		      , tblFileGroup.groupname
		      , tblFile.fileid
		      , tblFile.name
		      , tblFile.filename
		from   sys.sysfilegroups tblFileGroup

			  inner join sys.sysfiles tblFile
				on tblFile.groupid = tblFilegroup.groupid

		where tblFileGroup.groupname = 'fileGroup_MSSQLServer_CDC'
	)

	begin

	   print'File Create - ChangeDataCapture_20131217 ....'

	   ALTER DATABASE [DBLab] 
	   ADD FILE
	    ( 
  	          NAME = N'ChangeDataCapture_20131217'
	        , FILENAME = N'D:\Microsoft\SQLServer\Datafiles\CDC_20131217.ndf'   
                , SIZE = 3072KB 
	        , FILEGROWTH = 1024KB 
	    ) 
	   TO FILEGROUP [fileGroup_MSSQLServer_CDC]

	   print'File Created - ChangeDataCapture_20131217'

	end

end

Re-Check CDC Tables

Once file for designated file group is created, checked CDC but things are still no good!

 

Diagnostic Tools

Check CDC /Change-Tables, FileGroup, and Files

Here is a SQL for checking CDC File Groups mapping



use [DBLab]
go

select 

		  object_name(tblCDCTableChange.object_id) as objectName
		, tblCDCTableChange.capture_instance
		, tblCDCTableChange.start_lsn
		, tblCDCTableChange.end_lsn
		, tblCDCTableChange.filegroup_name
		, tblCDCTableChange.create_date as CDCCreateDate
		, tblFileGroup.groupname as fileGroupName
		, tblFile.filename as fileName

from   [cdc].[change_tables] tblCDCTableChange

   left outer join sys.sysfilegroups tblFileGroup
      on
        (

          (tblCDCTableChange.filegroup_name = tblFileGroup.groupname)    

         )

   left outer join sys.sysfiles tblFile
		on tblFileGroup.groupid = tblFile.groupid

Summary

In summary, read has much as you can, write detailed deployment steps, and investigate diagnostic paths; i.e dynamic management views.

References