Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Is There A Max Number Of Pages/parameters For Reports?

I created a report that contains 5 subreports (which retrieves data for meetings). There are currently 826 meetings and each report is 2 pages long (though this number is not static). Each time I run the main report (passing in the 826 MeetingIDs as a parameter), I get a time-out error.

However,  I pass in all the MeetingIDs for each of the subreports and it runs fine, even when I do a run several of the subreports together. Before trying to revise my queries, I was hoping someone could tell if there are any limitations on how many pages you can export to PDF? Also, in terms of performance, is there any difference between including subreports and just creating different data sets within the main report?

Please let me know if i'm not being clear. And thanks in advance for your help!

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Number Of Pages
Hi All,

How to estimate the number of pages there should be in the index? Thanks.

View Replies !   View Related
Calling Reports From ASP Pages

Hi All,
I have a requirement wherein I need to create an asp web page(not,  where the user will select the report criteria and using those selected criteria I need to build a report using SQL server 2005 reporting services.  
Previously I have used SSRS reported with application and I am comfortable with creating SSRS report but I am not sure how it will work with ASP pages. Could you please let me know how we pass the report parameters from asp page to SSRS.

View Replies !   View Related
Getting Reports On Multiple Pages
 I have a data set which has more than one record. Iam tyring to create a report without using the wizard. I want the report on multiple pages. I dragged some text boxes on to the design and added the expressions in there. If I run the program now Iam only getting the report with the last record. How do I get the report for all the records in the Dataset on multiple pages ? . I want each record on one page. I am forsure getting more than one record into the Dataset.
Thanks in advace for the help

View Replies !   View Related
Number Of Pages Different In Html, Pdf
I have reports that when rendered in html have a different number of pages
than when exported to pdf. I have noticed that none of the formats have the
same number of pages. I have InteractiveSize and PageSize set exactly the
same. Is there any way to get these to agree?Thanks,

View Replies !   View Related
How To Get Number Of Pages For A Group?
Like many others, I have a large report that groups on invoices, and does a page break between each invoice.  From reading other posts in this forum, I was able to reset the page number on the grouping, however, there are some things that I need to display only on the last page of each invoice, but I don't see how I will be able to determine when I'm on the last page for a particular invoice.  Can I somehow determine up front the number of pages for a group so I know when I'm at the last page?

Any help is greatly appreciated!

View Replies !   View Related
Problem With Number Of Pages

Hello everybody
I've built a few reports using Reporting Services. The number of pages in my reports isdifferent in the on-screen rendered report from the printed version.
My report properties are set to an A4 page (210 x 297) and also Globals.Page give me a different result. (on the screen I see 2 pages and I print 6 pages).
How can I solve this problem?
Best regards

View Replies !   View Related
Can We Drill Down Reports In A Another Different Pages With Different Layouts?
Hi, all experts here,

Thank you for your kind attention.

I am wondering if we can drill down reports in a different page with different layouts and columns? e.g. I have too many columns desired to see in a report, so I want to direct the users to another page with different layouts and columns, so the first view in the report wont display so many columns in a page. Is it possible to make it in SQL Server 2005 Reporting Services? And if it is possible then how? Hope my question is clear for your help.

Thanks a lot in advance and I am looking forward to hearing from you.

With best regards,

Yours sincerely,


View Replies !   View Related
Is There A Limit On The Number Of Pages On Printing
Hello All,

   I am cornered in a situation where I need to print huge printing. I can do that only if my report runs for a long time, which is not happenning. Here is what I did ....

Created a Snapshot to run the report at scheduled time for perticular parameter ranges. By doing this I was able to print around 40,000 pages.

       If I change my parameters to more than what I give then it is giving me "Cannot read the next data row for the data set"

I have followed all the articles pertaining to this but couldn't find a solution.


Please help me.


My Environment :  SSRS 2005 , SQL Server  2005


View Replies !   View Related
Count Number Of Pages Sent To Printer
Is there anyway to count number of pages sent to the printer, to track them?

View Replies !   View Related
Trying To Figure Out Number Of Pages In Report
I have a report that is created that is typically 2-3 pages long.  I've tried the "ExecutionInfo.NumPages" but it always results in "1" as the answer.  Code is something like this:


Dim reportHistoryParameters As sqlprod1_res.ParameterValue() = Nothing

Dim SessionId As String
Dim execInfo As New ExecutionInfo

Dim execHeader As New ExecutionHeader()

Dim result As Byte() = Nothing

Dim format As String = "PDF"

Dim devInfo As String = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
Dim extension As String = ""
Dim encoding As String = ""
Dim mimeType As String = ""
Dim warnings As sqlprod1_res.Warning() = Nothing

Dim streamIDs As String() = Nothing

result = rs.Render(format, devInfo, extension, encoding, mimeType, warnings, streamIDs)

execInfo = rs.GetExecutionInfo()


Dim Pages As Integer = execInfo.NumPages
Also tried to count streams with the same result.
Any ideas?
Dan Ribar

View Replies !   View Related
Pages Breaks On A Sub Reports With Visiable Expression

I have a master report which has three sub reports. I want to show the last sub report on the second page only if data is available otherwise I want to hide the sub report and as well as the second page. I use a expression to hide the subreport and it works. But the problem is with the page breaks. Even though I use a rectangle for page break, it does not work as I want.
Doesn anyone know the solution for this?

View Replies !   View Related
How To Determine Total Number Of Pages At Runtime?
It's no secret that the number of pages in a rendered report varies depending on the format.  I have no problem getting the total number of pages for reports rendered in image formats from the web service, but I can't figure out how to get the number of pages for reports rendered in HTML.
I've always been under the impression that the Report Manager that ships with SSRS uses the same web service (reportservice.asmx) and IT can get the number of HTML pages, so it has to be possible.
Does anyone know how to do it?

View Replies !   View Related
Print-preview Displays Different Number Of Pages
We have 2 users accessing exactly the same reports/data but who are getting different numbers of pages printed. 
The report viewer displays 12 pages of data; when one user selects Print preview they get 12 pages displayed and 12 pages print.  When the other user displays exactly the same 12 pages of report data and preview it, they get 24 pages, with the last column apparently spilling on to a second page.  I realise that physical pagination is controlled by the print control and is unrelated to the HTML rendering, but the users have the same printer selected and the print-preview shows the margins set to the same size.  What else can be causing the difference? 
We are using RS 2000, SP2.  TIA for any assistance with this.

View Replies !   View Related
Not Calculating The Correct Number Of Pages In Report

When viewing a report our users see that it is x number of pages, however when they print the report is actually y number of pages.  For example once a report is ran, it says that it is 43 pages, however in actuality it is 62 pages.  If they only want to print the last page, they choose to print 43 from the print dialog box, but that gives them a page in the middle of the report not the last page.  Is there a way to remedy this?


Thank you,


View Replies !   View Related
Reports Are When Printed SQL Server Report Is Printing Extra Pages

Hi ,
I have developed some report in Sql Server Reporting services my Page orientation is portrait that is report height and width  are 8.5 and 11 respectively and margins are set to 0.3 in when it is printed the pages are printed more that actually viewed Pages.
I need help from someone.

View Replies !   View Related
Error Processing Resource 'http://localhost/reports/Pages/Folder.aspx'
Hi all

After deploying my report on the report Server, I try to access it on the browser which gives me the following error.

The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

A name was started with an invalid character. Error processing resource 'http://localhost/reports/Pages/Folder.aspx'. Line...<%@ Page language="c#" Codebehind="Folder.aspx.cs" AutoEventWireup="false" Inherits="Microsoft.ReportingServices.UI.Folde...

How can I trouble shoot this error.?

Please help me out.

Thanx in advance.


View Replies !   View Related
Dashboard (Question) Reports In SSRS (automatic Scolling Or Automatically Loop Through Pages)
I need to create a simple dashboard report that will be displayed on a large screen.
My problem is that there can be potentially more records than the screen can display.
Based on how i size my report the report can be a single page with a scroll bar  or mutliple pages.
My question is: Is there any way of automatically looping through pages or to automatically scroll down through the records in SSRS
If anyone has any experience of something like this or suggest a work around in SSRS could you please let me know
Thanks in advance

View Replies !   View Related
Get The Number Of Reports Per Day
I have a table that has a DateTime field that stores something like this "2004/01/19 16:16:15" which lets me know the date and time of the report. Now I need an sql statement that would let me know how many reports are being made per day. Can someone help me out?
This is what I've been doing(see sql), but I have to change the date and then run the query for a certain date. But I want one query that will give me the count on every date. I hope someone understands what I'm talking about. Thanks.

select * from Table where
DateTime >= '2004/03/01' and DateTime < '2004/03/01 23:59:59';

View Replies !   View Related
Parameters In Reports
Anybody know, how can place more than Two Parameters in same row?



View Replies !   View Related
Parameters In Reports

Is it is possible to control the number of Parameters showing in a row ? Now, by default it shows 2 parameters in a row.

Can we decide the left and top positions of a Parameter ?


View Replies !   View Related
Reports Parameters
I use the Reports Servers Design to Design My report, but I need to use multi value fot Report Parameters, But Don.t to do.


Mulit Parameters  (Type = 'A' , 'B') 

A Value are '1' , '2'

B Value are '3' , '4'



I use SQL Script to Run


Select * From XXX


Where A IN (@Type) 


@Type may be 1,2,3,4 or 1,2 or 3,4


How to slove




View Replies !   View Related
Reports Sub Report Parameters
I get this error when a report renders that has a sub report on it. An error occurred while executing the subreport €˜subreport1€™: One or more parameters required to run the report have not been specified. It renders fine when the report itself is ran, but when trying to render it on another report it states the params are missing. How can I add the parameters from. Both of these reports have their own datasources, and they are cubes. Is there a way to do this, I tried setting theparameters by going to the sub reports (right click) properties> parameters> ... but the parameters this report needs are not there. Any help would be greatly appreciated.

View Replies !   View Related
How To Pass Parameters To Sub Reports?

how do u pass parameters to a subreport.... i tried doin it but got an error msg sayin "Sub report cannot be displayed"
any solution????

View Replies !   View Related
Combining Reports With A Set Of Parameters

Is it possible to run a report with some multiple parameters values and output it to a single file file? For example if you have a parameter list  (1,2,3) and you want to run the report for parameters (1,2). Our users  generate the  list of parameters and rather run the report for each one, they would like to  have reporting services run it for the whole set and just output 1 file.   I have searched everywhere and can't find anything that works. Someone suggested sub reports but based on what I saw, it didn't look very useful. I can go into more detail if neccessary, this has been bugging me for  a while now.

View Replies !   View Related
Cannot Deploy Reports With Parameters
I am a beginner with SQL Reporting Services and having a problem.  I can deploy reports with no parameters and the datasources fine.  But when I deploy a report with a parameter I get the following error "An unexpected error occurred in Report Processing."  I looked at the log file and this is what I found.  Any suggestions on how to fix this?
aspnet_wp!processing!7!10/03/2007-14:59:11:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An unexpected error occurred in Report Processing., ;
 Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An unexpected error occurred in Report Processing. ---> System.IO.FileNotFoundException: Could not find file 'C:Documents and SettingsD2P6PM51ASPNETLocal SettingsTempexpression_host_a23b56b0ae874a989c3e9c76fe9a07ff.dll'.
File name: 'C:Documents and SettingsD2P6PM51ASPNETLocal SettingsTempexpression_host_a23b56b0ae874a989c3e9c76fe9a07ff.dll'
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)
   at Microsoft.ReportingServices.ReportProcessing.ReportCompileTime.InternalCompile(Report report, AppDomain compilationTempAppDomain, Boolean refusePermissions)
   at Microsoft.ReportingServices.ReportProcessing.ReportCompileTime.Compile(Report report, AppDomain compilationTempAppDomain, Boolean refusePermissions)
   at Microsoft.ReportingServices.ReportProcessing.ReportPublishing.Phase3(ParameterInfoCollection& parameters, AppDomain compilationTempAppDomain, Boolean generateExpressionHostWithRefusedPermissions)
   at Microsoft.ReportingServices.ReportProcessing.ReportPublishing.CreateIntermediateFormat(CatalogItemContext reportContext, Byte[] definition, CreateReportChunk createChunkCallback, CheckSharedDataSource checkDataSourceCallback, PublishingErrorContext errorContext, AppDomain compilationTempAppDomain, Boolean generateExpressionHostWithRefusedPermissions, String& description, String& language, ParameterInfoCollection& parameters, DataSourceInfoCollection& dataSources, UserLocationFlags& userReferenceLocation, ArrayList& dataSetsName, Boolean& hasExternalImages, Boolean& hasHyperlinks)
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.CompileReport(CatalogItemContext reportContext, Byte[] reportDefinition, CreateReportChunk createChunkCallback, CheckSharedDataSource checkDataSourceCallback, PublishingErrorContext errorContext, AppDomain compilationTempAppDomain, Boolean generateExpressionHostWithRefusedPermissions, String& reportDescription, String& reportLanguage, ParameterInfoCollection& parameters, DataSourceInfoCollection& dataSources, UserLocationFlags& userReferenceLocation, ArrayList& dataSetsName, Boolean& hasExternalImages, Boolean& hasHyperlinks)
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.CreateIntermediateFormat(CatalogItemContext reportContext, Byte[] reportDefinition, CreateReportChunk createChunkCallback, CheckSharedDataSource checkDataSourceCallback, AppDomain compilationTempAppDomain, Boolean generateExpressionHostWithRefusedPermissions)
   --- End of inner exception stack trace ---


