Error using T-SQL UDF Scalar Function

[ahmerzaidi]

Hi All,

I've used the metadata wizard to import a UDF Scalar function from SQL Server 2008.  The function takes 4 parameters (int, string, string, date) and returns a float.

When trying to test the query I receive the following errors:

When running without Data Items from Query (using static parameters)

• RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-249'.
• UDA-SQL-0187 The database "<dbName>" was not found in the multi-database connection.

and

When running with Data Items from Query as parameters

• RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
• UDA-SQL-0219 The function "<functionName>" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.

Any suggestions?

[ahmerzaidi]

Update:  The function works as expected when used in a stand alone query but does not work when used in a JOIN Query.

Any explanation as to why this is the case?

[blom0344]

Please post the definition of what you call 'join query' What error message do you get when testing the query subject? 

[ahmerzaidi]

By 'JOIN Query' I am referring to the Query Subject output of a JOIN.  Errors are listed in the first post.

[blom0344]

If we can judge the SQL then we can perhaps make suggestions. In other words : The more you put into your posts , the better the chance you will get feedback

[ahmerzaidi]

I believe I have isolated the issue further than I had when I created the original post.

The Native SQL generated within the report (below) seemed to be 2 separate query statements.

I failed to mentioned this earlier, as I did not think it was an issue until now, our model contains stored procedures which have relationship with tables.  When removing a data item that references query items from the stored procedure the generated Native SQL was a single statement.

Removing the data items referenced within the query items from the stored procedure had to be removed from the simple query and subsequently the join query.  Removing them only from the join query did not work.

The question now becomes how to get the functions to work with the use of query items from stored procedures used as parameters or data items within the JOIN query?

BEFORE
----Native SQL (Begin)----
select "Entity"."FundCurrency", "Entity"."FundID"
from "DB"."dbo"."Fund" "Entity"
where "Entity"."CustomerID" = xxxxxx
order by 2 asc
{call "DB"."dbo".cognos_fn_GetSecurityInformation(?,?)}

select "OpenPosition"."FK_CustomerID", "Security"."SecurityID", "Portfolios"."FundID", "Portfolios"."PortfolioName", "OpenPosition"."Quantity", "OpenPosition"."CostBasis", ("OpenPosition"."CostBasis" * "OpenPosition"."Quantity") * "Security"."SecurityMultiplier", "OpenPosition"."TaxBasis", ("OpenPosition"."TaxBasis" * "OpenPosition"."Quantity") * "Security"."SecurityMultiplier", "Security"."SecurityMultiplier", "OpenPosition"."BookValue", ("OpenPosition"."BookValue" * "OpenPosition"."Quantity") * "Security"."SecurityMultiplier", (("OpenPosition"."BookValue" - "OpenPosition"."TaxBasis") * "OpenPosition"."Quantity") * "Security"."SecurityMultiplier", "OpenPosition"."Currency", cast("OpenPosition"."Date" as date)
from "DB"."dbo"."OpenPosition" "OpenPosition", "DB"."dbo"."Security" "Security", "DB"."dbo"."Portfolios" "Portfolios"
where "OpenPosition"."FK_PortfolioID" = "Portfolios"."PortfolioID" and "OpenPosition"."FK_SecurityID" = "Security"."SecurityID" and "Portfolios"."CustomerID" = xxxxxx and "Security"."CustomerID" = xxxxxx and "OpenPosition"."FK_CustomerID" = xxxxxx
order by 2 asc
----Native SQL (End)----

AFTER (removing SP query items)
----Native SQL (Begin)----
select cast("coguda00"."Date" as date), "coguda00"."FK_CustomerID", "coguda02"."PortfolioName", "coguda01"."SecurityID", "Entity"."FundCurrency", "coguda00"."Currency", case "coguda00"."Currency" when 'USD' then 0 else 1 end , "DB"."dbo".fn_Get_ExchangeRate("coguda00"."FK_CustomerID", "coguda00"."Currency", "Entity"."FundCurrency", cast("coguda00"."Date" as date)), "coguda00"."Quantity", "coguda00"."CostBasis", ("coguda00"."CostBasis" * "coguda00"."Quantity") * "coguda01"."SecurityMultiplier", "coguda00"."TaxBasis", ("coguda00"."TaxBasis" * "coguda00"."Quantity") * "coguda01"."SecurityMultiplier", "coguda00"."BookValue", ("coguda00"."BookValue" * "coguda00"."Quantity") * "coguda01"."SecurityMultiplier", (("coguda00"."BookValue" - "coguda00"."TaxBasis") * "coguda00"."Quantity") * "coguda01"."SecurityMultiplier"
from "DB"."dbo"."OpenPosition" "coguda00", "DB"."dbo"."Security" "coguda01", "DB"."dbo"."Portfolios" "coguda02", "DB"."dbo"."Fund" "Entity"
where "coguda00"."FK_CustomerID" = xxxxxx and "coguda01"."CustomerID" = xxxxxx and "coguda02"."CustomerID" = xxxxxx and "coguda00"."FK_PortfolioID" = "coguda02"."PortfolioID" and "coguda00"."FK_SecurityID" = "coguda01"."SecurityID" and "Entity"."CustomerID" = xxxxxx and "coguda02"."FundID" = "Entity"."FundID"
----Native SQL (End)----

[Rahul Ganguli]

In your DataSource set processing property to limited local.

[blom0344]

I wonder why you use stored procedures? If you want to pass parameters, then have a look what you can accomplish by using Cognos SQL queries with coded prompts. If your procedure is just a wrapper for some set based T-sql code, then you may be better off without them. I would only consider using stored procedures when no set based solution can be obtained and you need some procedural approach..

[ahmerzaidi]

Rahul - Processing is set to Limited Local

Blom0344 - I'm using stored procedures because in our SQL Server Reporting Services environments we heavily use table functions to get information based on specific parameters. Seeing that Cognos does not handle table function they had to be wrapped in a store procedure.

I've had a PMR ticket open with IBM regarding this issue for a month now and the explanation I received so far is that this is a "system limitation" and they have never heard of this issue before.

It's hard to believe that I am the first person to use data items from a table and stored procedure (Query 1) and joining to another table (Query 2) and then preforming a SQL UDF function within the JOIN query.

[Lynn]

I believe that Cognos is always going to generate SQL to just execute a stored procedure and bring back a result set, so joining it with anything else in the Cognos layer will result in the two result sets getting put together locally on the Cognos server and not via a database join. That is why you see two native SQL statements generated. That would eliminate the possibility of calling a SQL UDF in the joined query (as you've discovered).

Personally I suspect a poor data architecture if you need to use stored procedures in your reporting environment assuming it is intended to support reporting a la data warehousing principles, but that is another topic altogether.

In your situation you might consider creating a view that does the join between the SP and the table along with the UDF function and then model that in Framework Manager. Or maybe create a view that queries the SP and model the view instead of the proc. I would try an embedded prompt macro in the FM model as a way to satisfy the parameter passing. Not 100% sure I'm on the right track, but that's what I'd go for in your shoes.

The bottom line is that you have to force the join processing back on the database side instead of on the Cognos side if it is absolutely imperative that the UDF be executed based on the joined result set of the proc and the table.

[MFGF]

Wow! I just started reading this, and was going to suggest that your issue was local processing joining the SP result set and the SQL result set and building a projection list, preventing you calling a database function. Lynn pretty much said exactly what I was going to say! I would look at her suggestion of using a database view to join your SP with your table, utilising your db function, then model this view in FM.

Good luck!

MF.


Snet form my fumblefingers
iPhon 5 usig Tapatalk

[ahmerzaidi]

Thanks everyone for your replies and suggestions.

As Lynn eluded to, the store procedures are in fact used to perform functionality that would normally be done within a data warehouse.  And as much as I agree that it is poor architecture, it is the only thing I have to work with. 

The reservations I have around wrapping a view around the store procedure is that all the stored procedures all take multiple parameters.  In order for the view to work I would need to strip the parameters and filter directly on the view.  This will cause an enormous performance degradation has some of the commonly used stored procedures would return 1M+ records without the parameters.

[Lynn]

I was thinking the view could reference variables to pass to the SP and then a prompt macro in the FM model might work to satisfy those values, but you'd have to play with it to see. I'm really guessing on this so take it FWIW :)

[ahmerzaidi]

I honestly don't even know how to start looking into this.  To my knowledge you cannot have parameters be passed in to a view that are passed to a stored procedure.  This is possible with a table functions but that bring us back to square one and the reason I have to use stored procedures in my FM model. 

[Lynn]

I was thinking that something like Oracle's ability to prompt might exist in SQL Server and that the FM prompt macro might serve to satisfy that....long shot I suppose.

http://www.techonthenet.com/oracle/questions/parameter.php

So how about a SQL Server table UDF instead?

http://www.techrepublic.com/article/building-and-using-table-udfs-in-sql-server/6100403

Disclaimer: I'm not a DBA nor do I play one on TV.

[ahmerzaidi]

I am not aware of the prompting function being available in SQL Server.  With regards to table UDF, this is how my issue started.  Cognos does not have support of table UDFs which is why I had to wrap them into a stored procedure.

One thing I tried was to take the table UDFs SQL code and used it as the Query Subject Definition using macro prompts in-line as filters.  The import was successful but the issue with running the UDF scalar function still failed in the same manner.

[Lynn]

Sorry I'm out of ideas for you....apologies for all the red herrings :)

It would be worth posting if you find a solution as it is an interesting situation.

[ahmerzaidi]

I'll definitely post the solution or whatever we decide our work around to be.  Thanks again!

[MFGF]

I am not aware of the prompting function being available in SQL Server.  With regards to table UDF, this is how my issue started.  Cognos does not have support of table UDFs which is why I had to wrap them into a stored procedure.

One thing I tried was to take the table UDFs SQL code and used it as the Query Subject Definition using macro prompts in-line as filters.  The import was successful but the issue with running the UDF scalar function still failed in the same manner.

Did you try defining it as pass-thru SQL in the query subject? You can either do this in the options, or you can enclose the whole thing in two sets of braces {{Your code here}}

Just a straw to grasp at :)

MF.

Sent from my iPad using Tapatalk HD

[ahmerzaidi]

Just tried both suggestions and unfortunately no luck.

[CognosPaul]

I don't have any experience with table functions, unfortunately. Have you tried writing the query by hand in the framework query? Forget the import wizard, try with the curly brackets:

{select myFunction(#prompt('whatever','integer')#)}

[ahmerzaidi]

I don't have any experience with table functions, unfortunately. Have you tried writing the query by hand in the framework query? Forget the import wizard, try with the curly brackets:

{select myFunction(#prompt('whatever','integer')#)}

I did attempted this approach, it seems like Cognos it the same way as the store procedure.