Linked Server Query Problem

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • ashwingawande
    New Member
    • Sep 2007
    • 7
    #1

    Linked Server Query Problem

    Hi All,
    I have created a linked server on SQL Server 2005 to access data from DB2.
    I am able to execute it with the command "SELECT * FROM OPENQUERY(DB2SR V, 'SELECT * FROM dbname.dbowner. tblname') however, not able to execute the same thing with "SELECT * FROM DB2SRV.dbname.d bowner.tblname" . Seems very strange.
    Any help in this regard will be appreciated.
    Thanks in advance for any reply.

    Ashwin
    Tags: None
    • iburyak
      Recognized Expert Top Contributor
      • Nov 2006
      • 1016
      #2
      In Enterprise Manager when you go to Security – Linked Servers can you see DB2SRV name?
      If yes when you expend this name can you see Tables?
      If yes, when you click on it, can you see all tables?
      If answer to any of these questions is ‘NO’ it means your linked is not configured properly.



      Good Luck.

        Comment

        • ashwingawande
          New Member
          • Sep 2007
          • 7
          #3
          Hi,
          Thanks for your reply. Further, I have successfuly configured the linked server
          but now another issue.

          I have set up Linked Server to DB2 using MS OLEDB FOR ODBC DRIVER (MSDASQL) i am able to execute select, insert and delete but there are few tables in DB2 where data type of some of the columns is CLOB.

          I am not able to select, update or insert in to the DB2 tables. I guess it is the problem of data type mismatch between SQL Server and DB2. Can any body help me how to select DB2's CLOB data type in SQL Server through OPENQUERY and how to insert a text data value into DB2 CLOB data type column.

          Currently when i Insert the following error is encountered :

          OLE DB provider "MSDASQL" for linked server "TRYLINK" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

          Msg 7344, Level 16, State 1, Line 1

          The OLE DB provider "MSDASQL" for linked server "TRYLINK" could not INSERT INTO table "[MSDASQL]" because of column "MEMO_FALSEPOSI TIVE". Could not convert the data value due to reasons other than sign mismatch or overflow.

          When i try to select a CLOB column in DB2 the following error is encountered:

          OLE DB provider "MSDASQL" for linked server "TRYLINK" returned message "Requested conversion is not supported.".

          Msg 7341, Level 16, State 2, Line 1

          Cannot get the current row value of column "[MSDASQL].MEMO_VULNERABI LITYSOLUTION" from OLE DB provider "MSDASQL" for linked server "TRYLINK".

          Thanks in advance for any answer.

          Ashwin



          Originally posted by iburyak
          In Enterprise Manager when you go to Security – Linked Servers can you see DB2SRV name?
          If yes when you expend this name can you see Tables?
          If yes, when you click on it, can you see all tables?
          If answer to any of these questions is ‘NO’ it means your linked is not configured properly.



          Good Luck.

            Comment

            • iburyak
              Recognized Expert Top Contributor
              • Nov 2006
              • 1016
              #4
              Sorry I am not a DB2 programmer and can't help you with this but can you create a view on DB2 side where you convert to text this column before returning to SQL so conversion on SQL side would not require.

              To update you probably can create a stored procedure on DB2 side which will do conversion for you.


              Not sure if it is even possible but it is just a thought.

              You can go to DB2 forum for help.

              Good Luck.

                Comment

                • ashwingawande
                  New Member
                  • Sep 2007
                  • 7
                  #5
                  Hi,
                  Thanks for the reply. Well I had the same thought but the problem is there is no text data type in DB2 another way is varchar is there in DB2 but CLOB type data will not be accomodated in varchar so no sense in that. Another way i found is to have DB2 column get converted in XML data type which is also there in SQL Server 2005 so tried that but another problem is when i run select query using OPENQUERY to access xml data type from DB2 the error is
                  Msg 102, Level 15, State 1, Line 2
                  Incorrect syntax near '('.
                  My query goes like this........... .....

                  SELECT str_tmp.value(' (/tbltmp/@Description) [1]', 'varchar(max)')
                  FROM OPENQUERY(VTDEV 149, 'SELECT str_tmp.value(' (/tbltmp/@Description) [1]', 'varchar(max)') ' from tvng.tvnguser.t bltmp)

                  I don't understand where i am wrong.

                  Please suggest something if u can.

                  bye..
                  Ashwin


                  Originally posted by iburyak
                  Sorry I am not a DB2 programmer and can't help you with this but can you create a view on DB2 side where you convert to text this column before returning to SQL so conversion on SQL side would not require.

                  To update you probably can create a stored procedure on DB2 side which will do conversion for you.


                  Not sure if it is even possible but it is just a thought.

                  You can go to DB2 forum for help.

                  Good Luck.

                    Comment

                    Previous template Next
                    Working...