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

louise001Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
please include the primary key column(s) in the openquery select, that should help
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
louise001Author Commented:
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
0
 
louise001Author Commented:
Cool, that works. Thank you.
0
All Courses

From novice to tech pro — start learning today.