Linked Server error

Question

Post reply

Linked Server error

hughesj23

Ten Centuries

Points: 1349
More actions
April 1, 2011 at 9:51 am

#237922

I have a linked server that seems to be setup correctly. I can successfully execute a very basic delete query against my linked server like this:
delete from ls1.lsdb1.dbo.table1
This deletes all rows in the table.
But as soon as I add a simple where clause, I get an error. This doesn't work:
delete from ls1.lsdb1.dbo.table1 where year(column1) = 2011
This is my error:
OLE DB provider "SQLNCLI10" for linked server "ls1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7202, Level 11, State 2, Line 1
Could not find server 'ls1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Viewing 7 posts - 1 through 6 (of 6 total)

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

jvkondapalli SSCommitted Points: 1587 More actions · Answer 1

Column1 is varchar?

can you tell a bit more about the necessity of running delete statement over the linked server?

Thanks

Jagan K

Thanks
Jagan K

jvkondapalli SSCommitted Points: 1587 More actions · Answer 2

Column1 is varchar?

can you tell a bit more about the necessity of running delete statement over the linked server?

Thanks

Jagan K

Thanks
Jagan K

hughesj23 Ten Centuries Points: 1349 More actions · Answer 3

Column1 is a date field in my example. But it doesn't work no matter the data type. Once you add a where clause, it errors.

Server1 has detail transactions. I want a summary of those transactions in a table on Server2. It's a very simple query so I just wanted to query Server1 from Server2 instead of using SSIS.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

Some random thoughts...

- Try surrounding your identifiers that contain numbers with square brackets:

delete from [ls1].[lsdb1].[dbo].[table1] where year([column1]) = 2011

If that does not work try recreating your Linked Server without any numbers in the name.

- Does the remote table have a DELETE TRIGGER on it? If so, what is it doing?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

hughesj23 Ten Centuries Points: 1349 More actions · Answer 5

Thank you for your suggestion. I tried that this morning and it did not work with the brackets either.

But SUCCESS!! I can hardly believe how simple it was. But it doesn't make sense why still. Apparently, the linked server doesn't like the YEAR function in conjunction with the Delete statement???? Go figure!

This WORKS:

select * from ls1.lsdb1.dbo.table1 where year(column1) >= 2011

This WORKS:

delete from ls1.lsdb1.dbo.table1

This does NOT work:

delete from ls1.lsdb1.dbo.table1 where year(column1) >= 2011

This WORKS:

delete from ls1.lsdb1.dbo.table1 where column1 >= '1/1/2011'

Problem solved and lesson learned I guess.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

This will likely perform much better for you since you'll guarantee that all processing will take place on the remote server:

EXEC('delete from lsdb1.dbo.table1 where year(column1) >= 2011') AT [ls1];

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato