Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2007
    Posts
    15

    Red face Unanswered: Remote Server. How to move tables w/o DTS

    hi,

    I have a web hosting account with Network Solutions. I also have my databases hosted on their sql server 2005. (along with a few thousand other database which don't belong to me) . Network Solutions does not allow me to speak to their Technical Support. I must submit a request and they email me their response. Bottom line they are not going to help me with my problem.

    I am using SQL Server Management Studio Express to connect remotely to the server.

    I have 2 databases. Currently all data is stored in database1

    I need to move the tables with the data for my website forum from database1to database2 on the same SQL Server

    I have already generated a script which creates the schema for the tables on
    database2. The problem is moving the data. The web Host does not allow DTS

    Here are the things they won't allow:

    You can use all features of SQL 2000 (it is 2005) except for the following:

    - DTS Packages
    - Database Replication
    - Mail Services
    - XML Support
    - Distributed Transactions
    - Database Maintenance Plans
    - Web Assistant
    - Multiple Instances

    I can't for the life of me figure out how to transfer the data.
    I have tried copying and pasting the data and run into problems with Identity keys and constraints.
    I tried a sql generator script to create insert scripts for each line of each table but it won't work. I'm not sure why.

    Bottom-line I can't import and export directly. And I desperately need to.

    Thanks in advance!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If they're all stored on the same SQL Server instance then you can use 3 part naming

    database.schema.object

    If they're on separate instances, set up a linked server and use 4 part naming

    server.database.schema.object

    Example
    Code:
    INSERT INTO database1.dbo.mytable (field1)
    SELECT field1 FROM database2.dbo.mytable
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2007
    Posts
    15
    Thanks for the reply

    The settings won't allow me. I hate it. but I don't have any other option but to use the shared hosting plan, for now
    my username must be different for database1 and database2

    so when I run the query
    INSERT INTO database1.username1.mytable (field1)
    SELECT field1 FROM database2.username2.mytable

    it gives me the error message
    sg 916, Level 14, State 1, Line 1
    The server principal "username1" is not able to access the database "database2" under the current security context.
    If I use dbo like this
    INSERT INTO database1.dbo.mytable (field1)
    SELECT field1 FROM database2.dbo.mytable

    or

    INSERT INTO database1..mytable (field1)
    SELECT field1 FROM database2..mytable

    I get
    Msg 208, Level 16, State 1, Line 1
    Invalid object name

    Now, I'm trying to use Microsoft Access and Linked Tables and the Upsizing wizard.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you set up a linked server?
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    bcp is your friend in this situation. since you already have the schema in place, just bcp out, and bcp in. bcp can handle the data movement. just disable all your constraints on the new schema, do the import, then reenable the constraints.

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Don't forget BCP may not help with your

    "I have tried copying and pasting the data and run into problems with Identity keys and constraints. "

    Issue, which may be your most important challenge
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by GWilliy
    Don't forget BCP may not help with your

    "I have tried copying and pasting the data and run into problems with Identity keys and constraints. "

    Issue, which may be your most important challenge
    Willy ... you need to be familiar with all the bcp switches ... this is directly from BOL

    -E
    Specifies that the values for an identity column are present in the file being imported. If -E is not given, the identity values for this column in the data file being imported are ignored, and SQL Server 2000 automatically assigns unique values based on the seed and increment values specified during table creation. If the data file does not contain values for the identity column in the table or view, use a format file to specify that the identity column in the table or view should be skipped when importing data; SQL Server 2000 automatically assigns unique values for the column. For more information, see DBCC CHECKIDENT.

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    O OK - So the Target DB has'nt got any other tables with foreign Key constraints on the data being transfered in the these forum tables

    Missed that one ;-)

    Good Luck getting access to the Cmd Line on the Hosts box aish
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  9. #9
    Join Date
    Aug 2007
    Posts
    15
    Georgev, No I don't have the permissions to set up a linked server.
    I will look into BCP as I've never used it before. If I can do a tomh53 says,

    -E
    Specifies that the values for an identity column are present in the file being imported.
    then I may be able to take care of the problem of the Identity keys being overwritten.

    I just wonder if I can have the permissions necessary to use the command line to access the server.
    Also BCP syntax seems pretty complicated.

    Thanks for the help so far. i'll keep you posted.

  10. #10
    Join Date
    Aug 2007
    Posts
    15
    There are foreign Key constraints on the data being transfered in the the forum tables.
    Can I still use BCP?

  11. #11
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    If this is a one time data transfer, why don't you just ask the tech support guy to backup db1 and restore it to db2? You can then go into db2 and drop what you don't need\want and make the changes as you see fit. This is assuming you don't care if db2 is complete overwritten. From what I read in your first post it sounds like the two dbs are pretty similar if not identical.

  12. #12
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by aish1108
    There are foreign Key constraints on the data being transfered in the the forum tables.
    Can I still use BCP?
    Yes ... use a sql script to remove the FK constraints, load the tables, then add back the FK constraints

    Code:
    ALTER TABLE [dbo].[cmCommissionDetail] DROP CONSTRAINT [FK_cmCommissionDetail_cmCommission];
     
    ...  <later>
    ALTER TABLE [dbo].[cmCommissionDetail]
     ADD CONSTRAINT [FK_cmCommissionDetail_cmCommission]
     FOREIGN KEY ([CommissionID]) REFERENCES [dbo].[cmCommission] ([CommissionID]);
    ALTER TABLE [dbo].[cmCommissionDetail]
     CHECK CONSTRAINT [FK_cmCommissionDetail_cmCommission];

    -- This is all just a Figment of my Imagination --

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    or just load your parent tables before your children.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Using example from another thread (http://www.dbforums.com/showthread.php?p=6340139), you can incorporate this script into it:
    Code:
    if object_id('__admin.vw__DropAllFKConstraints') is not null
       drop view __admin.vw__DropAllFKConstraints
    go
    if object_id('__admin.fn__AddAllFKConstraints') is not null
       drop function __admin.fn__AddAllFKConstraints
    go
    create view __admin.vw__DropAllFKConstraints as
       select cmd =
          'alter table [' + rtrim(schema_name(ObjectProperty(parent_object_id,'schemaid')))  
            + '].[' + object_name(parent_object_id)  
            + '] drop constraint [' + object_name(object_id) + ']' 
          from sys.foreign_keys
    go
    create function __admin.fn__AddAllFKConstraints ()
       returns @t table (cmd varchar(8000) null) as begin
       declare @fkeyid int, @rkeyid int  
       declare @fkeycol smallint, @rkeycol smallint  
       ,@keys   nvarchar(2126) --Length (16*max_identifierLength)+(15*2)+(16*3)     
       ,@cnstdes  nvarchar(4000)-- string to build up index desc
       ,@cnstid int
       declare obj cursor local for
          select o.parent_object_id, o.object_id from sys.foreign_keys o
             order by object_name(o.parent_object_id), o.parent_object_id, o.object_id
       open obj
       fetch obj into @fkeyid, @cnstid
       while @@fetch_status >= 0 begin
          declare fkeys cursor local for
             select parent_column_id, referenced_object_id, referenced_column_id     
                from sys.foreign_key_columns where constraint_object_id = @cnstid
                order by parent_column_id
          open fkeys
          fetch fkeys into @fkeycol, @rkeyid, @rkeycol  
          select @keys = col_name(@fkeyid, @fkeycol), @cnstdes = col_name(@rkeyid, @rkeycol)  
          fetch fkeys into @fkeycol, @rkeyid, @rkeycol  
          while @@fetch_status >= 0 begin  
              select @keys = @keys + ', ' + isnull(col_name(@fkeyid, @fkeycol), ''),
                @cnstdes = @cnstdes + ', ' + isnull(col_name(@rkeyid, @rkeycol), '')
              fetch fkeys into @fkeycol, @rkeyid, @rkeycol  
          end  
          close fkeys
          deallocate fkeys
          insert @t
             select 'alter table [' + rtrim(schema_name(objectproperty(@fkeyid,'schemaid'))) +
                '].[' + object_name(@fkeyid) + '] add constraint [' +
                rtrim(object_name(@cnstid)) + '] foreign key (' + @keys + ') ' +
                'references [' + rtrim(schema_name(objectproperty(@fkeyid,'schemaid'))) + '].[' +
                object_name(@rkeyid) + '] (' + @cnstdes + ')'
          fetch obj into @fkeyid, @cnstid
       end
       close obj
       deallocate obj
       return
    end
    go
    select * from __admin.vw__DropAllFKConstraints order by 1
    select * from __admin.fn__AddAllFKConstraints() order by 1
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    May 2008
    Posts
    1
    Hi aish1108! I am one of the Network Solutions Level 3 support. I apologize for the issue that you were encountering.

    First, we are here 24/7 to help you in these kinds of problems, through email and as well as over the phone.

    Now to answer your issue, there are few ways that you can go with.

    First, Reghardt is right. You can request your DB1 to be migrated over to your DB2 wherever the 2 may be in our MS SQL farm. Another way that you can do this is by requesting or preparing a backup copy of the source DB. Then send it to us. We will then be able to restore it to your destination or second DB.

    Now, if you would like this done or fixed by yourself, you can do what tomh53, have provided you. Do a right click on each of your tables then -> Script Table as -> Create To –> (your choice). Then run each query on your destination database.

    However, the best way to do this is to generate a script for your entire database. To do this: right click your database -> Tasks -> Generate Script. The Script Wizard will popup. Click the “Script all the objects in the selected database”, then follow the instruction.

    If you get a timeout error, just script each of the tables that did not get finished.

    Then run this query on your destination database.

    Make sure to delete the first 2 lines:
    USE DB1NAME
    GO

    Once you have created your table structure you can now use, DTSWizard.exe to migrate your data over.

    I Hope that these answer your question. And once again, we are here 24/7 to help you in anyway possible.


    Regards,
    SHERWIN001

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •