How to prevent data to be changed, when copying over linked server connection in SQL Server

 

When I was inserting and updating data over a SQL Server linked server connection, special characters in “strings” were altered.

 

A character “ë” was changed to the character “Ù”.

 

Now the first thing I checked was the collation on both databases and columns.

They were exactly the same, so I expected the data NOT to be altered.

I think this is a bug in SQL Server, but I have a workaround: set the collation of the linked server connection:

 

use master

go

exec sp_serveroption ’93_WGD’, ‘collation compatible’, ‘false’

go

exec sp_serveroption ’93_WGD’, ‘use remote collation’, ‘false’

go

exec sp_serveroption ’93_WGD’, ‘collation name’, ‘Latin1_General_BIN2’

go

Filter SQL Server Profiler trace to only show queries send by specific machine / laptop

 

If you want to filet the SQL Server Profiler trace, so only queries send from your laptop are captured, just filter on HostName:

 

First:

Enable “Show all events”

Enable “Show all columns”

 

image

 

Click on [Column Filters…]

“Select Column” [HostName]:

image

 

Enter the name of the “laptop”:

 

image

Fix: Visual Studio 2013 / 2014–SQL Server 2014 – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet

 

Note

When you want to run / edit / debug PowerShell scripts inside Visual Studio 2013 / 2015 you will have to install the “PowerShell Tools for Visual Studio” Visual Studio extension found at: https://visualstudiogallery.msdn.microsoft.com/c9eb3ba8-0c59-4944-9a62-6eee37294597

 

I wanted to run and debug a PowerShell script inside Visual Studio 2013 / 2015, on a machine that only had SQL Server 2014 installed as database server. This threw an exception: The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet http://blog.sqlauthority.com/2014/11/23/sql-server-fix-the-term-invoke-sqlcmd-is-not-recognized-as-the-name-of-a-cmdlet/

 

Turns out you have to update the PowerShell “PSModulePath”, so it can find the PowerShell modules containing the SQL Server 2014 cmdlets.

 

Temp fix:

http://stackoverflow.com/questions/29562742/invoke-sqlcmd-failing-after-automated-sql-install-with-powershell

Existing Powershell session isn’t aware about Sql’s modules that were just installed. Take a look at environment variable $env:PSModulePath. Compare new shell’s variable to existing and you should see a missing path like …\Microsoft SQL Server\110\Tools\PowerShell\Modules\.
For a work-around, modify the path to include module dir. Like so,

$env:PSModulePath += ";C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules"

 

If you want to persist this extra “Module load path”, you have to change the registry:

http://tomtalks.uk/2013/06/powershell-add-a-persistent-module-path-to-envpsmodulepath/

 

image

 

Now I can run and debug the following PowerShell script inside Visual Studio 2013 / 2015:

# This sccript requires the system to allow running powershell scripts, when not allowed the following code can be used to allow running powershell scripts.
# Set-ExecutionPolicy RemoteSigned
# The following import statement enables the SQL Server PowerShell cmdlets, like "Invoke-Sqlcmd".
Import-Module SqlPs
$serverInstance = "(localdb)\v11.0"
$database = "App"
function ExecuteSqlFile {
[string]$file = $args[0]
Invoke-Sqlcmd -ServerInstance $serverInstance -Database $database -InputFile $file
}
ExecuteSqlFile('C:\Temp\test.sql')

Automatic SQL Server query execution optimization: "get of my lawn"

 

I had to write a SQL query, that would return the records where the column "ImportFileName" started with the year "2013"

 

-- Drop temp table if it exists.
if object_id('tempdb..#Import') is not null
begin
drop table #Import
end
-- Create temp table.
create table #Import
(
Id int not null,
ImportFileName varchar (255) not null
)
-- Seed temp table.
insert into #Import (Id, ImportFileName) values 
(1, '20130506101010.csv'),
(2, '20130606101010.csv'),
(3, 'This_is_an_error_file.csv'),
(4, '20130806101010.csv'),
(5, '20130906101010.csv')
-- Query the temp table
select  Id
ImportFileName
from    #Import
where   datepart(year, cast(substring(ImportFileName, 1, 8) as date)) = 2013
-- Drop temp table.
drop table #Import

