SSRS 2008 Transport Error Code 0x800ccc15

Hello all,

I received and email stating that a user did not receive the report from SSRS that he was subscribed to in the last couple of days. When I start looking into the report in the subscription tab of the report I saw the message below.

Failure sending mail: The message could not be sent to the SMTP server. The transport error code was 0x800ccc15. The server response was not available

I tried to open the report and had no problem running the report manually. Then I update the schedule of the report to see if the scheduled sql server agent job is updated as well. Then I looked at the jobs in the server hosting ReportServer database and found the corresponding job with that timed subscription and verified that the execution time of the job is updated with what I did using SSRS portal (In the SQL Server Agent Jobs the name of the jobs for SSRS are given using a system generated GUID and if you don't know the job it's hard to locate since there may be many of them, in my case there were only 8 jobs and I knew which job I need to check once I made the change. However in future blog I will post about this in little more detail). I waited for couple of minutes and saw that the job executed in SQL Server Agent hosting ReportServer database. However still saw the same error message.

I remote into the SSRS server to look into to the problem locally. As soon as I logged in I saw a red icon on the task bar coming from McAfee. I remember that I had to change the configuration of the McAfee to allow database mail to work couple weeks ago. I opened the log and found that the log event below.
1/19/2012 2:30:30 PM Blocked by port blocking rule C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe Anti-virus Standard Protection:Prevent mass mailing worms from sending mail 192.168.162.162:250

That is when I knew what was wrong. The McAfee ePO policy change forced to server so that processes that need to send email has to be excluded in the Prevent Mass Mailing Worms section. I talked to sysadmin who administers the ePO policy to exclude the ReportingServicesService.exe and push the policy again to the server. Within a minute policy was in effect. I update the report subscription to 3 minutes later and waited for the mail to arrive with the report attached. After 3 minutes voila, I received the email and solved the problem. In my case it was as simple as adding the process to exclusion list to send SMTP emails.

HTH,
Bulent

SQL Function QUOTENAME()

Hello all,

I support third party software databases at work. During the implementation of the application it needed to create a database to store the data. However the application created a database with a name that does not follow the rules for the format of identifiers. In my case we ended up with a database name '20120101_Survey'.

On the same server I have a custom built script that backups up all the databases at night. However, following the database creation my backup process failed and I got an alert about the process. Further looking into the problem I found out that there was a problem with the syntax. As soon as I see the statement I knew what the problem was but never thought about it while deploying the application and database. To keep the story short I used the SQL Server function called QUOTENAME() to fix the problem. Using the function in my custom script help me return the database names with delimiters so that backup process completes without error. Since then I have been using the function with all the object names. This simple function can prevent some headaches if a DBA needs to support 3rd party databases (SharePoint is also a good example because the SharePoint database names don't follow the rules of the identifier format). Here is the link to the msdn and below is simple statement that returns all the databases in your system with delimiters.

SELECT QUOTENAME(name) AS DelimitedDbName

FROM sys.databases

HTH,

Bulent

The distribution agent failed to create temporary files

Hello,

I ran into a replication error right after configuring the transactional replication. The error started after applying the snapshot and the exact the error message was 'The distribution agent failed to create temporary files in C:\Program Files\Microsoft SQL Server\100\COM directory. System returned errorcode 5.' In this specific configuration I was using the sql server agent account as the distribution agent and the AD account was just a simple domain user. In this case the culprit was that the account being used as distribution agent needed write access to folder and giving that permissions fixed the error. One thing to remember is if this is a clustered sql server you're working with you need to grant write access to the account for all the nodes in the cluster. Here is the link to MS KB article.

HTH,

Bulent

70-433 TS: Microsoft SQL Server 2008, Database Development

Another period of time just passed very quickly and no blogs I was able to post. During that time I passed exam 70-433 TS: Microsoft SQL Server 2008, Database Development and I got another certification under my belt.

So how did I prepare for the exam? I used this book and I think it's good book that covers the exam objectives. I have also used Transcender exam to prepare for the final week. I have to warn that you need to know the XML Query in SQL Server.

Good luck if you're preparing for this exam. Soon I will start preparing for 70-450: PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008 and if you have any tips for me please let me know.

HTH,

Bulent

Zemanta" style="border:none;float:right">

SQL Server 2008 R2 Express Edition and Full Text Search

Hello all,

Couple of months ago I installed SQL Server 2008 R2 Express Edition to be used as repository for test tool the QA department needed to use. Everything seems to be fine till end of August when QA team needed to enable Full Text Search and search for some of the databases during their test phase. I got an email stating that the full text did not work. So I dived in to investigate.

I found out that SQL Server 2008 R2 Express edition only does not support the full text search and I had to upgrade to SQL Server 2008 R2 Express Edition with Advanced Features. While searching I found this site that talks about how to upgrade the Edition.

In my case the upgrade did not work. I kept getting run time errors during the upgrade process. That forced me to uninstall but before uninstall I backed up all the user databases and scripted out the logins using sp_help_revlogin stored procedure (you can read all about transferring logins here). Then I installed SQL Server 2008 R2 Express Edition with Advanced Features. During the installation I checked the full text search in the feature selection and continue with the process. Then I executed the script to create the logins. After that I attached all the databases and voila.

I think from now on I will only install SQL Server 2008 R2 Express Edition with Advanced Features but install only the features I need (ie, database only) and if someone needs other features like Full Text Search or Reporting Services then I can easily add the features.

HTH,

Bulent

Dropping Local Temp Table

A collegue needed to create a local temp table in his work to insert records. When executed the script to create and insert records after first try he kept getting error message stating that 'There is already object named #TestTable in the database. I told him that he should clean up the Temp tables at the end when he is done with them and also to be safe in the beginning of the code to check and drop if the table exists. Here is an example.

-- CHECK AND DROP TEMP TABLE IF EXISTS

IF OBJECT_ID ('Tempdb.dbo.#TestTempTable') IS NOT NULL

BEGIN

DROP TABLE #TestTempTable

END

-- CREATE TEMP TABLE

CREATE TABLE #TestTempTable (

RowID tinyint not null,

City varchar(50) not null

)

-- POPULATE WITH DATA

INSERT INTO #TestTempTable

SELECT 1,'London'

UNION

SELECT 2, 'Chicago'

UNION

SELECT 3, 'Paris'

/***

MORE CODE HERE

***/

SELECT * FROM #TestTempTable

-- WHEN DONE CLEAN UP THE TEMP TABLE

DROP TABLE #TestTempTable

Remove Server from SSRS Scale Out Deployment

Hello all,

I worked in a project to move the existing SSRS report server to another server to consolidate servers. Both new and old report servers have been installed with SQL Server/SSRS 2008 Enterprise. We backed up the reportserver, reportservertempdb and encryption keys on the old server. Then we installed SSRS 2008 Enterprise to new server. Then restored the reportserver, reportservertempdb, encryption keys to the new server and vola we have a new SSRS running on the new server. It was not hard at all. The small detail that I found out later was when I was checking report server configuration manager. All the way at the bottom where you can configure Scale Out SSRS deployment both the new server and old server were listed. At the time the old server were removed from production and not accessible. To clean up the scale out deployment configuration and remove the old server we had to do the following. Of course backup was first done for any configuration changes to our servers.

1. Backup the reportserver, reportservertempdb databases

2. Backup report server encryption keys

Once we have a backup I proceeded to clean up. Please use caution while doing this task. We will use RSKeyMgmt.exe in command line window.

1. Open command line and go the the path C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\ (on a 64-bit SQL 2008 Reporting Services )

2. List the report servers currently in the database type the command

RSKeyMgmt -l

You will see the server listed with their GUID.

Note the GUID of the server that you want to remove.

3. To remove the instance of the Report server type the command below.

RSKeyMgmt -r GUIDoftheSSRSserverTobeRemoved

4. You will be prompted by a question if you're sure you want to do this. Press Y if you're sure you want to remove the server.

5. Type the command RSKeyMgmt -l to list to server and verify that the server has been removed.

HTH,

Bulent

Zemanta" style="border:none;float:right">