January 17, 2022 at 8:34 pm
Hello,
I have a Windows 2019 machine. I have SQL Server 2016 and the v18.1 version of SQL Server Management Studio. I have created a linked server & I am trying to execute some queries on a remote Db2 server. I am using MSDASQL provider, i.e. 'Microsoft OLE DB Provider for ODBC Drivers'.
The INSERT & SELECT queries work fine.
However the UPDATE & DELETE queries fail. For example, a DELETE query as shown gives the below error.
DELETE OPENQUERY (TEST2016, 'SELECT * FROM ABC.WORK_ITEM WHERE id = 123');
Msg 7399, Level 16, State 1, Line 12
The OLE DB provider "MSDASQL" for linked server "TEST2016" reported an error. The provider did not give any information about the error.
Msg 7320, Level 16, State 2, Line 12
Cannot execute the query "SELECT * FROM ABC.WORK_ITEM WHERE id = 123" against OLE DB provider "MSDASQL" for linked server "TEST2016".
As I mentioned, if I run the SELECT query separately, as shown below, the query works fine.
select * from OPENQUERY(TEST2016, 'SELECT * FROM ABC.WORK_ITEM where wi_id = 123');
Am I missing any configuration/settings?
Any thoughts/inputs is much appreciated.
January 17, 2022 at 8:55 pm
Is it id or wi_id?
In the select you are using
where wi_id = 123
But in the delete you are using
WHERE id = 123
January 18, 2022 at 3:25 am
It is a typo. In the select statement also, it is 'where id =123' and not 'where wi_id=123'.
However, irrespective of the typo, the issue still remains the same.
January 18, 2022 at 3:02 pm
I would look at the logs on your db2 server. The error is being thrown on that server, so that should tell you what is wrong. Could be something like permissions OR it could be something like a foreign key constraint OR something else completely.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 19, 2022 at 10:08 am
Thank you.
The comment on the permission got me thinking. I analyzed few logs.
Found the issue. I had a new 2019 Windows on which the user was created with admin privileges. However some of the SQL Server related folders inside the Windows folder did not have relevant permissions. As a result, part of the code which was trying to access the folder was failing. After granting all the permissions, the issue got resolved.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply