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