How to drop a foreign key based on a table name and column name with T-SQL.

If you want to delete a foreign key based on a given table name and column name in T-SQL, you can use the following code snippet:

Full table name = [MySchema1].[MyTable1]

Full column name = [MyColumn1]

declare @TableName varchar(255)
set @TableName = '[MySchema1].[MyTable1]'
declare @ColumnName varchar(255)
set @ColumnName = 'MyColumn1'
declare @TabelObjectId int
set @tabelObjectId = Object_ID(@TableName)
declare @ForeignKeyObjectId int
select        @ForeignKeyObjectId = fc.constraint_object_id
from        sys.foreign_key_columns fc
inner join    sys.columns c  on  c.object_id = parent_object_id and c.column_id = fc.parent_column_id
where        fc.parent_object_id = @tabelObjectId and c.name = @ColumnName
declare @ForeignKeyName int
set @ForeignKeyName = (select name from sys.objects where object_id = @ForeignKeyObjectId)
if object_id(@ForeignKeyObjectId) is not null
begin
exec('alter table ' + @TableName + ' drop constraint ' + @ForeignKeyName)
end

Fixing the error: The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to ‘GroupJoin’. in a left join with LINQ.

If you do a left join in LINQ on multiple columns / properties. The types and names of these properties must be the same else you will get the error:

The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to ‘GroupJoin’.

 

The following code generates the error, because the property [Company] is not the exact same as the property [Comp].

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Assert = Xunit.Assert;
namespace Research.Rli.Tests
{
public class Person1
{
public string Name { get; set; }
public string Company { get; set; }
}
public class Person2
{
public string Name { get; set; }
public string Comp { get; set; }
}
[TestClass]
public class ResearchTester
{
[TestMethod]
public void Should_join_person1_and_person2()
{
var p1List = new List<Person1>
{
new Person1 { Company = "MyCompany", Name = "John Do"}
};
var p2List = new List<Person2>
{
new Person2 { Comp = "MyCompany", Name = "John Do"}
};
var query = from p1 in p1List
join p2 in p2List on new { p1.Company, p1.Name } equals new { p2.Comp, p2.Name } into p2g
from p2g1 in p2g.DefaultIfEmpty(null)
select p1;
}
}
}

To fix this error add property names to the anonymous objects.

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Assert = Xunit.Assert;
namespace Research.Rli.Tests
{
public class Person1
{
public string Name { get; set; }
public string Company { get; set; }
}
public class Person2
{
public string Name { get; set; }
public string Comp { get; set; }
}
[TestClass]
public class ResearchTester
{
[TestMethod]
public void Should_join_person1_and_person2()
{
var p1List = new List<Person1>
{
new Person1 { Company = "MyCompany", Name = "John Do"}
};
var p2List = new List<Person2>
{
new Person2 { Comp = "MyCompany", Name = "John Do"}
};
var query = from p1 in p1List
join p2 in p2List on new { Company = p1.Company, Name = p1.Name } 
equals new { Company = p2.Comp, Name = p2.Name } into p2g from p2g1 in p2g.DefaultIfEmpty(null) select p1; } } }

How to update a property of an object in LINQ without returning new objects.

If you want to update an property of an object in LINQ without creating new objects, you can use the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Assert = Xunit.Assert;
namespace Research.Rli.Tests
{
public class Appointment
{
public int Id { get; set; }
public string Location { get; set; }
}
[TestClass]
public class ResearchTester
{
[TestMethod]
public void Should_update_appointments()
{
var appointments1 = new List<Appointment>
{
new Appointment { Id = 1, Location = "" },
new Appointment { Id = 2, Location = "" },
new Appointment { Id = 3, Location = "" }
};
var appointments2 = new List<Appointment>
{
new Appointment { Id = 1, Location = "My location 1" },
new Appointment { Id = 2, Location = "" },
new Appointment { Id = 3, Location = "My location 3" }
};
Func<Appointment, Appointment, Appointment> UpdateLocation 
= ((a, b) => { a.Location = b.Location; return a; });
var updatedAppointmets = (from a1 in appointments1
join a2 in appointments2 on a1.Id equals a2.Id
select UpdateLocation(a1, a2)).ToList();
foreach (Appointment a in updatedAppointmets)
{
Console.WriteLine
(
string.Format("Id [{0}] Location [{1}]", a.Id, a.Location)
);
}
// Output:
// Id [1] Location [My location 1]
// Id [2] Location []
// Id [3] Location [My location 3]
}
}
}

