Tag Archives: Tips

General Microsoft SQL Server tips

#0407 – SQL Server – Clearing out the list of servers in SSMS


Today, I will talk about a very common question that I see in the forums. When you  work with a lot of SQL Server instances, the list of servers seen on the login screen in the SQL Server Management Studio (SSMS) becomes quite long, raising the question:

How to clear out the list of SQL Server instance names in SSMS?

SQL Server 2014 and above

Clearing out servers that no longer exist or to which you no longer need to connect to is quite simple in SQL Server 2014 and above. All you need to do is:

  1. Open SSMS
  2. In the login window,  expand the list of available SQL Server instances
  3. Use the keyboard’s down arrow or use the mouse to scroll down to the instance that needs to be deleted
  4. Once the required instance is selected in the list, just press “Delete” on the keyboard
SSMS_LoginWindow

Just select the appropriate SQL Server instance and press “Delete” to remove it from the SSMS login history

If you are still using an older version of SSMS due to various reasons, there is a manual workaround to this as shown below.

SSMS for SQL Server 2012 and below

  1. Close all open instances of SSMS on your workstation
  2. Depending upon your version of the SSMS client tools, navigate to and delete the files as shown in the table below:
  3. Launch SSMS
  4. It might take a little while to launch, as it recreates the “SqlStudio.bin”/”mru.dat
    • Once launched, you will see that the entire SSMS history is gone
SSMS Client Tools Version Path File to Delete
SQL 2012 %USERPROFILE%AppDataRoamingMicrosoftSQL Server Management Studio11.0 SqlStudio.bin
SQL 2008 %USERPROFILE%AppDataRoamingMicrosoftMicrosoft SQL Server100ToolsShell SqlStudio.bin
SQL 2005 %USERPROFILE%AppDataRoamingMicrosoftMicrosoft SQL Server90ToolsShell mru.dat

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

#0406 – SQL Server – Remember that spaces and blank strings are the same


It was recently brought to my attention that a particular script was passing spaces when it should not. Here’s an example:

DECLARE @spaceCharacter NVARCHAR(1) = N' ';
DECLARE @blankCharacter NVARCHAR(1) = N'';

--Confirm that we are looking at different values
--The ASCII codes are different!
SELECT ASCII(@spaceCharacter) AS ASCIICodeForSpace,
       ASCII(@blankCharacter) AS ASCIICodeForBlankString;

--Compare a blank string with spaces
IF (@spaceCharacter = @blankCharacter)
    SELECT 'Yes' AS IsSpaceSameAsBlankString;
ELSE 
    SELECT 'No' AS IsSpaceSameAsBlankString;
GO

/* RESULTS
ASCIICodeForSpace ASCIICodeForBlankString
----------------- -----------------------
32                NULL

IsSpaceSameAsBlankString
------------------------
Yes
*/

01_Symptom

We then checked the LENGTH and DATALENGTH of both strings and noticed something interesting – the check on the LENGTH was trimming out trailing spaces whereas the check on the DATALENGTH was not.

DECLARE @spaceCharacter NVARCHAR(1) = N' ';
DECLARE @blankCharacter NVARCHAR(1) = N'';

--Check the length
SELECT LEN(@spaceCharacter) AS LengthOfSpace, 
       LEN(@blankCharacter) AS LengthOfBlankCharacter,
       DATALENGTH(@spaceCharacter) AS DataLengthOfSpace, 
       DATALENGTH(@blankCharacter) AS DataLengthOfBlankCharacter;
GO

/* RESULTS
LengthOfSpace LengthOfBlankCharacter DataLengthOfSpace DataLengthOfBlankCharacter
------------- ---------------------- ----------------- --------------------------
0             0                      2                 0
*/

02_LengthAndDataLength

Often, we loose sight of the most basic concepts – they hide in our subconscious. This behaviour of SQL Server is enforced by the SQL Standard (specifically SQL ’92) based on which most RDBMS systems are made of.

