.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

what is limit on max size of XML datatype which we can pass to stored procedure and what is most el

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
what is limit on max size of XML datatype which we can pass to stored procedure  and what is most elegant way to pass huge XML to Stored procedure

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

how to pass dataset from one stored procedure to another stored procedure

I'm wondering how a dataset returned by a stored procedure can be passed to another stored procedure.     mark it as answer if it answered your question :)

Can I pass a value for a output variable in a stored procedure in ADO.NET?

I am calling a stored procedure, that exists in a SQL Server 2005 database,  using ADO.NET, in which I instantiate a sql command object.The stored procedure has an ouput variable like '@packageCode VARCHAR(100) = NULL OUTPUT'.Can I set it's value when creating the sql command object? Or I can never set a value for an output type variable in  a stored procedure?


How do you limit the execution of stored procedure / or update of items only to happen on specific days.   i need this only to execute if today is day 3 through day 7 (tue - sat) UPDATE itm SET AVG_WHSL_PRC = AWP.CURR_AWP_AMT ,AWP_EFF_DT = @Pricing_Date ,UPDT_DTS = GETDATE() ,UPDT_OPR_ID = RIGHT(SUSER_SNAME(), 7) FROM dbo.T_ITEM_PRC_CHG itm JOIN #TEMP_AWP awp ON itm.EM_ITEM_NUM = awp.EM_ITEM_NUM WHERE itm.PRC_CURR_CD IN ('C','F') AND @Pricing_Date BETWEEN itm.PRC_EFF_DT AND itm.PRC_END_DT AND itm.HOME_ACQ_COST > 0 AND ISNULL(itm.AVG_WHSL_PRC, 0) <> awp.CURR_AWP_AMT

Pass list to stored procedure



I'm looking to pass a list into a stored procedure, stored as List<myClass>.  I've passed a table in before but I'm not sure how to pass a list - anyone help?

unable to pass dynamic dates to stored procedure with pivot


hi All,

                  I am unable to date as dynamic parameter to stored procdure with pivot.i am getting


Msg 8114, Level 16, State 1, Procedure Sample, Line 3

Error converting data type nvarchar to datetime.

Msg 473, Level 16, State 1, Procedure Sample, Line 3

The incorrect value "@date1" is supplied in the PIVOT operator.

below is my stored procdure



procedure Sample(@date1 datetime,@date2 datetime)



Please help URGENT - how to pass XML from aspx page to Stored procedure


Hi all,

I would like to take your help for a small task of mine. I have dataset whose contents have been converted as xml, the contents of which needs to be sent to a stored procedure. How do i go about creating methods in the data layer and the stored procedure.

What should be parameter type in the data layer's method and what should be the parameter type in the stored proc. I dont want to use a varchar at the stored proc level because it is limited to a length of only 8000 characters. Please suggest a solution

I would be glad if someone could post some sample code.

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

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,


Running SSIS package from Stored Procedure using dtexec and Pass boolean value



We are using SSIS 2005 and sql server 2005. My package has a boolean type package level variable. Eg: IsClientNull boolean type.

I call this package from stored procedure. In addition to passing values for other parameters, how can I pass boolean value.

My code is like this:

SET @CMD='dtexec ' +
'/FILE ' + @pShareName + @pPackageName + ' ' +
'/MAXCONCURRENT " -1 " ' +
'/SET "\Package.Variables[User::StartDate].Properties[Value]";' +
CONVERT(char(10), @pStartDate, 120) + ' ' +
'/SET "\Package.Variables[User::EndDate].Properties[Value]";' +
CONVERT(char(10), @pEndDate, 120)
IF (@pClient IS NULL)

 SET @CMD = @CMD + ' ' +
 '/SET "\Package.Variables[User::IsClientNull].Properties[Value]";' +

Datetime filter value pass to BCS stored procedure parameter


I have data coming from one of the stored procedures that has startdate and enddate as parameters.I have created a business data webpart tht accepts this ECT.When I give the start date and end date in this format----> 2010-02-02 00:00:00Z,I can able to get the data from the database.

               As this is not a feasible approach,I have used a two datetime filters that has calendar control attached to it,so that user can use this to pick the start date and end date.Now,my issue here is when I choose the startdate and enddate,ECT is not accepting the format of the date that these filters are passing.I have changed the filter descriptor LOBDateTimeMode to "Local" in the external content type,but still I am receiving the same error.Does any one has any solution for this??

how to pass XML file path as input string to a Stored Procedure


Hello All,

I need to pass the file path (or hardcode the XML file path ) of an XML file to a stored procedure. This SP will then read the values from the XML file and based on these values the Insertion / updation will be done thereafter.

I am not able to pass the XML file path to this stored procedure.  Right now I an doing as givn below :

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
  <add key="approvalMode" value="On" />
EXEC sp_xml_preparedocument @idoc OUTPUT,'c:\Inetpub\wwwroot\HealthandSafety\Aspx\ApprovalMode.xml'

FROM OPENXML (@idoc,'/configuration/appSettings/add',0)
      WITH ([key]  varchar(10),
            value varchar(20))
EXEC sp_xml_removedocument @idoc

Here any change in the xml file needs to be changed in the SP too. This will lead to double work as later on this SP will be made as an SQL job.

Is there a way I can pass or hardcode the XML file path in the SP rather than duplicating the XML file contents again

Please advice!!

How to pass perameters in exec command when we using XML in stored Procedure?


My SP is:

USE [PrintLableTest]




PROC [dbo].[sp_Insert_Table_PrintLabelTB1XML]

@pData varchar (1000)





DECLARE @CPART nvarchar(50)

DECLARE @ItemNo nvarchar(50)

DECLARE @TPART nvarchar(50)

DECLARE @Code nvarchar(50)

DECLARE @BarCode nvarchar(50)


pass datetime variable into execute sql task which has stored procedure


Hi all

I have a stored procedure something like this exec sp_name startdate, enddate. I have to run this in an ssis package.

The startdate and enddate values are loaded from a sql query, so i have declared variables (startdate,enddate) and got those values from the query using evaluateasexpression. Now, when i call the stored procedure in execute sql task it fails as the datatype from the variable is string and the stored procedure parameter (startdate,enddate) are datetime. Please help me.

The following are my settings in execute sql task editor

SQL statement : EXEC sp_name ?,?

parameter mapping

variablename: user::startdate

Direction: Input

data Type: Date

Parameter name : 0

Parameter size : -1


Passing a SSIS Variable with a string datatype to a Stored Procedure requiring a uniqueidentifier


I have a SSIS package that uses a SQL Server stored procedure as a data source. The parameter in the stored procedure is pulling data from a column with a uniqueidentifer (guid) data type. I created a package level variable in SSIS with a string datatype. SSIS variables do not offer a uniqueidentifier datatype.

I have the following text in the SQL Command text box:

exec dbo.spBillingReferenceSource
@BatchID = ? 

When I click the parameters button the mappings are @BatchID for the parameter (which is the exact name of the parameter in the stored procedure) and User::BatchID (the SSIS variable) 

When I run the stored procedure in preview mode in the SSIS dataflow, it returns an error message -- "No value given for one or more of the required parameters"

If I run the stored procedure by typing the value in the datasource box such as listed below, it works and delivers the proper records. I have even modified the stored procedure to convert the column to use varchar(36). However, it still returns the same error message. Does anyone have any suggestions on how I can get this to work. Thank you.


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

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