0

I'm using T-SQL and I want to delete a row from a linked server using MYSQL.

For this I've tried:

delete from openquery(MYSQLServer,
'select * from dTable where date = cast(cast(date_add(now(),interval -6 day)as date) as int) 
and ProductKey = 38410 and StoreKey = 3201')

But this gives the error 

There was a recoverable, provider-specific error, such as an RPC failure.

I then Googled this issue and realized that I need to adjust the RPC settings in management studios by right clicking the linked server and set RPC = True and RPC Out = True. Refreshing the linked servers and executing the Query again yield the same error.

From searching the web I've also realized that delete is case sensitive.

However, using 

exec sp_tables_ex MYSQLServer

showed me that I use the exact same case on the table. I've also controlled that each column has the same case. I've tried to rewrite the Query to have only upper case values just in case, but then I got the error that the table with uppercase does not exist.

I've also tried to use

SELECT * FROM MYSQLServer.Catalog.dbo.dTable

but that gave the error Invalid object name.

I've also tried:

select * from openquery(MYSQLServer,
'delete from dTable where date = cast(cast(date_add(now(),interval -6 day)as date) as int) 
and ProductKey = 38410 and StoreKey = 3201')

but this gave the error that either object has no columns, or the current user does not have permissions on that object.

Does anyone have any clue?

EDIT

I've found that Another linked server, from which I have successfully deleted a row earlier, had a user name with a remote userID and password defined under Security settings at the specific Linked Server in management studios.

I tried to mimic this by using:

EXEC sp_addlinkedsrvlogin 'MYSQL_UNIC', 'false', 'uid', 'rmtid', 'rmtpw'

but this gives me the error 

Access denied for user rmtid (using password: YES)

when I try to use the delete Query.

I'll try to find how to get permission to Query using a remote user.

Cenderze
  • 1,202
  • 5
  • 33
  • 56
  • since your simple select stattement doesn't work, I would guess the account configured for you linked server does not have any rights on the database. Can you find te linked server,database and table in the object explorer of sqlserver management studio? – Peter Jun 09 '17 at 09:07
  • @Peter Sorry I wasn't clear. I can do the `Select * from openquery(...)` statement, but I cannot do the `Delete openquery(....)` statement. I can find the linked server in the object Explorer, but I cannot find the database and table. – Cenderze Jun 09 '17 at 09:16

1 Answers1

0

Here’s the solution:

  1. Make sure the RPC and RPC Out are set to TRUE on the LinkedServer’s properties.

  2. Then use the following syntax (This is specifically for Teradata):

declare @SQL varchar(1000) SET @SQL = ‘DELETE FROM DB.tableName ALL’ EXEC (@SQL) AT TERADATA_LINKEDSERVER

user11346632
  • 1