Archive

Archive for the ‘SQL Server’ Category

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;>
 
<dependency>
  <dependentAssembly>
    <assemblyIdentity type=&quot;win32&quot; name=&quot;Microsoft.Windows.Common-Controls&quot; version=&quot;6.0.0.0&quot; processorArchitecture=&quot;*&quot; publicKeyToken=&quot;6595b64144ccf1df&quot; language=&quot;*&quot;>
    </assemblyIdentity>
  </dependentAssembly>
</dependency>
 
<dependency>
  <dependentAssembly>
    <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;>
    </assemblyIdentity>
  </dependentAssembly>
</dependency>
 
<trustInfo xmlns=&quot;urn:schemas-microsoft-com:asm.v3&quot;>
  <security>
    <requestedPrivileges>
      <requestedExecutionLevel level=&quot;asInvoker&quot; uiAccess=&quot;false&quot;/>
    </requestedPrivileges>
  </security>
</trustInfo>
 
<asmv3:application>
  <asmv3:windowsSettings xmlns=&quot;http://schemas.microsoft.com/SMI/2005/WindowsSettings&quot;>
    <ms_windowsSettings:dpiAware xmlns:ms_windowsSettings=&quot;http://schemas.microsoft.com/SMI/2005/WindowsSettings&quot;>false</ms_windowsSettings:dpiAware>
  </asmv3:windowsSettings>
</asmv3:application>
 
</assembly>

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
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\SideBySide]
&quot;PreferExternalManifest&quot;=dword:00000001

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
GO

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

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

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

set @name = 'usr_website'

SELECT 
	[sid]
	,[status]
	,[name]
	,[dbname]
	,[language]
	,[denylogin]
	,[hasaccess]
	,[isntname]
	,[isntgroup]
	,[isntuser]
	,[sysadmin]
	,[securityadmin]
	,[serveradmin]
	,[setupadmin]
	,[processadmin]
	,[diskadmin]
	,[dbcreator]
	,[bulkadmin]
	,[loginname]
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]
GO

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

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'))
begin
	drop table #tblTemp;
end
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes 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. http://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho
(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, https://www.wireshark.org/download.html
2. Download and Install Compass wildpackets free from, http://download.cnet.com/Compass-Free/3000-2085_4-75447541.html. This is used to visualise the throughput.

Wireshark was enabled and set to monitor only the remote server, (capture filter – “host 10.10.0.1” where 10.10.0.1 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
SSMS_Remote

Fig 2. Linked Server Test – 6m 29 sec
LSRemote

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
LSRemote_SNCLIDSN

Fig 4
LSRemote_ODBCDSN

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

ODBCSystemDSN

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.

LSConfig

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

(DT_DBTIMESTAMP) DateDeleted

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)
usr_test_disabled

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
    GO
    
  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:

exec sp_executesql nvarchar(max) truncating at 4000 characters

November 28th, 2013 1 comment

The Problem

Sometimes you may need to write dynamic SQL.

One example is writing query for running over linked server connections. You might write something like this

Create Procedure prcTest
    @start datetime
as
Begin
    Declare @sql nvarchar(max)

    set @sql = 'Insert Into #tblTemp
    select * from OpenQuery(LINKSERVER, ''Select * from dbo.tblRemote r
        inner join tblRemote2 r2 on r.Id = r2.fkId 
    .... lots more sql x 4000+ characters
    Where r.DateCreated > ''''' + Convert(varchar, @start, 106) + '''''
    '')';

    exec sp_executesql @sql

    select * from #tblTemp inner join tblOther on id = otherId

End

Assuming this has over 4000 characters it will truncate at 4000 characters, your SQL will be malformed as it is missing the closing quote ‘ and will crash.

The Resolution

So what is going on!

I’ve read everywhere on the internet about nvarchar(max) having a 2GB limit etc, but it is clearly truncating at 4000 characters.

Add in a

select Len(@sql)

to confirm that it is definitely a 4000 character limit.

So what is going on?

Implicit Type Conversion!!!

The innocent looking code adding a date to the query,

 ''''' + Convert(varchar, @start, 106) + '''''

is causing SQL Server to perform an implicit type conversion and our NVarchar(MAX) is somehow becoming an NVarchar(4000).

Change the addition of the date to the following

''''' + Convert(nvarchar(max), @start, 106) + '''''

The problem is averted and the select Len(@sql) will happily count beyond 4000.

(BTW convert(varchar, @date, 106), creates a ‘1-Jan-2000’ formatted date which resolves any issues that can occur with different regional settings on your sql server causing problems)

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

Categories: SQL Server, T-SQL Tags:

SSIS 2012 Project Deployment – Set CheckpointFile with Parameters

July 23rd, 2013 No comments

The Problem

SSIS in SQL Server 2012 now supports a “Project Deployment” model. Adding things like Connection Strings is relatively intuitive.
I want to use a parameter to set my CheckPointFileName property.
How can you do this so it can be configured by the environment.

The Solution

  1. Create a parameter in your package and call it “CheckpointFileName”
  2. Click On the ellipses “…” on the Expressions property.
  3. In property Choose CheckPointFileName from the drop down
  4. Set the expression by opening the expression builder and choosing the parameter you just created, (sometimes you need to drag and drop the paramater into the expression box)

    ExpressionBuilderCheckpoint

  5. Click OK

Now you can deploy your package and set this parameter as part of you environment setup and change the checkpoint file name as required

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

Categories: SQL Server, SSIS Tags: