SQL Server 2008 Email Notification Setup

I was trying to set up an email notification for a SQL Server agent job. I set up the Database Mail profile under Management, created operators under SQL server Agent, selected an operator under the notification section of a job. After the job is completed, guess what, there is no email. After diggin around I found this post(http://social.msdn.microsoft.com/Forums/en/sqltools/thread/839b5c27-9bc5-4467-a9d4-e78f9934a761). I tried restarting the agent serice and then realized that the mail profile was not enabled under SQL Server Agent Properties -> Alert System. After enabling that and restarting the agent service, it worked. The key here is to enable the mail profile first, then restart the agent service.

User Permission for Creating Linked Servers

I created a stored proc that drops and creates linked servers. The following system stored procs are called in my this stored proc:
master.dbo.sp_dropserver
master.dbo.sp_addlinkedserver
master.dbo.sp_addlinkedsrvlogin

It works fine in my dev environment. But when I ran it on the prod db server, the following errors came up:
Msg 15247, Level 16, State 1, Procedure sp_dropserver, Line 20
User does not have permission to perform this action.
Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 29
User does not have permission to perform this action.
Msg 15247, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 25
User does not have permission to perform this action.

After looking around, I found out that the account that runs the stored proc needs the "ALTER ANY LINKED SERVER", "ALTER ANY LOGIN" permission and the "dbcreator" server role. These were not setup on the SQL authentication account that runs the stored proc. So I did the following permission granting in addition to manually set the "dbcreator" role.

USE master;
GRANT ALTER ANY LINKED SERVER TO WarehouseAdmin;
GO

USE master;
GRANT ALTER ANY LOGIN TO WarehouseAdmin;
GO

Now it runs fine.

Linked Server Name Trick

I created a report that pulls data from Oracle databases via OpenQuery() agained SQL Server linked server. I initially set the linked server name with periods (.), i.e. Environment.Schemaname.abc. Then my SQL query failed because OpenQuery() doesn't like period in the linked server name. After replacing the (.) with (_) everying worked just fine.

SQL 2005 Scheduled Job Failed Due to Linked Server Access Error

There are two things I did to fix the error. I'm not sure why. But it worked.

1. On the linked server configuration screen select Be made using this security context. Then put in the SQl Authentication user name and password setup on the remote server.
2. Do NOT use the Run As User option. For some reason, eventhough the specified user can excute the stored proc without any problem, when run it in a scheduled job. It will fail.

SQL Server Linked Server Windows Authentication Config

There is a good blog post about Winodws Authentication config for double-hop linked server.
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

If Windows Authentication setup is too much trouble, an alternative would be using SQL Server Authentication.
http://itknowledgeexchange.techtarget.com/itanswers/linked-server-error-message-on-a-sql-server-2005-machine/

How to create new SQL Server Instance

1. Run the full setup.exe. Ignore the version warning message is Service Pack is installed.
2. Add a new DB instance there
3. Upgrade to the current SP version

SQL Server 2005 Security Patch KB954606 Install Issue

There is a known issue about the security patch KB954606 about reporting service failing to start. To fix it, add the following new registry key in path HKLM\SYSTEM\CurrentControlSet\Control\ :

ServicesPipeTimeout = 60000

Details at http://support.microsoft.com/kb/922918

Related discussion can be found at http://forums.microsoft.com/technet/showpost.aspx?postid=3878291&siteid=17&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

Report Manager Printing Issue

The issue: Cannot Print SSRS 2005 report after installing KB956391

The fix:
1. Uninstall KB956391 on the client PCs.
Or
2. Install SQL Server 2005 x64 Edition Service Pack 2 (KB954606) on the data server (The one that has SSRS service running). More details on http://www.microsoft.com/technet/security/Bulletin/MS08-052.mspx

Thanks to the following discussion:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PageIndex=1&SiteID=17&PageID=1&PostID=4006172