This query results in the error:

Msg 241, Level 16, State 1, Line 19
Conversion failed when converting date and/or time from character string.

This can be fixed with the SQL Server 2012 and above function: TRY_CONVERT:

-- The fixed query (>= SQL Server 2012)
select    Id
ImportFileName
from    #Import
where    datepart(year, try_convert(date, substring(ImportFileName, 1, 8))) = 2013

But in SQL Server 2008R2 this function does not exist, so I tried to trick SQL Server by using a subquery that would only return valid records:

-- Query the temp table
select  i.Id,
i.ImportFileName
from    (
select    Id,
ImportFileName
from    #Import
where    isdate(substring(ImportFileName, 1, 8)) = 1
) i
where   datepart(year, cast(substring(i.ImportFileName, 1, 8) as date)) = 2013

But this resulted in the same error, why????

Well the estimated execution plan tells you why:

image

 

image

 

Effectively SQL Server query optimization will merge the two where statements and convert the query above to something like:

 

select    Id,
ImportFileName
from    #Import
where    datepart(year, cast(substring(ImportFileName, 1, 8) as date)) = 2013
and     isdate(substring(ImportFileName, 1, 8)) = 1

which will fail.

 

To get around this problem, you could first insert the subquery records in a temp table and then check query the temp table, but in my case I used a case statement:

-- Query the temp table
select  i.Id,
i.ImportFileName
from    (
select    Id,
case    when isdate(substring(ImportFileName, 1, 8)) = 1
then    ImportFileName
else    null
end as ImportFileName
from    #Import
) i
where   datepart(year, cast(substring(i.ImportFileName, 1, 8) as date)) = 2013

The full script now look like:

-- Drop temp table if it exists.
if object_id('tempdb..#Import') is not null
begin
drop table #Import
end
-- Create temp table.
create table #Import
(
Id int not null,
ImportFileName varchar (255) not null
)
-- Seed temp table.
insert into #Import (Id, ImportFileName) values 
(1, '20130506101010.csv'),
(2, '20130606101010.csv'),
(3, 'This_is_an_error_file.csv'),
(4, '20130806101010.csv'),
(5, '20130906101010.csv')
-- Query the temp table
select  i.Id,
i.ImportFileName
from    (
select    Id,
case    when isdate(substring(ImportFileName, 1, 8)) = 1
then    ImportFileName
else    null
end as ImportFileName
from    #Import
) i
where   datepart(year, cast(substring(i.ImportFileName, 1, 8) as date)) = 2013
-- Drop temp table.
drop table #Import

How to simply compare two tables on different SQL Server instances.

I restored a backup of a database on a different SQL Server instance running on the same server and I wanted to compare data in a table found on SQL Server Instance 1 with the same table on SQL Server Instance 2.

 

First add a linked server (all queries will be run from [MyServer\MyInstance1])

exec sp_addlinkedserver @server = 'MyServer\MyInstance2'
go

Then compare the data in the two tables with a except statement:

 

select * from [MyServer\MyInstance1].MyDatabase1.dbo.MyTable1
except
select * from [MyServer\MyInstance2].MyDatabase1.dbo.MyTable1

Fix: ‘Could not find the Database Engine startup handle’ during SQL Server 2014 installation on Windows 8.1

After the installation of SQL Server 2014 was almost completed on a Windows 8.1 workstation, I got the error: "Could not find the Database Engine startup handle".

 

This was caused by the fact, that I used all standard settings during installation. When you choose all to install SQL Server 2014 with all default options, the SQL Server windows service will run under the NT Service\MSSQL$V2014 account, after changing this login to a correct login (a local admin account on the box) the service started correctly and I could login to the SQL Server 2014 instance.

 

image

