| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2004-02-20 : 11:43:42
|
| In Query Analyzer, I want to insert rows from a DB on my Laptop into a table on a remote server.INSERT INTO dbname.dbo.tablea (column1, column2) select column1, column2 from (local)dbname.dbo.tableaIs this possible in QA, or should I use EM?Sam |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-20 : 12:04:03
|
| Did you create a linked server...How did you create local btw...haven't done that yet...is msde or sql server developers addition?Brett8-) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-20 : 12:34:12
|
| Yes, start with creating a linked server. I would create the linked server on your laptop. It will point to the remote server. Then on your laptop inside Query Analyzer:INSERT INTO DBName.dbo.TableA ...SELECT ...FROM ServerName.DBName.dbo.TableATara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-02-20 : 12:43:55
|
| Hmmm.. I've never done linked servers before. I smell trouble coming.Brett .. (local) is just the default server name on the current machine. I think every server installation has (local), unless it's explicitly removed? Warrior Princess ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-20 : 12:47:14
|
| Yes that is true. But I never use (local). I always use the server/PC name. (local) just means connect to the SQL Server that is on the machine that you are using.Creating a linked server is easy. Go to EM, connect to your laptop, go to security, right click on linked servers, go to new linked server. Select SQL Server and type in the name of the remote server (you can use an alias but let's keep it simple for now). Go to second tab. On the second tab, I always select the third radio button. Click OK.Now in Query Analyzer, connect to your laptop and do this:SELECT TOP 10 * FROM LinkedServerName.DBName.dbo.TableADo you see your remote table?If so, you can now do the INSERT.Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-02-20 : 13:01:04
|
| Thanksalot Tara!Sam |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-20 : 14:31:11
|
| Tara,I think this is the first time I've seen you recommend EM.Sam, look up sp_addlinkedserver and sp_addlinkersrvlogin...BTW, did it work for you?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-20 : 14:44:38
|
| I only did it cuz Sam is a little reluctant about linked servers. So let's get his feet wet, then show him the right way!Tara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-02-20 : 15:01:05
|
quote:
SELECT TOP 10 * FROM LinkedServerName.DBName.dbo.TableA
The setup wasn't too tough, I can see the tables on the remote server using EM. I get this error on the queryServer: Msg 117, Level 15, State 1, Line 2The object name 'sql.linkedservername.com.nimc.dbo.' contains more than the maximum number of prefixes. The maximum is 3.Forgive me for not posting my server name, but it does have two dots in it BTW.Sam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-20 : 15:03:30
|
| Sam, reference your server with brackets around it:SELECT TOP 10 * FROM [sql.linkedservername.com].nimc.dbo.TableATara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-20 : 15:04:14
|
| Then you need to use qualifiers around your installation. [fun.com].dbo.blah.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-02-20 : 16:16:47
|
| <Like the turtle in Nemo>Dude ! This so totally rocks !</>Sam |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-02-20 : 16:18:20
|
<Subtle, on-the-sly, whisper-with-danger>So Tara... just how DO you setup linked servers in QA?</> quote:
sp_addlinkedserver and sp_addlinkersrvlogin...
I guess Brett gave the answer... |
|
|
|
|
Big Server, Little Server