Issue with CAST using linked servers (possible bug?) 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 &amp; 16 &lt;&gt; 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 &amp; 3758333096 &lt;&gt; 0)


we get an error:  

    System.Exception: The data types bigint and numeric are incompatible in the '&amp;' 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 &amp; CAST(3758333096 as BIGINT) &lt;&gt; 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"&amp;(3758333096))&lt;&gt;(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 &amp; @a &lt;&gt;  0) 


However I am concerned that this might be a bug.  Or is there something I'm missing?

thank you!

--suz (@capnsue)

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
Do you have the table definition for the table Detection?  I'm betting that infoFlag might not be set up as a bigint, but rather as a numeric with no decimal points.  You might also make sure that the data type is consistant on all of your servers.