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

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

Capture Stored Procedure Parameters in Trace

Posted By:      Posted Date: September 06, 2010    Points: 0   Category :Sql Server
Hi, I want to capture each execution of all stored procedures and the parameters supplied, I'm going to do this via a trace on RPC:starting, capturing TextData, Database and starttime. I'll need to parse the TextData column to get the information that I want. I assume I also need to capture nested stored procedure calls , i.e. SP: starting as well. Does this sound correct and is this the best way of extracting this information ?Sean

View Complete Post

More Related Resource Links

can a Workflow access a stored procedure and pass the parameters from the list data to the stored pr

The reason that I would like to consider this functionailty is because my table architecture is complicated and I do not want to modify my master table to accept all of this data where some of the data should be normalized into sub tables.  Has anyone see evidence of the stored-procedure parm approach?  Is this best accomplished through VS 2010 or can I do it through SPD? Thanks

SQL 2010 SP1 does not fill in Stored Procedure Parameters

I am executing a Stored Procedure in Server Management Studio.  The Procedure gives the expected results but it does not fill in the parameters?  I have stop and restarted the server to no avail. Please help? Thanks. STORED PROCEDURE USE   [char68003f] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SelectDonorXiD] ( @DonorID varchar(50) , @ContactTitle varchar(7)=null OUTPUT, @ContactFirstName varchar(40)=null OUTPUT, @ContactLastName varchar(40)=null OUTPUT, @ContactAddress1 varchar(40)=null OUTPUT, @ContactAddress2 varchar(40)=null OUTPUT, @ContactCity varchar(30)=null OUTPUT, @ContactStateCode varchar(2)=null OUTPUT, @ContactZipCode varchar(10)=null OUTPUT, @ContactPhone varchar(30)=null OUTPUT, @ContactEmail varchar(80)=null OUTPUT, @BillingTitle varchar(7)=null OUTPUT, @BillingFirstName varchar(40)=null OUTPUT, @BillingLastName varchar(40)=null OUTPUT, @BillingAddress1 varchar(40)=null OUTPUT, @BillingAddress2 varchar(40)=null OUTPUT, @BillingCity varchar(30)=null OUTPUT, @BillingStateCode varchar(2)=null OUTPUT, @BillingZipCode varchar(10)=null OUTPUT, @BillingPhone varchar(30)=null OUTPUT, @BillingEmail varchar(80)=null OUTPUT ) AS Select ContactTitle, ContactFirstName, ContactLastName, ContactAddress1, ContactAddress2, ContactCity, ContactStateCode, ContactZipCode, ContactPhone, ContactEmail, BillingTitle, Bil

Entity Framework - stored procedure mapping, excecuting SP with float parameters separated by commas



Im using Entity framework with POCO entity generator template + SQL Server. I have mapping to stored procedure that takes two floats as input.

After executing function from code with two doubles as parameters: 1.23 and 4.56, SQL Profiler shows:

exec storedProcedureName @arg1 = 1,23, @arg2= 4,56

This is causing an error because it looks like that stored procedure takes 4 arguments, but it should take only two and there should be dots insted of commas in these floats.

My SQL Server collation is SQL_Latin1_General_CP1_CI_AS. I tried to set:

           Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
           Thread.CurrentThread.CurrentUICulture = CultureInfo.InvariantCulture;

in DataContext Constructor, and di

Pass Multi-Value parameters to Stored Procedure problem



I am trying to pass multi-value parameters to stored procedure to filter data, but seems it does not work.

Stored procedure:


ALTER PROCEDURE [dbo].[test]

@StartDate DateTime,
@EndDate DateTime,
@FirstName varchar(8000),
@LastName varchar(8000),
@Location varchar(8000),

SELECT [Item No_],[Sales Staff],[Location Code],[Date],
[Price],[Quantity],[Item Category Code],[Product Group Code]FROM [Trans Sales Entry]
cte2 as
SELECT [ID],[First Name],[Last Name] FROM Sta

JDBC - stored procedure won't allow setting parameters by name



When I try to call CallableStatement.setString( name, value ) it throws an exception. Is there something I have to do to either change the parameter names or set them up? I use the full "@Beginning_Date" as the name.

thanks - dave

Very funny video - What's your Metaphor?

Error passing parameters from C# to Stored Procedure



I have a Stored Procedure that receive a parameter.
- SP name: hr_all_markets
- SP parametter: @cs_rep (type varchar 50)
In my C# code I already called this SP without parameters and works fine, but when I try to run with parameters I have this error:
- Incorrect syntax near 'hr_all_markets'.

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,


Trace My stored procedure Without using profiler



