IDalton


I created a function to call my CLR Object (See below). I keep getting the following error (failed because T-SQL and CLR types for return value do not match) no matter what data type I try in the function. The Dll is passing back a string data type. What am I doing wrong

CREATE FUNCTION [dbo].[fnGenerateTheoreticalValue]

(@LiborSpread float,

@Maturity int,

@LGD float,

@OID int,

@ForwardEDF1Yr float,

@ForwardEDF2Yr float,

@ForwardEDF3Yr float,

@ForwardEDF4Yr float,

@ForwardEDF5Yr float,

@LIBOR float)

Returns nvarchar(Max)

AS

EXTERNAL NAME [SQLCLRTheoretical_Values].[SQLCLRTheoretical_Values.Theoretical].[CalcValue]




Re: failed because T-SQL and CLR types for return value do not match. Need help.

nielsb


Hmm, that CREATE FUNCTION should work. You sure you're actually targetting the correct .NET method Can you please post the signature of your .NET method.

Niels







Re: failed because T-SQL and CLR types for return value do not match. Need help.

IDalton

I have been able to call the object before within a stored procedure and I had it setup as a Sub method and sent back the data via SqlContext.Pipe.Send. I changed it to a function and had it returning a string and now I'm having the problem. Here is my code please let me know if you see something.

Imports Microsoft.SqlServer.Server

Public Class Theoretical

Public Shared Function CalcValue(ByVal Spread As Double, ByVal TimeToMaturityQuarters As Int32, ByVal LGD As Double, ByVal OID As Double, _

ByVal Yr1 As Double, ByVal Yr2 As Double, ByVal Yr3 As Double, ByVal Yr4 As Double, ByVal Yr5 As Double, ByVal LiborSpread As Double) As String

Dim numberofquarters As Int32 = 161 '40 years max

Dim cdf(161), pdf(161), yr(4) As Double

Dim PayOffMid, PayOffLow, PayOffHigh, TheorecticalPrice, TheorecticalSpread As Double

Dim PriceMax As Double = 2

Dim PriceMin As Double = 0

Dim PriceMid As Double = 1

Dim PriceAnswer As Double = 100

Dim SpreadMax As Double = 0.5

Dim SpreadMin As Double = 0

Dim SpreadMid As Double = 0.25

Dim SpreadAnswer As Double = 100

Try

yr(0) = Yr1

yr(1) = Yr2

yr(2) = Yr3

yr(3) = Yr4

yr(4) = Yr5

'This section will calculate the Probability Density Function & the

'Cumulative Distribution Function

For counter As Int32 = 0 To numberofquarters

If counter < 4 Then

pdf(counter) = Math.Round((yr(0) / 4), 14)

ElseIf counter < 20 Then

pdf(counter) = Math.Round((yr(Math.Floor(counter / 4) - 1) * IIf(Math.Abs(((3 - counter) Mod 4)) = 0, 0, (4 - Math.Abs(((3 - counter) Mod 4)))) * 0.25 + yr(Math.Floor(counter / 4)) * IIf(Math.Abs(((counter + 1) Mod 4)) = 0, 4, (Math.Abs(((counter + 1) Mod 4)))) * 0.25) / 4, 14)

Else

pdf(counter) = Math.Round(yr(4) / 4, 14)

End If

If counter = 0 Then

cdf(counter) = Math.Round(pdf(counter), 14)

Else

cdf(counter) = Math.Round(cdf(counter - 1) + pdf(counter), 14)

End If

Next

'End PDF & CDF

'Calculate Price

While (Math.Abs(PriceAnswer) > 0.001)

PayOffLow = Payofffind(Spread / 10000, TimeToMaturityQuarters, cdf, pdf, PriceMin, LGD, LiborSpread)

PayOffMid = Payofffind(Spread / 10000, TimeToMaturityQuarters, cdf, pdf, PriceMid, LGD, LiborSpread)

