| Author |
Topic |
|
lemunk
Starting Member
9 Posts |
 Posted - 2011-11-15 : 06:02:14
|
| So ive created a linked server. The reason for this is that i need to join a table from a local server to a table from another server.the table on my local server is actually a view (this works no problem).But the following results in an errorSELECT A.Acct, A.Name, A.Document, A.Part, B.Pareto, A.PG, A.Qty, A.Unit, A.[datetime], A.Year_1, A.Month_1 FROM [vSpends] A INNER JOIN OPENQUERY(sacb3, 'SELECT Pareto FROM Shaftdata.dbo.NEWPareto') BON A.part = B.part;following is the error.Msg 207, Level 16, State 3, Line 1Invalid column name 'part'.Im hoping ive done somthing stupid, missing syntax or invalid etc.Any thoughts? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-11-15 : 06:05:22
|
select Pareto from ...has no 'part' in the select list so you can't address it.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-15 : 06:07:45
|
| >> i need to join a table from a local server to a table from another serverI wouldn't do it.==========================================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. |
|
|
|
lemunk
Starting Member
9 Posts |
Posted - 2011-11-15 : 06:08:38
|
ah thank god, knew it would be something stupid. Ive been losing sleep over this hehe,. thanks alot mate i owe u a brew! |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-15 : 06:37:27
|
quote:
Originally posted by nigelrivett
>> i need to join a table from a local server to a table from another serverI wouldn't do it.==========================================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.
Why ?PBUH |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-15 : 06:48:10
|
| It has to apply the join on one of the servers and depending on the setup will probably transfer all the data to one of them. I would do that transfer so that I am in control of what is done and where it happens.==========================================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. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-15 : 07:14:36
|
| I dont think so the that the setup will "transfer" all the data to one of them when you query it.I am looking at a scenario where you have multiple applications and an application1 on a different server has to query only a single table data of application2 on another server and that table has lot of refrential integrity to other tables.So how would you transfer the data from server2 to server1 ?PBUH |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-15 : 07:35:55
|
| >> I dont think so the that the setup will "transfer" all the data to one of them when you query it.It has to carry out the join on one of the servers - that could mean a nested loop sending the join column to one server and returning the data or sending the join column in a set to get the data.I usually send the data manually and retrieve the dataset to the local server then complete the join locally - this could be the columns from the whole of the remote table or a subset of rows (maybe opulate a table on the remote server to do that. Could be better to keep the copy of the table up to date by a different process.In that way you can be sure where any issues are.==========================================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. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-11-15 : 20:14:07
|
| Agree with Nigel 100%. These types of things are perfect candidates for transactional replication by the way.The calling server can and does utilize indexes on the remote server, but running it locally will always perform better. And in some cases a LOT better. |
|
|
|
|
Ah yes, the joy of linked servers!