question

mylo40 avatar image
mylo40 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; }
datetimemssql.net
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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!
0 Likes 0 ·
Thats useful :-)
0 Likes 0 ·
@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.
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

BTW, Language and DateFormat are not properties of the database. They can be set for the session OR User etc.
0 Likes 0 ·
If i am passing the .Net DateTime as a parameter into the procedure how would i set the outuput to the Universal Format? I can see from the profiler that the format is 'yyyy-MM-dd HH:mm:ss' i.e exec dbo.UpdateETA @id=19020,@eta='2012-07-21 11:45:00:000'
0 Likes 0 ·
You are missing 'T' in between the date and time parts. Its YYYY-MM-DDThh:mm:ss.mmm
0 Likes 0 ·
mylo40 avatar image
mylo40 answered
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!
14 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
If i try to format the DateTime using the string format with 'yyyy-MM-ddTHH:mm:ss:mmm' i get the same error. I can see in the profiler that the date time is formatted according to the custom format string and that it is now being passed as a string.
0 Likes 0 ·
It should end with .mmm not :mmm. Please try this and revert.
0 Likes 0 ·
Sorry usman i do in fact have it correct in my code so the error stands! Still confused as to why passing in a .NET DateTime doesn't result in the ISO formatting hitting the DB procedure? As i say i'm new to MSSql...not seen this issue in oracle :-)
0 Likes 0 ·
Actually it results in this error : Error converting data type nvarchar to datetime."
0 Likes 0 ·
Show more comments
mylo40 avatar image
mylo40 answered
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!
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Triggers are evil.....
0 Likes 0 ·
Evil they are. But I still cannot understand why the execution was successful through SSMS and how the input was passed to the trigger :-????
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.