If you want to use a LEFT OUTER JOIN in the linq query, use:

 

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Assert = Xunit.Assert;
namespace Research.Rli.Tests
{
public class Appointment
{
public int Id { get; set; }
public string Location { get; set; }
}
[TestClass]
public class ResearchTester
{
[TestMethod]
public void Should_update_appointments()
{
var appointments1 = new List<Appointment>
{
new Appointment { Id = 1, Location = "" },
new Appointment { Id = 2, Location = "" },
new Appointment { Id = 3, Location = "" }
};
var appointments2 = new List<Appointment>
{
new Appointment { Id = 1, Location = "My location 1" },
new Appointment { Id = 3, Location = "My location 3" }
};
Func<Appointment, Appointment, Appointment> UpdateLocation
= ((a, b) => { if (b != null) { a.Location = b.Location; } return a; });
var updatedAppointmets =
(
from a1 in appointments1
join a2 in appointments2 on a1.Id equals a2.Id into g
from g1 in g.DefaultIfEmpty(null)
select UpdateLocation(a1, g1)
).ToList();
foreach (Appointment a in updatedAppointmets)
{
Console.WriteLine
(
string.Format("Id [{0}] Location [{1}]", a.Id, a.Location)
);
}
// Output:
// Id [1] Location [My location 1]
// Id [2] Location []
// Id [3] Location [My location 3]
}
}
}

How to restore a database on a Microsoft SQL Server LocalDb instance, based on a *.bak file.

Input

The backup file is located at [C:\Temp\Research.bak]

The folder [C:\Databases] will contain all LocalDb database data (*.mdf) and log (*.ldf) files.

 

Open Microsoft SQL Server 2012 Management Studio

Connect to the LocalDb instance [(localdb)\V11.0]

image

 

Create a empty database with the name [Research]

By default you will get the error:

[Create failed for Database ‘Research’. (Microsoft.SqlServer.Smo)]

image

 

Add the following registry keys:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer\DefaultData

and

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer\DefaultLog

and

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer\BackupDirectory

With the values [C:\Databases]

image

 

If you don’t add the [BackupDirectory] key, then you will get the error:

image

 

Property BackupDirectory is not available for Settings ‘Microsoft.SqlServer.Management.Smo.Settings’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

 

Double check

Control the values by right clicking on the [(localdb)\V11.0] instance name in the Microsoft SQL Server Management Studio and click on propeties. In the properties dialog click Database Settings:

image

 

Now you are able to create a new database with the name [Research]. The data and log files will be created in [C:\Databases].

image

 

Now you are able to restore the database:

 

image

 

image

 

Make sure you check the [Overwrite the existing database (WITH REPLACE)] option]:

 

image

 

else you get the error:

Restore of database ‘Research’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

How to copy files from host to a Hyper-V virtual machine in Windows 8 by using the clipboard.

Often I hear people complain about the Microsoft Hyper-V manager, for instance: copying files from the host to virtual machine by using the clipboard is not possible, this is because the Hyper-V manager is not intended to be used like VMWare workstation or player.

 

Only use the Hyper-V manager to “manage” virtual machines eg start, stop, copy, clone, add or remove hardware etc.

Remote Desktop

When you want to connect to the desktop of the virtual machine, use Remote Desktop.With remote desktop it is possible to copy files from the host to the Hyper-V virtual machine by using CTRL-C and CTRL-V (using the clipboard). In windows 8 the remote desktop experience is really improved, so take a look at it.

 

TIP: You can still use free programs like Microsoft Remote Desktop Manager v2.2 to manage multiple rdp connections, but the “metro” windows 8 RDP app is pretty good.

How to clone / copy a Windows 8 Hyper-V client virtual machine

If you want to make a clone / copy of an existing Windows 8 Hyper-V client virtual machine, follow the steps below:

 

Run Import wizard

Run the Hyper-V manager.

Click [Export Virtual Machine…].

Choose a export folder eg [C:\VHD\W8RLIDEV2012]

image

 

I will always move the contents of the created folder to the root folder, see screendump, but this is not necessary.

 

image

 

Click [Import Virtual Machine…].
On the Locate Folder page, enter the folder containing the Virtual Machine you want to clone / copy eg [C:\VHD\W8RLIDEV2012\Virtual Machines]

 

image

On the [Choose Import Type] page choose [Copy the virtual machine (create a new unique ID)].

 

image

 

Note

I use the location of the virtual machine to store snapshots etc.

 

image

 

On the [Choose Folders to Store Virtual Hard Disks] choose [C:\VHD\W8RLIDEV2012\Virtual Hard Disks]

image

 

The last thing I do is rename the Virtual Machine in the Hyper-V Manager.

 

image

Fix for: Some breakpoints in Microsoft Visual Studio 2012 unit tests are not being hit.

When I opened a Microsoft Visual Studio 2010 solution in Microsoft Visual Studio 2012 and debugged my unit tests, some breakpoints where not hit and some code was stepped over, when using F11.

The problem was caused by the *.testsettings files in the solution folder after removing these files and restarting Microsoft Visual Studio 2012 the problem was fixed.

I found my solution at:

http://social.msdn.microsoft.com/Forums/pl-PL/vsdebug/thread/ff6db2d5-42b4-42af-8d3b-a583cb7eaa96

DEV TIPS

Just a dump blog post for short development tips.

 

Microsoft Windows 8

Always run pinned programs on the taskbar as administrator
If you want the programs pinned on the windows taskbar to always be [Run as Administrator], the open the folder containing the windows taskbar pinned shortcuts:

  • %APPDATA%\Microsoft\Internet Explorer\Quick Launch\User Pinned\TaskBar
  • Right click pinned shortcut > Properties > Advanced…
  • Check [Run as administrator’]image
How to Disable Caps Lock Key in Windows
http://www.howtogeek.com/howto/windows-vista/disable-caps-lock-key-in-windows-vista/
 

 

 

Microsoft SQL Server 2012

Run a *.sql script
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe" -S (localdb)\V11.0 -d MyDatabase -E -b -i "C:\Temp\script.sql"
Attach an existing database to a Local Db instance

Open the a command prompt with Administrator rights, then enter:
sqlcmd -S (localdb)\V11.0
use master
go

create database Cris on
(filename= N’C:\Databases\MyDatabase1.mdf’)
for attach;
go

 

 

Microsoft Visual Studio 2012

How to copy unit test output from the Test Explorer
You might think, you can’t copy the output of the unit test from the Test Explorer, because you can’t select the text, but if you click on the window and just press CTRL + C.
And in your code windows press CTRL – V, all output of the test is pasted in the code window.

image

Output pasted in the code window:
image

How to Assert code has thrown an exception in a MSTEST unit test

If you want to Assert if some code has thrown a specific exception with a specific message, you can wrap the code in a try – catch block, but you can also use XUnit assert in MSTEST, combining MSTEST en XUnit. 

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Assert = Xunit.Assert;
namespace Research.Rli.Tests
{
[TestClass]
public class ResearchTester
{
[TestMethod]
public void Test()
{
string message = "The system detected an error.";
Exception ex = Assert.Throws(typeof(ApplicationException),
() => { throw new ApplicationException(message); });
Assert.Equal(message, ex.Message);
}
}
}
How to show all pending changes for all users in TFS

http://geekswithblogs.net/MikeParks/archive/2009/09/16/tfs—view-all-pending-changes-for-all-users.aspx