View Replies !   View Related
Drill Through Reports And Cascading Parameters
Hi, all

I created [ReportA] to present details about [ObjectA] based on a date range and object name. I show only the [ObjectA] which are available based on the dates selected, making the report employ cascading parameters.

Then I created [ReportB] to drill through to [ReportA]. It shows aggregated data about all the [ObjectA] available in charts and a matrix, including the names of [ObjectA].

I set the date range based on the date range selected for [ReportB] and the name of the object is set as the path/name of [ObjectA] (passed in the result set).

(Sorry, I meant to say that) the Report Server is telling me that

The '[ObjectA]' parameter is missing a value

Does anyone know if there is an issue with "drill-through-ee" reports which employ cascading parameters to be populated by a "drill-through-er" report?


P.S. Is this confusing enough?

P.P.S. Do I have to delete the reports and re-upload them to make this work?

View Replies !   View Related
Caption Parameters And Linked Reports
Hi there..

We are having som problems over here, when using linked reports and wants to send over parameters to a report that is using OLAP as datasource.

First problem is with "available values". When we use the "parameter" checkbox in the OLAP querybuilding area to create the parameter, it will show all the available hierarchies and values. We want to limit the available values to for example "Year-Month" instead of the standard "year-month-date".. A workaround would be to "filter" the data with ordinary parameters in datetime format, but then the report would be extremely slow (it gets all the data from the cube, then filters.. Not optimal)..

