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 |
|
kirknew2SQL
Posting Yak Master
194 Posts |
 Posted - 2008-01-29 : 12:25:05
|
| I have SQL Server 2005 running on my local computer (OS Windows 2000). I also have SLQ Server 2005 running on a server (64 bit OS Windows 2003)I need to copy a table from a DB on my local server to a DB on the server. On my local computer I can see and connect to the DB on the server. How can i copy a table from my local servet to the remote server? I have tried this but i get an error saying i have more than the max number of prefixes. What is the correct syntax?select * into RemoteServer.TargetDB.dbo.MyTablefrom MyTablw |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-29 : 12:33:54
|
| Try exporting the data using export import wizard. Go to the database on your server select tasks->export data and select your destination server. |
 |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2008-01-29 : 12:35:46
|
| You'd need to set the remote server up as a linked server on your local server in order to use the four-part naming. Alternatively, you could a. use SSIS (just set up a couple of connection managers pointing to each of the servers and reference them in source and destination adapters in a data flow task) or b. have a look at OPENROWSET / OPENDATASOURCE if it's a one-time thing. |
|
|
|
kirknew2SQL
Posting Yak Master
194 Posts |
Posted - 2008-01-29 : 12:43:26
|
| Thanks fot the options. I am using the export wizard to solve the immediate problem. For a perminant solution looking at connection manager or creating a linked server is one faster or use fewer resources than the other. Is ther apreferedsolution? |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-01-29 : 13:48:21
|
| can't say if that would be perfect in your case,would depend if you are accessing data very often from the other db and whether or not you want users to give access to the other db through linked server |
|
|
|
kirknew2SQL
Posting Yak Master
194 Posts |
Posted - 2008-01-29 : 14:09:49
|
| I see what you are saying. I'll have to look into it more and decide what would be best. thankes |
|
|
|
|
|
|
How to copy a table from one server to another