.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

"Out-of-Range" Error When Passing DateTime (DD/MM/YYYY) into Stored Procedure

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :ASP.Net
Hi everybody, I hope some of you will be able to help me regarding this datetime problem. The query in the stored procedure doesn't accept DD/MM/YYYY format, although all dates are being saved in the format of DD/MM/YYYY hh:mm:ss. Example of query that throws the error: @sDate = '31/8/2010' @eDate = '1/9/2010' SELECT * FROM tb_Schedule where scheduledate >= @sDate and scheduledate <= @eDate  Error:  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value FYI, the SQL Server 2005 is defaulted to British English(2057)for all logins and the DB server itself and my server Regional and Language is set to English (United Kingdom). Any help will be appreciated. Thank you very much.  

View Complete Post

More Related Resource Links

error in passing xml to stored procedure


I have below function that return the error below.


System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: SELECT failed because the following SET options have incorrect settings: ARITHABORT. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.


public List<OrderLineEntity> wsGetShipmentInfoByBulk(string groupStatus, int? warehouseID,
                    int? searchType, DataSet SearchDetail)
            System.Xml.XmlDocument XmlDoc = new System.Xml.XmlDocument();
            System.Xml.XmlNode XmlNodeParent = XmlDoc.CreateNode(System.Xml.XmlNodeType.Element, "root", "");

            foreach (DataRow dr in SearchDetail.Tables[0].Rows)

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'.

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

TSQL: Passing array/list/set to stored procedure (MS SQL Server)

Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

Error Executing CLR Stored Procedure "Item has already been added. Key in dictionary"

Hello, I'm consuming a web service through assemblies in a Sql Server 2005 database. The client was made with VB.net 2005. Everything was fine in the deploy phase but once I compile the assembly generated in my sql server database and try to execute the CLR stored procedure defined in the assembly it crashes throwing the following error: Error: There was an error generating the XML document. Inner Exception: Item has already been added. Key in dictionary: 'SqlCifin.InfoComercial.ParametrosConsultaDTO' Key being added: 'SqlCifin.InfoComercial.ParametrosConsultaDTO' Where SqlCifin is the name of the assembly, InfoComercial is the web reference namespace and ParametrosConsultaDTO is a complex type defined in the WSDL to encapsulate the request parameters. I tried almost everything but nothing seems to work: Already checked the enviroment variables and . I would appreciate any help you can provide me.  PD: I'm using WSE 3.0. Thanks, Andres Diab.

passing dates in stored procedure?

I am trying to pass dates in  a stored procedure and keep getting an error - here is the code and error.  eventually the sproc will be used in SSRS.     ALTER PROC [dbo].[USP_SPROC_X] @DATE DATETIME AS SELECT ITEM , PRICE , SELL_DSCR , PRC_EFF_DT , PRC_END_DT , PROPOSED_BY ,CONFIRMED_BY FROM SALES WHERE @DATE BETWEEN PRC_EFF_DT AND PRC_END_DT   Msg 4104, Level 16, State 1, Procedure [PROCNAME], Line 141 The multi-part identifier "PRC_EFF_DT" could not be bound.

Passing Table Variable to Stored Procedure

My stored procedure expects a uniqueidentifier as a parameter.  However, this unique identifier is stored in a table variable, which looks like this:DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier )When I try to execute a stored procedure by passing the GiftGuid like so:EXEC my_procedure (SELECT GiftGuid FROM @TableGiftGuid) I get an error. What I ended up doing is declaring another variable, storing the GiftGuid into it and then passing the variable to the stored procedure like this: DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifieDECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifier SET @GiftGuid = (SELECT GiftGuid FROM @TableGiftGuid) EXEC my_procedure @GiftGuidThat works, but is there a more elegant way of doing this?

Executing an SSIS package from an stored procedure and passing a parameter to it:

Hello all, I'm trying to execute an SSIS package from an stored procedure, using xp_cmdshell. When I execute the package, hard coding the value on the variable, I get rows inserted to my destination tables, which indicates my package pulled the correct data based on the TransactionID parameter. When I execute the package from the stored procedure, the package executes without issues, but I get no rows inserted into my destination tables, which leads me to think it is a data type issue, between the package and the variables I'm using within the stored procedure. The variable TransactionID on the SSIS package, is a string data type, therefore I'm passing it as a varchar from the stored procedure. Am I doing this correctly? Maybe I'm missing something, can you guys see anything wrong? Also, I tried login into the server and executing these 2 commands on the cmd prompt: One with "" quotes between the TransactionID value: dtexec /FILE "D:\SSISPackages\MyOrderCheckProcess\MyOrderCheck_RenewalOrder.dtsx" /CONFIGFILE "D:\SSISPackages\MyOrderCheckProcess\MyOrderCheckProcess_Config.dtsConfig" /CHECKPOINTING OFF  /REPORTING EWCDI  /SET \Package.Variables[User::TransactionID].Properties[Value];"470219"   And also one without quotes between the TransactionID value: dtexec /FILE "D:\SSISPackages\MyOrderCheckProcess\MyOrde