The ideal solution for an accurate string comparison was therefore to also compare the data length in addition to a normal string comparison.

DECLARE @spaceCharacter NVARCHAR(1) = N' ';
DECLARE @blankCharacter NVARCHAR(1) = N'';

--The Solution
IF (@spaceCharacter = @blankCharacter) 
   AND (DATALENGTH(@spaceCharacter) = DATALENGTH(@blankCharacter))
    SELECT 'Yes' AS IsSpaceSameAsBlankString;
ELSE 
    SELECT 'No' AS IsSpaceSameAsBlankString;
GO

/* RESULTS
IsSpaceSameAsBlankString
------------------------
No
*/

03_Solution

Further Reading

  • How SQL Server Compares Strings with Trailing Spaces [KB316626]

Until we meet next time,

Be courteous. Drive responsibly.

#0405 – SQL Server – Msg 5133 – Backup/Restore Errors – Directory lookup for file failed – Operating System Error 5(Access is denied.).


We got a new server recently and one of my colleagues ran into an error when restoring a database. The error was a quite generic (reformatted below for readability):

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file 
"C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf" 
failed with the operating system error 5(Access is denied.).

Msg 3156, Level 16, State 3, Line 1
File 'AdventureWorks2014_Data' cannot be restored to 
'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf'. 
Use WITH MOVE to identify a valid location for the file.

My immediate reaction was to  review the restore script.

USE [master];
GO
RESTORE DATABASE [AdventureWorks2014]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak'
WITH
MOVE 'AdventureWorks2014_Data' TO 'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf',
MOVE 'AdventureWorks2014_Log' TO 'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Log.ldf';
GO

All looked well, I subsequently moved to the environmental aspect of troubleshooting. It was a new server and we had just created the target folders to which the database was to be restored.

We attempted to restore to the default database locations configured during SQL Server installation and the restore worked. So, one thing that became clear: the SQL Server service did not have appropriate security rights on the destination folder.

The Solution

Once we determined that it was the security rights on the destination folder, the only thing remaining was to grant the rights. Here’s how we do it.

  1. Cross-check the user set as the service user for Microsoft SQL Server database engine (use the SQL Server Configuration Manager for interacting with the SQL Server service – here’s why).
  2. Under Folder properties, ensure that this user has full security rights (or at least equivalent to the rights assigned on the default database folders specified at the time of installation)

Here’s are detailed screenshots showing the above process.

01_SQLServerConfigurationManager

Identifying the user running the SQL Server Database Engine service

02_GrantingPermissions_01

Navigating into file system folder security options to grant access to the SQL Server service

02_GrantingPermissions_02

Choosing the appropriate account running the SQL Server service

02_GrantingPermissions_03

Applying appropriate rights to folder

By the way: If you encounter similar issues in accessing your backup files, the root cause and solution are the same. Check the permissions on the folders housing your backups and you should  see that the database engine does not have the necessary security rights.

Further Reading

Maintaining permissions on data folders and appropriate registry entries is something that is handled by the SQL Server Configuration Manager when you change the service account under which  the database engine is running. If you use services.msc (the old way), this is not done and your SQL Server may stop working.

  • Changing SQL Server Service Account or Password – Avoid restarting SQL Server [Blog Link]
  • Blog Post #0344 – SQL Server – Missing Configuration Manager on Windows 8 [Blog Link]

Until we meet next time,

Be courteous. Drive responsibly.

#0403 – SQL Server – CAST/CONVERT to string – Pad zeroes or spaces to an integer


Helping the community via forums often leads to some very interesting moments. Recently, I came across quite a common question – as part of a data migration, someone wanted to pad integers with zeroes. There are various variations to this question, namely:

How do I pad zeroes to  convert an integer to a fixed length string?

How do I pad zeroes before an integer?

How to I pad blank spaces before an integer?

All of these questions have quite a simple solution, which I am going to present before you today.

The script demonstrates the process of padding the required values to a set of integers in a test table. The script:

  1. Converts the Integer to a string
  2. Appends this string representation of the integer to the padding string
  3. Finally, returns the required number of characters from the right of the string

