SQL Server Performance

Another Dynamic query problem

Discussion in 'SQL Server Reporting Services' started by dougwood, Feb 11, 2004.

  1. dougwood New Member

    Hi Jasper,
    Well I have another error which I haven't been able to get around with a dynamic query. It's very similar to what I was doing yesterday, but I'm getting an error that I have never recieved before.

    The original query was


    SELECT RESPONSIBLE_APP, SUBMITTED_ON, ASSIGNED_GROUP FROM DEFECTS WHERE (ASSIGNED_GROUP = 'CRM') AND (RESPONSIBLE_APP = ?) order by SUBMITTED_ON
    this works fine.

    But again I wanted to add the 'ALL' option, so I added an 'All' to my dropdown parameter.

    My code now reads:


    ="SELECT RESPONSIBLE_APP, SUBMITTED_ON, ASSIGNED_GROUP FROM DEFECTS WHERE (ASSIGNED_GROUP = 'CRM')" & IIf(Parameters!ResponsibleApp.Value = "All", "", " AND RESPONSIBLE_APP = ?") & " order by SUBMITTED_ON"

    The strange thing is, if I select 'ALL', everything works fine, but if I select any other fields, i get the error:
    "Query execution failed for Dataset, No error information available: DB_E_PARAMNOTOPTIONAL(0x80040E10)."

    I tried replacing the '?' with 'Parameters!ResponsibleApp.Value', but got another 'invalid column name' error.

    See anything I'm missing ??
    thanks again !
  2. dougwood New Member

    I've found the solution to the problem. thanks !
  3. kalambert New Member

    Hi, can you please share what your solution was? I have had this same error.

    Thanks!

    Kristi
  4. BrenBart New Member

    I am having a similar problem with trying to create a dynamic query. According to RS-BOL you need to add this IIF... code using the generic query editor but the text string has a 128 character limit.

    Anyone know of a solution to this?
  5. dougwood New Member

    I don't actually remember the slution i had to this particular problem, but as I recall, it was just a syntax problem. It really sucks that SQl Server rreporitng services doesn't allow for page breaks, etc in the SQL pane. it makes it much harder !!
  6. m_sadheesh New Member

    ="SELECT RESPONSIBLE_APP, SUBMITTED_ON, ASSIGNED_GROUP FROM DEFECTS WHERE (ASSIGNED_GROUP = 'CRM')" & IIf(Parameters!ResponsibleApp.Value = "All", "", " AND RESPONSIBLE_APP = ?") & " order by SUBMITTED_ON"


    Don't use the IIF stmt in the dynamic query

    use the if stmt

    if (Parameters!ResponsibleApp.Value = "All")
    begin
    ="SELECT RESPONSIBLE_APP, SUBMITTED_ON, ASSIGNED_GROUP FROM DEFECTS WHERE (ASSIGNED_GROUP = 'CRM') order by SUBMITTED_ON"
    end
    else
    begin
    ="SELECT RESPONSIBLE_APP, SUBMITTED_ON, ASSIGNED_GROUP FROM DEFECTS WHERE (ASSIGNED_GROUP = 'CRM') AND RESPONSIBLE_APP = ? order by SUBMITTED_ON"
    end
  7. Barbara New Member

    Did anyone try this approach and have it succeed?

    I had been having trouble with using the IIF for multiple parameters because I was getting lost in the IIFs (but the IIF was working). I read this thread and attemped the IF option above but I am getting an error on my parameter which is pre-defined in Report Parameters.

    Do you need to use a command type other than "text" in order for this to work?

    Thanks,
    Barbara
  8. Ravindra New Member

    Hi,

    I am facing error while retreiving from MS Sql server and inserting the same to MySql databse.

    The following error it displays when i u error handling.

    "DB_E_PARAMNOTOPTIONAL(0x80040E10)";


    This type of error i am getting only for some case where

    "What is the values of <em>t</em>?"

    Thanks,
    Ravi

Share This Page