Using additional custom indexes in SQL for custom ItemTypes - any best practice tips?
Hi community, for a couple of tables I use additional SQL indexes for performance reasons. I noticed that the SQL ItemType allows us to specify custom indexes too and even contains a few for the most common used ItemTypes. Aras for example typically uses them for versioned ItemTypes like classic Part and CAD. The Aras SQL ItemType has one big advantage -> it´s a visible item that can be exported. Indexes used directly in the SQL database are less obvious to see for admins. I wonder if we should use something similar in our own custom ItemTypes in general. Especially for versioned ItemTypes. Of course SQL indexes are mainly used for performance reasons. There are multiple ways to optimize performance. But has anyone some tips when custom indexes should be considered by default (!) in the context of Innovator? @ Aras: Do you have any official recommendations or guidelines regarding SQL indexes? Many thanks for any hint! Best regards Angela
How to use a query to get distinct values from a item table
Community, I have created a staging table capability that will be used to generate innovator eBOMs from Oracle manufacturing BOMs. My challenge is working with 1.7 million rows of data in SQL Server. I know I could create a SQL procedure to get the unique (distinct) oracle Assembly IDs but I would like to use a Query Definition. In the data model below, what is circled in RED is the Oracle Data. What is circled in Blue is where I am separating a 1) [multi-Division, multi-Ora Org, with non distinct Assemblies (by Ora Org)] : stg_Oracle_MfgItem_MfgItem INTO 2) [single-Division, multi-Ora Org, Distinct Primary Assemblies] stg_eBOM_PartBOM_Primary AND 3) [single-Division, multi-Ora Org, Other Assemblies (without Primary PartBOM)] stg_eBOM_PartBOM_Other The goal is as follows: 2) stg_eBOM_PartBOM_Primary will be used to generate the innovator [Part BOM] relationships 3) stg_eBOM_PartBOM_Other will be compared to stg_eBOM_PartBOM_Primary to derive Substitutes So I would like to use a Query Definition to gather all the Distinct Oracle Assembly IDs so I can populate the stg_eBOM_PartBOM_Primary table. Thanks in advance for your time and insight. Scott
Connecting to Linked Server (External SQL Database)
Hello all, After many attempts and support of Aras Support, we are still unable to execute SQL Stored Procedures on an external SQL database. Context Aras Innovator Version 26 SQL Server 2017 Goal Execute a Stored Procedure on an external SQL server (NOT the Aras SQL server itself) from a C# Server Method. Attempts Making a connection using Microsoft.Data.SqlClient and the legacy System.Data.SqlClient. This involves using a connection string and executing a plain command. Used code: [embed:dc8ab71f-3b98-42d9-b0f6-e21e02a0f8e2:f6796336-b0f8-4284-bca3-4d7d91f97dd5:type=csharp&text=Innovator%20inn%20%3D%20this.getInnovator%28%29%3B%0A%0Astring%20connectionString%20%3D%20%40%22Data%20Source%3DServerName%5CInstanceName%3BInitial%20Catalog%3DDatabaseName%3BUser%20ID%3DUserID%3BPassword%3DPassword%3BConnection%20Timeout%3DTimeoutValue%3B%22%3B%0Astring%20sqlQuery%20%3D%20%40%22Exec%20%5BServerName%5CInstanceName%5D.%5BDatabaseName%5D.%5Bdbo%5D.%5BStoredProcName%5D%20%40Variant%20%3D%20VariantValue%2C%20%40State%20%3D%20%27StateValue%27%2C%20%40ID%20%3D%20%27GUID%27%2C%20%40Test%3DTestValue%3B%22%3B%0A%0A%0Ausing%20%28Microsoft.Data.SqlClient.SqlConnection%20connection%20%3D%20new%20Microsoft.Data.SqlClient.SqlConnection%28connectionString%29%29%0A%7B%0Aconnection.Open%28%29%3B%0A%0Ausing%20%28Microsoft.Data.SqlClient.SqlCommand%20command%20%3D%20new%20Microsoft.Data.SqlClient.SqlCommand%28sqlQuery%2C%20connection%29%29%0A%7B%0Aobject%20result%20%3D%20command.ExecuteScalar%28%29%3B%20%2F%2F%20Executes%20query%20and%20returns%20the%20first%20column%20of%20the%20first%20row%0A%0Aif%28result%20%21%3D%20null%29%0A%7B%0Areturn%20inn.newError%28result.ToString%28%29%29%3B%20%2F%2F%20Logs%20the%20value%0A%7D%0Aelse%0A%7B%0Areturn%20inn.newError%28%22No%20result%20found.%22%29%3B%0A%7D%0A%7D%0A%7D] Result: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Adding the external server as 'Linked Server' to the Aras SQL server. Then using innovator.applySQL() referencing the external database. Used code: [embed:dc8ab71f-3b98-42d9-b0f6-e21e02a0f8e2:4f6ce192-841a-4134-8fa4-3bb5694c5708:type=csharp&text=Innovator%20inn%20%3D%20this.getInnovator%28%29%3B%0A%0Astring%20sqlQuery%20%3D%20%40%22Exec%20%5BServerName%5CInstanceName%5D.%5BDatabaseName%5D.%5Bdbo%5D.%5BStoredProcName%5D%20%40Variant%20%3D%20VariantValue%2C%20%40State%20%3D%20%27StateValue%27%2C%20%40ID%20%3D%20%27GUID%27%2C%20%40Test%3DTestValue%3B%22%3B%0A%0AItem%20response%20%3D%20inn.applySQL%28sqlQuery%29%3B] Result: This makes the browser client crash and we suspect another timeout in the back-end. Question Does anyone have a method (or workaround) to execute SQL on an external SQL server (NOT the Aras SQL server itself) from a C# server method? Best regards, Daan
Innovator SQL Procedures vs. Date Format - how to prevent conversion to American date format?
Hi community, I use an Innovator SQL procedure to get some data that is merged with a second database. The SQL procedure is called from a server Method When I test the procedure in SQL, date properties like modified_on are shown as in correct regular Innovator db format: But the SQL procedure doesn´t return the date in this neutral UTC format to the Server Method. Instead of the shown date in the SQL, I get a converted date that looks like this: 7/13/2002 1:49:27 PM In my case the date is later displayed in a Form, but in my country nobody uses this date format. I am able convert the date into correct DMY-format in C# or SQL. But these steps are unnecessary complicated cause of the American date in between. I wonder why I get the American MDY-date at the first place. I use German locals for Innovator and the server itself. Does anyone know, what can cause dates to be converted to American format when using SQL procedures? Is there away to prevent it? Thanks for any hint! Angela
How do I set a where statement on a Query Definition against the current date the query is run?
I want to only have it pull a specific relationship (Part AML) if a custom date field "Valid To" is greater than the current date. I am able to create the Where that compares it against a specific date, but this query definition is pulled by another process automatically and I don't want to have to update the Query Definition every day with the current date for whoever ends up running it that day. I currently have: [Valid to] > '2021-11-29T00:00:00' I want something like (this doesn't work) [Valid to] > current_date()Very large ItemTypes/tables in Innovator with SQL Express - what is your experience?
Hi Community, can anyone of you share some experience when using ItemTypes that contain a large number of items? I have a Innovator ItemType that is intended to store up to 1-2 Million items for the start. For me this amount of data is already "a lot". But I assume for other users this is still a very small amount of data. Anyway, I assume that ItemTypes intended for huge amounts of data also requires admins to pay extra attention to ensure system performance. Do you have practical experience in working with large amount of data? Then I would be happy if you could give me some tips! 1. Did you ever face performance issues, especially when using SQL Server Express (which as some performance limitations by default)? 2. Can you recommend me some tips to optimize my ItemType for large data in general? E.g. I want to prevent that users try to display all items as once in the grid. Can I limit this one with max records or are there better options? 3. Anything else that is important to know? Many thanks for any input and best regards! Angela
