.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Why Query will not run with a hidden parameter

Posted By:      Posted Date: October 22, 2010    Points: 0   Category :Sql Server

I am using RS2005

I have a query A that returns an order#    123456-0

I have query B that slects out the main order#  and returns 123456

I have query C that selects out the backorder# and returns 0

I have query D that then select the order lines based on orderno=b and ordersuf=c                                                                       select line# from orders where orderno = b and ordersuf  = c.            

This returns 3 lines of data as soon as I select the order# (Query A)

If I then mark query B and C as internal or hidden Query D does not run until I click "View Report"

What does the "internal"  or "hidden" setting do that makes the query D not run.

I need D run so the user can select which lines he wants and then click "View Report" to do the report on the selected lines.<

View Complete Post

More Related Resource Links

MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



Get ServerReport selected Parameter value to use in vb query


I'm using ReportViewer in Asp.net 2.0 to view a SSRS report. I need the value of the selected parameter to use in a vb query. The parameters are populated on the server and its a single selection. Doing searches I've come across  ReportViewer1.ServerReport.GetParameters() but I can't figure out if I can use this to determine which value the user has selected in the parameter dropdownlist. Any help would be appreciated.

How To Remove a Query Parameter from a URL


for a string such as this:


how would I remove the parameter id 12?


adding parameter to query string


hi all,

how do i add a new parameter to an existing query string?


now i need to add a new parameter say, showsearch.

Parameter Help in my Dataset query

I have a qurey in a data set in reporting services. What im trying to do is use the shipdateyear field as a parameter but i keep getting an error when im running it. I think its because how im getting the name shipdate year its coming from a field where im formatting the data to just show year.heres my query. its erroring in the where Clause i believe. any help would be awesome.   I have underlinded where i think my isues are.  Thanks.select bol.loadnumber, bol.ldhshipdate,bol.shiptocode,bol.destination,bol.shiptoname,bol.itemdesc,bol.abbrev,Datepart(week,bol.ldhshipdate) as 'weeknum',Datepart(weekday,bol.ldhshipdate) as 'weekdaynum',--case statement--Case when Datepart(weekday,bol.ldhshipdate) = 1 then 'Sunday'else case when Datepart(weekday,bol.ldhshipdate) = 2 then 'monday'else case when Datepart(weekday,bol.ldhshipdate) = 3 then 'tuesday'else case when Datepart(weekday,bol.ldhshipdate) = 4 then 'wednesday'else case when Datepart(weekday,bol.ldhshipdate) = 5 then 'thursday'else case when Datepart(weekday,bol.ldhshipdate) = 6 then 'Friday'else case when Datepart(weekday,bol.ldhshipdate) = 7 then 'saturday'else 'none'end end endendendendend as 'dayofweek',--case statement--ofstm.ostabr, ofstm.ostnam,ofstm.ostccd,Datepart(year,bol.ldhshipdate) as shipdateyear,--caseCase when d

Having problem accessing multi-choice parameter in SQL Query in Report.

Hi, I have a report with a multi-choice input parameter. My report contains a dataset that uses CHARINDEX on this multichoice parameter. The dataset query is in text, not in stored procedure. When I run the report I get "the charindex requires 2-3 arguments the reason being that the SQL is run as follows (You can see the multi-choice list screws up the string: exec sp_executesql N'Select test.Region [Region], test.Location [Location], nvarchar3 [Year], nvarchar4 [StatisticType], nvarchar5 [StatisticType2], ntext2 [Detail], float1 [Amount]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListId and AL.tp_Title=''Statistics''   left outer join   (       Select UD.tp_id [ID],nvarchar1 [Region],     nvarchar3 [Location]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListID and AL.tp_Title=''Regions''   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1   ) test on test.id = UD.int1   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1  &n

Duplicate URLs in Source Parameter of Query String for All SharePoint Lists

After performing a database attach method of upgrading our existing web application from MOSS 2007 to SharePoint Server 2010, all of our lists that migrated now exhibit an interesting but undesirable behavior.  The DispForm.aspx and EditForm.aspx pages that get loaded are given the Source parameter of the query string but instead of the URL being a single entry like: Source=http://sharepointsite.domain.com/Pages/Default.aspx It winds up putting the URL twice like this: Source=http://sharepointsite.domain.com/Pages/Default.aspx,http://sharepointsite.domain.com/Pages/Default.aspx Naturally this makes processing of the form -- either by choosing OK or Cancel buttons -- result in an HTTP 404 error.  The question we're left with is how in the world did the Source parameter get set that way to begin with.   It wasn't that way in 2007. We had thought that perhaps there was something that didn't migrate over correctly for the existing lists, so I tried creating a new list and then added a new item to the list.  So far so good.  Then attempted to Edit Properties or Edit Item or even View Item, etc. -- essentially triggering the load of the EditForm.aspx and DispForm.aspx  -- and we received the 404 again.  At this point we're not sure what's going on or how to fix it.  We've performed the migration three times so far with t

MDX Query for Defualt Value in SSRS Parameter

Hello everybody, I am new to MDX and struggling to come up with a query that works.  I have a  SSRS report  that filters by week. I need to write a query that will run for last week as default. The query i have cooked up is: WITH MEMBER Measures.Lastweek as [Time Dimension].[Report Week].currentmember.lag(1) Select Measures.lastweek on 0, [Time Dimension].[Report Week] on 1 from [sales] This returns a null value. I have searched and searched the internet for the answer but i have found nothing. I would really appreciate some help.   Thanks.

How to Create an MDX Query Parameter to Select 30 Values from a Dimension?

Hi, I'm using SSRS 2005 to report on an SSAS cube that contains a Procedure dimension.  I don't need to use members of this dimension in my report, but rather need to select records (patients) where their chart has one or more of the codes.  I've researched this today and cannot locate the best approach.  Thus far, I've attempted to create an MDX query parameter as part of my dataset.  However, I don't know whether this is the correct approach, and how to structure the syntax so that only records with one or more procedures are included in the report?  If so, what is the proper MDX syntax for setting my Procedure code equal to the query parameter? Thanks, Sid

AD Parameter Query

I have a lookup page that displays basic user data from AD in a grid control. I would like to create one of the grid columns as a hyperlink column and pass the username to a new page to display more user details from ad. Any assistance to be able to get the parameter from the query string and call the data from AD would be much appreciated. Here is the page I have been working on to populate the grid on page load based on the parameter query.  Imports System.Data Imports System.DirectoryServices Partial Class test Inherits System.Web.UI.Page Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs) Dim displayName As String Dim samAccountName As String Dim p_userGUID As String Dim objDE As New DirectoryEntry() objDE.Path = "LDAP://DC=tusd,DC=local/<sAMAccountName=" + p_userGUID + ">" objDE.Username = "abrowning" objDE.Password = "ab9156" objDE.AuthenticationType = AuthenticationTypes.Secure If objDE.Properties.Contains("sAMAccountName") Then ' make the People table and its columns Dim tblPeople As New DataTable("People") ' Holds the columns and rows as they are being Dim col As DataColumn Dim row As DataRow ' Create LogonNam

syntax problem passing parameter into Indexing Service Query

Hi everyone, I have the following query which works fine: select OriginalFileName from Document_Entries where EntryType like 'File%' and substring(entry,charindex('file_',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, 'SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS('' "green" '') ') )  It finds all documents in the document system which contain the word "green" using the index catalog.  My problem is that i need to include this query in a larger stored procedure which accepts a parameter for key words amongst others. I can't work out the syntax to get the @keywords parameter into query. The closest I've come is the following which runs but comes back with "incorrect syntax near keyword 'green'".  The @keywords parameter will contain any key words the user enters.   declare @keywords nvarchar(500) set @keywords='green'   Declare @query nvarchar(max) set @query = ' select OriginalFileName from Document_Entries where EntryType like ''File%'' and substring(entry,charindex(''file_'',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, ''SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS(''' + @keywords + ''') '')     )  )' exec(@query)   Any ideas? thanks Gus

Pass parameter to Hidden Field in SSRS Report

Hi, I have Two Columns in a Table Say As Table1 Column1 = Text Column2= id My SSrs Report has two parameter values as Column1 and Column2. Column2 should be hidden SELECT DISTINCT COlumn1 FROM Table1. i have passed this query dataset to Column1 parameter. Now My requirement is that, when ever column1 drop is selected. the hidden field column2 should fecth the corresponding ID. this column2 has Storeprocedure which will Display the Output when View Report button is clicked Thanks, Saraswathy

SharePoint Dataview Insert New Mode - Defaulting a Required Column to a Query String Parameter Valu

I've got a SharePoint DataView with an insert button. there is a required lookup column I want to default to the value of Querystring Value during new mode.  Can I do this in the generated code below? I tried replacing @ApplicantId with @QSApplicantID.. and  tried string(@QSApplicantID) too.. no luck gave me an error: The data source control failed to execute the insert command. Could it be that QSApplicantID (which I've used in my DataView Datasource filter with no problem) is not available during the insert? I think I can do what I want in Javascript but was hoping I could instead right in the XSL markup. *my query string parameter**     ParameterBinding Name="QSApplicantID" Location="QueryString(ID)" DefaultValue="2222222"/ **the new form** SharePoint:FormField runat="server" id="ff4{$Pos}" ControlMode="New" FieldName="ApplicantId" __designer:bind="{ddwrt:DataBind('i',concat('ff4',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@ApplicantId')}" / **the link** a href="javascript: {ddwrt:GenFireServerEvent('__cancel;dvt_1_form_insertmode={1}')}">Insert</a Thanks for any help or information!

sp_send_dmail doesn't work in Agent job with query parameter


I have the following query which needs to run on a schedule and email a list of all disabled jobs for the SQL Server instance (I've changed the server and email names for privacy):

 EXEC msdb.dbo.sp_send_dbmail
 @recipients = N'name@email.com'
 ,@body = N'The attachment shows the names of all SQL jobs that are currently disabled on SERVER1'
 ,@subject = N'Disabled Jobs on SERVER1'
 ,@query = N'SELECT [NAME] FROM [msdb].[dbo].[sysjobs]'
 ,@attach_query_result_as_file = 1

This query works fine when running from a query window.  however, when I try to run it from a SQL Agent job, I get the following error on the step output (username changed for privacy):

Executed as user: domain\user. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

If I try other queries in the @query parameter, everything works fine when running from the job.  As soon as I try to query [msdb].[dbo].[sysjobs] though, the job fails.  I ran a profiler trace, and I found a more detailed error message that occurs during the execution of the job, unfortunately it involves an undocument SP:

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status

Assign Query String Values to Selectcommand parameter in




I have two pages EventList.aspx and EventDetail.apsx. When user selects any events [basically list item] on EventList.aspx page [all events are rollup in single sote collection] in am redirected to EventDetail.aspx page with QueryString ListID and ListItemId.

On EventDetail.aspx page i am using


"<Webs Scope='SiteCollection'></Webs><Lists><List ID='{ListID}'></Li

How to change the Default Value of the Parameter to "Query Based"


Hi I have a Report which i deployed into my localhost.

In the Report Manager, Report Parameter Properties, I unchecked the HasDefault option for which Default value is coming from some Query.

When I retry to Set the Default Value to Query Based, Its not taking.

Need Help.

Order parameter MDX query



how can I arrange outcome of MDX query by ParameterName? In my query it doesn't work.



MEMBER [Measures].[ParameterCaption] AS '[Organisation].CURRENTMEMBER.MEMBER_CAPTION'



[Measures].[ParameterValue] AS

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend