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.