Moving tables to new schema

I am using sql05 and need to move some tables/data/keys to a new
schema (same database).
Any easy way to do that?
thanks, ChuckYou can use ALTER SCHEMA to move an object from one schema to another.
For example to move the HumanResources.Department table to the Sales schema
you can use the following command:
USE AdventureWorks
GO
ALTER SCHEMA Sales TRANSFER HumanResources.Department
HTH
- Peter Ward
WARDY IT Solutions
"Chuck P" wrote:
> I am using sql05 and need to move some tables/data/keys to a new
> schema (same database).
> Any easy way to do that?
> thanks, Chuck
>|||Thanks, you saved me a lot of work, I was getting ready to do it the
old fashion way.
Great command too, my thanks to MS for that one.

Moving tables to new schema

I am using sql05 and need to move some tables/data/keys to a new
schema (same database).
Any easy way to do that?
thanks, Chuck
You can use ALTER SCHEMA to move an object from one schema to another.
For example to move the HumanResources.Department table to the Sales schema
you can use the following command:
USE AdventureWorks
GO
ALTER SCHEMA Sales TRANSFER HumanResources.Department
HTH
- Peter Ward
WARDY IT Solutions
"Chuck P" wrote:

> I am using sql05 and need to move some tables/data/keys to a new
> schema (same database).
> Any easy way to do that?
> thanks, Chuck
>
|||Thanks, you saved me a lot of work, I was getting ready to do it the
old fashion way.
Great command too, my thanks to MS for that one.

Moving tables to new schema

I am using sql05 and need to move some tables/data/keys to a new
schema (same database).
Any easy way to do that?
thanks, ChuckYou can use ALTER SCHEMA to move an object from one schema to another.
For example to move the HumanResources.Department table to the Sales schema
you can use the following command:
USE AdventureWorks
GO
ALTER SCHEMA Sales TRANSFER HumanResources.Department
HTH
- Peter Ward
WARDY IT Solutions
"Chuck P" wrote:

> I am using sql05 and need to move some tables/data/keys to a new
> schema (same database).
> Any easy way to do that?
> thanks, Chuck
>|||Thanks, you saved me a lot of work, I was getting ready to do it the
old fashion way.
Great command too, my thanks to MS for that one.

Moving symmetric keys between servers

I'm having some issues restoring a backup of database that uses native encryption onto another server. I know there are a couple of articles on this but I seem to be missing something. Any help would be greatly appreciated.

Current Server Windows 2000
Destination Server Windows 2003

The original key setup for the current server was achieved by something like this:

CREATE SYMMETRIC KEY HR01 WITH algorithm=DES encryption BY password = 'HRpassword'

Running the command select * from sys.symmetric_keys on the current server I get the following:

name principal_id symmetric_key_id key_length key_algorithm algorithm_desc create_date modify_date key_guid HR01 1 256 56 D DES 2006-11-22 16:36:01.883 2006-11-22 16:36:01.883 BBD80500-338F-47D7-B336-85D46E00F2F0

So I restored the database onto the new server and ran this script:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

I don't have a master key... so I go back to the original server and ran this script and did another backup:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

Running select * from sys.symmetric_keys on the current server now looks like this:

name principal_id symmetric_key_id key_length key_algorithm algorithm_desc create_date modify_date key_guid ##MS_DatabaseMasterKey## 1 101 128 D3 TRIPLE_DES 2007-04-23 16:03:09.183 2007-04-23 17:02:46.630 1CB0D800-0173-4A1E-B841-362B454E60AC HR01 1 256 56 D DES 2006-11-22 16:36:01.883 2006-11-22 16:36:01.883 BBD80500-338F-47D7-B336-85D46E00F2F0

I then restored the new backup onto the 2003 server and ran this script:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

The alter script now runs fine but I get the error message: The decryption key is incorrect when trying to open the HR01 key

We have seen other cases of problems using DES on Windows 2000 (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1122121&SiteID=1). Unfortunately the DES (DES only, not TRIPLE_DES) implementation on Win2k is not as robust as on later versions of the OS. We typically recommend against using DES, and we strongly recommend against using DES on Windows 2000 platforms (use TRIPLE_DES instead).

My personal recommendation on this case would be to try to restore the DB temporarily on a Windows 2000 machine, recover the data and re-encrypt it using TRIPLE_DES algorithm.

By the way, because you are using protection by password instead of by a certificate, the DB master key steps should not be needed in your case.

I hope this information helps. Please, let us know if there is anything else we can do to help.

-Raul Garcia

SDE/T

SQL Server Engine

Moving ReportServer Databases

Followed the steps in Books Online to move the Reporting Services databases from one server to another. After resetting all the keys and configurations, the site comes up. However, all my shared datasources have lost their connection strings, and have reverted to requesting login information. Is this how it is supposed to work? If not, how do I fix this easily (as I have about 25 data sources)?

Hello,

Did you happen to backup the encryption key from your old reporting services instance?

Jarret

|||Nope. Didn't see that part until it was too late.
|||

In Books Online, it says...

After you move the report server database, you must reconfigure the connection information, which is stored as an encrypted value in the rsreportserver.confg file.

Here's a link that describes how to do this. http://support.microsoft.com/Default.aspx?id=842425

Hope this helps.

Jarret

|||

Sorry, but you're going to have to go back and set them up the way they were.

Jarret