Monday, November 30, 2009

Resolved: SQL Server Agent job notifications fail with "failed to notify '(operator name)'"

Running:
- Windows Server 2003 (5.2, build 3790)
- SQL Server 2005 (9.00.4053.00)

Symptoms:
- A job in SQL Server Agent is set to have an email notification sent to an operator when it fails, but the notifications don't send.
- Running Management > Database Mail > Send Test E-Mail does work, however.
- In the job history, each failed attempt is followed by "NOTE: Failed to notify '(operator name)' via email.".
- In the SQL Agent error log, there is a message saying, "Unable to start mail session... profile name is not valid..."

Solution:
- Go to SQL Server Agent > Properties > Alert System.
- If "Enable mail profile" is unchecked, check it and restart the SQL Server Agent.
- If is checked and still doesn't work, UNcheck it, restart the SQL Server Agent, then go back in and check it again, and then restart SSA again (this fixed my issue).

Commentary:
I found a lot of posts about this issue. Most revolved around the fact that by default the SQL Server Agent comes with the mail profile disabled. But it took me a few hours to resolve this, so it sounded like a good one to post here for others.

There is also a
Microsoft HotFix for certain related problems, but if you've been keeping your system up-to-date, you probably already have installed it in a cumulative update.

You may also want to check out this:
http://msdn.microsoft.com/en-us/library/ms175984.aspx

Thursday, November 5, 2009

SQL dyanmic queries run in separate connections

In SQL Server 2005, it appears that all dynamic queries run in separate connections from that of the query which generated them. This makes a big difference if you are using connection-specific temp tables (e.g., #MyTempTable).

Here's a few examples in SQL Management Studio to demonstrate what's going on.

Query:

--in a dynamic query, create a local temp table and select from it
PRINT 'Example A:'
EXEC('CREATE TABLE #ConnectionTempTable ( [Foo] INT, [Bar] BIT, [Junk] NVARCHAR(100) ) SELECT * FROM #ConnectionTempTable')
PRINT '------'

--in a dynamic query, create a global temp table; then in the static query, drop it
PRINT 'Example B:'
EXEC('CREATE TABLE ##GlobalTempTable ( [Foo] INT, [Bar] BIT, [Junk] NVARCHAR(100) )')
DROP TABLE ##GlobalTempTable
PRINT '------'

--in a dynamic query, drop the local temp table from Example A (FAILS!)
PRINT 'Example C:'
EXEC('DROP TABLE #ConnectionTempTable')
PRINT '------'

--in a dynamic query, recreate the temp table from Example A (FAILS!)
PRINT 'Example D:'
EXEC('CREATE TABLE #ConnectionTempTable ( [Foo] INT, [Bar] BIT, [Junk] NVARCHAR(100) )')
SELECT * FROM #ConnectionTempTable


Results:

Example A:

(0 row(s) affected)
------
Example B:
------
Example C:
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#ConnectionTempTable', because it does not exist or you do not have permission.
------
Example D:
Msg 208, Level 16, State 0, Line 20
Invalid object name '#ConnectionTempTable'.