SSMS can’t see network locations

I was having problems with SSMS (SQL Server Management Studio) 2016 which I have been running with administrator permissions, whereby I couldn’t open files on my mapped network drives.

Doing a bit of Google’ing, I came across this article – https://technet.microsoft.com/en-us/library/ee844140(v=WS.10).aspx

I applied the registry fix:

To configure the EnableLinkedConnections registry value

  1. Click Start, type regedit in the Start programs and files box, and then press ENTER.
  2. Locate and then right-click the registry subkey HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindowsCurrentVersionPoliciesSystem.
  3. Point to New, and then click DWORD Value.
  4. Type EnableLinkedConnections, and then press ENTER.
  5. Right-click EnableLinkedConnections, and then click Modify.
  6. In the Value data box, type 1, and then click OK.
  7. Exit Registry Editor, and then restart the computer.

And, it has fixed the problem 🙂

Script to archive offline databases

Below is the script that can be used to process the offline databases:

  • Bring database online
  • Backup the database
  • Detach the database
  • Move the MDF and LDF file(s)

The first part to bring the databases online, back them up, and detach them, is:

Please ensure you test any script taken from my website on a test/development machine, before running on a production server

USE master
GO

DECLARE @backuplocation VARCHAR(500)
SET @backuplocation = 'E:SQLBackups'

CREATE TABLE TempFilesToMove
( filename VARCHAR(500)
)

DECLARE csr CURSOR FAST_FORWARD FOR
    SELECT name FROM sysdatabases
    WHERE (status & 512) = 512

DECLARE @name VARCHAR(255)
DECLARE @sql VARCHAR(1000)

OPEN csr
FETCH NEXT FROM csr INTO @name

IF @backuplocation not like '%' SET @backuplocation = @backuplocation + ''

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER DATABASE [' + @name + '] SET ONLINE'
    EXEC (@sql)
    SET @sql = 'BACKUP DATABASE [' + @name + '] TO DISK = N' + CHAR(39) + @backuplocation + @name + '.bak' + CHAR(39)
    EXEC (@sql)
    SET @sql = 'INSERT INTO TempFilesToMove (filename) SELECT filename FROM [' + @name + '].dbo.sysfiles'
    EXEC (@sql)
    EXEC sp_detach_db @name
    FETCH NEXT FROM csr INTO @name
END

CLOSE csr
DEALLOCATE csr

Please ensure you take a copy of the data in TempFilesToMove using the statement below to make sure you have a list of where the files are:

SELECT * FROM TempFilesToMove

The second part to move the MDF and LDF files, is:

DECLARE @backuplocation VARCHAR(500)
SET @backuplocation = 'E:SQLBackups'

DECLARE csr2 CURSOR FAST_FORWARD FOR 
    SELECT filename FROM TempFilesToMove 

DECLARE @filename VARCHAR(500)
DECLARE @cmd VARCHAR(500)
OPEN csr2
FETCH NEXT FROM csr2 INTO @filename

EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

WHILE @@FETCH_STATUS = 0
BEGIN
    
    SET @cmd = 'MOVE ' + @filename + ' ' + @backuplocation    
    EXEC master..xp_cmdshell @cmd
    FETCH NEXT FROM csr2 INTO @filename
END

CLOSE csr2
DEALLOCATE csr2

DROP TABLE TempFilesToMove

If the service account that runs SQL Server doesn’t have necessary permissions to the archive location, then you may get Access Denied messages when you run the second part. That’s why I suggest taking a copy of the TempFilesToMove table, just in case you need to run it manually.

SQL: Revised list of table columns, with data types, keys, constraints, etc.

I have just improved the script in SQL: List of table columns, with data types:

Please ensure you test any script taken from my website on a test/development machine, before running on a production server

It now includes an indicate for nullable fields, primary and foreign keys, unique and check constraints, and identity columns

