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

.NET Framework inside SQL Server

marina B.


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

Top

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

Jens K. Suessmeyer


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




Top

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

nielsb

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



Top

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

marina B.

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


Top

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

nielsb

Check the Windows settings.

Niels


Top

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

marina B.

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.


Top

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

nielsb

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


Top

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

marina B.

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.


Top