Linked Server error

Question

Post reply

Linked Server error

Viewing 6 posts - 1 through 7 (of 7 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: 182293 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: 182293 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