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


Top 5 Contributors of the Month
david stephan
Asad Ali
Fauzul Azmi
Post New Web Links

MS Report Viewer not displaing all user selected values, but when testing SQL Stored Procedure & Dat

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :
 

Hi All,

I resently got some great help on a recent posting of mine.  However now in further testing I've noticed a problem with the postback.  I'm using MS Report Viewer, and when I ran the SQL Stored Procedure, as well as testing the Dataset (xsd), both returned all the variables I inputed manually.  However from the site, the report viewer is only returning 1 or 2 of the 4 variables I submitted.

Here is the Stored Procedure:

ALTER PROCEDURE dbo.GetDownloadDetails_Soft
 (@ProgramName varchar(2000))
AS
 DECLARE @x XML
SET @x = '<root><x>' + REPLACE(@ProgramName, ',', '</x><x>') + '</x></root>'

SELECT
StatusDate,
ProgramName,
UserFullName,
LoginName,
CostCenterCode,
EmailAddress,
Phone,
Manager,
ComputerName,
SCCMSiteCode,
Status,
FailureReason,
PONumber
FROM Installs
WHERE ProgramName IN (
 SELECT x.value('.', 'VarChar(2000)')
 FROM @x.nodes('/root/x') AS a(x))
ORDER BY StatusDate

On the website, a user selects the programs they want to generate a report from.  Once selected they click on a "Generate Report" button, which does:


View Complete Post


More Related Resource Links

how to include a user defined table type as input for stored procedure

  
Hi ,  I have a user defined table type which i need to pass as input parameter to the stored procedure .How can i do that?

How do I insert values into different colunms but same row in a table using stored procedure

  
Hi everyone, I wish to create a scenario whereby my Stored Procedure will be able to do an Insert values into two colunms but single row of a table. For instance: AS BEGIN INSERT INTO Table1 (Message1, Number1) VALUES(@Message1, @Number1) END When I do the above stored procedure using SQL Express, and Excecute the stored procedure in VS2005 C# using ADO.Net, the table is updated but the values of Message1 and Number1 are inserted into different rows i.e. when value is inserted into Message1, Number1 is '0' and vice-versa. what I really want is for both Message1 and Number1 to be inserted with values at the same row. How do I acheive this please? Many thanks in advance.

How do I insert values into different colunms but same row in a table using stored procedure

  
Hi everyone, I wish to create a scenario whereby my Stored Procedure will be able to do an Insert values into two colunms but single row of a table. For instance: AS BEGIN INSERT INTO Table1 (Message1, Number1) VALUES(@Message1, @Number1) END When I do the above stored procedure using SQL Express, and Excecute the stored procedure in VS2005 C# using ADO.Net, the table is updated but the values of Message1 and Number1 are inserted into different rows i.e. when value is inserted into Message1, Number1 is '0' and vice-versa. what I really want is for both Message1 and Number1 to be inserted with values at the same row. How do I acheive this please? Many thanks in advance.

How can I replace values in GridView returned from a Stored Procedure

  
I am working on creating a stored procedure that will output a pivot table.  In the pivot table will be either the string NULL or a number.  How can I reformat this in ASP.NET so the NULL value becomes a blank cell in the gridview and the number (whatever it is) becomes an 'X' ?

Entity Model- Add Function Import - selected stored procedure returns no columns

  
I am using stored procedures returns columns from pivot table generated dynamically "Execute (@PivotTableSQL)".So the resultset columns cannot be identified. Please let us know how to add function import and Get Column information(create complex Type). Thanks in advance. My stored procedure is alter PROCEDURE spGetQuestGrid( @QID as int)ASBEGIN SET NOCOUNT OFF SET FMTONLY OFF  Declare @optId varchar(10) set @optId = (select DISTINCT Optid from  QuestOptions where  QID = @QID)    DECLARE @PivotColumnHeaders VARCHAR(MAX) SELECT @PivotColumnHeaders =  COALESCE(@PivotColumnHeaders + ', [' + cast(Caption as varchar) + ']' ,  '[' + cast(Caption as varchar)+ ']' ) FROM OptionsDetail where OptId = @optId  DECLARE @PivotTableSQL NVARCHAR(MAX)  SET @PivotTableSQL = 'select * from  ( select  Caption,Optid  from OptionsDetail ) as dt PIVOT  ( min(Optid) FOR Caption IN ( '+ @PivotColumnHeaders + '))as pt'     Execute (@PivotTableSQL)  END 

