Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Posts
    21

    Unanswered: Linked Server Issue to update Progress Database Merant 3.60

    Hi:
    I am currently using sql server 2000. I also using Merant 3.60 32 Bit Progress Sql 92v9.1c progress driver. I am trying to retrieve information from sql server 2000. Based on the result set. I will need to update the progress database. I created a linked server to access the progress database. I had no issue when I used a select statement.

    My linked server is called "test_server"
    User Name = pub
    Table Name =xx-xx
    Column Name = wc

    Here is my select statement.
    select * from test_server..pub."xx-xx" where wc='a'
    I was able to return with data.

    But when I tried to use an update statement. It does not work. I am not even sure if it is possible to use linked-server connection to update the progress database or if my syntax is incorrect.

    Here is my update statement:
    UPDATE OPENQUERY (test_server, 'SELECT wc from pub.[xx-xx] where wc = ''b''') SET wc= 'a'

    This is the error that I received.
    An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
    [OLE/DB provider returned message: [MERANT][ODBC PROGRESS driver][PROGRESS]Syntax error (7587)]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].

    Any idea?
    Thanks for your assistance.
    kkmickey

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Posting "it did not work" if probably the bigest waste of time on the forum . . .

    WHAT did not work? Syntax error, ABEND, ETC?

  3. #3
    Join Date
    Nov 2009
    Posts
    21
    I know it is a waste of time if I am not specific. But I did specify it. I am sorry if my message confuses you.

    In my thread : I said the update statement does not work.

    Here is my update statement:
    UPDATE OPENQUERY (test_server, 'SELECT wc from pub.[xx-xx] where wc = ''b''') SET wc= 'a'

    This is the error that I received.
    An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
    [OLE/DB provider returned message: [MERANT][ODBC PROGRESS driver][PROGRESS]Syntax error (7587)]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].

    Thank you.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can't use the square brackets in PROGRESS. The ODBC parser is complaining because it can not parse them.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2009
    Posts
    21
    Hi Pat:

    If I use

    UPDATE OPENQUERY (test_server, 'SELECT wc from pub.xx-xx where wc = ''b''')
    SET wc= 'a'

    This is the error that I got

    An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
    [OLE/DB provider returned message: [MERANT][ODBC PROGRESS driver][PROGRESS]Table/View/Synonym not found (7519)]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].

    Thanks
    kkmickey

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Then you appear to have access to the PROGRESS database using OpenQuery. Since xx-xx is not a valid PROGRESS name, you must be using some other query than what you are posting. According to the error message you posted, something in the query string that you're using does not exist in the PROGRESS database.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Nov 2009
    Posts
    21
    Pat

    This is the select statement that I use.

    SELECT wc from test_server..pub."ux-wc" where wc = 'a'

    This will work. The table does exist. It will return data back.

    But when I use the update Openquery. It gives the error message.

    UPDATE OPENQUERY (test_server, 'SELECT wc from pub.ux-wc where wc = ''a''')
    SET wc= 'b'

    An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
    [OLE/DB provider returned message: [MERANT][ODBC PROGRESS driver][PROGRESS]Syntax error (7587)]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].

    kkmickey

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm assuming that test_server is just what its name imples, a test server and emphatically NOT a production server. If this is not correct, do not try the code below!

    Just to get connectivity and other issues out of the way so that we can focus on the meat of the problem, please try:
    Code:
    UPDATE test_server..pub."ux-wc"
       SET wc = 'b'
       WHERE wc = 'a'
    Does that do what you want?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Nov 2009
    Posts
    21
    Pat:

    I have attached two screen prints to this post.
    One is for the select statement. And the other one is for the update statement.

    Thanks,
    kkmickey.
    Attached Thumbnails Attached Thumbnails Capture.PNG   Capture_1.PNG  

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That one beats me. I suspect that the problem lies with the Merant ODBC driver, but I can't absolutely prove it.

    At least you now have a great case to submit to tech support. Sorry I can't be of more help.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Nov 2009
    Posts
    21
    I appreciate your help.

    Thanks again.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •