[This topic is migrated from our old forums. The original author name has been removed]
There seems to be a problem with refreshing monitors that use a temporary table.
I have several queries that monitor the number of times something happens every 5 mins between time A and time B.
To do so, I set up a temporary table and insert the data for each 5 mins between A and B using a while loop.
Sadly, this prevents you being able to monitor the values as they change. When you first create the monitor it retrieves fine but any subsequent attempt to refresh the data returns an error.
I've tried this in 6.5.7 and 6.5.9 with the same results. See the example SQL:
bq. create table #test( \\ myfield int, \\ myfield2 int \\ ) \\ declare @loop int \\ set @loop=0 \\ while @loop<10 \\ begin \\ insert into #test (myfield,myfield2) values (@loop, @loop*2) \\ set @loop = @loop + 1 \\ end \\ select myfield, myfield2 from #test \\ drop table #test
If I save this as a bookmark and tick the monitor checkbox I can then generate a graph for it once but subsequent refreshes of the data result in the error:
bq. An error occurred while executing the database request for: \\ Microsoft SQL Server \\ 08.00.2055 \\ jTDS Type 4 JDBC Driver for MS SQL Server and Sybase \\ 1.2.2 \\ Short message: \\ Cannot re-execute since this result set was produced as one of several in a multi result set operation (typically a stored procedure outputting several results). \\ System Information: \\ Product: DbVisualizer Personal 6.5.9 \\ Build: #1373 (2009/08/29 10:34) \\ Java VM: Java HotSpot(TM) Client VM \\ Java Version: 1.6.0_10 \\ Java Vendor: Sun Microsystems Inc. \\ OS Name: Windows XP \\ OS Arch: x86 \\ OS Version: 5.1
The problem with this is that DbVisualizer recognize the result set as being a result of Sybase implicitly executing multiple statements i.e. your procedure code with multi inserts and then the final select.
This check is done to prevent that a result set produced from such procedure invocation is reloaded since we cannot determine what result set belongs to what grid in DbVisualizer.
However, the current restriction seem to be a bit too harsh if there is only one result set produced. I will make a ticket for this to be fixed in a coming version.
almost 9 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Problem with monitors
I think a safer assumption would be that the final table in the resultset is the one to display. This might not be correct for all multi-statement queries but would be accurate for 90% of the ones I've seen.
A quick test (select 1,1; select 2,2; select 3,3;) shows that the first time a monitor runs it shows the first table in the resultset.
What surprised me at the time is that it allowed me to run it the first time but not refresh the data.
Thanks for the clarification though - I look forward to seeing what other features the next version holds.
This one's got a workaround if anyone needs it - just keep the monitor window open and open the Bookmark Editor too. When you want to refresh the data just double-click the monitor checkbox for your query and then click the refresh button in the monitor window and all your graph customisations will be kept.