A CLR SVF works on my DB instance but not on the other one

Hello,

I have scalar valued function that simply convert a date from UTC to LET by using the .NET functions..

The code is very simple:

Partial Public Class UserFunction

<Microsoft.SqlServer.Server.SqlFunction(isDeterministic:=True, Name:="ConvertLETDatetoUTC")> _

Public Shared Function ConvertLETDatetoUTC(ByVal DateLETFormat As SqlDateTime) As DateTime

' Add your code here

Dim DateLETFormatToConvert As Date

DateLETFormatToConvert = DateLETFormat.Value

Return DateLETFormatToConvert.ToUniversalTime

End Function
End Class

select ConvertLETDatetoUTC('2007-06-25 10:00:00')

Now the problem is that the function works properly on my sql server instanc but in the moment I deploy the same code on the sql server instance on a remote machine the conversion doesn't work that means that date I pass is not converted.

On the SQl server machine there the .net framework 1.1 and 2.0 ...

I really don't know what to check to solve the problem .. some idea?

Thank you

Can you debug the procedure ? I would use something like the Pipe.Send command to send some debbuging information if you are not able to debug the procedure right on the server. Its hard to tell where the problem is based on, so debugging using SQLPipe should be a good alternative for you.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||As Jens says, you probably should debuf the function - however I don't believe you can use SqlPipe in the function as it is a UDF. But, are you getting any errors, or is it just the wrong data coming back? If it is wrong data, what collation and default regional settings does the remote server have?

Niels
|||

Hi,

thank you for the reply...

I was able from my development environment to debug and the statement:

DateLETFormatToConvert.ToUniversalTime

doesn't change a comma to my date!!

It is really weird but I don't know which settings (windows or sql server) I should check..

Thankx

Marina

|||Check the Windows settings.

Niels
|||

Hello,

I checked the window settings and I found out that the PC where the SQL server instance that gives me problem, use as Date Time zone: (GMT) Casablanca,Monrovia

while my Date time zome is Amsterdam, Rome...

The DBA told met that all the server in test and production has this setting and it cannot be changed.

Could be this the reason of the difference?

If yes hor an I make my .net code run as it should?

Thank you

Marina B.

|||No, I don't think that's the issue. Do you know what the regional setting for dates etc is, not the actual timezone?

Niels
|||

On both server the Regional and Language Options use the English(United Kingdom) formats and the

sQL collation is:

SQL_Latin1_General_CP1_CI_AS

something to do maybe with the .Net framework installed?

ThankX

Marina B.