Call SQLServer UDF with decimal parameter

[Guest]

Hi,
when I attempt to call a SQLServer UDF from VBA/ADODB I get an errormessage:
Runtime error: -2147467259
Invalid scaling

UDF is as follows

CREATE FUNCTION [dbo].[fZeiteinheitUmrechnen] (
@Wert as decimal(10,2) ,
@QuellZeiteinheit as int,
@ZielZeiteinheit as int
)
RETURNS decimal(10,2) AS
BEGIN
Declare @ZeitZiel as decimal(10,2)

Select @ZeitZiel = Round (@Wert * UMRECHNUNGSFAKTOR , 2)
From TB_ZEITEINHEIT_UMRECHNUNG
Where ZEITEINHEITNUMMER = @QuellZeiteinheit and
ZIELZEITEINHEITNUMMER = @ZielZeiteinheit

Return (@ZeitZiel)
END

VBA Function:

Public Function ZeiteinheitUmrechnen( ZeitWert As Double,
Quellzeiteinheit As
Integer,
Zielzeiteinheit As
Integer) As Double

Dim cmd As New ADODB.Command, par As ADODB.Parameter

With cmd
.CommandText = "dbo.fZeiteinheitUmrechnen"
.CommandType = adCmdText
Set par = .CreateParameter("pReturn", adDecimal,
adParamReturnValue)
par.NumericScale = 10
par.Precision = 2
.Parameters.Append par
Set par = .CreateParameter("pWert", adDecimal, adParamInput, ,
ZeitWert)
par.NumericScale = 10
par.Precision = 2
.Parameters.Append par
Set par = .CreateParameter("pQZeiteinheit", adInteger,
adParamInput, , Quellzeiteinheit)
.Parameters.Append par
Set par = .CreateParameter("pZZeiteinheit", adInteger,
adParamInput, , Zielzeiteinheit)
.Parameters.Append par

.ActiveConnection = CurrentProject.Connection
.Execute
ZeiteinheitUmrechnen = .Parameters("pReturn").Value
End With

Set cmd = Nothing
Set par = Nothing

End Function

Can anyone help how to troubleshoot?

 

[Brendan Reynolds]

<quote>
par.NumericScale = 10
par.Precision = 2
</quote>

Shouldn't that be the other way around?
par.Precision = 10
par.NumericScale = 2

 

[Guest]

Thanks Brendan,
now that I swapped values I no longer get the error.
However I get another error related to OLEDB: "Error within a multiple steps
process. ... Data not processed"

I modified the vba code like this:
With cmd
.CommandText = "Select dbo.fZeiteinheitUmrechnen(?,?,?)"
.CommandType = adCmdText
...
' error occured the next line
.execute
end with

I couldn't find any online help code samples on how to specify udf
parameters from vba. Do you see anything wrong with the code above?




--
Thanks in advance
Bodo

<quote>
par.NumericScale = 10
par.Precision = 2
</quote>

Shouldn't that be the other way around?
par.Precision = 10
par.NumericScale = 2

--
Brendan Reynolds
Access MVP

Hi,
when I attempt to call a SQLServer UDF from VBA/ADODB I get an
errormessage:
Runtime error: -2147467259
Invalid scaling

UDF is as follows

CREATE FUNCTION [dbo].[fZeiteinheitUmrechnen] (
@Wert as decimal(10,2) ,
@QuellZeiteinheit as int,
@ZielZeiteinheit as int
)
RETURNS decimal(10,2) AS
BEGIN
Declare @ZeitZiel as decimal(10,2)

Select @ZeitZiel = Round (@Wert * UMRECHNUNGSFAKTOR , 2)
From TB_ZEITEINHEIT_UMRECHNUNG
Where ZEITEINHEITNUMMER = @QuellZeiteinheit and
ZIELZEITEINHEITNUMMER = @ZielZeiteinheit

Return (@ZeitZiel)
END

VBA Function:

Public Function ZeiteinheitUmrechnen( ZeitWert As Double,
Quellzeiteinheit As
Integer,
Zielzeiteinheit As
Integer) As Double

Dim cmd As New ADODB.Command, par As ADODB.Parameter

With cmd
.CommandText = "dbo.fZeiteinheitUmrechnen"
.CommandType = adCmdText
Set par = .CreateParameter("pReturn", adDecimal,
adParamReturnValue)
par.NumericScale = 10
par.Precision = 2
.Parameters.Append par
Set par = .CreateParameter("pWert", adDecimal, adParamInput, ,
ZeitWert)
par.NumericScale = 10
par.Precision = 2
.Parameters.Append par
Set par = .CreateParameter("pQZeiteinheit", adInteger,
adParamInput, , Quellzeiteinheit)
.Parameters.Append par
Set par = .CreateParameter("pZZeiteinheit", adInteger,
adParamInput, , Zielzeiteinheit)
.Parameters.Append par

.ActiveConnection = CurrentProject.Connection
.Execute
ZeiteinheitUmrechnen = .Parameters("pReturn").Value
End With

Set cmd = Nothing
Set par = Nothing

End Function

Can anyone help how to troubleshoot?

 

[Sylvain Lafontaine]

If you want to call an UDF instead of a SP, you should use the full string
with adCmdTxt:

"Select * from dbo.fZeiteinheitUmrechnen (1, 2, 3)"

However, UDF returns scalar variables or (in-memory) table variables, not a
resultset and as such, they should only be called from inside other
functions or SP and not by an application from outside SQL-Server. Calling
an UDF from ADP require the use of an intermediate extended SP on
SQL-Server, hence your multi-step error message. (The call to this extended
SP seems to be buggy when some strange parameters are used.)

When using ADP, you should always use SPs and adCmdStoredProc whenever
possible; not UDF and adCmdTxt.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thanks Brendan,
now that I swapped values I no longer get the error.
However I get another error related to OLEDB: "Error within a multiple
steps
process. ... Data not processed"

I modified the vba code like this:
With cmd
.CommandText = "Select dbo.fZeiteinheitUmrechnen(?,?,?)"
.CommandType = adCmdText
...
' error occured the next line
.execute
end with

I couldn't find any online help code samples on how to specify udf
parameters from vba. Do you see anything wrong with the code above?




--
Thanks in advance
Bodo

<quote>
par.NumericScale = 10
par.Precision = 2
</quote>

Shouldn't that be the other way around?
par.Precision = 10
par.NumericScale = 2

--
Brendan Reynolds
Access MVP

Hi,
when I attempt to call a SQLServer UDF from VBA/ADODB I get an
errormessage:
Runtime error: -2147467259
Invalid scaling

UDF is as follows

CREATE FUNCTION [dbo].[fZeiteinheitUmrechnen] (
@Wert as decimal(10,2) ,
@QuellZeiteinheit as int,
@ZielZeiteinheit as int
)
RETURNS decimal(10,2) AS
BEGIN
Declare @ZeitZiel as decimal(10,2)

Select @ZeitZiel = Round (@Wert * UMRECHNUNGSFAKTOR , 2)
From TB_ZEITEINHEIT_UMRECHNUNG
Where ZEITEINHEITNUMMER = @QuellZeiteinheit and
ZIELZEITEINHEITNUMMER = @ZielZeiteinheit

Return (@ZeitZiel)
END

VBA Function:

Public Function ZeiteinheitUmrechnen( ZeitWert As Double,
Quellzeiteinheit
As
Integer,
Zielzeiteinheit
As
Integer) As Double

Dim cmd As New ADODB.Command, par As ADODB.Parameter

With cmd
.CommandText = "dbo.fZeiteinheitUmrechnen"
.CommandType = adCmdText
Set par = .CreateParameter("pReturn", adDecimal,
adParamReturnValue)
par.NumericScale = 10
par.Precision = 2
.Parameters.Append par
Set par = .CreateParameter("pWert", adDecimal, adParamInput,
,
ZeitWert)
par.NumericScale = 10
par.Precision = 2
.Parameters.Append par
Set par = .CreateParameter("pQZeiteinheit", adInteger,
adParamInput, , Quellzeiteinheit)
.Parameters.Append par
Set par = .CreateParameter("pZZeiteinheit", adInteger,
adParamInput, , Zielzeiteinheit)
.Parameters.Append par

.ActiveConnection = CurrentProject.Connection
.Execute
ZeiteinheitUmrechnen = .Parameters("pReturn").Value
End With

Set cmd = Nothing
Set par = Nothing

End Function

Can anyone help how to troubleshoot?