Archive for the ‘SQL Server’ Category

Reset SQL Server SA Login via Powershell

June 13th, 2018 No comments

The Problem

You have lost or forgotten the SA password for you SQL Server box!!

The solution

This is really easy to fix.
On the server do the following:

  1. Launch Powershell as an administrator
  2. Run:
    Install-Module dbatools
  3. Answer “Y” when prompted (twice for me).
  4. Run
    Reset-DbaAdmin -SqlServer localhost\SQLEXPRESSS

    (or whatever your database is, a “.” will suffice for a standard default sql installation”)
  5. Answer “Y” to the prompts
  6. Enter your new sa password when prompted
  7. Wait

That should be it.
Try logging in as “sa” with your new password and you can now are good to go.

Full details of the dbatools can be found here;

VN:F [1.9.22_1171]
Rating: 8.0/10 (1 vote cast)

Categories: SQL Server Tags:

Grant Connect SQL Server – TSQL

July 27th, 2017 No comments

The issue

You are having connection problems to the database and all the permissions look correct but you cannot login.
Fire up the SSMS and expand the Security tree at the individual database and you notice that your login is there but with a little red down arrow.

The Fix

You need to grant “Connect” to your user.

Assume your user is a domain account DOMAIN\username

use MyDatabase 

Grant Connect to [DOMAIN\username];
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: SQL Server, T-SQL, Uncategorized Tags:

SQL Server Management Studio on Hi Res Screen

September 13th, 2016 No comments

The problem

You are using SSMS on one of these new wizbang hi res screens and all the dialogs are tidy and small and you cannot addminister your SQL Server.

The Fix

I found this somewhere on the internet and it works.

Step1: Create the Ssms.exe.manifest

Take the following code and save into a file called, Ssms.exe.manifest

<?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; standalone=&quot;yes&quot;?>
<assembly xmlns=&quot;urn:schemas-microsoft-com:asm.v1&quot; manifestVersion=&quot;1.0&quot; xmlns:asmv3=&quot;urn:schemas-microsoft-com:asm.v3&quot;>
    <assemblyIdentity type=&quot;win32&quot; name=&quot;Microsoft.Windows.Common-Controls&quot; version=&quot;; processorArchitecture=&quot;*&quot; publicKeyToken=&quot;6595b64144ccf1df&quot; language=&quot;*&quot;>
    <assemblyIdentity type=&quot;win32&quot; name=&quot;Microsoft.VC90.CRT&quot; version=&quot;9.0.21022.8&quot; processorArchitecture=&quot;amd64&quot; publicKeyToken=&quot;1fc8b3b9a1e18e3b&quot;>
<trustInfo xmlns=&quot;urn:schemas-microsoft-com:asm.v3&quot;>
      <requestedExecutionLevel level=&quot;asInvoker&quot; uiAccess=&quot;false&quot;/>
  <asmv3:windowsSettings xmlns=&quot;;>
    <ms_windowsSettings:dpiAware xmlns:ms_windowsSettings=&quot;;>false</ms_windowsSettings:dpiAware>

Save that and then copy it into the directory where your SSMS.exe lives.
Mine was SQL 2014 and it lived in “C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio”

Step 2: Update the registry

Save this into a .reg file and run it

Windows Registry Editor Version 5.00

Step 3: run it

Run SSMS and sit back and marvel at how it sizes its dialogs correctly.

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)

Categories: SQL Server Tags:

SQL Server Script user with SID

September 18th, 2015 No comments

The Problem

When you move sql logins around servers the logins are created with an SID that uniquely identifies the login.

Assume you setup two logins manually, one on your production server and one on your dev server.

When you restore your prod database to development your Login will be “disconnected” from the restored database and the SID of the “User” in the restored production database does not match the SID of the Login on your dev box.

The Solution

Create your login specifying the SID.
The easiest way is to let SQL set and SID and then read it off the DB and use it in your new login creation script
This script should do it

Use master

-- Step 1 - Create Login
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'usr_website')
DROP LOGIN [usr_website]

CREATE LOGIN [usr_website] WITH PASSWORD = 'password:-)'

-- Step 2 - Get SID
Declare @name nvarchar(254)

