| Author |
Topic |
|
xhostx
Constraint Violating Yak Guru
277 Posts |
 Posted - 2012-06-20 : 15:35:17
|
| Hi All,I need a suggestion please.I have to access 2 tables in a remotely to gather some data.My question is, would it be better to import data into my local table directly or use bcp to copy a data into a text file then read from that file.Any additional suggestions will be appreciated.Thanks to all.kml |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-06-20 : 15:43:46
|
| If the data is relatively static you can use SSIS to import the data. There are other ways as well - openrowset, BCP etc. BCP may work just as well, but somehow, the thought of saving the data into an intermediate location doesn't much appeal to me.If your data changes frequently and you want to keep in sync, replication may be the preferred choice. |
 |
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-06-21 : 08:28:02
|
| Hi,I'm using SQL 2000, I'm not sure what will be available as tool or script to copy this data from a remote databases!Can anyones please list an example with parameters (remote machine, database name...etc)?Appreciated.kml |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-21 : 09:05:46
|
| Advantage of expoting to a text file is that you don't need both servers to be available at the same timeThe source server can control the export and destination the import.Makes the transfer a lot easier to test and implement - also means that you don't have the issue of losing data.Downside is that there are two things to implement but does mean they can be done by different sets of people.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-06-21 : 10:59:53
|
quote:
Originally posted by sunitabeck
If the data is relatively static you can use SSIS to import the data. There are other ways as well - openrowset, BCP etc. BCP may work just as well, but somehow, the thought of saving the data into an intermediate location doesn't much appeal to me.If your data changes frequently and you want to keep in sync, replication may be the preferred choice.
Hi,You have mentionned the replication, How can I do that please?thanks.kml |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-06-21 : 11:50:54
|
| Google for SQL Server replication; you will find plenty of resources. This might be a good starting poing: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=176000 |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-06-21 : 13:55:24
|
| how often does the data need to refresh?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-06-21 : 14:11:22
|
quote:
Originally posted by jackv
how often does the data need to refresh?Jack Vamvas--------------------http://www.sqlserver-dba.com
Hi,I think regulary....probably once a week. any good idea please!Thanks :)kml |
|
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-06-21 : 14:12:20
|
quote:
Originally posted by sunitabeck
Google for SQL Server replication; you will find plenty of resources. This might be a good starting poing: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=176000
I think this link point to this page, can you please re-provide it again.Thanks,kml |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-06-21 : 14:38:30
|
quote:
Originally posted by xhostx
quote:
Originally posted by sunitabeck
Google for SQL Server replication; you will find plenty of resources. This might be a good starting poing: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=176000
I think this link point to this page, can you please re-provide it again.Thanks,kml
Sorry about that. The link I meant to post was simply the first link you would find if you google for "replication SQL 2000": http://msdn.microsoft.com/en-us/library/aa237426(v=SQL.80).aspxAlso, consider the implication of Jack's question. If you are trying to copy the data from one server to another just once, or once a day, replication would not be the best solution, hence his question. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-06-22 : 01:47:33
|
| Based on your response - re:once a week. An SSIS job - through a SQL Agent schedule - which has a number of advantages - including built in logging , easier integration with SQL Server (and all the components) ,easier for other admins to debug etcOr if you can't do an SSIS job - set up some other way of connecting through - such as a scheduled Powershell scriptJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-06-22 : 07:43:43
|
quote:
Originally posted by jackv
Based on your response - re:once a week. An SSIS job - through a SQL Agent schedule - which has a number of advantages - including built in logging , easier integration with SQL Server (and all the components) ,easier for other admins to debug etcOr if you can't do an SSIS job - set up some other way of connecting through - such as a scheduled Powershell scriptJack Vamvas--------------------http://www.sqlserver-dba.com
Thanks All, great ideas.Considering SQL 2000, would this still work?Best,kml |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-06-22 : 09:00:57
|
quote:
Thanks All, great ideas.Considering SQL 2000, would this still work?Best,kml
SQL Server Integration Services was introduced in SQL 2005 (I think). The predecessor was called DTS (Data Transformation Services) and had much fewer features and capabilities. DTS is capable of doing what you are trying to do - port data from one table on one server to a similar table on another server. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-06-22 : 10:14:33
|
| Good point , I hadn't noticed the 2000 reference. DTS also can be scheduled via SQL Server AgentJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Motumoyo
Starting Member
3 Posts |
Posted - 2012-06-30 : 01:24:50
|
| The source server can control the export and destination of imports.... |
|
|
|
|
Remote database access