May 21, 2003 at 7:45 am
I am new to transfering objects from one SQL box to another.
When I transfer tables, it fails; however, the table IS transfered - but without data and indexing.
Stored Procedures will not transfer at all.
I obviously am missing a setting somewhere.
Any help would be appreciated.
May 21, 2003 at 8:24 am
How are you transfering them?
The easiest way to transfer a database is to detach the database, copy the datafiles to the new server, attach the database on the other end.
Ross
May 21, 2003 at 8:53 am
I am using the Export/Import feature - it fails both ways.
I am not familiar with "detaching" a database.
However, I ONLY want to transfer a couple of tables and stored procedures.
May 21, 2003 at 9:27 am
If you only want to move a couple of tables and sps. I would take this approach.
1)Go into Enterprise Manager
2) Select Source Database
3) Right Click select All Tasks/Generate SQL Script
4) Select tables and sps you want to move over, make sure you select that indexes, etc... are also scripted.
5) Save script
6) Open in Query Analyzer
7) Run script
8) Use the Export/Import feature to transfer data between servers at this point.
HTH 😀
Ross
May 21, 2003 at 9:32 am
If you create your dts package using import/export and save it to local packages instead of running. then goto to DTS local packages in enterprise manager right click on pakage and select design package to open.
from menu select package >> properties. select loggin tab and turn on logging. then run package when it fails you can then go back to package and call up the logs to see why it failed.
May 21, 2003 at 10:39 am
The scripting works great; however, I am still missing something because I still do not get the data?
Sorry for being a pain. What am I missing?
May 21, 2003 at 10:46 am
I would go ahead and use the Export/Import wizard at this point, like you were before. The problem before was that the Export/Import wizard was not creating the tables as you wanted them. Now that you have created the tables properly that wizard should be able to move the data.
The other option is to link the two servers using Linked Servers in enterprise manager. Then you can use an insert into statement to move the data. Something like:
insert into server2.database1.dbo.table1 (field1, field2)
select field1, field2 from server1.database1.dbo.table1
You can read about linking servers in Books Online.
HTH
Ross
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply