1.) Any suggestions on a work around for the MSSQL INSERT query with 1000+ row result on the insert? The prompt suggests using LIMIT but that isn’t a function in MSSQL, and ultimately, the user needs to do this large insert at specific times so I need it tied to a “button” execution.
2.) Any suggestions on a work around for a query that is over 2000 rows long? (The actual MSSQL query is 2000+ rows) I have a separate work around I can use but figured I’d ask on the topic of SQL query.
3.) As a potential workaround to the above two items, but separate question too, I tried making the queries into stored procedures and executing them through CMT and the HMI but it doesn’t appear to work. I wanted to confirm that executing stored procedures is not a function in CMT/EBP?
I would recommend that you consider implementing this as a stored procedure as well and to confirm, the HMI can call a stored procedure. As a general rule, the syntax you use within our SQL Query object should match the syntax used to call this function within your workbench. The SQL commands are processed by the server not the HMI.
Got it working, I was using EXEC which isn’t recognized by the HMI for some reason, when I switched it to EXECUTE for the SP it worked with the addition of the two lines below to resolve the Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection error for anyone else who may run into the same issue.
Sorry to open this thread again but it is no longer working. When I posted two weeks ago it had successfully executed the stored procedure and left it alone, but I’ve since gone back and tried it multiple times with no luck.
The query in EBP:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
EXECUTE dbo.SYNCTABLE
I copy and paste the query above into SSMS and it runs with no issues. The command ID is LW-100 with a command value of 3. I have a Set Word Object to write continuous value of 3 in order to execute the query. I also have the status, error code, and error message word objects on display when LW-100 = 3. The command ID object, LW-100, shows the value of 3, but the status and error objects all remain 0.
So it’s not showing there is an error sending the command, but it isn’t actually sending the query to server side. I check the server activity monitor and never actually see the command come through to the server but the HMI isn’t showing there is an error so not sure where the disconnect is. Again, it did work once when I posted my response on 2/28 but hasnt since so unsure what I’m missing. When I run the exact same query in SSMS it runs no problem.
Can you please try using EXEC to call your stored procedure instead of EXECUTE? These commands should be equivalent, but in the past we have tested execution of stored procedures within MS SQL using this command without issue.
Sorry for the late response! Without SET ANSI_NULLS and WARNINGS set to ON within the EBP query before the EXEC SP I get a “Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.” error. They aren’t required to execute the SP from SSMS though. Additionally, while it has been about 30 days since I last messed around with this function, I am getting error code 1 on the query with no error message. SP’s seem to be the only issues I’ve been running into on the SQL side, all other queries run well.
I believe that SSMS may add the prefix “SET ANSI_NULLS ON” prior to the stored procedure by default. Can you confirm if this line was added to your stored procedure?
SSMS did add both of those to the SP, so I agree, the statement should have already been taken care of in the SP but for whatever reason when I go to execute it through the HMI the error always says it’s missing. If it helps, the SP is to do a remote insert to another SQL instance on an external server. This is to sync a specific local SQL table with the cloud server that the technician initiates periodically.
We have other SP’s that are executed through the HMI that simply write data to a local table with no issue so it seems to stem from the remote insert SQL. But when you execute the SP through SSMS there are no issues, it’s just when the HMI is trying to execute it.
I would suggest looking at the database role membership for the SQL user account that is embedded on the HMI.
Usually the following roles have to be checked: db_datareader, db_datawritter, and db_owner.
Other roles may be in need accordingly depending on your SQL scripts.