Then we have the problem of picking "default values" when linking from another report..
When we are using a parameter that gets its values from the olap cubes (as in the above example), the default value that the link tries to send over (string values) does not end up as default values on the linked report.

Is there any way to set default values to parameters that use OLAP to populate the available values, from the "outside"?

Thankful for every help I can get..


View Replies !   View Related
Using Parameters To Link Between Chart Reports
I have one report "Incident Summary" which has a Report Parameter called DimPoolPool using ParameterValue for Value Field and ParameterCaptionIndented for Label field. The default value is [Dim Pool].[Pool].[All]

From another chart report I want to be able to click on a column to make open the Incident Summary report parameterized by "Pool".  So I got to properties of the chart > Data > Values > Edit > Action > Jump to report > Parameter, and here I enter DimPoolPool = Fields!Pool.Value.

It seems to me that this should be picking up [Dim Pool].[Pool].&[poolname] and entering that into the parameters of Incident Summary report. However instead the Incident Summary opens with blank parameter waiting for user to enter.

How do you format parameters correctly between reports?

View Replies !   View Related
Links That Pass Parameters To Other Reports?

How do you create a link on one report to run another report passing a parameter (or two).


View Replies !   View Related
Deploying Jump To Reports With Parameters

I have seem many messages about this, mostly over a year old, with no satisfying answer.
I have a set or reports that jump between each other, passing parameters, that work fine in VS 2005. (They are mostly based on sql server 2005 stored procedures).
When the reports are deployed, the one that jumps without a parameter works.  The ones that pass parameters do not even 'activate'- that is, the text box to be clicked does not allow the user to click.
If I go to the report directly through the browser, it lists the missing parameters and the report does not render.
I am using IE7.  All SP on IE and SQL are current.
Microsoft- will you be making this feature work anytime soon?

View Replies !   View Related
A Zillion Reports When Using Cascading Parameters?
I need the ability to choose between quite a few cascading olap time query parameters for a report without my creating a report or subreport for every combination of these parameters. If the recordset were smaller perhaps I could filter it when it's passed back to the report but it's a few million. Will switching to expression-based command text and building the mdx queries in code work? I would like to be able to tell rs to remove the parameter from the query if no selections are made.

View Replies !   View Related
Crystal Reports Parameters From One Form To Other

hi friends,
i am having one crystal report from and one web application from which contains one crystal reportviewer i need to give the parameter in the web form and get the data for that parameter untill this its working fine for me.
my problem i need to display the value in the crystal report which i gave in the web form.

View Replies !   View Related
SP With Variable Number Filled Of Parameters
I have a SP search_post (e.g)

@id int,
@author varchar(40),
@keyword varchar(40),

select * from
id = @id and author =@author and message like @keyword

in my case a user can choose to search by one or more of this elements.

what will be the best approach to write a statement that will account for the posibility of the user leaving some "parameters" empty, while also allowing for the posibility that all parameters could be filled

one approach I have thought out is this

if (@id IS NOT null)
select * from
posts where id = @id

if (@author IS NOT null)
select * from
posts where author= @author

if (@keyword IS NOT null)
select * from
posts where keyword = (@keyword

but this does not really take care of the posibility that all of them or some of them will be null while others will not

any suggestions ?

View Replies !   View Related
Help Designing Query For Any Number Of 5 Parameters
Hello everyone,

I'm having trouble designing a query for an ASP.NET project I'm working on.  The main portion of the query is done and works, but I need to modify it to accept 5 parameters.  Below is the main query:

Code Block

@LocID as nvarchar(10)

SELECT dbo.ProfileNames.Name, dbo.AircraftGateInput.*
FROM dbo.ProfileNames
LEFT JOIN dbo.AircraftGateInput
ON dbo.ProfileNames.Name = dbo.AircraftGateInput.Gate
WHERE dbo.ProfileNames.Type = 'Gate' AND dbo.ProfileNames.Location = @LocID
The changes I need to make to this are confusing me because I've had to do something like this before.  There are 5 DropDownLists on the page the user can select as parameters for searching/filtering the information.  The query above is what's being used now, and causes the page to dynamically generate a certain number of colums based on the number of returned columns.

The parameters that are available are: Gate, Location, Offload Zone, Onload Zone and Equipment.  I wwant the user to be able to select all or any number of these as search options but I'm not really sure how to design the query to do this.  A co-worker suggested doing something like this:

Code Block

@LocID as nvarchar(10),
@Gate as nvarchar(50),
@Location as nvarchar(50),
@OffLZ as nvarchar(50),
@OnLZ as nvarchar(50)

SELECT dbo.ProfileNames.Name, dbo.AircraftGateInput.*
FROM dbo.ProfileNames
LEFT JOIN dbo.AircraftGateInput
ON dbo.ProfileNames.Name = dbo.AircraftGateInput.Gate
WHERE (dbo.ProfileNames.Type = 'Gate') AND (@Gate IS NULL OR dbo.ProfileNames.Name = @Gate)

AND (dbo.ProfileNames.Location = @LocID)

     AND (@Location IS NULL OR dbo.AircraftGateInput.Location = @Location)
     AND (@OffLZ IS NULL OR dbo.AircraftGateInput.OffLZ = @OffLZ)
     AND (@OnLZ IS NULL OR dbo.AircraftGateInput.OnLZ = @OnLZ)
At this point I'm not a 100% sure on how to test this query in the query designer in Management Studio 2005 (this is also a .NET 2.0 project).  If anyone can help, I'd really appreciate it, thanks.

View Replies !   View Related
What Is The Maximum Number Of Rows Retrieve From A Table To Reports
hi friends,

                   i got a error while retrieving more than 100000 rows (records) from a table .. can any one tell me  what is the maximum number of rows retrieve from a database to reports... and how can i overcome this issue...


View Replies !   View Related
Selected Date Parameters Not Working In Reports
Hi all,

I'm looking to return certain rows from our db into SRSS based on a user selected date range using the parameters calendar.

My query returns all required fields/rows, but how do I
pull the specific rows, (that are based on a date range that the user enters),into the report? I've tried expressions, and vb functions to no avail. The users will be using the calendar parameter to select dates.So far the reports pull in all rows from my query..



View Replies !   View Related
Cascading Parameters In Report Builder Reports!
Is it possible to create cascading parameters with in Report Builder? I have done this with Report Designer but i need a solution with Report Builder.

(I need the structure: First the user selects the state, afer the postpack the cirties of the state are listed in another dropdownlist for selection).

Thanks in advance

View Replies !   View Related
Filtered Report Parameters For Custom Reports

I am trying to build a report that allows a user to enter partial text that filters the values in a drop down
For examle;
Lets say I am selecting a value from a drop down for States and I want to type in FL to filter the collection down so that only Florida appears in the select list. 
I know the above example wont work since I cannot actaully enter text into a drop down, however
I have also tried to seperate this functionality by creating one report parameter for entering the search text and another parameter that will display a filtered data set using the text entered in the first parameter.
Is this something that reporting services can handle ? If it is please provide a brief example.

View Replies !   View Related
Sub: How To Pass Multiple Parameters Across Reports In SSRS

I am working on SSRS. I need to open a new report from one report when user clicks on some

particular summarized count link.

It is a sort of drilled down report. I am not getting how to pass the respective Ids (more

than one) to the next report when user clicks on the summarized link in the 1st report.

These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from

the database.

View Replies !   View Related
How To Pass Multiple Parameters Across Reports In SSRS


I am working on SSRS. I need to open a new report from one report when user clicks on some

particular summarized count link.

It is a sort of drilled down report. I am not getting how to pass the respective Ids (more

than one) to the next report when user clicks on the summarized link in the 1st report.

These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from

the database.

View Replies !   View Related
Cascading Parameters In Adhoc Reports - Report
HI All,
         Please tell me whether the Cascading parameter feature of Report Designer can also be implemented in Report Builder - Adhoc Reports also. Any work arounds needed for this..


View Replies !   View Related
Passing Multi-Valued Parameters Between Reports.

I have report A and Report B. In Report A I am using the Jump to Report functionality to go to Report B. I have a multi-valued parameter in botht the reports. So In Parmeters I am giving
Parameters!xx.value to pass the value from Report A to Report B. The multi valued parameter has values 1,2,3,4 in both the reports.
I have a scenario where I need to pass a value of 5 to the report B. When I try to hard code the value I run into error while going to the second reeport saying parameter not declared.
Can any one help me with this. Its very urgent.

View Replies !   View Related
Maxmimum Number Of Parameters For OleDB Command
I have a stored proc with 28 commmands but only 24 are loaded. If I move the parameters around the ones that didn't show are visible and the ones after the 24th parameter don't show up. Is there some sort of limit on parameters for OLEDB commands that execute stored procs in the exec procname @p = ? construct?

View Replies !   View Related
Stored Procs With Variable Number Of Parameters
I have received a change request for a project i am working on which im unsure if possible or not.

at the minute i have a very simple sp that simply selects data from a table by passing in one parameter


@int_ID int


 SELECT GradeID as ID, Grade as Description, RequiresText, SendEmail, Timestamp
 FROM tbl8Grade
 WHERE Obsolete=0
AND GradeID = @int_ID

the user now wants the option to select more than one grade type (there are 100's of different grades)

so they might decide they want to see details for 2 grades, 22 grades etc. these would be selected and stored in an array (via an project) and then the stored procedure is called from within a web service

my question is how would i pass this array into the stored procedure??

i am assuming i would need to do something as follows for sp syntax, but im stumped on how i pass my array of values into the sp from the webservice and then for the sp to read the array in SQL??


@myArray <what datatype?>


 SELECT GradeID as ID, Grade as Description, RequiresText, SendEmail, Timestamp
 FROM tbl8Grade
 WHERE Obsolete=0
AND GradeID IN (@myArray)

any ideas are greatly appreciated. or maybe its just not possible?!?


View Replies !   View Related
Passing Different Number Of Parameters To A Stored Procedure
Hi to all,

How can I Pass different number of parameters to a Stored Procedure?

In my Requirement,

Some times i want to pass 2 parameters only,

In some cases i want to pass 6 parameters.

How can i do this?

Please give me a solution.

Thanx in advance...

View Replies !   View Related
Passing Parameters To Stored Proc From Crystal Reports
Hi,I've written reports based on stored procedures that receive parametersand it usually seems straight forward.Today I would like to use Crystal Reports parameter interface t promptfor a "Date Range" and then pass the lower and upper date values to the@StartDate and @EndDate of my SPROC's range.Unfortunately when I try to build a report off of a SPROC havng twodates it insists on prompting for date AND time indivually.Is there a way to disable the "automatic prompting", use my own DateRange parameter and then pass the results of the date range to thestored procedure?Thanks

View Replies !   View Related
Unable To Set Report Parameters To SQL Reports Thorugh Code
Hi ALL, I am developing a tool in which which will generate a set of reports based on dates for list of parameters . i m trying to set these parameters at runtime from code. the reports which are hoseted at the report server has the following reports parameters which is defined in RDLs. the parameters names analyst regionid publishstartdate publishenddate readstartdate readenddate analyst parameter is a multivalue parameter. and also the value field for the analyst parmater is PersonID. since the RDL's developed are using stored procedures which are embeded as execute SP_name with parameters as @list,@region,@publishstartdate,@publishenddate,@readstartdate,@readnendate when i set the parameter as analyst or personId or @list in the below code Dim encoding As String = "", mimeType As String = "" Dim issueStartTime As Date = DateTimePicker1.Value Dim issueEndTime As Date = DateTimePicker2.Value Dim readStartTime As Date = DateTimePicker4.Value Dim readEndTime As Date = DateTimePicker3.Value Dim paramValues(5) As ParameterValue Dim parameters(5) As ReportsGenerator.ReportBD.ParameterValue Dim paramOut As New ParameterValue() Dim warnings(0) As Warning Dim streamIDs(0) As String Dim output As Byte() Dim i As Int32 i = 1 Dim execInfo As New ExecutionInfo Dim historyID As String = Nothing Try '@List, @Region, @PublishStartDate, @PublishEndDate, @ReadStartDate, @ReadEndDate While i <= list.Count ReDim paramValues(5) paramValues(0) = New ReportsGenerator.ReportBD.ParameterValue() paramValues(0).Label = "Analyst" paramValues(0).Name = "Analyst" paramValues(0).Value = CType(list(i)(0), Integer) --//function which returns a set of parameters value from notepad 'paramValues(0).Value = 2298 paramValues(1) = New ReportsGenerator.ReportBD.ParameterValue() paramValues(1).Label = "Region" paramValues(1).Name = "Region" paramValues(1).Value = -1 paramValues(2) = New ReportsGenerator.ReportBD.ParameterValue() paramValues(2).Label = "PublishStartDate" paramValues(2).Name = "PublishStartDate" paramValues(2).Value = issueStartTime paramValues(3) = New ReportsGenerator.ReportBD.ParameterValue() paramValues(3).Label = "PublishEndDate" paramValues(3).Name = "PublishEndDate" paramValues(3).Value = issueEndTime paramValues(4) = New ReportsGenerator.ReportBD.ParameterValue() paramValues(4).Label = "ReadStartDate" paramValues(4).Name = "ReadStartDate" paramValues(4).Value = readStartTime paramValues(5) = New ReportsGenerator.ReportBD.ParameterValue() paramValues(5).Label = "ReadEndDate" paramValues(5).Name = "ReadEndDate" paramValues(5).Value = readEndTime If Not Directory.Exists(folder) Then Directory.CreateDirectory(folder) End If execInfo = rsproxy.LoadReport("/ReadershipReports_Groups/Other Readership Rankings", historyID) rsproxy.SetExecutionParameters(paramValues, Nothing) output = rsproxy.Render("PDF", Nothing, "", mimeType, encoding, warnings, streamIDs) If output.Length <= 2500 Then GoTo cleanup End If Dim writer As New BinaryWriter(File.Open(folder + "" + CStr(list(i)(2)).Replace("", "_") + "_" + CStr(list(i)(3)).Split("/")(2) + "_Other Readership Rankings" + TextBox1.Text + ".pdf", FileMode.Create)) writer.Write(output) writer.Close() cleanup: writer = Nothing output = Nothing i += 1 End While Catch ex As Exception MsgBox("Failed to generate report. Error: " + ex.Message) End Try and run to genrate throws me the below error "This report requires a default or user-defined value for the report parameter 'Analyst'. To run or subscribe to this report, you must provide a parameter value. ---> This report requires a default or user-defined value for the report parameter 'Analyst'. To run or subscribe to this report, you must provide a parameter value. ---> This report requires a default or user-defined value for the report parameter 'Analyst'. To run or subscribe to this report, you must provide a parameter value." for second time i changed instead of parameter name i changed to PersonID which is valuefield in RDL's report parameter and based stored procs parameter value i.e @list but its not understanding which parameter to set. if you have any suggestions to fix this issue please reply . thanks charles

View Replies !   View Related
Reports Running Automatically When All Parameters Had Default Values
If all the parameters for a report have default values, the report runs automatically as soon as you open it. Is there a way to prevent this?

View Replies !   View Related
SqlDataSource With Different Number Of Columns And Insert Parameters And FormView
Dear All,
i have a SqlDataSource with a simple select command(e.g. "select a,b,c from foo"). The insert command is a stored procedure and takes less parameters than there are columns in select statement (e.g. "insertFoo(a char(10))").
When used in combination with form view, i get "Procedure or function insertFoo has too many arguments specified" error.
It seems that form view always posts all columns as parameter collection (breakpoint in formview_inserting event shows this) to insert command.
Am I doing something wrong or is this by design? Is the only solution to manualy tweak parameters in formview_inserting event?

View Replies !   View Related
Stored Procedure - Variable Number Of Parameters For Search
I have a repeater control which I populate with search results from SQL Server.

But I can't figure out how to cope with users who submit multiple search items and still use my stored procedure. Is this possible or do you have to build the query with a StringBuilder and execute it manually?

I'm using a stored procedure with parameters:

input parameters <-- PageSize & CurrentPage
output parameter --> TotalRecords

Am using a temporary table to store all records before Select-ing those required for the particular page.

If I compose the query manually then I can't figure out how to get TotalRecords back as a return parameter. Would appreciate help on this one.

Am hoping that stored procedures can cope with an unknown number of parameters.

View Replies !   View Related
Passing In Variable Number Of Parameters To A Stored Procedure
I am fairly new to MSSQL. Looking for a answer to a simple question.

I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.

The structure of this table is something like

email address

customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...

Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?

Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.

Any other ways to do this? Thanks

View Replies !   View Related

Copyright © 2005-08, All rights reserved