Powershell takes a dive

Yesterday I was happily running some PowerShell scripts as part of our build process, when suddenly PowerShell took a dive for the worse.

All attempts to run PowerShell scripts returned the following error:

AuthorizationManager check failed

After a lot of fiddling and checking the Group Policy settings for PowerShell, I decided to reboot. Guess what, problem solved!

Let that be a reminder of MS std support action #1: reboot

Locked out of SQL Server

Our active directory was recently migrated to a new domain, and because of this, I could not log in to SQL Server with my new user account. Problem is, I also forgot my sa password, so I could not add my new user account.

This is where single user mode comes in. You restart SQL Server in single-user mode, add the new user or reset the sa password, and then restart it in normal mode.

What do you need to do to start it in single user mode?

Open SQL Server Configuration Manager and click SQL Server Services
Right-click on the instance and click Properties.
Click the Advanced tab, and type the startup parameters into the Startup Parameters box (the parameters are semi-colon delimited, so add -m; to the front.
Click OK
Restart the database engine.

Remember to remove the single user startup option and restart the database after resetting the password or adding the user!

Reference: MSDN

SQL: Back to basics

Today's lesson might be common knowledge for you, but for me it was a good back to basics again. It concerns the calling of scalar functions from the WHERE clause of a SELECT statement.

One could expect the following statement to execute the function only once, but, then, you'd be wrong.

select * from sys.objects
where dbo.myfunc(1) > 1

SQL server executes the function once for every row in sys.objects, even though it is called with a constant parameter and will never return a different result. Typically this is something you would not pick up in development with a small dataset, but performance will decrease dramatically as the dataset size increases.

So, the right way to write the above SQL statement would be something like

declare @num int
set @num=dbo.myfunc(1)
select * from sys.objects
where @num > 1

Be aware, be very aware!