SELECT
      schema_name(O.schema_id) + '.' + O.name AS TableName,
      C.name AS ColumnName,
      T.name AS Type,
      C.max_length AS Length,
	  CASE WHEN c.is_nullable = 1 THEN 'YES' ELSE NULL END AS Nullable,
	  CASE WHEN P.ColumnName IS NOT NULL THEN 'YES' ELSE NULL END AS PrimaryKey,
	  CASE WHEN F.ReferencingColumN IS NOT NULL THEN 'YES' ELSE NULL END AS ForeignKey,
	  CASE WHEN U.ColumnName IS NOT NULL THEN 'YES' ELSE NULL END AS [Unique],
	  CC.Definition AS CheckConstraint,
	  CASE WHEN c.is_identity = 1 THEN 'YES' ELSE NULL END AS [Identity]
FROM        sys.objects O
INNER JOIN  sys.columns C ON O.object_id = C.object_id
INNER JOIN  sys.types T ON C.user_type_id = T.user_type_id
LEFT JOIN (
	SELECT p.name AS ParentTable, pc.name AS ParentColumn, r.name AS ReferencingTable, rc.name AS ReferencingColumn
	FROM sys.foreign_key_columns f
	JOIN sys.objects p ON f.parent_object_id = p.object_id
	JOIN sys.objects r ON f.referenced_object_id = r.object_id
	JOIN sys.columns pc ON f.parent_object_id = pc.object_id AND f.parent_column_id = pc.column_id
	JOIN sys.columns rc ON f.referenced_object_id = rc.object_id AND f.referenced_column_id = rc.column_id
) AS F ON O.name = F.ParentTable AND C.name = F.ParentColumn
LEFT JOIN(
	SELECT i.name AS IndexName, OBJECT_NAME(ic.OBJECT_ID) AS TableName, COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
	FROM sys.indexes AS i 
	JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
	WHERE i.is_primary_key = 1
) AS P ON O.name = P.TableName AND C.name = P.ColumnName
LEFT JOIN(
	SELECT i.name AS IndexName, OBJECT_NAME(ic.OBJECT_ID) AS TableName, COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
	FROM sys.indexes AS i 
	JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
	WHERE i.is_unique_constraint = 1
) AS U ON O.name = U.TableName AND C.name = U.ColumnName
LEFT JOIN(
	SELECT co.name AS TableName, c.name AS ColumnName, definition 
	FROM sys.check_constraints cc
	JOIN sys.objects co ON cc.parent_object_id = co.object_id
	JOIN sys.columns c ON co.object_id = c.object_id AND cc.parent_column_id = c.column_id
) AS CC ON O.name = CC.TableName AND C.name = CC.ColumnName
WHERE O.type = 'U'
ORDER BY
      schema_name(O.schema_id),
      O.name,
      C.column_id
GO

Question: SQL Query

Back in July 2010, I was asked the following question by Pramod:

Is it possible to get a Pivot Table kind of an output as a result of a query. For example I have a table like this

Id Date Sales Region

and as an output I want the a list of region wise sales with a monthly breakup and the respective sales.

Can you help?

My suggestion was to use the query below:

SELECT
  Region, [1] AS Jan2010, [2] AS Feb2010,
          [3] AS Mar2010, [4] AS Apr2010, 
          [5] AS May2010, [6] AS June2010, 
          [7] AS July2010
FROM
(  SELECT Region, Sales, 
          MONTH([Date]) AS SalesMonth
   FROM Sales) S
PIVOT
(
  SUM (Sales)
  FOR SalesMonth IN
    ( [1], [2], [3], [4], [5], [6], [7] )
  ) AS pvt
ORDER BY pvt.Region

This assumed at the time that all the data was for 2010.

Product Category Hierarchy (with Breadcrumb Trail) using CTE query

The example query below creates a product category table and populates it with sample data. I then use a CTE query to return the categories, along with the breadcrumb trail (lineage)

CREATE TABLE dbo.ProductCategory
( ID INT NOT NULL PRIMARY KEY,
  Category VARCHAR(100) NOT NULL,
  ParentID INT NULL)
GO

INSERT INTO dbo.ProductCategory (ID, Category, ParentID)
VALUES	(1, 'All Products', NULL),
	(2, 'Cars', 1), (3, 'Vauxhall', 2),
	(4, 'Corsa', 3), (5, 'Astra', 3),
	(6, 'Ford', 2), (7, 'Fiesta', 6),
	(8, 'Ka', 6), (9, 'Mondea', 6),
	(10, 'BMW', 2), (11, 'Motorbikes', 1),
	(12, 'Kawasaki', 11), (13, 'Suzuki', 11)
GO

WITH CTE_ProductCategories (ID, Category, ParentID, Level, Lineage)
AS (
	SELECT ID, Category, ParentID, 0 AS Level, CAST ([Category] AS VARCHAR (MAX)) AS [Lineage]
	FROM dbo.ProductCategory
	WHERE ParentID IS NULL
	UNION ALL
	SELECT pc.ID, pc.Category, pc.ParentID, p1.Level + 1, p1.Lineage + ' -> ' + CAST ([pc].[Category] AS VARCHAR (MAX))
	FROM dbo.ProductCategory AS pc
	JOIN CTE_ProductCategories AS p1 ON p1.ID = pc.ParentID
)
SELECT ID, Category, ParentID, Level, Lineage
FROM CTE_ProductCategories
ORDER BY ParentID, Category
GO


Results:

IDCategoryParentIDLevelLineage
1All ProductsNULL0All Products
2Cars11All Products -> Cars
11Motorbikes11All Products -> Motorbikes
10BMW22All Products -> Cars -> BMW
6Ford22All Products -> Cars -> Ford
3Vauxhall22All Products -> Cars -> Vauxhall
5Astra33All Products -> Cars -> Vauxhall -> Astra
4Corsa33All Products -> Cars -> Vauxhall -> Corsa
7Fiesta63All Products -> Cars -> Ford -> Fiesta
8Ka63All Products -> Cars -> Ford -> Ka
9Mondea63All Products -> Cars -> Ford -> Mondea
12Kawasaki112All Products -> Motorbikes -> Kawasaki
13Suzuki112All Products -> Motorbikes -> Suzuki

SQL: CONVERT style examples when converting Date/Time to String

The script below can be used to obtain an example of each of the different styles that can be output using the CONVERT Transact-SQL statement. I have output the styles including the century, but the script can easily be modified to output examples without.

DECLARE @Date DATETIME
SET @Date = GETDATE()

SELECT '100' AS [CONVERT style], 'Default' AS Standard, 'mon dd yyyy hh:miAM (or PM)' AS Format, CONVERT(VARCHAR(100), @Date, 100) AS Example
UNION SELECT '101', 'U.S.', 'mm/dd/yyyy', CONVERT(VARCHAR(100), @Date, 101)
UNION SELECT '102', 'ANSI', 'yyyy.mm.dd', CONVERT(VARCHAR(100), @Date, 102)
UNION SELECT '103', 'British/French', 'dd/mm/yyyy', CONVERT(VARCHAR(100), @Date, 103)
UNION SELECT '104', 'German', 'dd.mm.yyyy', CONVERT(VARCHAR(100), @Date, 104)
UNION SELECT '105', 'Italian', 'dd-mm-yyyy', CONVERT(VARCHAR(100), @Date, 105)
UNION SELECT '106', '', 'dd mon yyyy', CONVERT(VARCHAR(100), @Date, 106)
UNION SELECT '107', '', 'Mon dd, yyyy', CONVERT(VARCHAR(100), @Date, 107)
UNION SELECT '108', '', 'hh:mi:ss', CONVERT(VARCHAR(100), @Date, 108)
UNION SELECT '109', 'Default + ms', 'mon dd yyyy hh:mi:ss:mmmAM (or PM)', CONVERT(VARCHAR(100), @Date, 109)
UNION SELECT '110', 'USA', 'mm-dd-yyyy', CONVERT(VARCHAR(100), @Date, 110)
UNION SELECT '111', 'Japan', 'yyyy/mm/dd', CONVERT(VARCHAR(100), @Date, 111)
UNION SELECT '112', 'ISO', 'yyyymmdd', CONVERT(VARCHAR(100), @Date, 112)
UNION SELECT '113', 'Europe default + ms', 'dd mon yyyy hh:mi:ss:mmm(24h)', CONVERT(VARCHAR(100), @Date, 113)
UNION SELECT '114', '', 'hh:mi:ss:mmm(24h)', CONVERT(VARCHAR(100), @Date, 114)
UNION SELECT '120', 'ODBC canonical', 'yyyy-mm-dd hh:mi:ss(24h)', CONVERT(VARCHAR(100), @Date, 120)
UNION SELECT '121', 'ODBC canonical + ms', 'yyyy-mm-dd hh:mi:ss.mmm(24h)', CONVERT(VARCHAR(100), @Date, 121)
UNION SELECT '126', 'ISO 8601', 'yyyy-mm-ddThh:mi:ss.mmm (no spaces)', CONVERT(VARCHAR(100), @Date, 126)


