SQL data compare for free

Microsoft SQL Server comes with a command-line tool for comparing data. The tool is named tablediff.exe and you can find it in the following folder:

%ProgramFiles%\Microsoft SQL Server\<version>\COM

However, you need to have installed SQL Server with ‘Replication’ support, so if you can’t find tablediff.exe just re-run the installer and add Replication support.

You use tablediff like this:

tablediff.exe -sourceserver <> -sourcedatabase <> -sourceschema <> -sourcetable <> -destinationserver <> -destinationdatabase <> -destinationschema <> -destinationtable <>

Just replace <> with appropriate values.

Advertisements

Scheduling a manual SQL Server job

When testing SQL Server jobs I often have to schedule a manual job to run. I do this by editing the job, editing the manual schedule, enabling it and setting a time. This is tedious and fiddly and I’ve found that if you schedule it too soon – less than 90 seconds away – it’s likely to not run. I’ve wasted a lot of time either waiting for jobs to complete only to find out that they haven’t started, or twiddling my thumbs waiting 2 or more minutes for a job to start.

So with a bit of help from some forum questions I’ve come up with a script that schedules a job to run in 90 seconds’ time.

USE [master] 
GO
DECLARE @jobName sysname, @scheduleName sysname, @SCHED int, @JOB uniqueidentifier, @start_date int, @run_time int 
SET @jobName = 'My Job' 
SET @scheduleName = 'My Manual Schedule'

SELECT TOP 1 @JOB = job_id FROM msdb.dbo.sysjobs WHERE [name] = @jobName

SELECT 
 @SCHED = js.schedule_id 
FROM 
 msdb.dbo.sysjobschedules js 
JOIN 
 msdb.dbo.sysschedules s 
 ON js.schedule_id = s.schedule_id 
WHERE 
 js.job_id = @JOB 
 AND s.[name] = @scheduleName

SELECT @start_date = CONVERT(int, CONVERT(varchar(8), GETDATE(), 112)) 
SELECT @run_time = CONVERT(int, REPLACE(CONVERT(char(8), CONVERT(varchar(8), DATEADD(s, 90, GETDATE()), 108)), ':', ''))

--SELECT @SCHED, @start_date, @run_time

EXEC msdb.dbo.sp_update_schedule 
 @schedule_id = @SCHED, 
 @enabled = 1, 
 @active_start_date = @start_date, 
 @active_start_time = @run_time 

GO

Visual Studio 2005, Windows 7 and TFS 2010/2012

Installing Visual Studio 2005 on Windows 7
To install VS2005 on Windows 7 without using XP Mode:

• Run the installer as administrator
• Install Service Pack 1 as administrator: http://www.microsoft.com/en-us/download/details.aspx?id=5553
• Install Vista Update as administrator: http://www.microsoft.com/en-us/download/details.aspx?id=7524
• Edit the shortcut for VS2005 and on the ‘Compatibility’ tab check ‘Run this program as administrator’

To install as administrator, right-click the exe (usually setup.exe) and select ‘run as administrator’.
Connecting Visual Studio 2005 to Team Foundation Server 2010
To connect to TFS 2010, install the Forward Compatibility Update for Team Foundation Server 2010.
Connecting Visual Studio 2005 to Team Foundation Server 2012
To connect to TFS 2012, firstly install the TFS 2012 MSSCCI Provider. You will not be able to choose or edit workspaces in VS2005, so using a different version of Visual Studio that’s already connected to TFS 2012 do the following:

• Create a new workspace just for VS2005, using a different path to your other workspaces (not nested within any of their paths)
• Edit the new workspace, click the ‘Advanced’ button and set ‘Location’ to ‘Server’
• Change your current workspace to the new one and in source control explorer, right-click the project you’d like to open in VS2005 and select ‘Get Latest’

Now, launch Visual Studio 2005 and do the following:

• Go to Tools, Options, Source Control, set ‘Current source control plugin’ to ‘Team Foundation Server MSSCCI Provider’ and click OK
• Go to Tools, Connect to Team Foundation Server and add or connect to the TFS2012 server as normal
• Go to File, Open, Project/Solution and navigate to the .sln file for your project, making sure you choose the one in the new workspace’s path

