dcsimg
Results 1 to 6 of 6

Thread: how to delete rows using linked server

  1. #1
    Join Date
    Sep 2006
    Posts
    21

    how to delete rows using linked server

    Hi,
    I have two mssql databases. I am able to create a linked server.
    I want to delete some of the rows using this linked server.

    Here HAFEEZ is one mssql database and DPVSQSL is another.So, i want to delete rows from DPVSQL from HAFEEZ using HAFEEZDPVSQL linkedserver.

    DELETE OPENQUERY (HAFEEZTODPVSQL, 'SELECT EVENT_SYS_ID FROM DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = ''evesysid0015''');

    when i run the query i got the following error.

    "Msg 7356, Level 16, State 1, Line 1
    The OLE DB provider "SQLNCLI" for linked server "HAFEEZTODPVSQL" supplied inconsistent metadata for a column. The column "EVENT_SYS_ID" (compile-time ordinal 1) of object "SELECT EVENT_SYS_ID FROM DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = 'evesysid0015'" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time."

    Please note that i am able to query something using this linked server,please see the below query.

    SELECT * FROM OPENQUERY (HAFEEZTODPVSQL, 'SELECT EVENT_SYS_ID FROM DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = ''evesysid0015''');

    Can anyone please help me regarding this.

    Thanks and Regards,
    Hafeez.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Tried those:

    SELECT * FROM OPENQUERY (HAFEEZTODPVSQL, 'DELETE db_name.owner.DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = ''evesysid0015''');


    delete HAFEEZTODPVSQL.db_name.owner.DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = ''evesysid0015''
    Last edited by rmiao; 06-19-2007 at 09:19 AM.

  3. #3
    Join Date
    Sep 2006
    Posts
    21
    Hi,
    Thanks for the info.
    I tried your first statement whith some littile modifications and i got the follwoing error.

    SELECT * FROM OPENQUERY (HAFEEZTODPVSQL, 'DELETE dpvsql.dbo.DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = ''evesysid0015''');

    Msg 7357, Level 16, State 2, Line 1
    Cannot process the object "DELETE dpvsql.dbo.DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = 'evesysid0015'". The OLE DB provider "SQLNCLI" for linked server "HAFEEZTODPVSQL" indicates that either the object has no columns or the current user does not have permissions on that object.

    When i tried second statement with little modifications got the following error.

    delete HAFEEZTODPVSQL.db_name.owner.DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = 'evesysid0015'

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "SQLNCLI" for linked server "HAFEEZTODPVSQL" reported an error. The provider did not give any information about the error.
    Msg 7312, Level 16, State 1, Line 1
    Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "HAFEEZTODPVSQL". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.


    Please give ur input.

    Thanks and Regards,
    Hafeez.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Which version of sql server you use? Where did you run the query?

  5. #5
    Join Date
    Sep 2006
    Posts
    21
    Hi,
    My MS SQL SERVER version is :

    Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

    And i am running the queries in Microsoft SQL Server Management Studio.

    Please Please...try to help me.

    Thanks and Regards,
    Hafeez.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Tested with four-part name on my sql2k5 sp2 servers, worked fine. Did you replace db_name and owner with real ones?

Posting Permissions

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