Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    22

    Unanswered: Insert/Select/Delete openquery not working sql server to tereadata

    1) after executing the below statement It is saying one record inserted

    insert OPENQUERY(TeraDataSrv1, 'SELECT * from TVS.TVS1')values ('sateesh','sateesh','2003-10-24 00:00:00.000')

    2) below select statement is working but not retrieving any records

    select * from openquery(TeraDataSrv1, 'select * from TVS.TVS1')


    3) after executing the below delete statement it's displaying an error

    delete from openquery(TeraDataSrv1, 'select * from TVS.TVS1 where user_id = ''ASYS012'' or appl_name = ''sateesh'' ')

    4) Error:
    Server: Msg 7345, Level 16, State 1, Line 1
    OLE DB provider 'MSDASQL' could not delete from table '
    select * from TVS.TVS1
    where
    user_id = 'ASYS012' or appl_name = 'sateesh'
    '. There was a recoverable, pr...
    [OLE/DB provider returned message: [NCR][Teradata RDBMS] Column User//ID not found in wrkdvp01.wrkvw005. ]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange:eleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].


    Please give me solution for this problem.

    --Sateesh

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    Try replacing

    'select * from TVS.TVS1 where user_id = ''ASYS012'' or appl_name = ''sateesh''

    with

    'delete ..........

    Cheers

    SG

  3. #3
    Join Date
    Sep 2003
    Posts
    22
    I have replaced with "sateesh" for "ASYS012".....but not working....syntax and column values are correct....

    My user id is having select/insert/delete rights to TVS.TVS1. (I've been able to select and insert - but the DELETE is giving me an error.)

    I'm still getting the error message

    Server: Msg 7345, Level 16, State 1, Line 1
    OLE DB provider 'MSDASQL' could not delete from table '
    ....
    [OLE/DB provider returned message: Insufficient base table information for updating or refreshing.]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange:eleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

    Regards,
    Sateesh




    Originally posted by sqlguy7777
    Howdy

    Try replacing

    'select * from TVS.TVS1 where user_id = ''ASYS012'' or appl_name = ''sateesh''

    with

    'delete ..........

    Cheers

    SG

  4. #4
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy,

    I think this problem you are having may be a bug......

    However, I would link the the remote server to your server then try using a distributed query.

    e.g. if the remote server was called REM1...

    select * from REM1.<db_name>.<owner>.<table_name> aa

    where 'aa' is a derived table name. If you use a derived table, you can then treat the derived table like any other table.............

    e.g.

    select aa.<column_one>
    from REM1.<db_name>.<owner>.<table_name> aa
    where aa.<column_two> = 'some_value'


    This works - I have used it in production.

    Cheers,

    SG

  5. #5
    Join Date
    Dec 2003
    Posts
    1

    Case sensitive on delete

    I was having the same problem with a linked AS400 server the following would work:

    insert into OPENQUERY (CWSREPLICATION,'SELECT TRIDNO from tmfcntr') values (222222)

    &

    select * from OPENQUERY (CWSREPLICATION,'SELECT * from tmfcntr where TRIDNO = 222222')

    However this would NOT:

    delete OPENQUERY (CWSREPLICATION,'SELECT TRIDNO from tmfcntr where TRIDNO = 222222')

    It would give the following error:

    OLE DB provider 'MSDASQL' could not delete from table 'SELECT TRIDNO from tmfcntr where TRIDNO = 222222'. There was a recoverable, provider-specific error, such as an RPC failure.
    [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0204 - "tmfcntr" in FILES type *FILE not found.]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange:eleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

    Turns out the the the delete query is case sensitive.

    so
    delete OPENQUERY (CWSREPLICATION,'SELECT TRIDNO from TMFCNTR where TRIDNO = 222222')

    WOULD WORK!
    this is just FYI.

    For your problem try selecting only the primary key in your openquery statement. "select *" often gave me errors while deleteing of the "Insufficient base table information" type.

  6. #6
    Join Date
    Aug 2003
    Posts
    68

    Linked server

    attached are the selections you must make to be able to link to as/400.
    Then use the linked server in your openquery statement
    Attached Files Attached Files
    jm

  7. #7
    Join Date
    Dec 2004
    Posts
    1
    You don't need to use the OPENQUERY calls to access DB2 via a linked server. Simply use the 4-part naming convention.

    select * from [linked server name].[catalog].[schema].[filename]

    Obviously, the brackets aren't required. Also, it's case sensitive (very annoying).

    If you're having problems with INSERTs, UPDATEs and DELETEs you'll need to create journal entries on the DB2 server for the tables/files you're trying to update. Refer to this article:

    Insert, Delete, Update Article Fix for DB2 Linked Servers

  8. #8
    Join Date
    Apr 2011
    Posts
    1

    Regarding UPDATE and DELETE

    Quote Originally Posted by RolandGS View Post
    I was having the same problem with a linked AS400 server the following would work:

    insert into OPENQUERY (CWSREPLICATION,'SELECT TRIDNO from tmfcntr') values (222222)

    &

    select * from OPENQUERY (CWSREPLICATION,'SELECT * from tmfcntr where TRIDNO = 222222')

    However this would NOT:

    delete OPENQUERY (CWSREPLICATION,'SELECT TRIDNO from tmfcntr where TRIDNO = 222222')

    It would give the following error:

    OLE DB provider 'MSDASQL' could not delete from table 'SELECT TRIDNO from tmfcntr where TRIDNO = 222222'. There was a recoverable, provider-specific error, such as an RPC failure.
    [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0204 - "tmfcntr" in FILES type *FILE not found.]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange:eleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

    Turns out the the the delete query is case sensitive.

    so
    delete OPENQUERY (CWSREPLICATION,'SELECT TRIDNO from TMFCNTR where TRIDNO = 222222')

    WOULD WORK!
    this is just FYI.

    For your problem try selecting only the primary key in your openquery statement. "select *" often gave me errors while deleteing of the "Insufficient base table information" type.
    Hi,
    Were you able to figure out why the UPDATE and DELETE commands in OPENQUERY are case sensitive and work only in uppercase??

Posting Permissions

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