There may be some automatic changes to the solution and project files but after that everything should work OK. You won’t be able to navigate source control in VS2005 but you’ll be able to open recent solutions via the Start page.

Clear SharePoint Designer cache (2007)

SharePoint Designer 2007 is pretty flaky and I frequently run into problems where for no logical reason it can’t edit an item. This is usually down to a messed-up cache. I’ve found that simply deleting offending sites from the cache fixes it and with no apparent down-sides – the fact that the sites I work on are on our own LAN probably makes caching less important to me.

However, SP Designer keeps its cache data in more than one place. I can never remember where these are so I created this little batch file that opens up Explorer windows for both cache locations:

explorer "%LOCALAPPDATA%\Microsoft\WebsiteCache"
explorer /e,"%APPDATA%\Microsoft\Web Server Extensions\Cache"

Just run that then delete the entries for the site you’re having problems with (make sure SP Designer is closed first).

Get the ID of an IIS 6.0 site from a script

This took me a while to cobble together from various sources. It’s VBScript that takes either the path of a site or its description (technically, its ‘ServerComment’, but it’s what you see in the ‘Description’ column of IIS Manager or the ‘Description’ box of a site’s properties) and returns its Identifier.

Save this as GetSiteID.vbs:

Function GetServerComment(SiteNumber)
	Set IISWebSite = GetObject("IIS://localhost/w3svc/" & SiteNumber)
	GetServerComment = IISWebSite.ServerComment
	Set IISWebSite = Nothing
End Function

Function GetPath(SiteNumber)
	Set IISWebSiteRoot = GetObject("IIS://localhost/w3svc/" & SiteNumber & "/root")
	GetPath = IISWebSiteRoot.Path
	Set IISWebSiteRoot = Nothing
End Function

Function GetSiteIDByDescription(ServerComment)
	Set IISObj = GetObject("IIS://localhost/w3svc")
	For Each Web In IISObj
		If (Web.Class = "IIsWebServer") Then
			If (GetServerComment(Web.Name) = ServerComment) Then
				GetSiteIDByDescription = Web.Name
			End If
		End If
	Next
	Set IISObj = Nothing
End Function

Function GetSiteIDByPath(Path)
	Set IISObj = GetObject("IIS://localhost/w3svc")
	For Each Web In IISObj
		If (Web.Class = "IIsWebServer") Then
			If (GetPath(Web.Name) = Path) Then
				GetSiteIDByPath = Web.Name
			End If
		End If
	Next
	Set IISObj = Nothing
End Function

Set colNamedArguments = WScript.Arguments.Named

If colNamedArguments.Exists("description") Then
	returnValue = GetSiteIDByDescription(colNamedArguments("description"))
ElseIf colNamedArguments.Exists("d") Then
	returnValue = GetSiteIDByDescription(colNamedArguments("d"))
End If

If colNamedArguments.Exists("path") Then
	returnValue = GetSiteIDByPath(colNamedArguments("path"))
ElseIf colNamedArguments.Exists("p") Then
	returnValue = GetSiteIDByPath(colNamedArguments("p"))
End If

Set colNamedArguments = Nothing

If len(returnValue) > 0 Then
	WScript.Echo returnValue
	WScript.Quit returnValue
Else
	WScript.echo "Usage: GetSiteID.vbs [/description:] [/path:]"
	WScript.Quit -1
End If

You can use this batch file to call the VBScript file if it’s in the same directory:

@Echo off
SetLocal
Set siteName=mysite.co.uk
GetSiteID /description:%siteName%
REM If that way doesn't work do it this way:
REM cscript GetSiteID.vbs /description:%siteName%
Set siteID=%ErrorLevel%
REM Now do something with that Identifier
Echo The ID of the site named '%siteName%' is %siteID%
EndLocal

I expect to use this when I’ve created a site with the Iisweb.vbs script and then need to make further changes to it with Adsutil.vbs.