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">

More on SQL Server 2008 Variable Enhancements

Let's continue where we left last week. I want to point out another enhancement around assigning values to parameters in SQL 2008 and above. It's called compound assignment operator, starting with SQL 2008 we can declare a parameter and assign value and also we can do mathematical operations.

-- SQL Server 2008 and 2008 R2

DECLARE @DueAmount MONEY = 101

-- 10% off purchases over 100$ promotion

IF @DueAmount > 100

BEGIN

SET @DueAmount *= 0.9

END

SELECT @DueAmount

GO

-- SQL Server 2005 and before

DECLARE @DueAmount MONEY

SELECT @DueAmount = 101

-- 10% off purchases over 100$ promotion

IF @DueAmount > 100

BEGIN

SET @DueAmount = @DueAmount * 0.9

END

SELECT @DueAmount

If you pay attention how the value is being set inside the if logic you will understand how compound assignments works. Here is the link to msdn page for more detailed info around setting the values to variables. It does help with less coding in your T-SQL but if there is sql server 2005 or earlier versions exist in your environment be careful to remember which environments that statement will be used.

HTH,

Bulent

SQL Server 2008 Variable Declaration

Another day a developer approached me stating that the code works in local development server but it does not work when deployed to QA. I know that QA server is running SQL Server 2005. I heard that lately the development team was getting new workstations with new set up. So I was sure that they were running something other than SQL server 2005. I asked the developer if the sql server running on the workstation was SQL 2008. The answer was yes and I then asked how variables were declared and assigned the valued. It was just the first thing that came to my mind. And I was right.

So, starting with SQL Server 2008 Microsoft made improvements on declaring variables and assigning values to them. Here is an example for SQL Server 2008 and newer versions.

DECLARE @Startdate DATETIME = GETDATE()

DECLARE @AccountID INT = 12345

SELECT @Startdate AS StartDate,

@AccountID AS AccountID

However if you run the above statement in SQL 2005 or earlier you will get an error message. The only way above statement will work on SQL 2005 and earlier versions is;

DECLARE @Startdate DATETIME

DECLARE @AccountID INT

SELECT @Startdate = GETDATE(),

@AccountID = 12345

SELECT @Startdate AS StartDate,

@AccountID AS AccountID

As you see it is less of t-sql code for SQL Server 2008 and newer versions. There is some more enhancements but I will cover that in my next blog.

HTH,

Bulent

70-432 TS: Microsoft SQL Server 2008, Implementation and Maintenance

Hello all,

I have been pretty quite lately. There are many reasons for that. One of them is I was preparing for the 70-432 exam. I see a lot of questions in the net about the preparation and books. So I wanted to blog something around that.

Good news is I passed the exam and scored 833 out of 1000. It's not too high of a score but if you score 700 or above, you're certified.

Now the preparation part. I have used MS Press book by Mike Hotek. I like how each chapter is divided into lessons that are not too long to complete. From my experience standpoint the book pretty much covers the material in the exam. The study gave me a chance to look at the features of SQL Server that I don't use day in and day out. I have been working with SQL Server since 2005 including version 2000/2005/2008/2008 R2. I did pass the 2000 Admin and developer certifications back in 2007. At the time where I work we did not have anything but SQL Server 2000. Since then a lot changed. However I still wanted to grab a book and study. However I do think that this book is not just enough. The questions at the end of each chapter are quite easy so it may give you a false sense of confidence (at least for me it might have been). So I turned to practice exam by Transcender. When I started the practice exam it was a lot tougher than the questions in the book. Transcender exam practice really helped me tune more towards the exam format. I definitely recommend the exam practice since it also helps with the explanation of the answers for each question. I also kept the BOL (Books On Line) open.

So one exam is down and I am trying to decide which MS SQL Server certification exam will be the next. Let me know about your certification thoughts. By the way I don't work for Transcender.

Good day,

Bulent