Fixing: User ‘Domain\Admin’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed. On a Windows 8.1 machine with SQL Server 2012 installed.

 

After I installed Microsoft SQL Server Reporting Services 2012 and configured it with the Reporting Services Configuration Manager and clicked on de Report Manager URL:

image

 

I got the error message:

image

 

User ‘Domain\Admin’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

 

Solution

Turns out you have to run your browser (e.g. Internet Explorer) as an Administrator

 

image

 

The same is true for the Web Service URL:

 

Error

The permissions granted to user ‘Domain\Admin’ are insufficient for performing this operation. (rsAccessDenied).

image

 

Run as administrator:

image

How to create multiple constraints on one SQL Server column in one statement.

If you want to add a column to a table in SQL Server and the column should contain multiple constraints, you can declare these constraints inline in one statement by separating them with spaces.

if not exists (select 1 from sys.columns where name = 'MyColumn1' and Object_ID = Object_ID('dbo.MyTable1'))
begin 
alter table dbo.MyTable1 with nocheck add MyTable2Id int not null 
constraint FK_dbo_MyTable1_MyTable2Id foreign key references dbo.MyTable2Id(Id)
constraint DF_dbo_MyTable1_MyTable2Id default(1)
end
go

Implement conditional (IF THEN ELSE) logic based on a SQL Server Store Procedure result in SSIS 2012

There are many ways you could implement conditional (IF THEN ELSE) logic based on a SQL Server Store Procedure result in SSIS, here is just one way:

 

Note

For demo purpose I will be using the master database here.

 

Stored Procedure

Create sproc on master database

if  object_id('dbo.ColumnExists') is not null
begin
drop procedure dbo.ColumnExists
end
go
-- Determines if the given column exists in the given table.
create procedure dbo.ColumnExists
@TableName varchar(128), -- Full table name, like dbo.MyTable
@ColumnName varchar(128)
as
begin
set nocount on
declare @ColumnExistsResult bit = 0
-- Check if table exists.
if exists (select top 1 1 from sys.objects where Object_ID = Object_ID(@TableName)) 
begin 
-- Check if column exists in table.
if exists (select top 1 1 from sys.columns where name = @ColumnName and Object_ID = Object_ID(@TableName)) 
begin 
set @ColumnExistsResult = 1
end
end
-- Return single row to SSIS.
select @ColumnExistsResult as ColumnExistsResult -- This name should be used as ResultSet name in 
end
go

Add connection

image

 

 

Add package variables

image

 

For demo purposes I entered a default value for TableName (“dbo.spt_monitor”) and ColumnName (“lastrun”).

 

Add Execute SQL Task

Change Connection to the name of connection created in the previous step.

Change ResultSet to “Singel row”

Change SQLSourceType to “Direct input”, so the task will use the t-sql query in the property “SQL Statement”

Change SQLStatement to “exec dbo.ColunExists ?, ?”

Note multiple parameters should be separated by “,”.

 

image

 

Add parameter mapping

De parameters are linked to the question marks in the SQL Statement.

The first “?” in exec dbo.ColumnExists will be linked to Parameter Name “0” on the Parameter Mapping page.

Note on Date Type (data type “DATE” is datetime in t-sql, data type VARIANT_BOOL” is t-sql data type bit).

 

image

 

Setting Result Set

On the Result Set page you  can map the column names from the stored procedure call to SSIS package variables.

 

image

 

Setting the values for the precedence constraints

If  you want a data flow to be executed, when the “Execute SQL Task” fails or the given column does not exists, you should set the precedence constraint as follow:

 

image

 

If you want a data flow to be executed, when the “Execute SQL Task” succeeds and the given column exists, you should set the precedence constraint as follow:

image

 

The end result should look like

image

When we execute the package and the column exists the debugger should be paused on the left data flow:

image

 

When the column does not exist the debugger should be paused at the right dataflow:

image

 

 

When the “execute sql task” fails the debugger will be paused at the left dataflow:

image