Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pushpa81
Starting Member
5 Posts |
 Posted - 2004-08-22 : 04:49:35
|
| Hi, Im new to SQL Server and just discovered DTS. Currently Im developing a ASP.Net system with SQL Server as the database. The problem is I have to download data from another SQL Server (main database) on a different PC to my SQL Server. This data has to be downloaded into 2 tables of my SQL Server. 1st table needs to download all data fields.2nd table needs to download only 2 data fields. Can DTS do this? If yes, please tell me how? or is there any other solution? I urgently need help. My system is due end of this month... |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-08-22 : 07:05:26
|
| You'd be better off using Linked Servers IMHO. Look up Linked Servers in Books Online for more info.You could also do it via DTS quite easily. Use the Import/Export wizard in Enterprise Manager as this should do it all for you. |
 |
|
|
Pushpa81
Starting Member
5 Posts |
Posted - 2004-08-22 : 08:29:34
|
| Thank you for ur reply. But I forgot to mention another detail. I want to make this function an automated function which is done once a month. So that any new data in the main database will also be updated in my system.As I know the Import/Export wizard can only be used for 1 time data transfer. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-22 : 08:59:44
|
| You can create dts package, create an SP that uses linked servers, create an SP that uses openrowset to access the remote server, create an SP that uses bcp to export the data, xcopy t ocopy the files and bulk insert to insert the data.All of these can be scheduled to run once a month.Which is best depends on the environment you are working in - all but the bccp solution need a connection to both servers at the same time (but you could make a dts solution to extract to files then use those files for import).The linked server solution is probably simplest but has most impact on the servers.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Pushpa81
Starting Member
5 Posts |
Posted - 2004-08-22 : 12:30:24
|
| Thanks nr... but Im new to SQL Server.. what u mention is a bit confusing me. Can u give me some good sites to refer to... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-22 : 14:24:22
|
| www.nigelrivett.comFor the linked server just add a linked server (under security) and access the table using the fully qualified namesvr.database.user.table==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Pushpa81
Starting Member
5 Posts |
Posted - 2004-08-23 : 11:26:53
|
| thanks, Nigel. I'll go through ur website and figure it out. |
|
|
|
|
|
|
Data transformation from SQL server to SQL server