Getting error using EXECUTE('UPDATE..') on a MySQL server from SQL Server

Question

I intend to do:

EXECUTE('UPDATE tableA SET campaignkey = ''20170101'' where storekey = 16
 and campaignkey LIKE ''%,%''') at MYLINKEDSERVER

but I get the error that:

You have an error in your SQL syntax; [...] for the right syntax to use near 'where storekey = 16 and campaignkey LIKE '%,%''

Does anyone have any idea what is wrong? To me it seems like I might have one ' too many on my LIKE statement, but I have Always used '' to indicate a non-numeric value. I don't want to fiddle with this to prevent updating far too many values on this server.

campaignkey is non-numeric (I Believe varchar) and storekey is integer.

Edit

I must not use OPENQUERY() because it is not set up correctly, and this is a urgent update.

Edit 2

Seems like it is because of apostrophes 's in the EXECUTE statement.

When I conduct:

select * from openquery(linkedserver,'Select * from tableA where storekey = 16 
and campaignkey = ''20170826,151''')

it works, but when using:

EXECUTE('Select * from tableA where storekey = 16 
and campaignkey = ''20170826,151''') at linkedserver

I get the error that I need to check the manual by the where clause. From googling it appears however that the correct syntax in fact is:

EXECUTE('UPDATE TableX SET StringVar = ''stringValue'' WHERE intVar = 3 
AND stringVar = ''xxxxx''') AT LinkedServer

I don't know why this won't work for me.. I have tried many combinations of '', '" etc.

sepupic · Accepted Answer · 2017-09-11 08:38:38Z

1

What about this one?

update openquery(linkedserver,'Select * from tableA where storekey = 16 
and campaignkey = ''20170826,151''')
set campaignkey = '20170101'

answered Sep 11, 2017 at 8:38

sepupic

8,7371 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cenderze Over a year ago

I am unfortunately unable to use OPENQUERY. The server is not set up for that (some error about bin logs or something like that) and I am not authorized to conduct those kind of changes. Thanks for input however, it is a useful Query I use when its possible.

Collectives™ on Stack Overflow

Getting error using EXECUTE('UPDATE..') on a MySQL server from SQL Server

Edit

Edit 2

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit

Edit 2

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related