Using the Transfer SQL Server Objects Task to copy table to a new schema in another db

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  October 4, 2012 at 12:28 pm

  #282620

  I am attempting to use a Transfer SQL Server Objects Task to copy tables, udf, stored procs, and views from a src db on another server and copy them to a database and schema on another server. I see where it is pretty staright forward on selecting which objects to copy but I dont see where I can assign the objects to a specific schema in the destination db. Can someone help out with this please?

• Lowell

  SSC Guru

  Points: 323522

  More actions

  Follow

  October 4, 2012 at 1:01 pm

  #1545745

  dndaughtery (10/4/2012)

  ---

  I am attempting to use a Transfer SQL Server Objects Task to copy tables, udf, stored procs, and views from a src db on another server and copy them to a database and schema on another server. I see where it is pretty staright forward on selecting which objects to copy but I dont see where I can assign the objects to a specific schema in the destination db. Can someone help out with this please?

  As i remember it, changing the target schema's not an option;

  in the past i've had to copy the objects, then change all theuir schemas after they were copied...so copying dbo.TableName to be Orange.TableName was a two step process...copy dbo.TableName, then doing the ALTER SCHEMA stuff like this:

  select 'alter schema Orange transfer ' + sch.name + '.' + tab.name

  from sys.schemas sch inner join sys.objects tab on sch.schema_id = tab.schema_id

  where sch.name = 'dbo' and tab.type = 'P'

  order by tab.name

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  October 4, 2012 at 1:08 pm

  #1545749

  OK, thanks I will try it that way. I have another question. I was trying tp update my task to only copy one table over but when I tried to display the table list I got the following error.

  Property DefaultSchema is not available for Database '[MyDB]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

  Any idea how I can get around this?