Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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 '12 at 0:44

1 Answer 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.

share|improve this answer
    
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 '12 at 15:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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