Hi, 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
I have a initiator and a target service broker peer.
Both are controlled by a C# unit test. The initiator uses the Microsoft.Samples.SqlServer class. The target service uses stored procedure activation.
Sending a message from the initiator to the target, saves the content of the message, along with its conversation handle in the target's database specific table.
The unit test needs - at a later time - to instruct the target to send a message back on the same conversation handle to the initiator service.
For this the C# unit test creates a Conversation off of the saved conversation handle:
Service client = new Service("cleintservicename", conn, tran);
Conversation dialog = null;
dialog = new Conversation(client, convHandle);
Sending the message on this dialog generates an error "Message body: <Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8495</Code><Description>The conversation has already been acknowledged by another instance of this service.</Description></Error>".
Is the error due to the fact that a service - using the activated stored procedure already picked up the conversation, so that a new reference to the service can not be created through the Service class in CLR?
If so, I might need then to skip the activated stored procedure in favor or a CLR service, alltogether?
Any help - greatly appreciated.
I need to create a stored procedure (SQL 2005). I did it in C# (Visual Studio). The .dll file (inside) uses web services of Project Server 2007 (I just need to log on, create a project and log out). I prepared 2 files (exactly the same code): dll an exe. the .exe works fine. the problem is with .dll file (stored procedure in SQL)...
by the way: I created serialization file with sgen tool.
the problems start when I am trying to register these 2 .dlls in SQL server. I can do that in unsafe mode only. when I am trying to register them in externall access (normal dll) and safe mode (serialization .dll) I am getting errors:
CREATE ASSEMBLY failed because method "add_QueueCheckInProjectCompleted" on type "ConsoleApplication11.ProjectWebSvc.Project" in external_access assembly "ConsoleApplication11" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies
thus I did it in unsafe mode (both of them). than I created the procedure.
Now, I tried to call that procedure. I am getting error:
A .NET Framework error occurred during execution of user defined routine or aggregate 'Main':
System.Net.WebException: The request failed with the error message:
--
<html><head><title>Object moved</title></head><body>
<h2>Object moved to <a href="http://my_comp:22278/projectserver/_layouts/1033/error.aspx?ErrorText=Object%20reference%20not%20set%20to%20an%20instance%20of%20an%20object%2E">here</a>.</h2>
</body></html>
--.
System.Net.WebException:
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at ConsoleApplication11.ProjectWebSvc.Project.QueueCreateProject(Guid jobUid, ProjectDataSet dataset, Boolean validateOnly)
at ConsoleApplication11.Program.Main()
That means "Object reference not set to an instance of an object". the problem occurs when calling QueueCreateProject method
I dont understand that error. I wrote the same code for .dll and .exe. the .exe file is working ok (no errors) but .dll (as a stored procedure) shows this error
my code in C#:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Net;
using PSLibrary = Microsoft.Office.Project.Server.Library;
namespace ConsoleApplication11
{
public class Program
{
public static void Main()
{
const string LOGINWINDOWS = "_vti_bin/PSI/LoginWindows.asmx";
string baseUrl = "http://localhost:22278/projectserver/";
CookieContainer cookies = new CookieContainer();
LoginWindowsWebSvc.LoginWindows loginWindows = new LoginWindowsWebSvc.LoginWindows();
loginWindows.Url = baseUrl + LOGINWINDOWS;
loginWindows.Credentials = CredentialCache.DefaultCredentials;
loginWindows.Login();
ProjectWebSvc.Project project = new ProjectWebSvc.Project();
project.Credentials = loginWindows.Credentials;
project.Url = baseUrl + "_vti_bin/psi/project.asmx";
ProjectWebSvc.ProjectDataSet dsProject = new ProjectWebSvc.ProjectDataSet();
ProjectWebSvc.ProjectDataSet.ProjectRow projectRow = dsProject.Project.NewProjectRow();
Guid projectGuid = Guid.NewGuid();
projectRow.PROJ_UID = projectGuid;
projectRow.PROJ_TYPE = 0;
projectRow.PROJ_NAME = "Jakis";
projectRow.PROJ_SESSION_UID = Guid.NewGuid();
dsProject.Project.AddProjectRow(projectRow);
Guid jobGuid = Guid.NewGuid();
project.QueueCreateProject(jobGuid, dsProject, false);
System.Threading.Thread.Sleep(5000);
loginWindows.Logoff();
project.Credentials = null;
}
}
}
any help will be appreciated. my email is: stro.na@interia.pl
Ok, first some background.
I am writing my first (complex) CLR Stored Procedure using Visual Studio 2005.
This SP worked fine until I added code to make a web service call. That web service is a wrapper web service I created because the actual web service I need to call uses System. Web.Extensions which was not available in my VS2005 Database Project.
At first I was getting the standard "External Access Assembly" errors, so I created a new user (was using SA) and assigned database ownership to the new user, then assigned permissions to that user. This worked to get it deployed, but I get the following error when its run:
<code>
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, S
</code>
Anyone have any ideas?
Thanks!
Dave Borneman
Solution Architect,
anyWare Mobile Solutions.
Hi,
I need to call a webservice directly from a tigger or stored procedrue instead of creating a window service to read from a table then call the webservice .Is it possible ? if yes, please i need your support.
Thanks,
Hi,
i am having a problem with service broker. here is what i have
i have two databases (DB1 and DB2) and both are on seprate servers (seprate instances)
in DB1 i am putting raw data from plc using rssql.
once data gets into the table, i am executing a trigger which bind the data and send it to the target service.
in the target service i have a activated stored procedure which unbind data and then call a stored procedure which is on in DB2( i am using Linked Server ). on DB2 i am doing really really complex calculations thats why i have to use service broker to seprate the calculation to data input for performance.
i ran this query on both databases so i won't have to create the certificate.
(ALTER DATABASE MyDatabaseName SET TRUSTWORTHY ON)
now my problem is, its not executing the stored procedure from DB2 and disabling the service everytime i enter the data into rawdata table(DB1).
But if i run the stored procedure manuly from TSQL it works fine.
can you guyz help me out on this issue.
Thanks.
This is killing me...and not slowly.
I have a stored procedure (a) which calls another stored procedure (b).
Stored procedure (b) is a C# stored procedure which simply writes out to a file data in XML format. Internally, it calls...select fld1, fld2, fld3, fld4, fld5from #tmptable for xml auto, elements
If I call stored procedure (a) from Query Analyser / SQL Management Studio everything works fine. Perfect.
But....we need this all to run asynchronously. So we used the Service Broker, configured the queues and messages and off we went. All worked as planned except our XML files were empty.
Further investigation showed that if we call select fld1, fld2, fld3, fld4, fld5from #tmptable
- without the 'xml' bits, we got a resultset back. But if we call it with the for xml auto, elements, the reader was empty. No errors are visible in the profiler, but the XmlReader refuses to read.
The binary / extended stored procedure is the same pysical binary that is called from Query analyser that works, but via the Service Broker refuses to do anything XML based. Outputting the data as normal text is cool, but not what we want.
----------------- UPDATE --------------
I changed the code so the CLR Stored proc was fired as a trigger on an update to a table. If I update the table in Query analyser, the trigger fires, the CLR Stored proc is called, the XML is generated.
If I update the table as part of my message handling in the Service Broker queue, the trigger is fired, the CLR Stored proc is called, the XML is generated EMPTY!!! The other TSQL statements work fine, but selecting for xml simply will not work for a procedure called, either implicitly or explicitly from the service broker.
I use Try ... catch blok in my activation stored procedure. When SQL Server raise error (e.g. Primary key violation) in Try blok, XACT_STATE in Catch blok has value 1 = commitable transaction and I can use rollback transaction to savepoint. But when I use Raiserror() in Try blok, XACT_STATE in Catch blok has value -1 = uncommitable transaction and I can't use rollback transaction to savepoint. When I drop automatic activation for given queue and I run this stored procedure with Raiserror(), XACT_STATE has value 1 = commitable transaction.
What a problem may cause this different behavior ?
Best Regards,
Pavel
I've written a stored procedure that copies records from tables in one database to the same tables in another database. Here is a snippet for a single table:
delete from washmaster_mirror.dbo.batches
DBCC CHECKIDENT (washmaster_mirror.dbo.batches', RESEED, 0)
alter table washmaster_mirror.dbo.batches disable trigger tr_BATCHES_INSERT
alter table washmaster_mirror.dbo.batches disable trigger tr_BATCHES_UPDATE
insert into washmaster_mirror.dbo.batches(processorid,batchnum,processdate,transcount,batchamount,dtmCreate,dtmUpdate,keypreserve)
(select processorid,batchnum,processdate,transcount,batchamount,dtmCreate,dtmUpdate,[ID]
from washmaster.dbo.batches)
alter table washmaster_mirror.dbo.batches enable trigger tr_BATCHES_INSERT
alter table washmaster_mirror.dbo.batches enable trigger tr_BATCHES_UPDATE
This kind of block is repeated in the same stored procedure for each table. There are 20 tables populated this way. The idea is to copy records from the working (washmaster) database to a mirror (washmaster_mirror) database then back up the mirror database so I can avoid having down time during the back up process (due to database locking).
The problem I have is that the above stored procedure sometimes causes the SQLEXPRESS service to stop, thus the Sql Express connection to break. I've checked the error logs and the event log (I'm running on Windows Vista) and nothing sheds light on the issue. When I originally coded the stored procedure I surrounded it by a TRY - CATCH block, which forced it to lose the connection every time. When I eliminated the TRY - CATCH block the broken connection issue became rare, but still occurs occasionally. Any ideas why this might be occuring or what I can do to solve it?
Thanks,
SJonesy
Hi,
Can anybody tell me How to access Analysis Service 2005 cubes through normal Stored Procedure.
Basically can write a stored procedure that we normally write in database service and use it access the Analysis Service 2005 cubes.
Is it possible
Regards,
gokul
We are looking for some guidance with an issue we have picked up with our implementation of Service Broker here on the ABSA Capital project and I am hoping you can help or point us in the direction of someone.
The architecture we have implemented for service broker is to make use of an Activation stored procedure on two queues (1 SP per queue) to process the messages received. What we have found is that the activation stored procedure runs on a background session and its CPU time and memory just grows to the point where it brought one of our UAT servers to a grinding halt.
Is there anyway we can reduce the memory consumption of the activation stored procedure or is this one of those things that still need to be ironed out in Service Broker?
Hi,
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????
i 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:
Hi
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
Using 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 RelatedI 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
Hi, 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 RelatedHi 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...
Can 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.
I'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
I'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!
Hi 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
Hi 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.
Hi,
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
Almost 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.
Created 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.
Hi,
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
I 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
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?
I 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.
Hi, 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 RelatedI 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 RelatedHi 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