Linked Server UPDATE & DELETE fails . But INSERT & SELECTs work fine

  • 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.


    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.

  • 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
  • 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.

  • 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.

  • 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