how to delte rows using linked server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to delte rows using linked server

Author

Topic

hafeez
Starting Member

14 Posts

Posted - 2007-06-19 : 08:25:04

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.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-19 : 10:46:02

Tried this:

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

hafeez
Starting Member

14 Posts

Posted - 2007-06-20 : 01:04:47

Hi,
Thanks for the info.
When i execute this command i got the following error.

"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'evesysid0015'."

After i modified the command like below and execute but i got a different 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."

Thanks and Regards,
Hafeez.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-20 : 11:06:54

Did you replace db_name and owner with real ones?

hafeez
Starting Member

14 Posts

Posted - 2007-06-21 : 00:56:20

Yes, I replaced db_name and owner with real ones.
If you want more info i will provide.

Thanks and Regards,
Hafeez.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-21 : 11:42:59

The code works on my sql2k5 sp2 servers. What's your sql version and sp level?