PayOffHigh = Payofffind(Spread / 10000, TimeToMaturityQuarters, cdf, pdf, PriceMax, LGD, LiborSpread)

TheorecticalPrice = PriceMid

If PayOffHigh > 0 Then

PriceAnswer = 0

TheorecticalPrice = 0

ElseIf (PayOffLow < 0) Then

PriceMax = PriceMax * 2

PriceMid = PriceMid * 2

PriceAnswer = PayOffMid

ElseIf (PayOffMid > 0) Then

PriceAnswer = PayOffMid

PriceMin = PriceMid

PriceMid = (PriceMid + PriceMax) / 2

Else

PriceAnswer = PayOffMid

PriceMax = PriceMid

PriceMid = (PriceMid + PriceMin) / 2

End If

End While

'Calculate Spread

While (Math.Abs(SpreadAnswer) > 0.001)

PayOffLow = Payofffind(SpreadMin, TimeToMaturityQuarters, cdf, pdf, OID, LGD, LiborSpread)

PayOffMid = Payofffind(SpreadMid, TimeToMaturityQuarters, cdf, pdf, OID, LGD, LiborSpread)

PayOffHigh = Payofffind(SpreadMax, TimeToMaturityQuarters, cdf, pdf, OID, LGD, LiborSpread)

TheorecticalSpread = SpreadMid

If PayOffLow > 0 Then

SpreadAnswer = 0

TheorecticalSpread = 0

ElseIf (PayOffHigh < 0) Then

SpreadMax = SpreadMax * 2

SpreadMid = SpreadMid * 2

SpreadAnswer = PayOffMid

ElseIf (PayOffMid < 0) Then

SpreadAnswer = PayOffMid

SpreadMin = SpreadMid

SpreadMid = (SpreadMid + SpreadMax) / 2

Else

SpreadAnswer = PayOffMid

SpreadMax = SpreadMid

SpreadMid = (SpreadMid + SpreadMin) / 2

End If

End While

Return (((TheorecticalPrice * 100) & "|" & (TheorecticalSpread * 100 * 100)))

Catch ex As Exception

SqlContext.Pipe.Send("Error - " & ex.Message)

End Try

End Function

Private Shared Function Payofffind(ByRef Spread As Double, ByRef QuarterCalc As Int32, ByRef cdf() As Double, ByRef pdf() As Double, _

ByRef OID As Double, ByRef LGD As Double, ByRef LiborSpread3m As Double) As Double

Dim Payoff As Double = 0

Dim counter As Int32 = 0

Try

While counter < QuarterCalc

If counter = 0 Then

Payoff += Math.Round((pdf(counter)) * (1 - LGD) * ((QuarterCalc - counter) / QuarterCalc), 14)

ElseIf counter < (QuarterCalc - 1) Then

Payoff += Math.Round((((counter) * (1 + LiborSpread3m + Spread) / (QuarterCalc)) + (1 - LGD) * ((QuarterCalc - counter) / QuarterCalc)) * (CType(1, Double) - cdf(counter - 1)) * (pdf(counter)), 14)

Else

Payoff += Math.Round((1 - cdf(counter)) * (1 + LiborSpread3m + Spread), 14)

End If

counter += 1

End While

Return Payoff - (OID * (1 + LiborSpread3m))

Catch ex As Exception

Throw ex

End Try

End Function

End Class






Re: failed because T-SQL and CLR types for return value do not match. Need help.

IDalton

OK I tried something different and I created the function as a "Table value" instead of the Scalar-valued and it compiled this time...I thought I was on my way, but then I ran the following example and I got a different error this time about the return type. Please any help would be appreciated.

CREATE FUNCTION [dbo].[fnGenerateTheoreticalValue]

(@LiborSpread float,

@Maturity int,

@LGD float,

@OID float,

@ForwardEDF1Yr float,

@ForwardEDF2Yr float,

@ForwardEDF3Yr float,

@ForwardEDF4Yr float,

@ForwardEDF5Yr float,

@LIBOR float)

