GetDataSourceContents will always return null for the Password member of the instance of DataSourceDefinition it returns

Even though current and past versions of the documentation have never explicitly said so, for obvious reasons the password is no returned . In fact there is no public SSRS API that can return passwords in stored credentials. Just to let you know until the documentation is improved to include that information. By reflecting the…

1

Adjusting the maximum number of worker threads per SQL Server Agent’s subsystem when processors are added or removed from the machine after the instance of SQL Server was installed

Inside MSDB, there exists a system table named dbo.subsystems which is populated the first time SQL Server Agent services runs. Every time SQL Server Agent starts up, among the several things it does, it interrogates the contents of that table to find out what subsystems are enabled and how they are configured. To do so,…

4

SQL Server Agent failing to start because "The EventLog service has not been started" (Second Part)

First part of this post is available at SQL Server Agent failing to start because "The EventLog service has not been started". I’ve just confirmed an improvement has been already checked-in into SQL Server 2008’s code which keeps up calling EnumServiceStatus when it returns FALSE and GLE returns ERROR_MORE_DATA. It retries up to 10 times…

4

SQL Server Agent failing to start because "The EventLog service has not been started";

(This post has been extended with SQL Server Agent failing to start because "The EventLog service has not been started" (Second Part)) One customer reported to us that their SQL Server 2000’s SQL Server Agent services, recently started to fail when trying to come up running, automatically after a reboot of the operating system hosting…

8

Is it possible to set MSDB’s recovery model to Full?

The following topic in SQL Server 2000’s documentation “Backing Up the model, msdb, and distribution Databases” (http://msdn2.microsoft.com/en-us/library/aa173541(SQL.80).aspx), and the equivalent in SQL Server 2008 “Backing Up the model and msdb Databases” (http://msdn2.microsoft.com/en-us/library/ms188274.aspx) recommend to set the recovery model of msdb to Full, under the circumstances specified below. If you use the backup and restore history…

1

Error 22022: SQLServerAgent Error: Network problems are preventing connection to the Microsoft Exchange Server computer.

Another troubleshooting story to share…   Customer was using SQL Server 2000 (build 8.00.2162). They use SQL Agent mail for sending notifications about job completion. They upgraded their Exchange Server 5.5 to Exchange Server 2003, and since then they started experiencing the following problem: Occasionally they receive error message indicating that connection to Exchange server…

1

Is SQL Agent manipulating the errors raised by SQL Server, before dumping them to the output file?

One customer reported that when he was executing the following batch use master exec sp_updatestats from SQL Server Management Studio (SSMS) he was getting different results than when the same stored procedure was ran from a SQL Server Agent’s Job. Specifically from a step of the T-SQL subsystem.   SSMS was showing the following output:…

3