set @name = 'usr_website'

FROM [msdb].[sys].[syslogins]
where name = @name or @name is null

-- Step 3 - Create Login with SID
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'usr_website')
DROP LOGIN [usr_website]

CREATE LOGIN [usr_website] WITH PASSWORD = 'password:-)', SID = 0x346837D1EC9C484C91A3B46D7EC7EABF

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: SQL Server, T-SQL Tags:

T-SQL Drop temp table if it exists

September 9th, 2015 No comments

The Problem

I often work in SQL Server with temp tables and they can be a pain when developing as you may have code like

Create Table #tblTemp( Id int , Value nvarchar(32))

Insert Into #tblTemp
select Id, Value from SomeOtherTableAndLotsOfJoins

--Drop Table #tblTemp

You comment out the drop as you want to check the results and do some testing but next time you want to run the script you have to remember to call the “Drop Table”

The Solution

Put this at the top of your script

if exists(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tblTemp'))
	drop table #tblTemp;
VN:F [1.9.22_1171]
Rating: 5.0/10 (1 vote cast)

Categories: T-SQL Tags:

Terrible Performance of SQL Server Linked Server query over WAN

April 14th, 2015 2 comments

The problem

I have a linked server connection between 2 SQL servers approx half the world away. One server resides in Sydney, Australia and the other in London, UK.

I was getting terrible performance.

To simulate this for testing purposes, on my home internet connection, I created an Azure database in Northern Europe and my local SQL server is in Sydney.

I used the DimDate table as created in this code project example.
(This is a 36 column table)

I ran this against my azure database and populated it with 45,656 records, (days between 1-Jan-1974 to 31-Dec-2099, just adjust the populate script)

On my local sql server, I have a Linked Server connection called LSEUROPE set to use SQLNCLI – SQL Native Client, configured to connecting to the Azure database in Europe.

On the local sql server:

1. Download and Install Wireshark on the local server,
2. Download and Install Compass wildpackets free from, This is used to visualise the throughput.

Wireshark was enabled and set to monitor only the remote server, (capture filter – “host” where is the IP address of the remote server).

2 tests were executed:

Test 1 – SSMS Direct

1. Open SSMS and connect directly to the REMOTE server.
2. Start Wireshark Capture
3. execute “select * from dbo.DimDate”
4. once finished stop wireshark
5. Save the wireshark session as a .pcap file, (compass only opens .pcap files)

Test 2 – Linked Server

1. Open SSMS and connect to the LOCAL server.
2. Start Wireshark Capture
3. execute “select * from LSEUROPE.TestDB.dbo.DimDate”, (Note: OpenQuery in this instance makes no difference as all we are testing is the throughput of transferring data from the other side of the world.)
4. once finished stop wireshark
5. Save the wireshark session as a .pcap file.

Fig 1. SSMS Direct Test – 35 sec

Fig 2. Linked Server Test – 6m 29 sec

As you can see, throughput on the Linked server query is limited to 0.22 Mbits/s, whilst the remote SSMS query happily averages around 3Mbits/s (again, this is just on my standard ADSL2+ home internet connection, but I have replicated this exact same pattern at work on a 20MBps dedicated WAN link).

There is some bottle neck that is slowing performance of the data being sent via a linked server connection!!

A solution of sorts

After much playing and testing of the various Linked server connections I have managed to get the following results over a linked server connection.

Fig 3

Fig 4

Figure 4 was obtained by creating a ODBC System DSN of type ODBC Driver 11 for SQL Server. This provided the fastest performance, bringing down the same 45,656 rows in 27 seconds.

Figure 3 was obtained by creating the ODBC System DSN using the SQL Native Client. It is now approx 46 seconds which is much better than 6 minutes, but still not the 27 seconds of the ODBC driver.

After multiple runs I was getting similar performance regardless of the Client used in the DSN


In both cases Fig 3 and Fig 4, the linked server was created using the “Microsoft OLE DB Provider for ODBC Drivers” and configured the Linked server to use the System DSN.


Now the real question, why does this seem to work?

I am going to test using this as I am sure there will be synax type issues of changing the linked server connection, (I think ODBC doesn’t handle TEXT/NTEXT fields)

Microsoft, what is going on here?

I am going to be trying to find out what the reason is for the poor performance of the “Microsoft OLEDB provider of SQL Server”, and if there is something that can be done to improve the performance.

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)