Returns Table(result nvarchar(Max))

AS

EXTERNAL NAME [SQLCLRTheoretical_Values].[SQLCLRTheoretical_Values.Theoretical].[CalcValue]

Example:

Select Result

from dbo.fnGenerateTheoreticalValue(300, 20, 0.25, 1, 0.0498329849, 0.0300874608, 0.0254211649, 0.0227929745, 0.021016988, 0.0535953)

Error:

Method, property or field 'CalcValue' in class 'SQLCLRTheoretical_Values.Theoretical' in assembly 'SQLCLRTheoretical_Values' has invalid return type.





Re: failed because T-SQL and CLR types for return value do not match. Need help.

Sergei Almazov

Your method CalcValue returns String. Corresponding SQL type for String is nvarchar(4000), not nvarchar(max).
So to solve your problem either change method's return type to SqlChars (which maps to nvarchar(max)), or change return type of the SQL function to nvarchar(4000)




Re: failed because T-SQL and CLR types for return value do not match. Need help.

IDalton

it did not work (I changed the function to nvarchar(4000)). I got the same error. I also tried creating a scalar-valued function and got the error listed below. I guess data type string does not match to nvarchar(4000)

CREATE FUNCTION [dbo].[fnGenerateTheoreticalValueTest](@LiborSpread [float], @Maturity [int], @LGD [float], @OID [float], @ForwardEDF1Yr [float], @ForwardEDF2Yr [float], @ForwardEDF3Yr [float], @ForwardEDF4Yr [float], @ForwardEDF5Yr [float], @LIBOR [float])

RETURNS nvarchar(4000)

AS

EXTERNAL NAME [SQLCLRTheoretical_Values].[SQLCLRTheoretical_Values.Theoretical].[CalcValue]

Error:

CREATE FUNCTION for "fnGenerateTheoreticalValueTest" failed because T-SQL and CLR types for return value do not match.





Re: failed because T-SQL and CLR types for return value do not match. Need help.

nielsb

OK, so between you had it working and now - have you re-deployed the assembly to the database I.e. by dropping the functions created against the methods and dropping the assembly and then re-deploying and re-creating. You cannnot just run an ALTER as the method signature has changed.

Niels




Re: failed because T-SQL and CLR types for return value do not match. Need help.

IDalton

I'm confused on what the procedure is.

Am I suppose to do the following

1) Drop the function and recreate it if I change the return value data type

2) For the Assembly I have just been replacing it on the sql server directory. Am I suppose to be doing something else





Re: failed because T-SQL and CLR types for return value do not match. Need help.

nielsb

What do you mean with the SQL Server directory Is that a file directory or what

Wat do you use as development tools, Visual Studio or If you use Visual Studio, what project type do you use

The reason I'm asking is that when you use CLR assemblies (dll) in SQL Server you need to deploy the assembly to SQL Server and then create T-SQL wrapper methods around the methods in your assembly (that's what's going wrong right now for you). But before you create a wrapper method, the assembly has to be in the database.

If you use the "SQL Server Project" project type from VS you have on the build menu a command to automatically deploy the assembly to SQL Server (and that will also create the wrapper mehods if you have attributed your .NET methods). If you don't use that particular project type (regardless if you use VS or not) you have to manually deploy the assembly to the database and then manually create the wrapper methods.

It sounds to me that the assembly at one stage or another has been deployed to the database (as you have had a working proc against it), but that you right now have just re-compiled the assembly, pu it into some directory on a machine, and then tried to create the wrapper.

If that's the case look in SQL Server Books Online for the subject CREATE ASSEMBLY. That should tell you a bit of what you need to know.


Niels




Re: failed because T-SQL and CLR types for return value do not match. Need help.

IDalton

Thank you for the info. Let me look into your suggestions.