4

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?

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

1
  • 1
    Assuming that there's no data type issue like @Dforch42 talked about then it might be a legit bug which should be submitted to the product team via connect.microsoft.com.
    – mrdenny
    Apr 16, 2012 at 0:44

1 Answer 1

1

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.

1
  • Here is the table definition of Detection (warning, it's big.) Yeah, infoFlag is a Bigint. I'll paste the def. in the initial question though.
    – capnsue
    May 11, 2012 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.