asked
Error calling MSSql Stored Proc from WebService with DateTime
I have a .Net WCFWebService that connects to an MSSQL database. I am calling a stored procedure that takes an sql datetime value as a parameter which is passed from the web service application as a .NET DateTime value via a parameterised procedure call. However, i am getting the following error and i cant work out what the issue is (am new to MSSQL!):- "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated." Interestingly if i use the MsSQL profiler i can grab the executed SQL command and run it now problems from within Management Studio? It appears as:- exec dbo.VRS_UpdatePositionAndDistance @voyageid=19020,@portid=115191,@eta='2012-07-18 11:54:00:000' The database language is set to British, set language British set dateformat dmy Any help is greatly appreciated!!! Thanks. The Stored Procedure code is as follows: ALTER PROCEDURE [dbo].[VRS_UpdateETA] @index int, @eta datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; UPDATE dbo.VMVoyagePort set ETA = @eta, where ID = @index END This procedure is invoked by the follwoing .NET code (ETA is a DateTime): dbWrapper.ExecuteProcWithParams("dbo.VRS_UpdateETA", dbWrapper.CreateParameter("@index", ), dbWrapper.CreateParameter("@eta", ETA)); public void ExecuteProcWithParams(string procName, params DbParameter[] parameters) { try { using (DbCommand cmd = db.CreateCommand()) { cmd.Transaction = trans; cmd.CommandText = procName; cmd.CommandType = CommandType.StoredProcedure; foreach (DbParameter param in parameters) { cmd.Parameters.Add(param); } cmd.ExecuteNonQuery(); } } catch (Exception) { throw; } } public DbParameter CreateParameter(string name, object value) { DbParameter result = null; switch (databaseType) { case DatabaseType.SqlServer: // Sql Server: NULL parameters have to initialised with DBNull rather than NULL result = new SqlParameter(name, value ?? DBNull.Value); break; default: throw new Exception(String.Format("Unknown database type {0}", databaseType)); } return result; }
The problem with grabbing the SQL from profiler, is that it assumes you are running with US defaults on the settings - hence the dates/times are often formatted incorrectly for any other region!
@Kev Riley Sir, I do not think so. If someone is sending a String literal then that will be shown as it is. And I am sure this is the case here as well.
There are quite a few solutions to the problem. If you want it to be handle it in your procedure (just for the application), then add the following at the start of the procedure SET DATEFORMAT MDY --OR --SET LANGUAGE English But that means you then have to pass the date according to MDY/English(US ENGLISH) format. Otherwise, you may still face the same error. Now for the application part, we always tend to send the data with one UniversalFormats like YYYY-MM-DDThh:mm:ss.mmm. This way we do not face any conversion issues.
BTW, Language and DateFormat are not properties of the database. They can be set for the session OR User etc.
You are missing 'T' in between the date and time parts. Its YYYY-MM-DDThh:mm:ss.mmm
But this works if i execute it in Management Studio: exec dbo.UpdateETA @id=19020,@eta='2012-07-21 11:45:00:000' Yet if i execute the stored proc from my web service management studio reports the executed sql as above and the web service reports an exception indicating that 'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value'? I'm a bit lost as to what the difference is? Also if i want to format the .NET DateTime to ensure it gets converted to the ISO format how do i do it? Thanks!
It would work because of the user/session settings you have. I am pretty sure that would be us_english. For .NET, there is [Format Function][1] in which you can define [custom formats][2] [1]:
http://msdn.microsoft.com/en-us/library/59bz1f0h%28v=vs.71%29.aspx [2]:
http://msdn.microsoft.com/en-us/library/73ctwf33%28v=vs.71%29.aspx
Actually i take that back its the same error: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.\r\nThe statement has been terminated." The output from the profiler is: exec dbo.UpdateETA @id=19020,@eta=N'2012-07-18T14:06:00.06'
That's strange. I have checked the same against all the language/DateFormats and it gives no error. Are you sure there is nothing missing in the posted procedure. I can see a surplus comma in there so it would not execute.
I am not a .NET guy. But I have asked one of the application developers and he said that why do not you add the parameters like this SqlParameter paramETA = cmd.Parameters.Add(param, SqlDbType.DateTime); paramETA = DateTime.Now; cmd.ExecuteNonQuery();
How about the suggestion I added i.e. change the application code?
Oh !$%^*&(! Ok so i failed to notice a trigger that was taking my input value and doinf some validation etc. The error message returned made it look like the value i was supplying was at fault. It wasn't! Sorry and thanks for your help!
Evil they are. But I still cannot understand why the execution was successful through SSMS and how the input was passed to the trigger :-????
9 People are following this question.
