locked
Linked Server Connection Set up RRS feed

  • Question

  • Hi,

    I'm trying to set up a linked server connection to update a DB2 database using a IBM I series navigator.

    Followed this to create the Linked Server set up. Set Up steps

    We are able to fire up SQL Update query using c# ODBC and update the records but with our linked server connection its throws up below error.

    - Select statement works perfectly fine.

    SELECT * FROM OPENQUERY(LinkedServerName, 'SELECT ColumnToBeUpdated FROM DSN.Table ')

    - Update Query 

    UPDATE OPENQUERY(LinkedServerName, 'SELECT ColumnToBeUpdated FROM DSN.Table ')
    SET COlumnTObeUpdate = 201634

    Error Messages:

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "MSDASQL" for linked server "LinkedServerName" reported an error. Access denied.
    
    Msg 7390, Level 16, State 2, Line 1
    The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "LinkedServerName" does not support the required transaction interface.

    Monday, October 24, 2016 4:03 PM

Answers

  • Where did you get your UPDATE OPENQUERY syntax from?

    I found this from another message in the forum. I use linked servers to read iSeries data, but have never tried to update the iSeries data.

    -- T-SQL OPENQUERY UPDATE remote table 
    UPDATE oq 
    SET    oq.ListPrice = p.ListPrice 
    FROM   OPENQUERY([LINKEDSERVER],
           'select ProductID, ListPrice from tempdb.dbo.Product') oq 
           INNER JOIN AdventureWorks2008.Production.Product p 
             ON p.ProductID = oq.ProductID 

    • Marked as answer by StSingh Thursday, March 16, 2017 11:07 AM
    Monday, October 24, 2016 7:07 PM

All replies

  • Where did you get your UPDATE OPENQUERY syntax from?

    I found this from another message in the forum. I use linked servers to read iSeries data, but have never tried to update the iSeries data.

    -- T-SQL OPENQUERY UPDATE remote table 
    UPDATE oq 
    SET    oq.ListPrice = p.ListPrice 
    FROM   OPENQUERY([LINKEDSERVER],
           'select ProductID, ListPrice from tempdb.dbo.Product') oq 
           INNER JOIN AdventureWorks2008.Production.Product p 
             ON p.ProductID = oq.ProductID 

    • Marked as answer by StSingh Thursday, March 16, 2017 11:07 AM
    Monday, October 24, 2016 7:07 PM
  • So I tried to update my core data and it worked.

    UPDATE oq 
     set oq.XXCKLO = 0
     FROM OPENQUERY (Store72_LinkedServer, 
     'select * 
    	from Store72.xxstop 
    	where XXckdt = 20020301') oq

    Monday, October 24, 2016 8:51 PM
  • Hi StSingh,

    Based on my research, You could try to run the script below and check if it works.

    EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'NonTransactedUpdates', 1
    
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'remote proc transaction promotion', @optvalue=N'false'

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 25, 2016 9:19 AM
  • Thanks Brenda for the response 

    I followed these steps and update my linked server connection and unchecked all other but one and it works I believe.

    But its very very slow for select queries but quick when it open query... it takes ages to return 1 row.

    http://www-01.ibm.com/support/docview.wss?uid=nas8N1014514

    I tried your update query and got below error message

    OLE DB provider "MSDASQL" for linked server "LinkedServer" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7008 - tableName in Schema not valid for operation.".Msg 7343, Level 16, State 4, Line 2The OLE DB provider "MSDASQL" for linked server "LinkedServer" could not UPDATE table "[MSDASQL]". 


    • Edited by StSingh Tuesday, October 25, 2016 7:21 PM
    Tuesday, October 25, 2016 6:25 PM
  • Thanks for the response Sam Zha. Linked server response is very slow....

    But when I try with Open query it just works like ninja

    Tuesday, October 25, 2016 6:26 PM
  • Any help to resolve this is highly appreciated... please.

    Also, please not I'm able to update using c# odbc code but only Linked Server fails to do the update.

    Do you have any idea what the user is trying to say...  where do I find these options ?

    http://stackoverflow.com/questions/17060142/error-when-insert-through-a-link-server-from-sql-server

    There are a number of ways of resolving this problem:

    • switch off Options > Run SQL > Safe Update Mode. When Safe Update mode is selected, AQT will run Updates and Deletes within a transaction.
    • ensure Options > Technical Parameters > Auto Commit is checked.
    • if you are using DB2/Connect, ensure that AutoCommit is set to Yes. This is configured with the DB2 Configuration Assistant.
    • If you are using DB2 Client Access Express, set the Commit mode to Commit immediate (*NONE ). This is configured on the ODBC Configuration for your Datasource.
    Tuesday, October 25, 2016 7:25 PM
  • Can you show us the exact statement that is fast, the one that is slow and you update statement. I need more information from you to help.
    Tuesday, October 25, 2016 8:58 PM
  • This is my select statement with open query with works like ninja

    SELECT * FROM OPENQUERY(LinkedServerName, 'SELECT Column1, Column2, Column3 FROM Schema.Table ')


    I've created Synonyms for these linked server table so that I can switch between environment easily

    CREATE SYNONYM [dbo].[SYNONYM Name] FOR [linkedservername].[databasename].[schemaname].[tablename] --Four part naming convention

     My Select query -- This select takes ages... and mostly returns with error 

    select column name from SYNONYM

    Error Details:

    Msg 7399, Level 16, State 1, Line 3
    The OLE DB provider "MSDASQL" for linked server "B ..... M" reported an error. The provider reported an unexpected catastrophic failure.
    • Edited by StSingh Wednesday, October 26, 2016 9:06 AM
    Wednesday, October 26, 2016 9:05 AM
  • Check if the below links help:

    https://blogs.msdn.microsoft.com/sqlsakthi/2011/05/08/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server/

    https://connect.microsoft.com/SQLServer/feedback/details/615000/sql-server-2008-r2-linked-server-to-db2-using-microsoft-oledb-provider-for-odbc-fail  

    Wednesday, October 26, 2016 9:45 AM
  • Not trying to be critical, but I have a low attention span and have lost the plot on this issue and no longer know what you need. We started with "unable to UPDATE using a linked server", then there was a post asking about a StackOverflow question about "c# Error when INSERT using a linked server", and now we are talking about why a SELECT that uses a linked server synonym is slow. 

    1) what is your version of SQL - SELECT @@VERSION

    2) what provider are you using for your linked server

    3) UPDATE - did you ever get it to work using a linked server in tsql. If so, was it just a syntax change or did you change some settings? Please tell us exactly your syntax and what you changed - if it is now working. People learn from these threads - including me.

    4) c# INSERT - my recommendation would be not to mix c# using the linked server questions (the stackoverflow INSERST question) into this thread until you get the tsql worked out.

    5) Synonym - what happens when you say SELECT column from  [linkedservername].[databasename].[schemaname].[tablename] - if that does not work I don't believe the synonym will work

    Wednesday, October 26, 2016 1:17 PM
  • In order to do the updates you may need the table on the iSeries to be journaled.
    Wednesday, October 26, 2016 2:04 PM