It is really odd that it should be so hard getting SSMS 2014 on a Windows 8 box.
The problem is that SSMS needs .Net 3.5, but that the ordinary methods to get .Net 3.5 installed (Windows Features in Programs and Features) do not work but only result in some very unhelpful messages!
Kunal Chowdry has made a nice description of the whole thing here, but to cut a long story short here is the gist of it:
DISM /Online /Enable-Feature /FeatureName:NetFx3 /All /LimitAccess /Source:x:\sources\sxs
You run from an administrative command prompt – remember to replace “x:” whith the path to your Windows 8 installation path, ie a DVD.
DISM (Deployment Image Servicing and Management) has come to my help before. So sad that Windows has always lacked proper package management, so we need to rely on a mix of Control Panel, DISM, Installers, Chocolatey, Boxstarter etc.
Well, you can’t, really. What you have to do is to make sure that all the the columns have the same datatype and then perform the unpivot.
Here is the table I want to partially unpivot:
So what I do is to cast all columns to a suitable common format, in this case VARCHAR(100), before the actual pivoting:
SELECT Id, ColumnName, Value
CAST(SysNr AS Varchar(100)) SysNr,
CAST(KredsNr AS Varchar(100)) KredsNr,
CAST(EjendomsNr AS Varchar(100)) EjendomsNr,
CAST(Navn AS Varchar(100)) Navn,
CAST(EjendomStatusId AS Varchar(100)) EjendomStatusId,
CAST(Aktiv AS Varchar(100)) Aktiv
) AS S
Value FOR ColumnName IN (SysNr, KredsNr, EjendomsNr, Navn, EjendomStatusId, Aktiv)
) AS U
Voila – quite a bit of typing, but pretty easy peasy otherwise.
In our Hyper-V test environment we use a mixture of dynamic and static IP addresses. Some servers need to have static IP addresses and in order to facilitate the process of provisioning a new server I wanted to have a script that toggles the current network adapter setting from dynamic (DHCP) to static.
Do remember to mark that IP in the DHCP server as unavailable as well in order not to get IP confilcts…
Write-Host "Switch Current IP configuration to to Static IP"
$wmiip = Get-WmiObject win32_networkadapterconfiguration -filter "DHCPEnabled = 'true' and ipenabled = 'true'"
$ipaddress = $wmiip.IPAddress
$ipgateway = $wmiip.DefaultIPGateway
$ipdnsordr = $wmiip.DNSServerSearchOrder
Write-Host "Static already set"
Get-WmiObject win32_networkadapterconfiguration -filter "ipenabled = 'true'"
"DNS : " + $wmiip.DNSServerSearchOrder
This script was inspired by an article by Scripting Guy
The other day I needed a table of datetimes for each second for a given period, and investigated various suggestions of which there are many around the web.
What I came up with as the, in my opinion, easiest solution was this code. I already had a very large table that could provide me with a set of records to iterate over (millions), avoiding some complicated T-Sql looping.
SELECT TOP 86399
ROW_NUMBER() OVER (ORDER BY Id) AS Number,
ROW_NUMBER() OVER (ORDER BY Id),
DATEDIFF(DAY, 0, GETDATE()))) AS DateTime
The number 86399 corresponds to the number of seconds (One day = (60*60*24) – 1) to generate in the table.
The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.
Here’s an excerpt:
The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 18,000 times in 2014. If it were a concert at Sydney Opera House, it would take about 7 sold-out performances for that many people to see it.
Click here to see the complete report.
I’ve just spent some really fun hours trying to clean up users and logins from both our production and test-servers. Over the course of several years and restores from production to test, a number of orphaned users and logins have accumulated, that is, database users that do not have an associated login and logins that do not have any associated database users.
The following script attempts to identify both by extracting users (and user roles) from all databases and comparing them with the server logins. The output suggests the Sql statements to clean up the mess, but do peruse it throughly before executing!
Nuff said, here is the code:
- — Script to assist cleaning up orphaned Logins and Users on Sql Server
- — Query to extract Users and Roles
- DECLARE @sqlquery varchar(500)
- SET @sqlquery =
- 'SELECT DB_NAME() AS DatabaseName, P1.name AS UserName, P2.name AS UserRole FROM sys.database_role_members DRM '
- +'JOIN sys.database_principals p1 ON P1.principal_id = DRM.member_principal_id '
- +'JOIN sys.database_principals p2 ON P2.principal_id = DRM.role_principal_id '
- +'WHERE P1.type IN (''S'', ''U'') AND LEN(P1.sid) > 1'
- — Query to extract User-databases only, ie not master, tempdb etc
- DECLARE @dbname char(50)
- DECLARE c1 CURSOR READ_ONLY FOR
- SELECT Name FROM sys.databases WHERE LEN(owner_sid) > 1
- — Temporary table to hold all Database Users and Roles
- IF OBJECT_ID('tempdb..#USERNAMES') IS NOT NULL DROP TABLE #USERNAMES
- CREATE TABLE #USERNAMES
- DatabaseName varchar(100),
- UserName varchar(100),
- UserRole varchar(100)
- — Loop over databases with a cursor
- OPEN c1
- FETCH NEXT FROM c1 INTO @dbname
- WHILE @@FETCH_STATUS = 0
- PRINT 'Adding ' + @dbname
- EXEC('USE [' + @dbname + ']; ' + 'INSERT INTO #USERNAMES ' + @sqlquery);
- FETCH NEXT FROM c1 INTO @dbname;
- CLOSE c1 DEALLOCATE c1
- — Logins not used in any databases. NOTE a specific login is excepted
- SELECT Name, 'DROP LOGIN [' + Name + '];' AS Sql
- FROM sys.server_principals P
- LEFT OUTER JOIN #USERNAMES U ON U.UserName = P.Name
- WHERE type = 'S'AND is_disabled = 0 AND LEN(sid) > 1 AND U.DatabaseName IS NULL AND name NOT IN ('udvikler')
- — Users in databases without server Logins. NOTE a specific login is excepted
- SELECT DISTINCT DataBaseName, UserName, 'USE [' + DatabaseName + ']; DROP USER [' + UserName + '];' AS Sql
- FROM #USERNAMES U
- LEFT OUTER JOIN sys.server_principals P ON U.UserName = P.Name
- WHERE name IS NULL AND UserName NOT IN ('dbo')
- –SELECT * FROM #USERNAMES
The ability to restore a SQL Server database over and over can sometimes be handy in a test scenario, for instance when you need to test a schema-update for autodeployment through DbUp/Octopus – or you otherwise need to have your database in a known state prior to testing.
However this is quite often blocked because the database is in use, resulting in the above message.
My solution to this is to script the restore to run in single user mode like so:
- USE [master]
- ALTER DATABASE [BenchmarkDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- RESTORE DATABASE [BenchmarkDB] FROM DISK = N'D:\BenchmarkDB.bak' WITH FILE = 1, MOVE N'BenchmarkDB_log' TO N'D:\Data\BenchmarkDB.ldf', NOUNLOAD, REPLACE,STATS = 5
- ALTER DATABASE [BenchmarkDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE
To achieve the same in Management Studio then go to Options and check this box:
To check whether your database got correctly reset to Multiuser Mode, go to Database Properties > Options, and look at the “State” subgroup, “Restrict Access” item (you’ll need to scroll down to the bottom of the list to see it):