Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2012
    Posts
    6

    Unanswered: Query errors when going from SQL 2k5 to 2k8

    I recently upgraded to SQL Server 2008 r2 from a 2k5 server. My SQL Reports Server generates a report with the following code.

    SELECT SV00100.CPRCSTNM, max(SOPHisIt.CUSTNMBR), MAX(RM00101.CUSTNAME) AS Expr1, SUM(SOPHisIt.XTNDPRCE) AS TotalSales
    FROM SOPHisIt INNER JOIN
    SV00100 ON SOPHisIt.CUSTNMBR = SV00100.CUSTNMBR INNER JOIN
    RM00101 ON SOPHisIt.CUSTNMBR = RM00101.CUSTNMBR
    WHERE (SOPHisIt.USRDEF05 IN (200, 250)) AND (SOPHisIt.DOCDATE >= @StartDate) AND (SOPHisIt.DOCDATE <= @EndDate) AND sv00100.cprcstnm > '0'
    GROUP BY SV00100.CPRCSTNM
    ORDER BY TotalSales desc


    This code still works on old report server no problem but I get a couple of errors on the new one.

    *Msg 8120, Level 16, State 1, Line 1
    Column 'SV00100.CPRCSTNM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    *Second is the multi part identifier could not be found

    Any ideas would be appreciated to help me get this up and running on my new SQL server.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The code that you posted works fine on my systems (2005, 2008, 2008 R2, and 2012). I'd peel the code from your report server and attempt to run it using SSMS (SQL Server Management Studio) to see what that reports.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2012
    Posts
    6

    Putting in SSMS

    I did do that in this case and that is where I get the errors. When I run in SSMS or from the SSRS it doesn't matter.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The code that you posted runs fine for me in all of the environments. I don't have your tables or data to test against, but the syntax in your posted code is correct.

    If you are getting errors using the code that you posted, I strongly suspect that something is wrong with your SQL installation.

    If you are using different code, then your milage may vary.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2012
    Posts
    6

    Ssms

    With the code I posted in SSMS the first time I was actually asked what StartDate and EndDate are. Now I can't get it to ask me that. How can I set start date and end date for the purposes of recreating the exact error I am getting to try and demonstrate?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Adjust the values assigned to the dates as necessary:
    Code:
    DECLARE
       @EndDate     DATETIME = '1900-01-01'
    ,  @StartDate   DATETIME = GetDate()
    
    SELECT SV00100.CPRCSTNM, max(SOPHisIt.CUSTNMBR), MAX(RM00101.CUSTNAME) AS Expr1, SUM(SOPHisIt.XTNDPRCE) AS TotalSales
    FROM SOPHisIt INNER JOIN
    SV00100 ON SOPHisIt.CUSTNMBR = SV00100.CUSTNMBR INNER JOIN
    RM00101 ON SOPHisIt.CUSTNMBR = RM00101.CUSTNMBR
    WHERE (SOPHisIt.USRDEF05 IN (200, 250)) AND (SOPHisIt.DOCDATE >= @StartDate) AND (SOPHisIt.DOCDATE <= @EndDate) AND sv00100.cprcstnm > '0'
    GROUP BY SV00100.CPRCSTNM
    ORDER BY TotalSales desc
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Mar 2012
    Posts
    6

    Query runs now

    So when I assign dates ranging from 12/1/2011 to present the query runs successfully but comes back with this error about the data.

    It returns no data but the error message is...
    Msg 245, Level 16, State 1, Line 7
    Conversion failed when converting the varchar value 'PDA DELIVERED ' to data type int.

    Suggestions how I can fix this?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The SOPHisIt.USRDEF05 column must be character data, not a numeric. This makes the query itself suspect, so you need to re-think what it is doing and what you really want to happen.

    To confirm that this is the problem, rows should return when you execute:
    Code:
    SELECT *
       FROM SOPHisIt
       WHERE  0 = IsMumeric(SOPHisIt.USRDEF05)
    As a work around, you may be able to use something like:
    Code:
    SELECT SV00100.CPRCSTNM, max(SOPHisIt.CUSTNMBR)
    ,  MAX(RM00101.CUSTNAME) AS Expr1, SUM(SOPHisIt.XTNDPRCE) AS TotalSales
       FROM SOPHisIt
       INNER JOIN SV00100
          ON SOPHisIt.CUSTNMBR = SV00100.CUSTNMBR
       INNER JOIN RM00101
          ON SOPHisIt.CUSTNMBR = RM00101.CUSTNMBR
       WHERE  (1 = IsNumeric(SOPHisIt.USRDEF05))
          AND (SOPHisIt.USRDEF05 IN (200, 250))
          AND (SOPHisIt.DOCDATE >= @StartDate) 
          AND (SOPHisIt.DOCDATE <= @EndDate) 
          AND sv00100.cprcstnm > '0'
       GROUP BY SV00100.CPRCSTNM
       ORDER BY TotalSales desc
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Note that if the first query returns rows, that confirms that your basic query is flawed. That means that until you find the flaw and understand how it affects your query, any data that is returned is suspect (and definitely not usable for making any kind of decisions).

    I can't stress this enough: If I'm correct, the results of your query are meaningless until you understand what went wrong and how that wrongness affects your query.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Mar 2012
    Posts
    6

    Solved

    That piece of code worked. Thank you very much for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •