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
Login failed for user 'innovator' - MSSQL
I actually found a locked thread that answers this after 20 or so comments. No answer is marked as accepted. There are other very similar threads. None have a screenshot to tell the story of what to do. It ends up I had to enable mixed mode auth for the db. One comment from 9 years ago eluded to this, but with no "How-to", so: Right-click on DB name at root of tree and select "Properties". Enable Mixed-Mode as shown: After this is done right-click on DB name again and select, "Restart". Viola! Well, for me anyway. I really really really think the installer should have told me to do this.
DB Lock
When I use applyAML, sometimes it will cause DB Lock. What conditions will cause this cause? By the way, I have the error message. When I applyAML =><AML><Item type='Activity' action='EvaluateActivity'><Activity>109890D287C24699A1EA639B921F3E18</Activity><ActivityAssignment>698758D37ACA41FDA7A23A3B412868C1</ActivityAssignment><Paths><Path id='3DD97DD4DAE948608796D353E71AF337'><![CDATA['Approve']]></Path></Paths><DelegateTo></DelegateTo><Tasks></Tasks><Variables></Variables><Authentication mode=''></Authentication><Comments></Comments><Complete>1</Complete></Item></AML> => System.Xml.XmlException: An error occurred while parsing EntityName. Line 4, position 203.
Commit the transaction half way through the code and continue the execution from there.
Hi Team, We have a scenario where we want to commit the transaction half way through the code and continue the execution from there. For example : 1. We are working on Background tasks implementation 2. Where we want to change the status [property] of the task from "new" to "in progress" at the beginning of the execution of task. 3. After that we execute some business logic. 4. Later we change the status of task from "in progress" to "done"/"failed" based on the result from 3rd step. What we have observed is Aras considers step 2-4 as a single transaction and commits that at once. But we want to see the change in status of task from new -> in progress [step 2] -> done/failed. [This change of status is must.] We found an reference from Aras Community, where we can use the "CCO.DB.InnDatabase.CommitTransaction();", to commit the transaction half way and continue from there. Questions : 1. Are there any repercussions/issues by using the solution that we have found ? 2. Is there any other way to achieve this ? Thanks !