Error when trying to create a stored procedure

Hello allI am using c# to create a new stored procedure and i keep getting a stupid failed operation exception. This is the code i use static private void CreateProcedure(string connectionString, string userName, string password, string DTName, List<DataColumnsObj> parametersIn, List<DataColumnsObj> parametersOut) { SqlConnection connection = new SqlConnection(connectionString); ServerConnection serConnection = new ServerConnection(connection.DataSource); serConnection.LoginSecure = false; serConnection.Login = userName; serConnection.Password = password; Server server = new Server(serConnection); string storedProcedureName = _procedureName; StoredProcedure sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); sp.Refresh(); try { sp.Drop(); sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); } catch (Exception ex) { } sp.TextMode = false; sp.AnsiNullsStatus = false; sp.QuotedIdentifierStatus = false; AddParametersStoredProcedure(sp, parametersIn, false); if (parametersOut != null) { AddParamet

Error while executing SSIS package from a Stored Procedure


I am getting an error while executing a SSIS Package from SQL Server Stored Procedure.  But when I run it in BIDS, it executes successfully. Any help on this ???

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occured.  Error Code: 0x80040ED. An OLE DB record is available. Source "Microsoft OLE DB Provider for Oracle" Hresult 0x80040eD Description: "ORA-01017: invalid username/password; logon denied"

How can I catch an error from a SqlDataSource stored procedure


I have a stored procedure that creates a dynamic query.  Sometimes the query is not built because there are no elements in the dynamic query.  When the aspx page loads the SqlDataSource throws an error that the query is malformed.  How can I catch this error, and handle it with a default message, before it returns the error to the aspx page?

finding error while excuting stored procedure


Hi friends,

  I have created stored procedure in sql server 2005 and connected that database to asp.net application

when i submit a data from application form corresponding stored procedure

stored procedure(sp1) giving following error

Could not find stored procedure 'sp1'.

I have written following code on buttonclick event

please provide me a solution and point out the error in code if exists

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
    DataSet ds = new DataSet();
    SqlConnection con;
    //Here we declare the parameter which we have to use in our application
    SqlCommand cmd = new SqlCommand();
    SqlParameter sp12 = new SqlParameter();
    SqlParameter sp2 = new SqlParameter();

Stored procedure giving strange error


I have as stored procedure which executes a dynamic SQL  at the end. The dynamic SQL inserts into a temporary table declared at the begining of the procedure. When i execute the proc, it executes for a while and then gives me a truncation error.

When i test the code witht out the proc, it works fine and come back with the result set. I am not able to figure out what culd be the issue.

Kindly assist me in this..I have tried setting all colum lenghts to very high values then required but still it does not work when i execute the proc.

CLR Stored Procedure error:There was an error generating the XML document


I am using a CLR stored procedure to call a webservice. I have marked the project as  strongly named assembly and also created a serialization assembly using Generate Serialization assembly in VS 2008. It created both files which I copied on to the database server. I have created assemblies of both






CREATE PROCEDURE TestList(@date datetime) AS EXTERNAL NAME XXXCLRProcs.[XXXCLRProcs.CLRStoredProcedure].TestList



When I tried to execute stored procedure with @date = N'2000-1-1' I get following error:


A .NET Framework error occurred during execution of user-defined routine or aggregate "TestList": 

System.Exception: Failed to send outbound message There was an error generating the XML document.


   at XXXCLRProcs.CLRStoredProcedure.ConsumeTestList(DataTable dt)

   at XXXCLRProcs.CLRStoredProcedure.TestList(DateTime date)



Here is the code:


   public static void TestList(DateTime date)

custom stored procedure error


We have been presenting an issue randomly since we changed our replication to use custom stored procedures for replication.

We have several offices which replicate to our central datacenter, each of this offices have different data corresponding only to their site and we centralize the data from all databases in only one database.

We use custom stored procedures for some tables only for the insert method, but randomly the update method of that table drops and I have to create again. We have been set the replication by using the 'replication support only' method so it only creates the replications sp. All our articles have the create update, insert and delete stored procedures for replication.

We have done traces for this and the replications is generating the drop but it doesn't recreate the procedure. It only occurs for the articles with custom procs for insert.  

The snapshot jobs for each office are disabled.

error in pivot stored procedure




                    Please check my pivot stored procedure which i am getting error.I think there is a mistake in quotations.I am unable to figure it out.

I have a column name in the table but still getting errors

Invalid column name 'Totalhou.rsInvalid column name 'EmpNo'.


PROCEDURE [dbo].[SAR_Sp_GetScheduledHours](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME

Stored Procedure Control Error


I am wirting a stored procedure, which basically just has some SQL Statements. In one line I am Casting to XML and inserting. Everything works good, but if an un formatted XML comes in the whole SP fails. I want to know a mechanism where by I can control this so what ever records are well formatted should pass through, and the ones that fail should be recorded in some other table something like Error table to just insert ID of the records that failed. Any suggestions. So end result is that SP should run successfully for well formatted XML documents and record ID in some other table for Failed ones.

Thanks Jess

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