Help! Github is ignoring my .gitignore file!

It’s been a while since my last post, but now I’m fully emersed in learning .NET Core I’m sure I’ll start to post more regularly again. For my first trick, an aides-memoires on getting the .gitignore file to work. Here goes…

I normally use GitHub (or other git-based systems) as my source code repository when writing code in Visual Studio. I really like the fact that I can use the .gitignore file to prevent all the chaff that Visual Studio creates from being sent to the repository. For example, the ‘bin’ and ‘obj’ directories, and all the temporary solution files Visual Studio creates that have no business being in a source code reposity. A good rule of thumb is not to allow any generated files to be commited. They can be re-generated at any point so committing them just bloats commits and slows things down.

However, I often find that if I make changes to the .gitignore file in an established project these changes are not honoured in subsequent commits. Executing the the following commands in Git bash solves this:

git rm -r --cached .
git add .
git commit -m "fixed untracked files"  

Now, back to my .NET Core learning…

Help! My SQL Server 2008 ‘sa’ account got locked out and I can’t do anything with my databases!

I recently had an issue where the sa account for one of my SQL Servers got locked out. I assume the reason for the lock out was that the account was marked with Enforce password policy and someone had got the password wrong too many times. Regardless, I was no longer able to log in as sa.

Unfortunately there was a database on my server that only the sa account had access to, and it was starting to look like there was no way to get access back. To make matters worse, none of the other accounts had the correct access level to alter the sa account, and without the sa account, I was unable to elevate the access level of existing accounts. My server has mixed-mode authentication enabled but even domain administrator accounts couldn’t touch the sa account. It seemed like the perfect catch-22.

I Googled a lot of pages, many of which weren’t helpful, but then I found this:

Connect to SQL Server When System Administrators Are Locked Out

Good old Microsoft for once came up with the goods with an easy to follow, step-by-step guide to gaining access again.

The solution involved temporarily setting up single-user mode for the server, and then logging in to both the server and SQL Server Management Studio using an account with local machine administrator access. I was then able to remove the lock on the sa account, reset the password and gain access once more. Phew!

For all but production database servers, I would definitely recommend removing the Enforce password policy on all sa accounts in future. In the meantime, I hope this post has been useful.

Installing two build agents on TeamCity

Other than jogging my memory in the future when I need to do the same thing again, this post adds no value other than to direct people to Marcos Placona’s great blog post on how to run two build agents on the same TeamCity instance. Unfortunately this doesn’t work out of the box so there is some tweaking involved, as you’ll see.

So, without further ado, over to you Marcos…

http://www.placona.co.uk/1327/technology/new-teamcity-agents-the-right-way/

Granting folder/file access permissions to IIS AppPool users

By default, processes than run under IIS won’t have permission to access folders you create on your server hard drive. So, if you want your web-sites to interact with folders and files you’ll need add access permissions using the standard folder properties dialog:

Security Permissions Dialog

Permissions must be granted to the AppPool that your site uses. To grant permissions to AppPool users, the user name you enter in the Select Users or Groups dialog:

Select User or Group Dialog

…should match the following pattern:

IIS AppPool\[AppPool Name]

When you click Check Names the text you entered will magically detect the AppPool.

Some useful DOS commands

Useful DOS commands I’ve come across:

  1. Write to file
    [cmd] > [file]

    The result of the command on the left will be written to the file on the right. The file will be created if it doesn’t already exist, and overwritten if it does.

    Example:
    echo abc > c:\temp\myfile.txt

    Writes the text ‘abc’ to a new file called c:\temp\myfile.txt

  2. Append to file
    [cmd] >> [file]

    The result of the command on the left will be appended to the file on the right. If the file doesn’t exist it will be created.

    Example:
    echo abd >> c:\temp\myfile.txt

    Appends the the text ‘abc’ to the file c:\temp\myfile.txt

Ignore SSL errors when calling web-services in C#

Often when developing .NET systems that call secure web-services, I find myself running code against development and test servers with invalid SSL certificates. This causes service calls to fail and prevents me from making progress. To get round this potential blocker, I include the following snippet somewhere in the code before the service call is made:

ServicePointManager.ServerCertificateValidationCallback = delegate { return true; };

This code only needs to appear once, and it is not good practice to leave it in production code as it means that data sent over HTTPS isn’t actually secure. Consider making the inclusion of this code configurable, or wrap it up in #DEBUG statements so that it is not compiled into production code.

Getting the auto-incremental ID of a record in SQL Server

Time for another SQL Server aide-memoire…

I can never remember which command to use in T-SQL to get the ID of the most recently added record when using auto-incrementing IDs. There are three choices:

  • @@IDENTITY
  • SCOPE_IDENTITY()
  • IDENT_CURRENT(‘{TableName}’)

