SQL Server Q&A

Symptoms
In Microsoft SQL Server 2008, new functionalities are added to SQL Server Management Objects (SMO). An application that uses the SQL Server 2005 version of SMO cannot be compiled by using the SQL Server 2008 version of SMO. You must make some changes to the application, and then you must recompile the application. Then, you can use the new functionalities in the SQL Server 2008 version of SMO.
Resolution
This article describes what you must do to use the new functionalities of the SQL Server 2008 version of SMO in an application that uses the SQL Server 2005 version of SMO.

Symptoms
Consider the following scenario:
You move msdb database from one instance to another either using the backup and restore process or by copying over the database files (mdf and ldf). Then, on the destination server, a user who is not part of Sysadmin fixed role in SQL Server tries to do either of the following operations:View a job in SQL Server management studioCall SQL agent related stored procedures (for example xp_sqlagent_enum_jobs or sp_get_composite_job_info) directly using T-SQL.
In this scenario, the user will get anerror message similar to the following:
 An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
 
The EXECUTE permission was denied on the object ‘xp_sqlagent_enum_jobs’, database ‘mssqlsystemresource’, schema ’sys’. (Microsoft SQL Server, Error: 229)
 
Resolution
This issue occurs because the SQL Agent certificate is different on different instances.

Symptoms
In Microsoft SQL Server 2008, you enable the IntelliSense feature in the Query Editor in SQL Server Management Studio. When you type Transact-SQL statements in the Query Editor, the IntelliSense feature may become unavailable, and you do not receive an error message.
Resolution
This problem occurs because of an invalid statement in the Query Editor. When the database engine parses the statement, the database engine throws a System.FormatException exception. This exception stops the IntelliSense feature.

Symptoms
When you start SQL Server Management Studio in Microsoft SQL Server 2008, you may receive an exception error message that resembles the following:

Unable to read the list of previously registered servers on this system.You receive this error message when you start SSMS for the first time after you upgrade from Microsoft SQL Server 2005.
Resolution
This problem can occur if the Regsrver.xml file becomes corrupted during the upgrade process.