The SQL server specified in SSIS service configuration is not present or is not available.

If your connected to SQL 2005 Integrated Services and you get the following error message when expanding MSDB

TITLE: Microsoft SQL Server Management Studio
——————————

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic “Configuring the Integration Services Service” in Server 2005 Books Online.

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)

——————————

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

Check the following,

1. Launch SQL Server Configuration Manager
2. Check that the SQL Server Integrated Services is running and that the Log On As account is an Active Directory account with correct permissions.
3. Under SQL Server 2005 Network Configuration make sure Names Pipes is enabled for the SQL instance.

If you have multiple instance then you may need to change the MsDtsSrvr.ini.xml to state the default instance.

The registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile specifies the location and name for the configuration file that Integration Services service uses. The default value of the registry key is C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ MsDtsSrvr.ini.xml. You can update the value of the registry key to use a different name and location for the configuration file.

In the configuration file, go to . and replace the . with ServerName\InstanceName

Invalid Object Name when using sp_send_dbmail in SQL 2005

If you are trying to send a query result via email in SQL 2005 then you will need to use sp_send_dbmail procedure. As with other Database Stored Mail procedures, the sp_send_dbmail procedure lives in the msdb database. Therefore, you need to ensure that you are a member of the DatabaseMailUserRole in the msdb database.

Now secondly if you are using the @query parameter then you need to make sure that you specify the database where you would like the query to run. You can do this by using the @execute_query_database parameter. If you do not do this then you will receive an invalid object name error.

Example:

EXEC msdb.dbo.sp_send_dbmail
@recipients = ‘youremail@domain.com‘,
@subject = ‘Subject‘,
@profile_name = ‘DBMail Profile Account Name‘,
@query = ‘your query‘
@execute_query_database = ‘database you would like to query‘,
@body = ‘your message here or you can leave out if you don’t want anything other then the query in the body‘,
@attach_query_result_as_file = 0 or 1;

Another thing to remember is if you want the query result to be displayed in the body and not as a file attachement then make sure you use the @attach_query_result_as_file parameter and set the value to 0. If you would like to send it as a file attachment then you will need to set the value as 1.