JOIN on like comparison

declare @tbl table (aaa varchar(50))

declare @src table (aaa varchar(50))

insert into @tbl values ('aaa'), ('aaabcd'), ('bcd'), ('zzzaaa')

insert into @src values ('aaa')

-- select only equel strings (same as t.aaa = s.aaa)

select * from @tbl t join @src s on t.aaa like s.aaa

update @src set aaa = '%' + aaa + '%'

-- select all the records from t that contain s.aaa

select * from @tbl t join @src s on t.aaa like s.aaa

Incorrect syntax near ' '

select * from dbo.MyTable

where ID = 101 

Error message:

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ' '.

The case:

I wrote this simple query and copy-paste the ID number from e-mail/some chat or something like this.

Cause:

Pay atention - there is a space in the end of the query.

So? it's still not suppose to be a problem....

BUT - this is not realy a space, but "Non-breaking space".

Check it, by select the ascii code of this "Non-breaking space" (copy it from the end of the query and of a regular space:

select ASCII(' '), ASCII(' ')

Solution:

Very simple, as you already can guess - remove this ' '.

DATALENGTH is not like LEN !

Two main differences between DATALENGTH and LEN:
DATALENGTH returns the number of bytes used by an expression.

LEN returns the number of characters contained in an expression.

DATALENGTH supports expressions of any type.

LEN supports only string expressions.

declare @TestLength TABLE 
(
  char1 CHAR(10),
  nchar2 NCHAR(10),
  varchar3 VARCHAR(10),
  nvarchar4 NVARCHAR(10)
) 
INSERT INTO @TestLength VALUES('test', 'test', 'test', 'test')
INSERT INTO @TestLength VALUES('test   ', 'test   ', 'test   ', 'test   ')

SELECT
  LEN(char1) AS len1, DATALENGTH(char1) AS datalength1,
  LEN(nchar2) AS len2, DATALENGTH(nchar2) AS datalength2,
  LEN(varchar3) AS len3, DATALENGTH(varchar3) AS datalength3,
  LEN(nvarchar4) AS len4, DATALENGTH(nvarchar4) AS datalength4
FROM @TestLength

CDC on SQL Server

CDC = Change data capture

CDC is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format.

http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

Check if cdc enabled:

USE [master]

GO

SELECT [name], database_id, is_cdc_enabled FROM sys.databases

GO

Enable cdc on DB:

USE [DB_NAME]

GO

EXEC sys.sp_cdc_enable_db

GO

after enable cdc:

1. New schema cdc will be created
2. New cdc system tables will be created

-- disable cdc:

EXEC sys.sp_cdc_disable_db 

GO

Check which tables of database have been enabled for cdc:

USE [DB_NAME]

GO

SELECT [name], is_tracked_by_cdc FROM sys.tables 

GO

Activate cdc on a table:

USE [DB_NAME]

GO

EXEC sys.sp_cdc_enable_table 

@source_schema = 'SCHEMA_NAME',

@source_name = 'TABLE_NAME',

@role_name = NULL,

@capture_instance = 'CDC_INSTANCE_NAME',

@supports_net_changes = 0,

@index_name = 'PK_OF_TABLE'

• after enable cdc on a table, a new system table will be created: cdc.CDC_INSTANCE_NAME_CT

For example:

EXEC sys.sp_cdc_enable_table 

@source_schema = 'dbo',

@source_name = 'TableItai',

@role_name = NULL,

@capture_instance = 'dbo_TableItai',

@supports_net_changes = 0,

@index_name = 'PK_TableItai'

-- cdc.dbo_TableItai_CT will be created

Disable cdc on a table:

USE [DB_NAME]

GO

EXECUTE sys.sp_cdc_disable_table

    @source_schema = N'SCHEMA_NAME',

    @source_name = N'TABLE_NAME',

    @capture_instance = N'CDC_INSTANCE_NAME';

GO

https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

-- enable triggers ON DATABASE
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'DDL_Audit_Trigger')
ENABLE TRIGGER DDL_Audit_Trigger ON DATABASE
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'TRD_AuditObjects')
ENABLE TRIGGER TRD_AuditObjects ON DATABASE
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trg_ProceduresSign')
ENABLE TRIGGER trg_ProceduresSign ON DATABASE

GO

-- disable triggers ON DATABASE
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'DDL_Audit_Trigger')
DISABLE TRIGGER DDL_Audit_Trigger ON DATABASE
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'TRD_AuditObjects')
DISABLE TRIGGER TRD_AuditObjects ON DATABASE
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trg_ProceduresSign')
DISABLE TRIGGER trg_ProceduresSign ON DATABASE

GO