An example output that I have just run is:

CONVERT styleStandardFormatExample
100Defaultmon dd yyyy hh:miAM (or PM)Jun 16 2015 11:43AM
101U.S.mm/dd/yyyy06/16/2015
102ANSIyyyy.mm.dd2015.06.16
103British/Frenchdd/mm/yyyy16/06/2015
104Germandd.mm.yyyy16.06.2015
105Italiandd-mm-yyyy16-06-2015
106 dd mon yyyy16 Jun 2015
107 Mon dd, yyyyJun 16, 2015
108 hh:mi:ss11:43:59
109Default + msmon dd yyyy hh:mi:ss:mmmAM (or PM)Jun 16 2015 11:43:59:457AM
110USAmm-dd-yyyy06-16-2015
111Japanyyyy/mm/dd2015/06/16
112ISOyyyymmdd20150616
113Europe default + msdd mon yyyy hh:mi:ss:mmm(24h)16 Jun 2015 11:43:59:457
114 hh:mi:ss:mmm(24h)11:43:59:457
120ODBC canonicalyyyy-mm-dd hh:mi:ss(24h)2015-06-16 11:43:59
121ODBC canonical + msyyyy-mm-dd hh:mi:ss.mmm(24h)2015-06-16 11:43:59.457
126ISO 8601yyyy-mm-ddThh:mi:ss.mmm (no spaces)2015-06-16T11:43:59.457

SQL: Count of email domain names

The script below, returns the number of records grouped by the domain name of each email address (i.e. to the right of the @ sign).

SELECT
  RIGHT(Email, LEN(EMAIL) - CHARINDEX('@', EMAIL)) AS EmailDomain,
  COUNT(*) EmailCount
FROM Users S
GROUP BY RIGHT(EMAIL, LEN(EMAIL) - CHARINDEX('@', EMAIL))
ORDER BY EmailCount DESC

Excel: Find and replace across all worksheets

I needed to remove carriage returns, line feeds and tab characters from an Excel spreadsheet that was copied straight out of Microsoft Access, and have written the script below, which has the other options for replacing if you need to change it:

Please ensure you test any script taken from my website on a test/development machine, before running on a production server

Option Explicit
 
Sub FindAndReplaceInAllWorkSheets()
     
    Dim WS              As Worksheet
    Dim Search          As String
    Dim Replacement     As String
     
    Search = Chr(10) 'Find Lf
    
    'Other options
    'Search = Chr(9) 'Find tabs
    'Search = Chr(13) + Chr(10) 'Find CrLf
    'Search = Chr(13) 'Find Cr
    
    Replacement = "
"
     
    For Each WS In Worksheets
        WS.Cells.Replace What:=Search, Replacement:=Replacement, _
        LookAt:=xlPart, MatchCase:=False
    Next
     
End Sub