This has been an ongoing problem for me since installing Ignition last year. We have 3 sites using Ignition, essentially the same app, with minor differences due to location. The original site is where this problem occurs. When querying SQL Server to populate various charts and tables, it frequently returns only the 1st row. To work around the problem, I added a button to each object to force a requery to obtain the data. This works maybe on the first click but usually after 3 or 4 times. The other 2 plants all have newer server equipment and this problem never occurs. This particular plant has the Gateway running on the same machine as SQL Server 2000 under Windows Server 2003. The other 2 plants have the Gateway running on the same machine as SQL Server 2000 under Windows Server 2003 R2. I have recently upgraded the original system to ver 7.2.3 as well. The problem is becoming extremely annoying as we are adding additional charts that need to update automatically. I have used the db query browser within the designer and it always returns the complete data as well as when using the MS SQL query tool.
Looking for suggestions at this point as to how to help resolve this problem.
Seems the problem has stopped after inserting a “Set Rowcount” statement in every query I’m using… Anyone care to explain why?
We’ve recently been researching various problems caused by the rowcount setting being on or off.
One thing that we’ve observed that might be coming into play in your case, if ROWCOUNT is what’s throwing your query off, is that once set in a query (such as a call to a stored procedure- even if that SP is actually called off a table trigger), it stays for that connection, even as it goes back into the connection pool. That means that for future queries, the results are going to depend on which connection you happen to get from the pool.
One thing that we discovered is that you can actually reset the connection automatically by modifying the connections validation query to the following:
set nocount off;SELECT 1;and making sure that “test on borrow” is enabled.
By doing that, you should get the same results without having to change all of your queries. Let me know if it helps.
Regards,
That makes sense, I have one query on the same window that has a ‘set rowcount 1’. Along with adding what you suggested, except for changing it to ‘set rowcount 0’ for MS SQL Server, I also added it as a last line to my query that has the ‘set rowcount 1’ to reset after performing it. This seems to have resolved the problem… Will be watching it a bit more, but so far so good. Thanks!