Can JasperSoft Studio use MSSql functions with 2 parameters?

[mdavidson_2]

I have a function declared on MSSql Server.  The function works as intended on the MSSql Server.  When I try to call it in a select statement in JasperSoft Studio 6.5, I'm getting syntax errors and no results.  The error is specific to the call to the function.

Function name: dbo.GetPersonTypeDesc( )

The select statment looks like this:

SELECT dbo."Incident"."IncidentID",       dbo."Incident"."IncidentTypeENum",       dbo."Person"."PersonTypeCde",       dbo.GetPersonTypeDesc(Incident.IncidentTypeENum,Person.PersonTypeCde) as 'VALUE'FROM   dbo."Incident"          LEFT INNER JOIN dbo."Person" ON             dbo."Incident"."IncidentID" = dbo."Person"."IncidentID"[/code]

What's the proper syntax to call the procedure?

[reportdev]

If its a database function, your syntax could be correct. I'm saying could be because I dont know the in/out parameters for the function. 

In order to debug properly, test your function in SSMS first and see if it gives you the required results.

your post is contradictory. have you created a function or procedure ?.

if dbo.GetPersonTypeDesc is a procedure, then the language should be selected as plsql and it's a different syntax

Check here for more info on the execution of MS SQL procedures. https://community.jaspersoft.com/wiki/how-execute-ms-sql-stored-procedure-jasperreports-server

[mdavidson_2]

Its a scalar function on the database.  It works in SSMS and returns the expected results.

[reportdev]

dbo.GetPersonTypeDesc(Incident.IncidentTypeENum,Person.PersonTypeCde) AS 'VALUE'

change that 'VALUE' as "VALUE" and see if it works. 

[mdavidson_2]

I'm going to post what fixed my issue in case another user ends up with a similar problem:

To reiterate the problem:  I was trying to reference a scalar function declared on a MSSql Server through jasperstudio.  I was getting syntax errors and no dataset would populate in the "data preview". 

What I was doing wrong:  I was focusing on the "syntax error issue".  The problem was not with my syntax referencing the function.  The problem was the data adapter that I was using.  I had used the net.sourceforge.jtds.jdbc.Driver as my adapter to this point.  I was hitting many different databases (Postgres, MySQL, MSSql...) with it without issue.  *BUT*  in order to use the function declared on the MSSql db Server, I needed to create a new data adapter using the com.microsoft.sqlserver.jdbc.SQLServerDriver driver.  NOTE: The "Dataset and query editor dialog" still displayed a syntax error on the line in the select statement that referenced the function, but the data set was sucessfully returned as expected.