Post reply

OPENQUERY DELETE

  • May 18, 2016 at 2:09 am

    #327149

    Hi All,

    I used this query.

    DELETE OPENQUERY(DB2400_WRITEPROD, 'Select * from PALTEMP.CNTUPDR')

    then it throw an error.

    OLE DB provider "MSDASQL" for linked server "DB2400_WRITEPROD" returned message "Key column information is insufficient or incorrect. Too many rows were affected by update.".

    Msg 7345, Level 16, State 1, Line 1

    The OLE DB provider "MSDASQL" for linked server "DB2400_WRITEPROD" could not delete from table "Select * from PALTEMP.CNTUPDR". Updating did not meet the schema requirements.

    What is my error in that query? Is there any other way to delete all files in PALTEMP.CNTUPDR table using stored procedure one by one? My problem is that openquery didnt want me to delete many files at once.

    Regards,

  • May 18, 2016 at 2:19 am

    #1879757

    DELETE FROM DB2400_WRITEPROD.MyDatabase.PALTEMP.CNTUPDR

    John

  • May 18, 2016 at 2:24 am

    #1879758

    Hi John,

    Thank you for the fast response.

    I've tried your solution and it gives me an error of

    An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DB2400_WRITEPROD".

    Regards,

  • May 18, 2016 at 2:27 am

    #1879759

    Yes. You have to change MyDatabase for the actual name of the database on the remote server.

    John

  • May 18, 2016 at 2:38 am

    #1879762

    Hi John,

    I am using as/400 as linked server.

    Library name: PALTEMP

    File name: CNTUPDR

    DB2400_WRITEPROD

    My colleague who handles as/400 said that the database itself is the PALTEMP.

    or is there any other way to create a stored procedure that will delete data one by one?

    Regards,

  • May 18, 2016 at 2:48 am

    #1879763

    I don't know anything about AS/400, but when using linked servers you have to use the four-part naming convention, specifying (I think) the server, the catalog, the library and the file. With SQL Server, that maps to server, database, schema and table. You'll need to consult the documentation (or speak to the administrator of the server) to find out how it works for AS/400. That applies whether you delete the rows one at a time or all at once.

    John

  • May 18, 2016 at 3:00 am

    #1879766

    It is actually weird, i can use

    DELETE openquery(DB2400_WRITEPROD, 'Select * from PALTEMP.CNTUPDR')

    if the data is below 30, if it is higher than 30. the error occur.

  • May 18, 2016 at 3:04 am

    #1879767

    DELETE FROM A

    FROM OPENQUERY(DB2400_WRITEPROD, 'Select * from PALTEMP.CNTUPDR') A

    JOIN dbo.Client_Contact_Details_Update B ON A.CONTPNO = B.CONTPNO

    DELETE OPENQUERY (DB2400_WRITEPROD, 'Select * from PALTEMP.CNTUPDR')

    WHERE CONTPNO IN(SELECT CONTPNO FROM dbo.Client_Contact_Details_Update)

    i've already used these sql scripts. But it didnt allow me to delete in bulk. My last resort was to delete is one by one by using stored proc?

  • May 18, 2016 at 3:06 am

    #1879768

    I'm sorry - I'm out of my depth here. I don't know why you're seeing this behaviour.

    John

  • May 18, 2016 at 3:27 am

    #1879772

    Hi John,

    Maybe it is because it has the same value.

    Row1 has the same value with Row2

    Maybe it needs to have a column with unique value

  • May 18, 2016 at 3:28 am

    #1879773

    Here you can go how to use DELETE OPEN QUERY...at the end .

    https://msdn.microsoft.com/en-IN/library/ms188427.aspx

  • May 18, 2016 at 3:31 am

    #1879774

    My theory was right.

    Thank you John

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply