asked on

Update statement through openquery with a variable

Hi,
The attached stored procedure runs without an error but when I check I find that my field hasn't updated. I'm using SQL Server 2005 and the db on the linked server is MySql. I have non transacted updates ticked in the MSDASQL linked server provider's properties and the user I'm authenticating as when connecting to the linked server has update permissions.

Any help appreciated.

Louise

declare @candidate_id int
set @candidate_id = 3

declare @downloaded bit
set @downloaded = 1

declare @select varchar(150)
declare @update varchar(150)
declare @sql varchar(300)

set @select = '
update OPENQUERY(webserver,
''select downloaded from job_application.candidate where (candidate_id = @candidate_id)'')'

set @update = ' set downloaded = 1'

set @sql = @select + @update

exec (@sql)

Open in new window

Guy Hengel [angelIII / a3]

this should work better:

declare @candidate_id int
set @candidate_id = 3

declare @downloaded bit
set @downloaded = 1

declare @select varchar(150)
declare @update varchar(150)
declare @sql varchar(300)

set @select = '
update OPENQUERY(webserver,
''select downloaded from job_application.candidate where (candidate_id = ' + cast(@candidate_id as varchar(10)) + ')'')'

set @update = ' set downloaded = 1'

set @sql = @select + @update

exec (@sql)

Open in new window

louise001

ASKER

Hi angelIII,

Thanks for replying. With your suggested syntax I get this error: OLE DB provider "MSDASQL" for linked server "webserver_ukfast" returned message "Key column information is insufficient or incorrect. Too many rows were affected by update.".
The OLE DB provider "MSDASQL" for linked server "webserver_ukfast" could not UPDATE table "[MSDASQL]"

Do you have any further advice?

Thanks,

Louise

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

louise001

ASKER

Cool, that works. Thank you.