Warning about SQL Server Scheduled Tasks

I've had cause to create scheduled tasks on SQL Server in the past and in general it's a simple process, write a bit of T-SQL, test it, put it on a job and schedule it, not much can go wrong, right?

Well on occasion it does go wrong if you're logged into a SQL Server as 'domain_name\trevor.best' then that will be the name of the owner of the job, seems logical. What isn't logical is when you get an error stating that it cannot verify my user name when it's OK with me being logged in normally to create the job.

The workaround for this is to set the owner to a SQL Server user (e.g., sa).

The moral of the story is once you've set up a scheduled task, hang around to make sure it runs on schedule.

Assigning Variables in SQL Server (T-SQL)

I came across a problem today whereby a stored procedure didn't behave as expected. I suppose when I think about it, it behaved as it should have but my expectation was lacking the thought mentioned before.

There's two different ways of assigning a variable to a value stored in a table, one is to use a subquery in a "set" statement and the other is a simple select, e.g.

Set Statement
set @MyVar = (select field from table where otherfield=@othervar)

Select Statement
select @MyVar = field from table where otherfield=@othervar

Both ways will yield the same result as long as there is a result in the table, the difference is when there are no records in the table that you are selecting from. The first example will yield a null whereas the second won't assign a value to the variable at all. If the variable was null to start with then the result will look the same but if @MyVar held a value other than null before then it will still have that previous value.

Something to think about if assigning variables this way within a loop.

SQL Server Management Studio Reconnecting

As I run several virtual machines, I usually save their state overnight when I shut my laptop down. If I'm running SQL Server Management Studio it will get disconnected as suspending a virtual machine will temporarily disconnect it from the network.

Upon restoring the VM, if I go to run a query in a window that's already open, I will get the following error.

Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

This was quite frustrating as the "reconnect" button was greyed out so I thought it wise to reconnect to the server before running the query again. This presented two problems:
1) I don't always remember to reconnect
2) Reconnecting logged me onto the "master" database and the query might not work very well there or accidentally create an array of unnecessary objects in the wrong database.

I have since found though, that if you run the query and get the above error, simply running the query again will automatically reconnect you to the server and the correct database.

Access newer Microsoft Access databases from VB6

I had to update a legacy program today that was written in VB6 and imports data from Access (jet) databases, the company that make the Access applications have upgraded to Access 2010 from the 2003 format that they were using so the import program immediately failed.

Luckily, Microsoft still support this scenario with the aid a new download http://www.microsoft.com/en-us/download/details.aspx?id=23734 that replaces the DAO library. Once installed, you can remove the reference to DAO 3.6 (or earlier) and add a reference to "Microsoft Office 12 access database engine object library". This new library appears to be backwards compatible with the old .MDB formats as well.

GNOME 3 on two monitors

I've been using the GNOME 3 desktop in Linux for a while now, since they made it available in Ubuntu 11.10 and I've now upgraded both my work and home machines to 12.04 LTS. Alas when I got to work, I plugged in my external monitor, ran the NVidia configuration and all was well until I switched to a new desktop and found the windows from the first desktop on the second monitor were still visible.

It appears the default behavior in GNOME 3 is only enable switching desktops on the primary monitor, anyway it didn't take a lot of googling especially as Greg Cordt's blog was the first result http://gregcor.com/2011/05/07/fix-dual-monitors-in-gnome-3-aka-my-workspaces-are-broken/ Thanks Greg.

Tortoise Right-Click Menu

Just a little one I found, on the right-click in Explorer you sometimes get a "SVN Get Lock" and sometimes you don't (If you don't then it's available in the Tortoise sub-menu anyway). I thought that this was a little inconsistent at first but since playing with the properties on the server I see that the menu option appears if the "svn:needs-lock" property is set for that file on the server.

BTW, setting the lock property of multiple files is rather difficult in Tortoise, well, not difficult, just laborious as it only allows you to do it one at a time. You can do it to a whole load by using a Linux machine, install SVN on it ("sudo apt-get install svn" for Ubuntu/Mint/Debian based distros) then you can use the command line:

$ svn propset svn:needs-lock 1 *.asp

The above sets it for all asp files, use a different spec for that, remember to commit your changes after though using the "svn commit" command.