For the purposes of this demo, I have shown the result with two padding characters – a zero (0) and an asterisk (*).

Have you ever faced such a requirement as part of a data migration or an integration? Do you use a similar approach? Do share your thoughts and suggestions in the space below.

--Pad zeroes in string representation of a number
USE tempdb;
GO
--Safety Check
IF OBJECT_ID('dbo.TestTable','U') IS NOT NULL
BEGIN
   DROP TABLE dbo.TestTable;
END
GO

--Create the test tables
CREATE TABLE dbo.TestTable
            (RecordId    INT NOT NULL IDENTITY(1,1),
             RecordValue INT     NULL
            );
GO

--Populate some test data
INSERT INTO dbo.TestTable (RecordValue)
VALUES (123),
       (1023),
       (NULL);
GO

/**************** PADDING CHARACTER: ZERO (0) ****************************/

--Change the padding character and the number of strings as required
DECLARE @requiredStringLength INT = 10;
DECLARE @paddingCharacter CHAR(1) = '0'

--The script:
--1. Converts the Integer to a string
--2. Appends this string representation of the integer to the padding string
--3. Finally, returns the required number of characters from the right of the string
SELECT RecordId,
       RecordValue AS OriginalValue,
       RIGHT( (REPLICATE( @paddingCharacter, @requiredStringLength )
              + CAST(RecordValue AS VARCHAR(20))
              ),
              @requiredStringLength
            ) AS PaddedValue
FROM dbo.TestTable AS tt;
GO

/* RESULTS
RecordId    OriginalValue PaddedValue
----------- ------------- ------------
1           123           0000000123
2           1023          0000001023
3           NULL          NULL

*/

/**************** PADDING CHARACTER: ASTERISK (*) ****************************/

--Change the padding character and the number of strings as required
DECLARE @requiredStringLength INT = 10;
DECLARE @paddingCharacter CHAR(1) = '*'

--The script:
--1. Converts the Integer to a string
--2. Appends this string representation of the integer to the padding string
--3. Finally, returns the required number of characters from the right of the string
SELECT RecordId,
       RecordValue AS OriginalValue,
       RIGHT( (REPLICATE( @paddingCharacter, @requiredStringLength )
               + CAST(RecordValue AS VARCHAR(20))
              ),
              @requiredStringLength
            ) AS PaddedValue
FROM dbo.TestTable AS tt;
GO

/* RESULTS
RecordId    OriginalValue PaddedValue
----------- ------------- ------------
1           123           *******123
2           1023          ******1023
3           NULL          NULL
*/

Until we meet next time,

Be courteous. Drive responsibly.

#0402–SQL Server 2016 – KB3207512 fails–Msg 17054 – Unable to shutdown the instance; Operating System Error 21 (The device is not ready)


I recently updated my personal sandbox to use SQL Server 2016. While the installation succeeded, one of the first few problems that I ran into were:

  • The SQL Server 2016 instance failed to shutdown
  • Error 17054 was logged every time a shutdown is attempted with the error: “The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full.
  • Installation of KB3207512 (update for SQL Server 2016 SP1 Reporting Services) and latest CUs kept failing
  • User databases would not be accessible with an error: “The operating system returned error 21(The device is not ready.)

While I was trying to figure out what was wrong, I ran into the same problem with a few other instances.

The Solutions

After a lot of rounds of trial and error, the following changes finally did the trick. The items below collectively make up the solution and all items need to be performed in order to get the SQL server instance up-to speed again.

  • Launch the SQL Server Configuration Manager
  • Under “SQL Server Network Configuration” ensure that the TCP/IP protocol is enabled
  • Under “SQL Server Services”, ensure that the following services are started:
    • SQL Server PolyBase Engine
    • SQL Server PolyBase Data Movement

Once the steps provided above are done, all the problems listed above should cease to exist.

Until we meet next time,

Be courteous. Drive responsibly.