Read more about the article Error 3624 when creating natively compiled stored procedure
Microsoft SQL Server

Error 3624 when creating natively compiled stored procedure

I was trying to upgrade my MACD technical analysis calculation to use the new In Memory technologies introduced in SQL Server 2014. The statement used in the SQL 2012 calculation worked just fine until I put it in a natively compiled stored procedure.

(more…)

Continue ReadingError 3624 when creating natively compiled stored procedure
Read more about the article Add missing lookup in SSIS
Lookup

Add missing lookup in SSIS

Before a fact table is loaded, a common pattern is to do a lookup in SSIS before the loading. The lookup can be some key from a dimension for instance, or maybe a repository of primary keys in a MDM solution. But what if the lookup doesn’t return a matching value? Sometimes when this happens, we want to insert new values to the lookup table (to get the key) before the loading of the fact table starts. Here I’ll show one way to handle this scenario.

(more…)

Continue ReadingAdd missing lookup in SSIS
Read more about the article Table Valued Parameters in Stored Procedures
Microsoft SQL Server

Table Valued Parameters in Stored Procedures

In most cases stored procedures are developed so that it accepts a number of input parameters with a single value. But a common requirement is to be able to submit multi value parameters. That is, parameters that accept several values as opposed to a single value.

In SQL Server 2008, table values parameters in stored procedures was introduced.

(more…)

Continue ReadingTable Valued Parameters in Stored Procedures
Read more about the article Manual failover in a SQL Server cluster
Microsoft SQL Server

Manual failover in a SQL Server cluster

In this blog post, I’ll demonstrate how to find the currently active node in a SQL Server cluster, and how to do a manual failover to another node.

Note that a failover will result in a short down time for your SQL Server environment. This is because a recovery (rollback and roll forward of transactions) will be performed on the new active node in the SQL Server cluster. In an environment with long running transactions, the time for the recovery may be substantial (how to check the latest recovery time). So don’t do failover in a production environment unless absolutely necessary. Preferably, use a cluster lab environment to test things out first.

(more…)

Continue ReadingManual failover in a SQL Server cluster
Read more about the article CDC Capture job
Microsoft SQL Server

CDC Capture job

An introduction to CDC can be found here.

When CDC (Change Data Capture) is enabled on a database, a SQL Agent job is automatically created called cdc.XXX_capture. The capture job reads asynchronously from the SQL Server transaction log to retrieve all modifications (DML statements) made to all CDC enabled tables. The changes are then written to a change table.

In this blog post I’ll describe the CDC capture job and how it can be configured.

(more…)

Continue ReadingCDC Capture job