question

ecomma avatar image
ecomma asked

Compatibility error with set Operator Except to sent differences between two databases

I need to sent certain record from my central database to the sub one for business purpose. We use datacompare functionality in VS2013. But it take a lot of resource. Use set operator:Except to sent only records present in the main db and not the sub database. But am having this error:The image data type cannot be selected as DISTINCT because it is not comparable. with tables without images it works fine. a linkserver connection is used to get to the remote server. I did a lot of research but i can seem to fine solution that address my case. here is my code ------------------------------------ INSERT INTO Person(FullName,[photo],[gender],[receiptNo],[Status],[DateCreated]) Select top(1000) FullName,[photo],[gender],[receiptNo],[Status],[DateCreated] from Mylinkservername.Mydatabasename.dbo.Person EXCEPT SELECT FullName,[photo],[gender],[receiptNo],[Status],[DateCreated] From Person
sql-server-2008-r2tsqllinked-server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
One of the columns (I'm guessing [photo]) is an image datatype and columns of type text, ntext or image cannot be used this way. Depending on what version of SQL server you are using, you can convert this column to varbinary(8000) or varbinary(max), and the EXCEPT operator should work for you
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.