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.