Sql – SSRS – Renamed TempDB and now Subscription Reports not Emailing

I found the following techniques helpful:

1. Make sure your database engine is configured to accept remote connections:

   • Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
   • Click on Surface Area Configuration for Services and Connections
   • Select the instance that is having a problem > Database Engine > Remote Connections
   • Enable local and remote connections
   • Restart instance

2. You may need to create an exception on the firewall for the SQL Server instance and port you are using:

   • Start > Run > Firewall.cpl
   • Click on exceptions tab
   • Add sqlservr.exe (typically located in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Bin, check your installs for the actual folder path) and port (default is 1433)
   • Check your connection string as well

   • From FIX : ERROR : Could not open a connection to SQL Server:

3. Check if your SQL server services is up and running properly:

   • Go to All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager > SQL Server Services
   • Check to make sure SQL Server service status is Running.

   In addition, ensure that your remote server is in the same network. Run sqlcmd -L to ascertain if your server is included in your network list.

4. Enable TCP/IP in SQL Server Configuration

   When two or more SQL Servers are connected across network they do all communication using TCP/IP. The default port of SQL Server installation is 1433. This port can be changed through SQL Server Configuration Manager. TCP/IP should be enabled for SQL Server to be connected.

   • Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select TCP/IP
   • Right Click on TCP/IP >> Click on Enable

   You must restart SQL Server Services for all the changes to take effect. Right click and go to menu properties to select location where default port of SQL Server can be changed.

Some other places you could check are:

SSRS Log:

c$\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles

You could try enabling verbose logging (level 4) here

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe.config

Look for this tag:

<switches>
  <add name="DefaultTraceSwitch" value="4" />
</switches>

I'm fighting very similar issue but to make it a bit complex, my subscription fails only every other time or so and runs fine sometimes. So far I ruled out:

• No. of parallel SSRS sessions
• Memory pressure on DB server & Report
• Server Kicking off the subscription directly vs remotely(running
  the agent job)

Sorry, I do not have a good answer for you but this issue seems to bother more than few people.

Update: I found out that the Subscription was a created by a windows user who is no longer valid (ID expired). Updating the Subscription owner fixed it.

-- List SSRS users (check to make sure the new Owner exists in this list)

SELECT *
FROM Users
WHERE UserName IN ('DomainName\_SSRSServiceAccount') 

-- DFGRYH-DFGRYH--DFGRYH-DFGRYH

-- Identify the subscription whose ownership you would like to edit

SELECT *
FROM Subscriptions
WHERE OwnerID = 'DFGRYH-DFGRYH--DFGRYH-DFGRYH'
    AND Description = 'Send e-mail to RichG@DomainName.com'
ORDER BY LastRunTime
    ,LastStatus

-- Update the Subscription with new Owner, the change should reflect on the SSRS reports

UPDATE Subscriptions
SET OwnerID = 'DFGRYH-DFGRYH--DFGRYH-DFGRYH'
WHERE SubscriptionID = 'B33A78FC-933D-47DB-AB50-43D36B24C0B8'