However, as described in great and excellent detail by the ‘SQL Authority’ in one of his blog posts, the one to use is SCOPE_IDENTITY(). This to restrict the ID to one generated in the current scope, rather than accidentally getting an ID generated by a trigger further down the line.

Enabling ‘xp_cmdshell’ in SQL Server

I was doing some work with SQL Server today and came across a solution I’ve used a lot over the years. This is a perfect candidate for my “Aides-Memoires” category.

Part of the task involved copy a file from one location to another using T-SQL. This is simple to achieve:

DECLARE @CommandText varchar(1000)
SET @CommandText = 'copy [source] [destination]'
EXEC master..xp_cmdshell @CommandText

However, when I executed the code above I got the following error:

“SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, search for ‘xp_cmdshell’ in SQL Server Books Online.”

The solution to this is simple. Run the following command to enable the use of xp_cmdshell:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

After running the T-SQL above, the code to copy the file worked as expected.

Deleting a local branch using the GitHub command line

Although it’s taken me a while to get to this point, I have to say that I really like GitHub. Historically I’ve used either VSS, TFS or SVN for source control. Each has its quirks, but essentially they all work in a similar fashion. GitHub feels (and probably is) vastly different.

Not being a massive fan of using command lines for simple tasks I do everyday, I use GitExtensions to add a UI to GitHub. It allows me to do pretty much everything I can do using the GitHub command line, and integrates really well with Visual Studio. However, one of the things it won’t let me do is delete a local branch quickly, especially if I have a few such branches to delete. I tend to use the GitHub website to deleted remote branches.

So, here’s the command to delete a local branch:

git branch -d the_local_branch_name 

Correction: I’ve since realised that it is possible to delete a local branch using the UI, as shown in the following screenshot:

Deleting branches

NOT EXISTS queries

This is the second post in my “Aides-Memoires” category. It’s a bit more complex than my first aides-memoires post on linking JavaScript and CSS, but still pretty basic.

This time I’m tackling the NOT EXISTS query in SQL. Again this is something that I often forget the syntax for and have to Google.

Aside: I haven’t actually had to write a NOT EXISTS query for a number of years now, and with the proliferation of ORMs such as NHibernate and Microsoft Entity Framework I rarely write raw SQL now anyway. However, there are always going to be times when highly complex queries and operations are required, and when execution time is paramount. At those times the most efficient way to get the job done is often to execute SQL directly on the database server using stored procedures, particularly when temporary tables and indexes are required.

For those moments, here’s how to write a NOT EXISTS query…

Note that I’m using T-SQL in SQL Server for these examples, although they will probably work with most relational databases without too much tweaking given the basic nature of the SQL used.

First let’s make some tables:

CREATE TABLE [Table1]
(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)
)

CREATE TABLE [Table2]
(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)
)

Now we populate some test data. Note that records are missing from Table2:

TRUNCATE TABLE Table1
TRUNCATE TABLE Table2

INSERT INTO Table1 (Name) VALUES ('One')
INSERT INTO Table1 (Name) VALUES ('Two')
INSERT INTO Table1 (Name) VALUES ('Three')
INSERT INTO Table1 (Name) VALUES ('Four')
INSERT INTO Table1 (Name) VALUES ('Five')

INSERT INTO Table2 (Name) VALUES ('One')
INSERT INTO Table2 (Name) VALUES ('Four')
INSERT INTO Table2 (Name) VALUES ('Five')

SELECT * FROM Table1
SELECT * FROM Table2

Note that I am clearing down the tables each time I add data to ensure a clean starting position, and returning the contents of both tables to show what’s been added. Running the query gives the following results:

NOT EXISTS 1

NOT EXISTS 2

To find rows that exist in Table1 but not Table2, use the following:

SELECT 
	Name
FROM
	Table1 T1
WHERE 
	NOT EXISTS
    (
        SELECT  
			Name
        FROM    
			Table2 T2
        WHERE   
			T1.Name = T2.Name
    )

As expected, this returns the following results set:

NOT EXISTS 3

Finally, to find missing rows and insert them into Table2, use the following:

INSERT INTO Table2
(
	Name
)
SELECT 
	Name
FROM
	Table1 T1
WHERE 
	NOT EXISTS
    (
        SELECT  
			Name
        FROM    
			Table2 T2
        WHERE   
			T1.Name = T2.Name
    )

On execution, this reports:

(2 row(s) affected)

…and a quick look into the Table2 using:

SELECT * FROM Table2

Returns:

NOT EXISTS 6

…as expected. Note that the values are in an odd order, proving that Two and Three were inserted last.