Categories: SQL Server Tags:

SSIS Convert between datetimeoffset and datetime

June 11th, 2014 No comments

The Problem

I had a sql table that was storing data in a DateTimeOffset(7) datatype, DateDeleted.

I was trying to use SSIS to sync the data to another table where the DateDelted column was a DATETIME datatype.

How do you cast/convert between a DateTimeOffset(7) datatype to a DateTime object

The Solution

In a derived column object use the following formula

DATEADD("hh", DATEDIFF("hh",((DT_DBTIMESTAMP)DateDeleted), DateDeleted), ((DT_DBTIMESTAMP)DateDeleted))

I will walk you through what this actually is.

Part 1 – Base Cast


This is just a standard type conversion.
The problem!!

When you try this you will receive a “Truncation Error”.

  1. Open the derived column transformation editor
  2. Click on “Configure Error Output”
  3. On this page for your derived column (in my case C_DateDeleted), set the Truncation value to “Ignore failure”

SSISConfigureError output

So now it will work, but the problem is the offset is lost. In my case I am in Australia, +10 UTC.

Part 2 – Add the Offset back

So I want to add 10 hours back so I changed my derived column to

DATEADD("hh", 10, ((DT_DBTIMESTAMP)DateDeleted))

This works great, but what about daylight saving time when the offset may alter to 11 hours etc.

Part 3 – calculate the offset

DATEDIFF("hh",((DT_DBTIMESTAMP)DateDeleted), DateDeleted)

This says get the hours difference between that DateTime version of the date ((DT_DBTIMESTAMP)DateDeleted) and the DateTimeOffset version of the date.

Part 4 – Final version

Now put it all together

DATEADD("hh", DATEDIFF("hh",((DT_DBTIMESTAMP)DateDeleted), DateDeleted), ((DT_DBTIMESTAMP)DateDeleted))

Easy :-)… not!!!

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: SSIS Tags:

SQL Server Enable User

March 5th, 2014 No comments

The problem

You have a SQL Login created for your account usr_test
Everything appears fine, but you cannot connect.
When you look in the users section of your database, the user appears with a disabled icon? (little red down arrow)

The issue

Your user does not have “CONNECT” permission and therefore shows as disabled.

The solution

In SQL management studio, do the following;

  1. Connect to your SQL Server
  2. Switch to the database having the issues
    Use MyDatabase
  3. Execute the following statement
    Grant Connect to usr_test
  4. Refresh the list of users and now the user looks normal and you can connect to the database

    VN:F [1.9.22_1171]
    Rating: 0.0/10 (0 votes cast)

Categories: SQL Server Tags:

SQL Sever DB Mail Logs

January 6th, 2014 No comments

If you use SQL Server Database mail sometimes you will have trouble and you want to see a log of all the emails that have been sent or failed to send.

To do this there are some management views that will help check what has been going on:

select * from msdb.dbo.sysmail_allitems
Order By mailitem_id desc

select * from msdb.dbo.sysmail_faileditems;

Note Also if you need to force a restart of the DBMail service, then use the following:

exec msdb.dbo.sysmail_stop_sp;
exec msdb.dbo.sysmail_start_sp;
VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)

Categories: SQL Server, T-SQL Tags:

Visual Studio SQLCompare does not Compare SQLCMD Variables correctly

November 29th, 2013 6 comments

The problem

When using the Visual Studio SQL Compare to compare your database schema to your physical database it returns heaps of false positives, where SQLCompare is suggesting that your CMD variable does not match what is on the database.

The Solution

SQLCompare uses the default or local CMD variables that you have on your project when performing the compare.

You need to ensure the default variables match the target database you are comparing against.

To change your defaults open the properties of your Database project, switch to the “SQLCMD Variables” tab and edit the defaults.

When you get your variables in alignment with your target comparison database you will get a true indication of the differences.

VN:F [1.9.22_1171]
Rating: 7.3/10 (3 votes cast)

Categories: SQL Server, SSDT, Visual Studio Tags: