Start a new topic

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

[This topic is migrated from our old forums. The original author name has been removed]

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"();

[This reply is migrated from our old forums.]

Re: Function Editor "Execute" should generate select for SQL Server TVF
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
[This reply is migrated from our old forums.]

Re: Function Editor "Execute" should generate select for SQL Server TVF
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
[This reply is migrated from our old forums. The original author name has been removed]

Re: Function Editor "Execute" should generate select for SQL Server TVF
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
[This reply is migrated from our old forums.]

Re: Function Editor "Execute" should generate select for SQL Server TVF
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
[This reply is migrated from our old forums.]

Re: Function Editor "Execute" should generate select for SQL Server TVF
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
[This reply is migrated from our old forums. The original author name has been removed]

Re: Function Editor "Execute" should generate select for SQL Server TVF
The fix for this in the 9.0 beta is working well. Thanks! David
[This reply is migrated from our old forums.]

Re: Function Editor "Execute" should generate select for SQL Server TVF
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