SQL Server Full Text Search Syntax

 
-- Search a single word
select * from Property where contains([Address], '"king"')

-- Search a single word with ', 
select * from Property where contains([Address], '"O''Brien"')

-- Search a single word with wildcard'
select * from Property where contains([Address], '"king*"')

-- Search multiple word
select * from Property where contains([Address], '"2" and "king*"')

Find all tables containing column with specified name

 
SELECT      c.name  AS 'ColumnName',
            t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t 
            ON c.object_id = t.object_id
WHERE       c.name LIKE '%changeSignOffStaff%'
ORDER BY    TableName, ColumnName;

Rererence
https://stackoverflow.com/questions/4849652/find-all-tables-containing-column-with-specified-name

Select Year and Month from a date range

 
declare @dtStartDate datetime = '2017-01-31';
declare @dtEndDate datetime = '2017-12-31';

WITH YMTable AS
(
    SELECT @dtStartDate AS cte_start_date
    UNION ALL
    SELECT DATEADD(MONTH, 1, cte_start_date)
    FROM YMTable
    WHERE DATEADD(MONTH, 1, cte_start_date) <= @dtEndDate
)

SELECT YEAR(cte_start_date), DATENAME(month,cte_start_date)
FROM YMTable

Check If A Row Is Indexed By A Full Text Catalog

Check if a row/record is indexed

 
declare @objectId int = OBJECT_ID('dbo.TableName')
EXEC sp_fulltext_keymappings @objectId, NULL, TablePrimaryKeyID

Example

 
declare @objectId int = OBJECT_ID('dbo.Property')
EXEC sp_fulltext_keymappings @objectId, NULL, 12

List all row primary keys that are indexed in full text catalog

 
declare @objectId int = OBJECT_ID('dbo.Property')
EXEC sp_fulltext_keymappings @objectId;

Show the current running SQL query on SQL Server

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.database_id,
DB_NAME(req.database_id) as DBName
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
where database_id = xxx

Get Database ID from name and vice versa

select DB_NAME(9) as DBName
SELECT DB_ID(N'DatabaseName') AS [DatabaseID]; 

View Full Text Catalog Status

 
SELECT cat.name as CatalogName,
       (SELECT CASE FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus')
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Full Population In Progress'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Throttled'
        WHEN 4 THEN 'Recovering'
        WHEN 5 THEN 'Shutdown'
        WHEN 6 THEN 'Incremental Population In Progress'
        WHEN 7 THEN 'Building Index'
        WHEN 8 THEN 'Disk Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus,
	   DATEADD(ss, FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated,
       FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount') AS [ItemCount],
       FULLTEXTCATALOGPROPERTY(cat.name,'MergeStatus') AS [MergeStatus],
       FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') AS [PopulateCompletionAge],
       FULLTEXTCATALOGPROPERTY(cat.name,'ImportStatus') AS [ImportStatus]
FROM sys.fulltext_catalogs AS cat

Delete All Tables in a Database

EXEC sp_MSforeachtable @command1 = “DROP TABLE ?”

Could not load file or assembly XXX or one of its dependencies.

Problem:
ASP.Net website compile OK. But when open the webpage, it throws exception:

Message:Could not load file or assembly ‘Common, Version=3.0.2405.33299, Culture=neutral, PublicKeyToken=dd08d87373c9b5b9’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

Cause:
In solution property settings, the relevant project is not ticked as build.

Solution:
In solution property settings, make sure all relevant projects are ticked to build. Clean the rebuild solution, problem should be solved.

Creating X509Certificate2 certificate causes program stopped. No exception captured.

Problem
Creating X509Certificate2 causes program stopped. No exception captured.

Cause
For some reason the constructor is trying to get access to the private key store although the private key is in stored in the file being opened. By default the user key store is used but ASP.NET (and probably non-interactive Windows services in general) are not allowed to open it. Chances are the user key store for the selected account doesn’t even exist.

Solution
Another solution is to pass an additional parameter to the constructor – a flag indicating the private keys are (supposed to be) stored in the local computer – X509KeyStorageFlags.MachineKeySet, like this:

var certificate = new X509Certificate2(fileName, password, X509KeyStorageFlags.MachineKeySet);

Reference: http://vdachev.net/2012/03/07/c-sharp-error-creating-x509certificate2-from-a-pfx-or-p12-file-in-production/

Round Decimal to Two Digits

decimal xxx = 23.3446M;
Console.WriteLine(xxx);

xxx = decimal.Round(xxx, 2);
Console.WriteLine(xxx);