April 24, 2015 at 9:43 am
Hello
I have a Server running MSSQL Server 2012, I have created a Linked Server to a MYSQL on a Remote Server. When I use OpenQuery to Query a Table on the Remote Server the Results as expected.
When I try to Update a Table in MySQL from MSSQL I get an Error Message:
This is my Query:
SELECT * FROM OPENQUERY
(
TMS6,
'UPDATE Tms6Data.Tank SET name = "2015-B-001550" Where tank_id = "T632"'
)
This is the ERROR Message:
Msg 7357, Level 16, State 2, Line 8
Cannot process the object "UPDATE Tms6Data.Tank SET name = "2015-B-001550" Where tank_id = "T632"". The OLE DB provider "MSDASQL" for linked server "TMS6" indicates that either the object has no columns or the current user does not have permissions on that object.
Any Help on this would be appreciated.
I am NOT an SQL programmer, but I know enough to be dangerous.
George
April 24, 2015 at 10:30 am
The first thing to check is whether the linked server user haas the correct permissions on the remote server.
you can use the following to find which user is being used to login to the remote server.
SELECT [srv].[name],
[srv].[data_source],
[ll].[remote_name]
FROM sys.servers AS [srv]
INNER JOIN sys.linked_logins AS [ll]
ON [srv].[server_id] = [ll].[server_id]
WHERE [srv].[name] = 'TMS6';
then once use have your remote user name, you can run Show Grants for that user on the MYSQL server.
SHOW GRANTS FOR 'user'@'server';
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply