Stored Procedure And Crysral Report
Apr 19, 2004i have the stored procedure in the SQl and i m using the crystal report how to pass the paramaters to the stored procedure ..from the crystal report .:confused:
pls its urgent:mad:
ADVERTISEMENT
Results From More Than Stored Procedure In A Report
Sep 23, 2004Hi,
Is there a way I can display the data returned from more than one stored procedure in a report using reporting services. Or can I use more than one dataset in the report????
Can Report Service Run A Stored Procedure?
Jul 16, 2007Hi, there,
I have a report that requires to join a lot of tables.
May I ask if I can use a stored procedure to make all the joins and populate a temp table, then make the report sit on the temp table. so every time the user runs the report, it will kick off the stored procedure which will truncate the temp table and repopulate it, then show the report.
Thanks
Creating A Report From A Stored Procedure
Aug 22, 2007Hi
I'm not sure if this is possible, i want to create a report from a stored procedure, all the stored procedure does is select data from my database, however, the code is 1800+ lines of code, it executes in about 1 minute, which is fine. My problem is that i can't paste 1800+ lines of code into the dataset in Reporting Services, so is it possible to get the values from my stored procedure into reporting services so that i can use them to design and execute my report?
Or musst i find an alternative way to do this?
Any help or suggestions will be greatly appreaciated and welcome.
Thanks in advance
Kind Regards
Carel Greaves
How To Create A Report Against A Stored Procedure
Nov 14, 2007Using sql server reporting services, I want to create a report against a stored proc which has input parameters to the stored proc. Thanks!
View 5 Replies View RelatedUsing Stored Procedure In Report Designer
Jul 11, 2007I have the following stored procedure. I have a few questions regarding its use in Reporting Services.
1) Invalid Object ##Tmp_Table1, this prevents the report designer from displaying the available fields.
2) The @totalcount isn't available (I need this value for a calculated field in the report)
3) How would I go about creating dropdowns/calendar controls for users to fill in the parameter values?
ALTER PROCEDURE [dbo].[spTermCodeReport]
@tablename char(12) = 'results_NAF',
@source varchar(20) = 'AmExpProvList',
@sourcedate datetime = '05/30/2007',
@TotalCount int = 0 OUTPUT
AS
DECLARE @SQLString varchar(255)
BEGIN
SET NOCOUNT ON;
SELECT @SQLString = 'SELECT count(*) AS TRCount INTO ##Tmp_Table1 FROM ' + @tablename + ' WHERE source=''' + @source + ''' and sourcedate=''' + convert(varchar(10),@sourcedate,110) + ''''
EXEC(@SQLString)
SELECT @TotalCount = TRCount FROM ##Tmp_Table1
DROP TABLE ##Tmp_Table1
SELECT @SQLString = 'SELECT termcd, count(termcd) AS TermCDCount FROM ' + @tablename + ' WHERE source=''' + @source + ''' and sourcedate=''' + convert(varchar(10),@sourcedate,110) + ''' GROUP BY termcd'
EXEC(@SQLString)
END
Passing A Parameter Into A Stored Procedure In A Report ..........
May 8, 2007Hi, I have found this question asked many times online, but the answers always consisted of getting a parameter from the user and to the report. That is NOT what everyone was asking. I would like to know how to pass a parameter I already have into my stored procedure. Using Visual Studio 2005, in the "Data" tab of a report calling something likeUnique_Login_IPsreturns correctly when that stored proc takes no params, but when I have one that takes the year, for example, I do not know how to pass this info in. Note: the following does not work in this context : EXEC Unique_Login_IPs ParameterValue nor does EXEC Some_proc_without_paramsVisual studio seems to want only the procedure name because it returns errors that say a procedure with the name "the entire line above like EXEC Unique_Login_IPs ParameterValue" does not exist.... Thanks in advance,Dustin L
View 1 Replies View RelatedWriting A Monthly Report With Stored Procedure
Aug 13, 2007Hi Guys,
I need some help and suggestion to rewrite one of my screens (using ASP.NET) which is using stored procedure. The processing on this screen is taking more than 3 minutes (which i know is totaly
unacceptable). I am making use of cursors within the stored procedure (SQL Server 2005). I really intend to get rid of cursors as they have their performance hit. I have been told to rewrite this screen
(or the stored procedure) so i need some help for SQL Gurus. Following are the details:
1. This is a Monthly Employee Attendance Report on a day by day basis for any given month (maximum 31 days in a month)
2. The values (for each day) have to be computed at runtime and not stored. e.g. Since an employee may have signed in/out several times in a day
3. There are around 500 employees data im dealing with
4. The user will select any given department and employee's data for the respective department has to be displayed for any given month
5. If the user selects [All Department], the entire 500 employees have to be displayed on the screen
6. This report will look like an excel report on the screen i.e. Employee's basic info and record of 31 days (maximum days in a month) are displayed in one row for each employee
7. This report involves are 7-8 tables. 7 tables are for employees basic info whereas one table has the attendance record
Kindly give me your suggestion on writing the SQL stored procedure. I cannot use any other option such as a real Excel Sheet or anything. I need suggestion on how to write this monthly report. By the
way, we dont intend to Cache the data since the report can be viewed at anytime of the day, so fresh data is required everytime. Also the data for 500 employees may be too much to be cached. Also in
the attendance table, we are dealing with approximately half a million attendance records.
Thanks and waiting for your suggestions...
Report Based On Stored Procedure - ODBC
Jun 25, 2001Can anyone tell me if this can be done?
I'm using CR8 against SQL Server 7 and am trying to use a stored procedure as my data source.
Basically my boss would like to move all the code that is now client-side(formula fields, parameters, suppressions, etc.) to the server-side.
I connect by Database>ODBC and then choose my sp here.
I get the error: "There are no fields in the file"
All my sp is doing is accepting two parameters: a report type and a user name, and then generating a report based on this data.
I can post the exact sp but it is a pretty long IF THEN ELSE block.
I checked the Seagate site and it said to convert the database driver to the native driver;
I guess this would mean to connect by: Database>More Data Sources>Microsoft SQL Server
But we need to connect by ODBC since we don't want the popup for the Login to Database, Server, etc., to be entered by the client.
Can someone tell me if there is a way around this to connect by ODBC using stored procedures.
Thanks in Advance.
SQL Stored Procedure To Populate Crystal Report
Mar 15, 2004I'm working on a stored procedure to populate a Crystal report. My company insists that we put the report parameters in the stored procedure instead of in Crystal...so that the SQL server (rather than the desktop)does the work of restricting the data. Is there anything I can do on the SQL side(possibly User Defined Data Type) to get Crystal to prompt me for a date WITHOUT the time? I started with this:
CREATE proc uspReportData @BeginDate datetime, @EndDate datetime
When Crstal prompts me for the parameters, I can type the date or use the calendar to pick a date, but I AM FORCED to enter a time. I know I could choose to ignore the time in the stored procedure, but the users don't want to see the time section of the parameter. Apparently SQL doesn't have a plain "date" parameter without a time.
I've also considered this:
CREATE proc uspReportData @BeginDate char(10), @EndDate char(10)
However if I do it this way, I can't seem to find a way to make sure a valid date is entered when Crystal prompts the user for the dates.
If using char(10) turns out to be the best method, is there a way I can pre-populate the Crystal prompts like this:
@BeginDate = 1st day of the current month
@EndDate = the current system date
Crystal seems to allow hard coded default values, but I can't find a way to do calculated default values.
I'm open to suggestions.
Thanks,
Mike
Calling A Stored Procedure From Crystal Report
Aug 7, 2007I've tried to find this documented on the Internet and found Crystal Reports User's Guid online documentation (582 pages of it!) It looks like it will be helpful for my other questions, but what I'm trying to do right now is call a stored procedure from my crystal report. So in Field Explorer under Database Fields my stored procedure is there with the three columns it selects. How do I put these three columns on my report? I try dragging them to Section 3 details and they're there but when I preview the report all I get are the headers, not the records. Is there something else I need to do? It requires an input parameter which I am not getting prompted for - maybe that is my problem, without an input parameter it can't successfully run the stored procedure, so how do I make it prompt me?
Thanks for helping me with my first Crystal Report!
CLR Stored Procedure Not Visible In Crystal Report XI
Jun 29, 2007Hi guys
i am developing a Performance Management System for my organisation and reports are quite complex. so i thought to create a CLR stored procedure and then use this in Crystal Report.
The problem I am facing is that the CLR stored procedures I create using Visual Studio 2005 are not visible from Crystal Reports XI come with Visual Studio 2005. I can create stored procedures using standard transact sql and these are visible, but the CLR stored procedures are not.
I Simply create a CLR store procedure in Visual Basic and debug it. This CLR procedure then appeares under stored procedure node in SQL Server 2005.
I've noticed when I browse the stored procedures in my SQL Management Studio that the image of the stored procedure has a padlock shown in the icon, as if they are locked?
Do I have to explicitly enable a security attribute on CLR stored procedures to make them visible?
any help would be highly appretiated.
best regards
Ali
Problem Report Binding With Stored Procedure.
Mar 12, 2008Hi all,
I have some problem about bind field from stored procedure to report.
Now I have dataset that come form stored procedure. In Dataset Dialog, "Fields" tab, I add Fieldname like fieldname in stored procedure. And "Parameters" tab, I add parameters those are neccessary for my stored procedure.
I test on data tab view. When I click "!" to run, then there is input dialog parameter.
I filled it, then the result is ok.
But when I build project, I noticed warning message....
[rsMissingFieldInDataSet] The data set €˜MyDataSet€™ contains a definition for the Field €˜MyField€™. This field is missing from the returned result set from the data source. c:.....visual studio 2005projects
eport project1
eport project1Report7.rdl 0 0
[rsErrorReadingDataSetField] The data set €˜MyDataSet€™ contains a definition for the Field €˜MyField€™. The data extension returned an error during reading the field. There is no data for the field at position 6. c:.....visual studio 2005projects
eport project1
eport project1Report7.rdl 0 0
After I build my project, I run the report, each textbox that binded field, display empty value.
What happen with my report? And how can I solve this problem?
Thank you very much.
Problem With Accessing Stored Procedure In The Report
Mar 14, 2007Hi,
I am new to Sql Server 2005 Reporitng Services. I created a report in BI and used stored procedure as a dataset. When I run the report in preview mode it works fine and when I run it in report server/report manager, I am getting the following error:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'dsetBranch'. (rsErrorExecutingCommand)
Could not find stored procedure 'stpBranch'.
But I have this procedure in the db and it runs fine in the query analyzer and the query builder window in report project. When I refresh the page in Report manager, I am getting this error.
Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[FormatException: Input string was not in a correct format.]
System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2753715
System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +102
Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +149
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
I have changed the dataset from procedure to a sql string and the report is working fine everywhere. But I have a business requirement that I need to use a stored procedure.
I am not sure why I am getting this error and I greatly appreciate any help.
Thanks
Using Multiple Stored Procedure In Master/detail Report
Aug 19, 2007Almost new to ssrs, accessing Oracle's stored procedures to feed the reports (no other choice), working on a master/detail report and badly stuck. This report needs to access 3 stored procedures one for master and two for small tables under it. The master's order ID is fed to the two stored procedures that may or may not pull any thing for certain order ID's
I am approaching it by creating 2 datasets and a page-long list, added desired text boxes and a table in the list, grouped the list on order ID and passing it to tables using default values etc. but keep getting the following message.
"The Group expression for the table €˜table1€™ refers to the field €˜ProductName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope".
The list seems to be associated to one dataset only and any item in it cannot refer to anyother dataset, even if I try to access the table's dataset in its properties (The 'current data set scope' implies that).
(Not sure what will happen when I will try to add the second table and dataset with the same parameter name).
How should I approach this issue. I will appreciate help, if explained in steps but won't mind a good tip.
Thanks.
Reporting Services :: Different Results In Stored Procedure And In Report
Oct 21, 2015Created a report with parameters that can have multiple values.
Ran Stored procedure with same above criteria and data is retrieved
Ran the report and no data is displayed
I am unable to figure out where it might have gone wrong.
Report Error Using Stored Procedure With Exec Sp_executesql
Feb 13, 2008Hi,
Our report is working fine with data loaded from a stored procedure (#1) that contains a fairly simple Select statement. We need the same report to work with a dataset loaded from a stored procedure (#2) that uses 'Exec sp_executesql @queryString'. Unfortunately, attempts to call the latter cause an error in the report. From everything that I've read, there should be no difference between datasets created using either method. Any ideas what could be getting in the way of the latter?
I have doublechecked that the dynamic query is returning a valid dataset and that all the columns are in the same format as sp #1. The designer shows the dataset and the report with the data loaded, but the live system produces an error.
Any help is much appreciated.
Debbie
Stored Procedure Works In Dataset Editor, But Not In Report
Jun 21, 2007I have a stored procedure that works in my dataset editor, but when i try to run the report, only the "amount" field shows up. Everything else is blank. why is this happening. Here is the stored procedure.
USE [RC_STAT]
GO
/****** Object: StoredProcedure [dbo].[PROC_TE_MKT_DETAIL_EXPENSE] Script Date: 06/21/2007 09:56:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PROC_TE_MKT_DETAIL_EXPENSE]
(@Territory varchar(10) = Null)
AS
BEGIN
IF @Territory IS Null
BEGIN
SELECT
[Item_Description]+' '+'('+[Item_No]+')' Entry_Description
,ISNULL(RC_STAT.dbo.udf_Correct_Price(Item_No, Item_Ledger_Posting_Datetime, 'SALESAMP') * -1*Item_Ledger_Invoiced_Qty,Item_Ledger_Cost_Posted_GL * -1 ) Amount
,-1*[Item_Ledger_Invoiced_Qty] Quantity
,Customer_Name
,'' External_Doc_no
,[Item_Ledger_Sales_Responsible] SR_Code
,[Item_Ledger_Mars_Period_Code] ThePeriod
, Budget_Reporting_Group.Budget_Reporting_Group_Description
, Budget_Type.Budget_Type_Code, Budget_Type.Budget_Type_Description
, Budget_Reporting.Budget_Forecast_Period, Salesperson_Purchaser.SalesPerson_Purchaser_Code
, Salesperson_Purchaser.SalesPerson_Purchaser_Description
, CASE WHEN Budget_Reporting_Group.Budget_Reporting_Group_Id = 1 THEN Budget_Reporting_Amount ELSE - 1 * Budget_Reporting_Amount END AS Amount
, Salesperson_Purchaser.Territory_Code
,Territory.Name AS Territory_Name
,Region.Region AS Region_Name
, Budget_Reporting_Group.Budget_Reporting_Group_Id
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting AS Budget_Reporting
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Group AS Budget_Reporting_Group
ON Budget_Reporting_Group.Budget_Reporting_Group_Id = Budget_Reporting.Budget_Reporting_Group_Id
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Type AS Budget_Type
ON Budget_Reporting.Budget_Type_Code = Budget_Type.Budget_Type_Code
INNER JOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser
ON Budget_Reporting.SalesPerson_Purchaser_Code = Salesperson_Purchaser.SalesPerson_Purchaser_Code
INNER JOIN [NavisionReplication].[dbo].[Qry_Item_Ledger_Detail]
ON [Item_Ledger_Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory
ON Territory.Code = Salesperson_Purchaser.Territory_Code
LEFT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region
ON Territory.Region_Key = Region.Region_Key
WHERE Budget_Reporting.Budget_Year = 2007
AND Budget_Type.Budget_Type_Code in ('T&E', 'MKT')
END
IF @Territory IS NOT Null
BEGIN
SELECT Budget_Reporting_Group.Budget_Reporting_Group_Description
, Budget_Type.Budget_Type_Code, Budget_Type.Budget_Type_Description
, Budget_Reporting.Budget_Forecast_Period, Salesperson_Purchaser.SalesPerson_Purchaser_Code
, Salesperson_Purchaser.SalesPerson_Purchaser_Description
, CASE WHEN Budget_Reporting_Group.Budget_Reporting_Group_Id = 1 THEN Budget_Reporting_Amount ELSE - 1 * Budget_Reporting_Amount END AS Amount
, Salesperson_Purchaser.Territory_Code
,Territory.Name AS Territory_Name
,Region.Region AS Region_Name
, Budget_Reporting_Group.Budget_Reporting_Group_Id
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting AS Budget_Reporting
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Group AS Budget_Reporting_Group
ON Budget_Reporting_Group.Budget_Reporting_Group_Id = Budget_Reporting.Budget_Reporting_Group_Id
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Type AS Budget_Type
ON Budget_Reporting.Budget_Type_Code = Budget_Type.Budget_Type_Code
INNER JOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser
ON Budget_Reporting.SalesPerson_Purchaser_Code = Salesperson_Purchaser.SalesPerson_Purchaser_Code
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory
ON Territory.Code = Salesperson_Purchaser.Territory_Code
LEFT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region
ON Territory.Region_Key = Region.Region_Key
WHERE Territory.Code = @Territory
AND Budget_Reporting.Budget_Year = 2007
AND Budget_Type.Budget_Type_Code in ('T&E', 'MKT')
END
END
Report Based On Optional Parameters From Stored Procedure
Jan 12, 2008
I have the following stored procedure:
Code Block
CREATE PROCEDURE udsp_td_queryowner
@state varchar(10) = NULL,
@businesstype varchar(20) = NULL,
@size int = NULL,
@sortorder varchar(20) = 'state'
AS
SELECT gl_t.state AS [State],
gl_t.business_type AS [Business Type],
gl_t.lowsize AS [Low Size],
gl_t.highsize AS [High Size],
e.InternetAddress AS [Owner]
FROM gl_territory gl_t JOIN employee e ON gl_t.employeenumber = e.EmployeeNumber
WHERE state = COALESCE(@state, state) AND
business_type = COALESCE(@businesstype, business_type) AND
COALESCE(@size, lowsize, highsize) between lowsize AND highsize
ORDER BY CASE WHEN @sortorder = 'state' THEN gl_t.state
WHEN @sortorder = 'business type' THEN gl_t.business_type
WHEN @sortorder = 'owner' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName)
END ASC,
CASE WHEN @sortorder = 'low size' THEN gl_t.lowsize
WHEN @sortorder = 'high size' THEN gl_t.highsize
END ASC,
CASE WHEN @sortorder = 'statedesc' THEN gl_t.state
WHEN @sortorder = 'business typedesc' THEN gl_t.business_type
WHEN @sortorder = 'ownerdesc' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName)
END DESC,
CASE WHEN @sortorder = 'low sizedesc' THEN gl_t.lowsize
WHEN @sortorder = 'high sizedesc' THEN gl_t.highsize
END DESC
What it allows me to do is enter in any number of the variables when running the stored procedure. For example, EXECUTE udsp_td_queryowner @state = 'IA' would give me everything in the table in the state of IA regardless of the other field values. Likewise, if I ran EXECUTE udsp_td_queryowner @state = 'KY', @size = 15 it would return the records that are in KY and the size of 15 is in the range of the low and high value.
If I run the first example in Query Analyzer I get 53 records from the data I have. It returns every record that has IA as the state. I run the same thing in Reporting Services and all I get is 3 records. Just the 3 records for the state of IA where the business type is either null or blank (I can't tell.) If I allow all of the variables to accept Nulls then it returns the correct data. However, I would like to find a better alternative because when the report is run it returns all of the records in the table initially and if they user wants to enter in any parameters they have to toggle off the null box for the corresponding paramter.
Any ideas?
Report Calling A Stored Procedure Taking Too Much Time While
Aug 23, 2007I have a CLR stored proceudure than runs under 2 or 3 mins when run in management studio. But when the report calls the same SP, it runs in around 30 mins and the CPU usage goes to 90 or 100% and the sqlserver service uses massive amount of memory. Previously the report worked fine, only after made a few data driven subscriptions and redeployed the reports with minor changes that it began to happen.
Please help--Iam stumped.
There are no infinite loops in the report. I checked.
How To Write A Stored Procedure To Report Weekly Sales For The Last 5 Weeks
Feb 3, 2006Hi, Is there a way to write a stored procedure to get weekly report for 5 weeks?I currently use a stored procedure with 5 select statement to get the result for each week, but I was wondering it there is a way to do that with only one statementthanks
View 7 Replies View RelatedSQL Server 2008 :: Calling Stored Procedure Into Report Builder
Oct 23, 2015I am trying to call a stored procedure into report builder. The stored procedure has 2 parameters. When I run the report with allowing nulls and blank value in the parameters it works fine, but when I enter a value in a parameter it ignores the where clause I had in the original query(stored procedure) and displays everything.
View 3 Replies View RelatedError With Multi-Valued Report Parameter Using Stored Procedure
Dec 19, 2007Hi All,
I'm unable to run the report the report with multi-valued parameter using the below StoredProcedure as dataset:
CREATE PROCEDURE spprodsales
@productid int
AS
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
RETURN
And when I'm replacing this dataset to a query as below I'm able to run the report with multiple values selected for productid parameter :
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
So, can anyone please help me out possibly using the same stored procedure as above.
Thanks,
Kripa
How Do I Pass Values To My Stored Procedure In Remote Report Mode
Feb 3, 2007Hi,
I have created an report by selecting stored procedure as dataset data source.
Now when I run the report, it is asking for the parameter values for my stored procedure, but I want to pass the values from another .aspx page.
Could any one please let me how do I pass values to the stored procedure parameters in the report. I tried doing the Local method of reports, but the printer icon is not appearing in that, if I use Reportviewer control of my own. When I use the Remote method of reports, I am facing this problem.
Please could any one help me in this.
Regards,
Sudhakara.T.P.
Help Needed : Not Able To See Fields Value When Creating A Report By Calling A Stored Procedure
Mar 23, 2008Details :
Reporting Services 2000, SQL 2000 database, Visual Studio . Net 2003
In Report Design view
In "Data" tab, I can see records for column 'sRCName' returned from the stored procedure(usp_GetRouteCodeData) after clicking '!' icon. When I moved to "Preview" tab, I am getting below error message.
"The value expression for the textbox €˜sRCName€™ refers to the field €˜sRCName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
Observation : there is no value returned from the dataset on 'Fields' panel. The SP is accessing a table called tblRCM.
If I go to the Data--> Dataset --> Query, change the "Command Type" from 'Stored Procedure' to 'Text' and entered
select * from tblRCM at Query string area, the report is running fine.
Issue: This issue only happens at my laptop, my team member can create the same report using the same stored procedure without any error. The database is sitting on a server.
In the troubleshooting process, I tried to create a simple report by calling a stored procedure(CustOrderHist) from NorthWind DB in my local SQL server, I am able to see the data/value in 'Fields' panel and sucessfully view the data in 'Preview' tab.
Looks like the issue only happen on my machine, for a report that using stored procedure to access a DB sitting on a server.
I hope to hear from anyone who have encountered the similiar issue before, or, have any clue to resolve the issue.
Thanks.
SQL 2012 :: Stored Procedure Never Finishes Running From SSRS Report Manager?
Jul 9, 2015My SSRS report never finishes running for some reason.
If I run the same SP with the same parameters from SSMS it runs in 0.5 sec (returns 8-10 rows).
When running from report manager, I see extremely high CPU Time and Disk IO values, no blocking, no waiting at all.
SQL 2012 :: SSRS - Force Report To Use Columns Returned From Stored Procedure?
Jul 10, 2015I have a stored procedure which returns a result set as follows:
(Headers)Total,WV1,WV2,WV3,WV4,WV5.....
(Example data) "Some total name",1,2,3,4,5.....
The WV1, WV2, WV3 column names will be different depending on parameters passed to the stored procedure. In other words, the column names or number of columns aren't fixed (apart from "Total").
What I would like to be able to do is to just force SSRS to use the column headers supplied by the stored procedure as the column names in the report.
Generating A Pdf Report From Reporting Services Directly From Sql Server Stored Procedure
Dec 12, 2007Hi
I was wondering if it was possible to call reporting server web service directly from my sql server stored procedure. The call that I need to make to reporting web service needs to generate the report in a PDF format.
How To Use A Stored Procedure With Parameters To Create The Datasource View For Report Model Builder
Mar 12, 2007I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?
View 2 Replies View RelatedCalling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)
Nov 1, 2007Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Mar 3, 2008Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
RS 2005: Stored Procedure With Parameter It Runs In The Data Tab But The Report Parameter Is Not Passed To It
Feb 19, 2007Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''
AS
BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end
Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure
Nov 14, 2014I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.