I am working on some application. We are using Sqlserver2008. We dont have Sql profiler to trace stored procedure and all. We can't install also imidiately.

can anyone give some idea how to trace which stored procedure is hiting my Sql server.

we are using asp.net 2.0 and sqlserver2008.

Is there any same kind offeatures avl in VS 2005?  

plz suggest.



Database Tuning Advisor, Stored Procedure with datetime parameters, syntax error


I receive these error(s) in the Tuning Log. I have several stored procedures and they all use a datetime parameter.

E000    exec <some sproc name> ''2009-01-01 00:00:00:000'',''2010-09-30 00:00:00:000'',N'',N'',NULL,NULL,NULL,NULL,NULL,NULL         1    [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2009'.

I am using SQL 2005. Any suggestions?

Thanks, Reece

Creating BCS entity for a SQL stored procedure that has parameters using visual studio


Hi Everyone,

                 can anyone help me in creating  BCS entity using BCDM model in visual studio using Linq to sql query for a SQL stored procedure.I am receiving remote debugging error and few other errors.Your step by step approach helps me a lot,I have been playing with this for long,but still no answer.


Thanks ,

Help on Stored Procedure with multiple like parameters


Hi All,

Please go through the screenshot below.


I have a table with 4 columns.The first column is a list of teams.The other three columns specify whether the team member with the particular id is present on any particular day .Say for example in the third row the running Team has 2 and 3 which means team member with the id 2 and 3 are present.Multiple values are seperated by the pipe symbol '|'.

I have a Query like this

Select Teamname from tblTeam where Running not like '%|2|%' and Running not like '%|3|%' and Running not like '%|7|%' and Biking not like '%|1|%' and Biking not like '%|4|%' and Biking not like '%|7|%' and shooting not like '%|3|%' and shooting not like '%|4|%' and shooting not like '%|7|%'

Basically i am searching the columns with like clause.The search criteria for each column may vary.In the above query i am searching for Teams where Running column does not contain 2,3 and 7,Biking column does not contain 1,4 and 7 and the shooting column does not contain 3,4 and 7.

I would like to set a stored procedure where i would be able to pass 3 set of parameters from the code behind(I am using VB.net) for Running column sear

Oracle stored procedure using parameters


Hi, am having a problem sending parameters to the stored procedure, I'm not sure if i'm sending Oracle the parameters in the right way.

USING:oracle 11g x64, C#, ASP.net 4.0, ODP.NET


The header of the stored procedure on Oracle server is:


?PROCEDURE create_new_user(p_log_in_name IN VARCHAR2, p_password_text IN VARCHAR2, isSuccessful OUT NUMBER)


The code the use the stored procedure in C# asp.net:

       OracleConnection connect = new OracleConnection("User Id=xpcal;Password=password123;Data Source=ora11g;");
        OracleCommand command_create_user = new OracleCommand();

        command_create_user.Connection = connect;

        command_create_user.CommandText = "xpcal.xpcal_users.create_new_user";
        command_create_user.CommandType = CommandType.StoredProcedure;

        OracleParameter p_log_in_name1 = new OracleParameter();
        p_log_in_name1.OracleDbType = OracleDbType.Varchar2;
        p_log_in_name1.Direction = ParameterDirection.Input;
        p_log_in_name1.Value = tbox_user.Text;

        OracleParameter p_password = new OracleParameter();
        p_log_in_name1.OracleDbType = OracleDbType.Varchar2;
        p_log_in_name1.Direction = ParameterDirection.Input;
        p_log_in_name1.Value = tbo

c# code to create an array of parameters for oracle stored procedure



Working on a c# project that has oracle as backend.  I have problem creating a function in my codebehind page to create an array of paramenters for oracle stored procedure. for example i have the follwoing code for ms sql server...

SqlParameter[] parm = 


                         DataCon.createSqlParameter ("@id", DataCon.DBNullIfBlank(txtid.Text.ToString()), SqlDbType.Char,9 ) ,

                         DataCon.createSqlParameter ("@FName", DataCon.DBNullIfBlank(txtFName.Text.ToString()), SqlDbType.VarChar,14),

                         DataCon.createSqlParameter ("@MI", DataCon.DBNullIfBlank(txtMI.Text.ToString()), SqlDbType.Char,1),


 For the same in oracle I am using the followin

Send Email from SQL Server Express Using a CLR Stored Procedure

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Inserting rows via stored procedure and under certain conditions


I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

PersonID (FK)
LocationID (FK)

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

Creating A Stored Procedure Which Searches Team Names



I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.

Sort by gridview SortExpression parameter via Stored Procedure


I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />
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