Post reply

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

  • 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?

  • 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!

  • 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?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply