So I've got a some SQL Server 2008 DBs arranged in a star schema. Server A is the one users submit queries to. On Server A there is a DPV called Detection that's basically a UNION of table Detection on servers X, Y and Z.
One of the columns in Detection is a BIGINT called infoFlag that is basically a bitmask. Users need to use bitwise operators to filter their queries on this mask. This works fine in situation such as this:
SELECT TOP 500 colA colB from Detection WHERE (infoFlag & 16 <> 0)
But if you use a mask that is too big for an INT, for example:
SELECT TOP 500 colA, colB FROM Detection WHERE (infoFlag & 3758333096 <> 0)
we get an error:
System.Exception: The data types bigint and numeric are incompatible in the '&' operator.
Fair enough. A simple cast should fix the problem, right? It should, but it doesn't.
SELECT TOP 500 colA, colB FROM Detection WHERE (infoFlag & CAST(3758333096 as BIGINT) <> 0)
gives the same error.
While troubleshooting this, I ran the query directly on the Detection table on Server X and it worked fine (with the cast.) So I figured that the problem had something to do with the linked servers.
So I fired up profiler on Server X and submitted the query against the Detection DPV on Server A. Here is what came through:
SELECT TOP (5000) "Tbl1002"."colA" "Col1008","Tbl1002"."colB" "Col1009" FROM "myDbName"."dbo"."Detection" "Tbl1002" WHERE ("Tbl1002"."infoFlag"&(3758333096))<>(0)
It seems that the CAST statement is not getting propogated to the remote server!
I did figure out a workaround, which is to do this:
declare @a bigint set @a = 3758333096 SELECT TOP 500 colA, colB FROM Detection WHERE (infoFlag & @a <> 0)
However I am concerned that this might be a bug. Or is there something I'm missing?
Version: Microsoft SQL Server 2008 (SP1) - 10.0.2710.0 (X64) Apr 7 2009 20:36:04 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
EDIT: Here is the definition of the Detection table. http://pastebin.com/ErGJ9fSG