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

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

Report using Stored Proc calling multiple Stored Procs

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

Hi guys,

I am new to SSRS. I have to create a report from a Stored Proc. Thi Stored Proc excepts just one parameter 'ErecNo'. This parameter is passed to fetch the columns from table1. This result set contains OrderID. The OrderId is further passes to 5 SP's in to fetch 5 more result sets. Output of the main stored proc contains 6 result sets.

Now I have to create a report using this SP to show all the Result sets in a Single report. But When I pass the Parameter in SP in SSRS. It ends up showing just one result set with Order ID. 

Can you please guide me how can I show all the result set in a Single report? 



View Complete Post

More Related Resource Links

how to write a stored proc to prevent multiple users in uploading the reports at a time? plsss help



I wanted to know how to prevent more than one user from uploading the report at a time .I am new to .net ,plss help with the code that would be great.Details are below:

This is a Windos based app written in c# . When User A  clicks Upload option on  one server  from the menu  to upload the files  and at the same time when User B clicks Upload option on different server , User B should be alerted a message saying "User A's uploading is in progress,pls wait" . How to achieve this, plsss help with the code.. I am thinking this logic should be kept in a stored proc, How do I write that proc? plss help


Thanks in advance!

calling one stored proc from another in a query


Is it possible to call 1 Stored procedure  from another withing a sql query

sp1 :

select ID1,ID2,ID3,ID4 from table1 ;

sp2 :

select name from table2 where ID = ID1;

I want to get corresponding names of ID1,ID2 and ID3,ID4 in sp1 as a query result.Is it somehow possible to call sp2 from sp1???thanks in advance.

Data Truncation issue with Enterprise Library Logging WriteLog stored Proc


Hi ,

I'm using Enterprise Library Logging  feature for logging. The issue i am facing is when the Logging message is too large(more than 65534 chars) ,complete data  is not logged in the Formatted Mesage column which is  of data Type nText .

I am able insert complete data if i try inserting from Sql insert Query from sql management studio. Do i need to add any attributes to data base listener or do i need to change the sp.

 Is there any way to increase the WriteLog stored proc param size in EnterpriseLibrary.Logging config file ? . Please let me know.


Thanks In Advance.

Test Run: Automate Testing of Your Stored Procs


Many Windows®-based applications have a SQL ServerT back-end component that contains stored procedures. Although techniques to automatically test functions in the front-end code are well known, the techniques to write test automation for stored procedures are not.

James McCaffrey

MSDN Magazine September 2004

Search does not find text in Word document stored in document library on a Report Center site within



We have a document library on a site created off of the Report Center site template. We uploaded a Word 2007 document with the word zebra in the text. Using the search bar at the top of the page we selected This list: Our document library and searched for zebra. We received no results.

We tested the same thing on another site (site template Team site) within the same site collection and search worked fine.

Does the Report Center search function differently then the search functionality on the Team site?

Tamara Bredemus SharePoint Minion...working up to Maven

Populating a list box in InfoPath from a sql stored proc


I have an InfoPath web enabled form that gets published to a SharePoint site.  I need to add a list box to the form that gets populated with data returned from an existing call to a SQL Stored Proc.  

As the form works now, the user types in a patient id #, then clicks a button that calls the Stored Proc and returns any patients that match the given patient ID.  This part works.  

My task is to add a list box so that if more than one patient is returned , display the list box with all of these patients populated in it.

I am hoping to do this within the confines of the existing code.  The existing code looks like this:

private bool LoadPatientInformationFromDB(string patientId, string patientFirstName,
      string patientLastName, string patientMiddleInitial, string birthDate, string gender,
        string admitDate, string location)
      bool completed = false;
      //Load from DB
      SqlConnection conn = new SqlConnection(GetOrderSetDBConnectionString()); 
      //Create Form Builder
      formBuilder = new FormBuilder();
      SqlDataAdapter adpt = null;
        if (!String.IsNullOrEmpty(patientId) || !String.IsNullOrEmpty(patientFirstName) ||
          !String.IsNullOrEmpty(patientLastName) || !String.IsNullOrEmpty(patientMiddleInitial)
          || !St

Sending Email by exeucting a stored proc on SQL Server that references CLR

Hi Guys, I want to modify the data type of the @body variable in the stored procedure that sends an email. The stored procedure is as follows:   CREATE PROCEDURE [dbo].[spSendMail4]<br/> @recipients [nvarchar](4000),<br/> @cc [nvarchar](4000),<br/> @subject [nvarchar](4000),<br/> @from [nvarchar](4000), @body [nvarchar](4000), @attachment [nvarchar](4000)<br/> WITH EXECUTE AS CALLER<br/> AS <br/> EXTERNAL NAME [SMTPCLR].[StoredProcedure].[spSendMail]<br/> GO   I want to change the @body variable to data type: text. The reason for this, is because I am sending an HTML email whose content (characters) exceed 8000. Basically the new stored procedure should looks like below:   CREATE PROCEDURE [dbo].[spSendMail4] @recipients [nvarchar](4000), @cc [nvarchar](4000), @subject [nvarchar](4000), @from [nvarchar](4000), @body text, @attachment [nvarchar](4000) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SMTPCLR].[StoredProcedure].[spSendMail] GO   If attempt to make this change, I get the below error:   CREATE PROCEDURE for "spSendMail" failed because T-SQL and CLR types for parameter "@body" do not match.   Understandably so, because from the article http://msdn.microsoft.com/en-us/library/ms131092%28SQL.100%29.aspx CLR data type (.NET Framework) is not compa

Calling Stored Procedure in asp.net

Hi! I am trying to insert a row via button event that should call an Oracle Stored Procedure, but the insert never happens. Please see code below: C#-code {code} using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.OracleClient; using System.Data;   public partial class new_user : System.Web.UI.Page {     protected void button1_click(object sender, EventArgs e)     {         OracleConnectionStringBuilder b = new OracleConnectionStringBuilder();           b.UserID = "Test";         b.Password = "xxx";         b.DataSource = "ora11";           OracleConnection con = new OracleConnection(b.ToString());           try         {             con.Open();             OracleCommand spcmd = new OracleCommand();               spcmd.CommandType = CommandType.StoredProcedure;     &nbs

Extended stored procs on 64 bit MSSQL 2008

We need to develop some extended stored procedures for 64-bit SQL Server 2008. We have written the DLL in Visual C++ but when we try to register it with the database, we see the following error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not load the DLL c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\xp_test.dll, or one of the DLLs it references. Reason: 193(failed to retrieve text for this error. Reason: 15105).(42000,17750) Procedure(xp_test) Can anyone please tell me if you have come across such an issue and how it was successfully resolved? 

MOSS 2007: list column that lookup to multiple documents stored in doc library

Hi All, I'm trying to understand how to customise MOSS to achieve the following: I need to lookup from a list to multiple documents stored in a documetn library. Example: - List item 1 --------> Lookup to Doc 2                   --------> Lookup to Doc 5 - List item 2 --------> Lookup to Doc 3                   --------> Lookup to Doc 5                   --------> Lookup to Doc 8 - List item 3 --------> Lookup to Doc 1 How can I achieve this? is it possible that in the lookup I have a "search" option? Thanks all Vit

How to pass the file path in EXEC of Stored Proc for getting IIS Logfile data

Hello,                  I need to get IIS Logfile data from Stored Proc. How to do this. I am using Bulk Insert Method in my stored Proc When I execute the stored proc I am getting an error that  Incorrect syntax near 'C:\WINDOWS\system32\Logfiles\W3SVC1\ex100802.log'.     Below is the code.                 Create  PROCEDURE  spWebLog @path varchar(300) AS begin exec('BULK INSERT Weblog from''' + @path + ''')  end go Declare   @SQL Varchar(100 ) Set    @SQL= 'C:\WINDOWS\system32\Logfiles\W3SVC1\ex100802.log' exec   spWebLog @SQL    

Error in executing a stored proc

Hi Team, I have problem in executing a stroed proc SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --exec PBT_Broker_Report 'PBTBFDAGT ' Alter PROCEDURE PBT_Broker_Report  -- Add the parameters for the stored procedure here @BrokerID nchar(10)  AS BEGIN SET NOCOUNT ON; declare @sql nvarchar(3000) ;   IF EXISTS (    SELECT *    FROM information_schema.tables    WHERE Table_Name='Test_Broker'    )    BEGIN    DROP TABLE Test_Broker    END     set @sql = (case @BrokerID     when 'All' then    N'    SELECT PO.COMPANY_CODE,PO.POLICY_NUMBER, PO.primary_insured    INTO Test_Broker   from dbo.POLICY po   where PO.AG_AGENT_NUMBER <> ''PBT001''   '     else        N'   SELECT PO.COMPANY_CODE,PO.POLICY_NUMBER, PO.primary_insured,   INTO Test_Broker   from dbo.POLICY po   where    PO.AG_AGENT_NUMBER = @BrokerID   AND AG_AGENT_NUMBER <> ''PBT001''  '   end ) --print @sql execute sp_executesql @sql     Error: Must declare the variable '@BrokerID' Please suggest me some solution

Reading data to a table from a stored proc

This has me brain locked.  1. I've got a stored procedure.  2. It does a select with a couple joins.  I want to take the results of that stored proc and read them into some a table -- temp table would be ideal.  here's my (really tough) constraints... these really complicate things: 1. the stored proc uses dynamic SQL. As a consequence, I don't know the columns it's going to return (it's kind of the whole point of using it).  2. Since the stored proc uses dynamic SQL, it needs to use sp_executesql internally.    What won't work(i don't think?): 1. I can't create a function that returns a table and does a "return select * from....", since you can't (I don't think?) call a stored a stored proc in the return statement of a function 2. I can't just do an "Insert into #xyz exec .." because I can't pre-define the temp table (I think this is needed?). 3. I don't have a linked server. and i'd rather not. All of this runs local in the same instance of SqlServer, and will have to handle loads of volume (each hit will return a few million rows).  So (I think?) OPENQUERY  is out.  4. OPENROWSET is a possibility, I suppose, but again, everything is running locally. I think that goes through OLEDB (?) and I'm not sure that's even an option.    THOUGHTS???  HELP?? is it even possible?? Pl

Testing Foregin Key without error out of stored proc

I have a Customer table. Several dirrect mapping tables link to this record. In my stored procedure I would like to try and delete this Customer record and if any keys are bound to it, then catch that error without breaking the stored proc ( try - catch ) Is this possible in a stored proc? I would like to just use a print statement that the record couldnt be deleted. Thanks, Nick

SqlDataSource : in what order do parameters get fed to stored proc?

Hello, I'm wondering if someone can explain how a SqlDataSource determines the order in which parameters are fed to a stored procedure. I'm not able to find clear documentation.So suppose a proc has: CREATE PROCEDURE  updateOneCar@CarActiveFlagCheck int, @CommentsForCar varchar(1500) = NULL, @manufacturerID int...and then suppose the ASP.NET page has the manufacturerID supplied by a querystring, and has a FormView as follows: <asp:FormView ID="FormView1" runat="server" DataSourceID="SQL01"          DataKeyNames="manufacturerID" EditRowStyle-Wrap="False" RenderOuterTable="False">         <EditItemTemplate>         <div>             manufacturerID: <%# Eval("manufacturerID")%>             <br />             Is my Car on?: <%# Eval("carFlag")%>             <br />             Is my car Active Flag:             <asp:CheckBox ID="CarActiveFlagCheckBox" runat="server"              

Oracle stored proc optionally returning a ref cursor.

Hi, I would like to use a technique for exception handling on our database stored procs whereby, each proc exposes a p_error_code output parameter of type int. 0 indicates no error, and other values indicate application specific exceptions. Now, where a stored proc will return a ref cursor if no exceeptions occur, we would like to check the p_error_code and if not 0, then use the ref cursor. If an exception does occur, then the stored proc may or may not have populated the ref cursor, but the dotnet code will almost definately not read the contents. Now, we have done this with the following code:            OracleCommand command = CommandFactory.CreateCommand(transaction, "RTS.p_get_audit_trail");             command.Parameters.Add(ParameterFactory.CreateParameter("p_doc_id", RTSId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_rev_id", RevisionId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_c_item_id", ContentItemId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_error_code", OracleType.Number, ParameterDirection.Output));          &nbs

Displaying Image stored in rtf format in access database displaying it on crystal report

Hello, I'm working on a C# project combined with "Crystal Reports 10". When a user inserts something in a "TextField" in my C#-Program, it's saved  as RTF-code (Bolt,Pictures,Underlined,....) to a database.. So if they  reopen it in my "C#"-program they're able to see what they've entered  including their pictures .... But when I created a "Crystal Report"-file and combined it with the same  database used in my "C#"-program, it only show the formatted text and not  the pictures or images... Is there a way to show the "Picture" or "Image" they've inserted in RTF-code  ??? When I copy the code from the database in a simple Text-File and rename  it too "Test.rtf", it wil open correctly in word (with picture and  everything else)... So I hope there is a way to get the picture, Cz it's rather important  they've got a picture of the product they wanna print ...
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