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 |
|
besvn
Starting Member
6 Posts |
 Posted - 2008-05-06 : 09:23:28
|
| (I don't know if this is the correct place for this topic, if not, then please move it)I have to make a historisation script, and I need to transfer data from a database on one server to a database on another server.I.e. from table T1 in database D1 on server S1 to table T2 in database D2 on server S2I saw there is something like sp_addlinkedserver, but to be honest, I don't get the syntax correct.Both servers are SQL2005 servers (but I will have to go from SQL2000 to SQL2005 in the future) and the migration script will be runnning at the source database.Can you guys help me out on the syntax, or else provide other suggestions on doing this kind of transfer? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-06 : 10:56:26
|
| http://msdn.microsoft.com/en-us/library/ms190923.aspx |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-06 : 11:27:57
|
| What are you transfering? table or whole databaseUse export/import wizard for tables |
|
|
|
besvn
Starting Member
6 Posts |
Posted - 2008-05-07 : 03:08:24
|
| The purpose is to transfer the data in some perticular tables, and to remove it afterwards out of the source table.I have got a script that installs a SQL job (and its underlying steps), but I need to get that working cross server. Letting it work cross DB on the same server is no problem, but getting the syntax right for cross server is not that easy.I guess the original post was not that clear about me having a script available already, sorry for that.So I guess the options for BCP and DTS-transfer will be too much work (would mean rewriting everything) and therefore I am searching for an quick win here.I can easily do a 'search and replace' on the database name, and replace that by some extended 'server + database' notation. But that would mean I have to get that linked server thing working. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-07 : 16:08:59
|
| Create linked server then reference remote tale with four-part name like: server.db.schema.table. |
|
|
|
besvn
Starting Member
6 Posts |
Posted - 2008-05-08 : 05:56:27
|
| Problem was apparently that if you link to a SQL server, the name you chose is not 'just' a name. I thought that you could give a name to the linked connection (that you could use in code), and determine the server to connect to on another place. This was not correct, and you should give the full server location up as name.When I did that, and added the security info (userid and password), I could do anything with it.I added the linked server via the menu, (and not by script).The second step was an easy adaptation of the job script I had :changing [DBname].dbo.tablename to [linked servername].[DBname].dbo.tablenamethx for the input. |
|
|
|
|
|
|
|
|
transferring data from one server to another