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!