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 |
|
msbolton
Starting Member
12 Posts |
 Posted - 2003-03-21 : 06:54:54
|
| I am trying to update an existing table using a linked server. I cannot get the four part reference to work so I am using the open query method.What I am trying to do is pass a variable to the openquery so that it does not need to return a massive dataset.One way I can think of doing this is to return the records from the exiting table and then loop trough each row passing a variable to the open query method. The openquery should then return only one record and this can be used to update the current row. Does anyone know a way of doing this?If poissble I would much prefer to do this as a single update statement but I am not aware of a way of doing it.Any help would be much appreciated. |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-03-21 : 07:21:51
|
| There should not be a "massive dataset" returned from an update statement.You really haven't given enought detailed information for someone to give you any specific advice.It's not clear why you "cannot get the four part reference to work".I would start with that.If you need help, post the ddl for the tables involved and explain what you are trying to update.Jay White{0} |
 |
|
|
msbolton
Starting Member
12 Posts |
Posted - 2003-03-21 : 08:40:58
|
| Sorry for not providing enough info. I will try to give you and example of what I am doing.I have a data extract located in a SQL server table. The extract contains a unique ID for a particular person. I need to extract some additional fields from a remote system, hence the linked server.What I would like to do is pass the unique id to the openquery statement. I tried using similar to the following:Update personSet dob = u.dob, address1 = u.address1, address2 = u.address2, address3 = u.address3from person p, openquery('linkserv', 'select dob, address1, address2, address3 from persondetail) as uwhere p.idnumber = u.idnumberThe message that I got back was that there was not enough space to complete the openquery.What I am looking to try and do is pass a variable to the openquery so that it would readopenquery('linkserv', 'select dob, address1, address2, address3 from persondetail where personid=@pid')In this way I am hoping that the openquery should only return one record in each instance when it matches the unique id.I hope that gives a better idea of what I am trying to achieve. If there is another way or doing then I would appreciate any hints/tips. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-03-21 : 09:21:31
|
What error message do you get when you try...Update person Set dob = u.dob, address1 = u.address1, address2 = u.address2, address3 = u.address3 from person p inner join linkserv.database.owner.persondetail u on p.idnumber = u.idnumber The problem with "passing a variable" is that you are only going to be able to process one person at a time rather than the whole set at once.Jay White{0} |
|
|
|
|
|
|
|
|
Updating tables using linked server