none
The data types varchar and sql_variant are incompatible in the add operator. (Microsoft SQL Server, Error: 402) RRS feed

  • Question

  • What kind of error does one get when hitting the limits of VS2005 report server project?  I've been incrementally adding pieces to the dataset and with the last added chunk I get this error.  There is no addition anywhere let alone the new piece.  The new piece has a subquery using ROW_NUMBER() and comparing the number to 1, but so did the previous, working set.  All other equal signs are comparing uniqueidentifier data types.  What else could this error be indicating?  Could I be hitting the system limits?  For joins or columns or something?

    To top it all off, I test the script before introducing it to the VS project in SS Management Studio 2008 and it runs fine.

    TITLE: Microsoft Report Designer
    ------------------------------

    Could not generate a list of fields for the query.
    Check the query syntax, or click Refresh Fields on the query toolbar.


    ------------------------------
    ADDITIONAL INFORMATION:

    The data types varchar and sql_variant are incompatible in the add operator.
    The data types varchar and sql_variant are incompatible in the add operator. (Microsoft SQL Server, Error: 402)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=402&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    • Edited by mardukes Wednesday, September 29, 2010 6:18 PM forgot something
    Wednesday, September 29, 2010 6:16 PM

Answers

  • It's a false error. 

    It was a dynamic query where the string was too long.  The solution was to make multiple string variables and concatenate ... exec (@sql1+@sql2+@sql3+...+@sqlN)

    The limit in VS is 4000 (assumedly it passes nchars) while in the Enterprise Manager it allows the full 8000 chars.

    • Marked as answer by mardukes Wednesday, October 6, 2010 2:23 PM
    Wednesday, October 6, 2010 2:23 PM

All replies

  • It has nothing to do with datatypes or operators.  I have 56 columns in my query, all is fine.  Add a 57th, error pops.

    Come on guys!  I remember something like this with Access a while back where you had to increase the file handles or something in the registry setting to make it work.  What about the SSRS setup?

    Wednesday, September 29, 2010 10:08 PM
  • the continuing saga...

    I tried to preview with 56 columns and got an error that it couldn't bind "cisupe".  What is cisupe?  So I searched the query and got a hit towards the end of the script...cisupervisor.  It would appear the script is getting truncated around 3923rd character.

    Thursday, September 30, 2010 4:56 PM
  • Hi Mardukes,

    Based on your information, it seem that the query have no issue, which could return the datafileds correctly, while the error come out when you add  more than 57 columns. Right? Actually, in SQL Server, 57 have not exceeded the limitation number of columns. We could get a reference from here Maximum Capacity Specifications for SQL Server   I also test it in local environment with 57 columns, and the result is fine. So if possible could you please post your query in the forum or send your query and datasource to sqltnsp AT microsoft DOT com, With the beginning “hi Challen”, please attach the thread link as well. Once I receive your email, I will give you a feedback as soon as possible after confirmation. 

    Regards,

    Challen Fu

    Monday, October 4, 2010 6:19 AM
    Moderator
  • It's a false error. 

    It was a dynamic query where the string was too long.  The solution was to make multiple string variables and concatenate ... exec (@sql1+@sql2+@sql3+...+@sqlN)

    The limit in VS is 4000 (assumedly it passes nchars) while in the Enterprise Manager it allows the full 8000 chars.

    • Marked as answer by mardukes Wednesday, October 6, 2010 2:23 PM
    Wednesday, October 6, 2010 2:23 PM