update stored procedure for existing values of column(salperyr)

  
tbl_salary salary           salperyr   hike20      hike20yr10000.0000    NULL      12000.00    144000.0012000.0000    NULL      14400.00    172800.0014000.0000    NULL      16800.00    201600.0015000.0000    NULL      18000.00    216000.0018000.0000    NULL      21600.00    259200.0020000.0000    NULL      24000.00    288000.0022000.0000    NULL      26400.00    316800.00in above table salaryper yr (salperyr) has to be modified after caliculation my null values has to be removed and place (salary*12)in one shot.so plz suggest me update stored procedure for this.

update stored procedure for existing values of column(

  
tbl_salary salary            salperyr    hike20       hike20yr 10000.0000    NULL      12000.00    144000.00 12000.0000    NULL      14400.00    172800.00 14000.0000    NULL      16800.00    201600.00 15000.0000    NULL      18000.00    216000.00 18000.0000    NULL      21600.00    259200.00 20000.0000    NULL      24000.00    288000.00 22000.0000    NULL      26400.00    316800.00 in above table salaryper yr (salperyr ) has to be modified after caliculation my null values has to be removed and place (salary*12 )in one shot.   so plz suggest me update stored procedure for this

Testing a stored procedure

  
I am looking to test a stored procedure which returns some sets of rows of data, the problem that I have is that the stored procedure is called within a loop and I want to get a feel for the time it takes, now if I call the stored procedure in a loop with the parameters, It needs to run for about 2,000 times, but if i  use management studio for this, then the results will come back to the screen and fill it up, is there a way for me to test such that the results will get overwritten and not fill up the result display window in management studio, yet I want to track how long the stored procedure is running for each call.

Report viewer web part: Dependency of parameter choices on user rights?

  

Hi there,

I am wondering if it is possible to restrict the choices for a parameter depending on the user rights. For instance, if an "administrator" can select three different options for one particular parameter, a "normal user" should only be able to choose from two.

I am using Sharepoint 2007 and Reporting Services 2005.

Best wishes

Oliver

 


How do I call an SSRS Report from a stored procedure, and pass parameters?

  

Hi all,

I have a Report, that I want to run ad-hoc from a Stored Procedure. 

I want to render this report to PDF format, and save it to a drive.

The difference between this post and most threads I've seen is I don't want to run a stored proc within the report and send parameters to the stored proc.

I DO want to call a report FROM the stored proc, and send parameters TO the report.

How can I do this?

Many thanks,

Jason


Rdl Report's Dataset and its associated Stored Procedure

  

Hi,

 

I have to design a Page Similar to Report Server's Viewer Page where if any Parameter is multivalued and if any Stored Procedure/Query Name is specified in the Report, then a it is internally executed and checkbox dropdown list is displayed to select the values.

Similar to that I have to design an aspx page with the similar behaviour. How to get Stored Procedure Name(if Specified/Query) of any Parameter of a report? So that I can bind my Checkbox list on the aspx page.

 

Please Help!


Unexpected Error when running large stored procedure report in SharePoint

  

I have a report which uses a stored procedure to retreive a resource intensive dataset from a Data Warehouse.  With a small scope it renders in SharePoint no problem, but when a larger scope is specified ShrePoint throws this non-descript error. 

I can execute the procedure in SQL Server Management Studio, and though it takes 2+ minutes with the larger scope (30 sec with smaller) it does return the data.  Similarly, in Business Intelligence Development Studio it returns the rendered report with data after 2-3 minutes.

Only in SharePoint and only with a larger dataset does this occur.  Timeout is set at 30 minutes and it occurs long before then.  SSRS server has over 12 gigs memory, running SQL Server 2008, SharePoint 2007 is in integrated mode. No errors in SharePoint server or SSRS server logs.  Ideas?


Getting Values output from Stored Procedure

  

Hi,

 

From this below stored Procudure, using VB.Net code can i get the Values like "Number of Rows Affected" and "Yes / No" Value from "Select"

 

Stored Procudure:

ALTER PROCEDURE [dbo].[lookup]  @week_date datetime

AS

DECLARE @currweek datetime,  @inweek datetime

IF @week_date = '10/14/2010'

BEGIN

  UPDATE dbo.work_status SET  ob_flag = 0, ob_update_date = NULL WHERE dbo.work_tatus.week_date ='10/14/2010'

  SELECT 'YES'

END

ELSE

  SELECT 'NO'

RETURN 0

 

 

 

 


Unexpected Error when running large stored procedure report in SharePoint

  

I have a report which uses a stored procedure to retreive a resource intensive dataset from a Data Warehouse.  With a small report it renders in SharePoint no problem, but when a larger scope is specified ShrePoint throws this non-descript error. 

I can execute the procedure in SQL Server Management Studio, and though it takes 2+ minutes with the larger scope (30 sec with smaller) it does return the data.  Similarly, in Business Intelligence Development Studio it returns the rendered report with data after 2-3 minutes.

Only in SharePoint and only with a larger dataset does this occur.  Timeout is set at 30 minutes and it occurs long before then.  SSRS server has over 12 gigs memory, running SQL Server 2008, SharePoint 2007 is in integrated mode. No errors in SharePoint server or SSRS server logs.  Ideas?



-Erik

Looping through Textbox Values and Inserting into SQL Server via Stored Procedure

  

Hello All,

I have a database table as follows:

USE [Diel_inventory]
GO
/****** Object:  Table [dbo].[QUOTEDETAILPARTS]    Script Date: 05/09/2010 17:42:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QUOTEDETAILPARTS](
	[QuoteDetailPartID] [int] IDENTITY(1,1) NOT NULL,
	[QuoteDetailID] [int] NOT NULL,
	[PartNumber] [float] NULL,
	[Quantity] [int] NULL,
 CONSTRAINT [pkQuoteDetailPartID] PRIMARY KEY CLUSTERED 
(
	[QuoteDetailPartID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[QUOTEDETAILPARTS]  WITH CHECK ADD  CONSTRAINT [fkQuoteDetailID] FOREIGN KEY([QuoteDetailID])
REFERENCES [dbo].[QUOTEDETAIL] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[QUOTEDETAILPARTS] CHECK CONSTRAINT [fkQuoteDetailID]


This table receives data from my web application via a stored procedure, snippet pasted below:

 

nsert into dbo.QuoteDetailParts
	(QuoteDetailID, PartNumber, Quantity)
	values (@QuoteDetailPartID, @PartNumber, @Quantity) 
	
	set @QuoteDetailPartID=scope_identity()

 

In my quote.aspx page, I have a wizard control that collects numerous data points.  In one of the wizard steps, I hav

SQL Server 2005 Grant User Permission to Create Stored Procedure and Grant Execute Access to another

  

I'm trying to determine the minimal amount of access I can give my developers to be able to run create stored procedure scripts with an inclusion of the grant execute to the application user group.

We have been developing some interesting work arounds, but I think there must be an easier way to grant the access.

Grant Create Stored Procedure to user1 on a specific database

Grant Permission to Grant Execute permission for Stored Procedures on a specific Database to user1 for any user.

 


Passing the values from the Deleted virtual table to another Stored Procedure

  

Hello,

I am wondering if something can be done.  I have a large database that I would like to implement change tracking on selected tables.  We've come up with a way to do it the least painful way we can, but I do not know if it will work.

I've created a trigger that will pass the table name to a stored procedure.  That stored procedure creates a Tracking table by appending the suffix 'Changes' to the past in table name.  But now how do I get the data from the Deleted table?

I can't do a SELECT INTO or INSERT INTO because the tables have timestamp fields and blobs.  I don't care if I do net get the data from those fields but I don't want to have to enter all the field names besides those two types.  I can make a stored procedure get the column names and types from a system table and dynamically build the SQL.  But I would prefer to do that in one place instead of each trigger.

Basically the problem is how do I get the data from the Deleted table to another stored procedure without explicitly naming all \the fields in the trigger.

If you don't understand what I mean then please ask, I will clarify with examples.

Thanks

JM


Categories: 
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