Another Dynamic query problem | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Another Dynamic query problem

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 !
I’ve found the solution to the problem. thanks !
Hi, can you please share what your solution was? I have had this same error. Thanks! Kristi
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?
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 !!
="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
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
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
]]>