Function Editor "Execute" should generate select for SQL Server TVF

The Execute feature of the Function Editor generates non-functioning code when invoked for a SQL Server table-valued function. The Execute action generates a @call statement such as the following: @call "MyFunction"(); Table valued functions should be tested using a select statement such as the following. The presence of function parameters, of course, require additional handling. select * from "MyFunction"();

Hi David, Thanks for reporting this. The @call command is the DbVisualizer database independent command for calling both functions and stored procedure. It should work even for SQL Server by sending an appropriate statement (e.g. SELECT * FROM MyFunction) to the database to invoke the function and assign the result to the DbVisualizer variable. We will look into why this doesn't work for SQL Server. Best Regards, Hans

Hi David, I finally had a chance to look into this today, and I cannot reproduce this problem with MS SQL Server 2005 (09.00.2047), neither with the jTDS driver nor the Microsoft JDBC driver). What database and driver versions do you see this with? Best Regards, Hans

I am using jTDS against SQL 2000, 2008 and 2008 R2. This issue does not seem to be specific to jTDS or SQL Server version since I have upgraded many times over the last few years. Here is a simple TVF that reproduces the problem, at least in my environment: *+create FUNCTION "dbo"."Test"()+* *+returns table+* *+as+* *+return+* *+(select 'Foo' as Bar)+* DbVis generates the following code to call the function: +_*@call "Test"();*_+ Here is the output when executed showing the SQL Server error message: *+... Physical database connection acquired for: Navision 2009 (Development)+* *+15:37:23 [@CALL - 0 row(s), 0.000 secs] [Error Code: 2809, SQL State: S1000] The request for procedure 'Test' failed because 'Test' is a table valued function object.+* *+... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]+* Edited by: David Taylor on Oct 30, 2012 8:36 PM

Hi David, Ah, my mistake. I tested with functions returning simple values and that works fine with @call. I'll have a look at what we can do about TVF's. Best Regards, Hans

Hi David, Just wanted to let you know that this will be fixed in the next 8.0 maintenance release as well as in the next 9.0 Beta. The @call command results in a SELECT * FROM myTVFunction(params?) statement being executed behind the scene to get the result. Best Regards, Hans

The fix for this in the 9.0 beta is working well. Thanks! David

Hi David, Just wanted to let you know that this is now also fixed in the just released DbVisualizer 8.0.12. Best Regards, Hans