Hello-
I have been experiencing a strange problem for quite some time that I have not been able to solve by talking with Ignition support or asking people generally smarter than me. Here’s whats happening:
I have a MSSQL database instance that is one of 5 database connections (i.e. dbo.Quality, dbo.Production, etc) When I query a table from dbo.Quality and dbo.Production from SQL Management Studio (SELECT * FROM table), I get all rows from the table (4000+ rows, etc). This makes sense!
When I run exact same query from Ignition SQL Query binding, or from the DB Query Browser, the query is limited to 100rows, even though I am not putting a limit on it. If i refresh the database connection from the Gateway, the table will show 4000+ rows for 15+ minutes, then will start flashing between 4000+ and 100rows (table basically becomes unusable because it keeps refreshing and scroll bar goes big>small>big>small).
After a while, all queries from this database only return 100 rows, no flashing. However, if I change the query to “SELECT * FROM table ORDER BY xyz ASC” to “ORDER BY xyzzy DESC” it changes the 100 rows that I’m getting in the table. So this makes me think its an issue with however Ignition is receiving the data from SQL and populating the table? Truncating during that step right before it populates table?
If I do the same task from dbo.Production, I do not have this issue. From SQL Management Studio I cannot find any differences in formatting, etc between the two DBs.
I am using:
Ignition version 7.8.3
DBs are formatted MSSQL 2008 (although I have tried reformatting to 2012 and 2014 with no luck)
driver file I have for MSSQL in gateway is called sqljdbc4.jar
Anyone want to be my hero and solve this mystery? Its really annoying!
No idea what is causing it but a few things for narrowing it down:
Are you using the same login credentials in Ignition and SQL Studio?
If you do a SELECT TOP 1000 does it return 1000 rows?
The DB Browser has a check box that limits to 100 rows, is that checked?
Yes, same credentials from Ignition Gateway settings and SQL studio to access all the DBs (the one with the issue and the rest that don’t have this issue)
If I do a SELECT TOP any number above 100, it only returns 100 rows, this includes from the DB browser.
I am really at a loss on this, can you try to create a new temporary table with 1 column in the bad database, add a bunch of random records to it and then test against it?
Yes, no luck. I’m actively building this database so I add new tables all the time and they all experience this issue from that connection. This is a weird one, right?
I’m thinking of duplicating one of the functional databases, deleting all of the tables from it, copying all the tables from the bad DB, and seeing if that helps. I’ll let you know if it works 
Hey,
I’ve seen this type of situation before (not exactly, but the type of problem) when database connections call into a stored procedure, and the stored procedure changes certain environment variables. The problem is that the sql server jdbc driver doesn’t seem to do a good job of resetting the connections when “closed” (which really means returned to connection pool), and so subsequent queries that happen to get those connections can end up with weird results.
I don’t think that Ignition uses JDBC limits any more (due to strange issues like this), so unless you’re using a 3rd party module, my guess is that a stored procedure (or maybe table trigger or function) somewhere is calling “SET ROWCOUNT 100”, and then not setting it back to 0 at the end. This particular command can limit the number of rows processed in a query, and I believe can stick around in the session. That’s why resetting your db connection helps for a bit- that throws out all existing connections and creates new ones.
(See this for a bit more info about rowcount: https://blog.sqlauthority.com/2007/04/30/sql-server-set-rowcount-retrieving-or-limiting-the-first-n-records-from-a-sql-query/ )
One thing you can try is to add “SET ROWCOUNT 0” (no quotes) to the “Initialization Commands” in the advanced section of the db connection. That way, it will be called each time the connection is used. I suspect this will resolve your isssue…
1 Like
Got it, thank you so much! I added to initialization command and after a few minutes so far it is working… fingers are crossed.
Is there any downside to leaving this in the initialization commands instead of going and finding where the problem is coming from?
Well, the main downside I would say is probably that whatever is setting that property could be having unexpected consequences downstream of where it’s set, for further procedure calls or triggers in that chain. There isn’t really a downside I can think of from the Ignition point of view, in that case, you’re just resetting it back as (I believe) the JDBC should be doing.