-
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.
-
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.
-
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.
-
Which version of sql server you use? Where did you run the query?
-
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.
-
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
-
Forum Rules
|
|