Sample code connection error

I have Quantify installed and am able to successfully connect.
I have downloaded the sample code for Invoice Sync.
I am receiving the following error executing the sample code.
What is causing his error?
Thanks

Avontus.Core.DataPortalException
HResult=0x80131500
Message=DataPortal.Update failed (System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) —> System.ComponentModel.Win32Exception (0x80004005): No process is on the other end of the pipe
at System.Data.SqlClient.SqlInternalConnectionTds…ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at Avontus.Core.Data.ConnectionManager1.GetManager(String database, Boolean isDatabaseName, String label)
at Avontus.Rental.Library.Utility.DbUtil.GetConnectionManager()
at Avontus.Rental.Library.Security.AuthenticateUserCommand.DataPortal_Execute()
at dm(Object , Object[] )
at Avontus.Core.Reflection.MethodCaller.CallMethod(Object obj, DynamicMethodHandle methodHandle, Object[] parameters)
ClientConnectionId:c4decc04-6a18-407a-ba06-030a31a7891f
Error Number:233,State:0,Class:20)
Source=Avontus.Core
StackTrace:
at System.Data.SqlClient.SqlInternalConnectionTds…ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at Avontus.Core.Data.ConnectionManager1.GetManager(String database, Boolean isDatabaseName, String label)
at Avontus.Rental.Library.Utility.DbUtil.GetConnectionManager()
at Avontus.Rental.Library.Security.AuthenticateUserCommand.DataPortal_Execute()
at dm(Object , Object[] )
at Avontus.Core.Reflection.MethodCaller.CallMethod(Object obj, DynamicMethodHandle methodHandle, Object[] parameters)
at Avontus.Core.Reflection.MethodCaller.CallMethod(Object obj, DynamicMethodHandle methodHandle, Object[] parameters)
at Avontus.Core.Server.SimpleDataPortal.Update(Object obj, DataPortalContext context)
at Avontus.Core.DataPortal.Update(Object obj)
at Avontus.Core.DataPortal.Update[T](T obj)
at Avontus.Rental.Library.Security.AuthenticateUserCommand.Execute(String username, String attemptedPassword)
at Avontus.Rental.Library.Security.AvontusPrincipal.Login(String username, String password)
at Quantify.InvoiceSync.Program.Main() in C:\B1_Versions\QuantifyInvoiceSync\Program.cs:line 52

Inner Exception 1:
CallMethodException: DataPortal_Execute method call failed

Inner Exception 2:
SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Inner Exception 3:
Win32Exception: No process is on the other end of the pipe

Hi, rhitch.

Since we could not reproduce this on our end, can please review the below conditions and if any of it is not true for your test setup; can you try with satisfying all the below and please let us know.

  • Quantify is installed and can login from the same machine that the sample code is tested from.

  • The libraries - Avontus.Core, Avontus.Rental.Library, Avontus.Rental.Utility are referenced in the sample project from the above installation folder.

  • SQL Server setup that is being used does not impose any additional access restrictions.

Thank you for posting!

I have been working on the sample C# code all the morning. I found out with the help of the software TCPView from SysInternal (TCPView for Windows - Sysinternals | Microsoft Learn) that when the software calls the line of code:

   bool success = AvontusPrincipal.Login(User, Password);

The NIC sees a communication on port 1443 (SQL port) to my PC IP address instead of the server address “www.mywebsite.com:4443”.

Because the connection string is encrypted, I do not know if the error root cause is in the connection string or another part of the code. Is this API is only consumable within the same local network where is the Quantify server hosted.

I found in the forum a user that has a request similar to my needs, but the Avountus response was that the QuantifySettings.xml file in C:\ProgramData\Avontus\Quantify\Settings\ has the values to perform a remote connection using the Quantify website interface/port. If that is true why the communication seen on my end is trying port 1443 and not 4443.

My Connection String:

<?xml version="1.0" encoding="utf-8"?> 33A8131105BFEA77487C0DD8EB9A935E996BF373913FD721D04063174472350CB8C490E46ECB63BA192ACA41AB57F3BD5AEE4DF4B31FC70712E09E0FF652E90501DF5081E8171C143B460CBD0144E5E7BC9F2C7B99F84938DEDFE9C88AC3B56C0C950F22779394D3E41D25F0C653FE76F8A5B6C45FD5151CC31C8294CC6A4C5C https://www.mywebsite.com:4443/quantify/WcfPortal.svc True true false

Hi, rhitch.

Thank you for the details.

Is this API is only consumable within the same local network where is the Quantify server hosted.

The API can connect to local or remote. The prerequisite is to have the Quantify Client installed and working on the same machine you’re testing it from and is successfully connecting to the same server that you’d like your code to connect to. Basically, the code would be sharing the same configuration and connecting to the same database as the installed Quantify.
Ideally, in your Visual Studio project, you should be referencing the three Avontus.* DLLs from the Quantify installation folder.
Fulfilling both the above should suffice for connection using the first two options (“This computer” and “Local network server”) which are direct SQL connection (not web based). If you’re using the third option, URL based, additional configuration is needed in the Visual Studio project. Please let us know if you need details for it.

image

Thanks for posting.

Hi.
As I posted in my original message…
Yes, I can make a connection to Quantify using the client.
And yes I have the required libs.
image
image

And yes, I am using the 3rd option - URL based.
So Yes I will need further details

Thank you

Hi, rhitch.

Thanks again for the details. To connect to the remote URL, a <system.serviceModel> section, similar to below, may be defined in the config (App.config). Once you’ve got the section setup, you should be able to connect by setting the below properties before invoking Login.

        Avontus.Core.ApplicationContext.DataPortalProxy = "Avontus.Core.DataPortalClient.WcfProxy, Avontus.Core";
        Avontus.Core.ApplicationContext.DataPortalUrlString = "https://quantify-web-url/wcfportal.svc/binary";
<system.serviceModel>
		<behaviors>
			<endpointBehaviors>
				<behavior name="AvontusLargeObjectBehavior">
					<dataContractSerializer maxItemsInObjectGraph="10000000"/>
				</behavior>
			</endpointBehaviors>
		</behaviors>
		<bindings>
			<customBinding>
				<binding name="AvontusBinding" closeTimeout="00:02:00" openTimeout="00:02:00" receiveTimeout="00:42:00" sendTimeout="00:42:00">
					<binaryMessageEncoding>
						<readerQuotas maxArrayLength="2147483647" maxDepth="131070" maxStringContentLength="2147483647"/>
					</binaryMessageEncoding>
					<httpTransport maxReceivedMessageSize="2147483647" allowCookies="false" authenticationScheme="Anonymous" bypassProxyOnLocal="true" hostNameComparisonMode="StrongWildcard" transferMode="Streamed" keepAliveEnabled="true" unsafeConnectionNtlmAuthentication="false" useDefaultWebProxy="true"/>
				</binding>
				<binding name="AvontusSslBinding" closeTimeout="00:02:00" openTimeout="00:02:00" receiveTimeout="00:42:00" sendTimeout="00:42:00">
					<binaryMessageEncoding>
						<readerQuotas maxArrayLength="2147483647" maxDepth="131070" maxStringContentLength="2147483647"/>
					</binaryMessageEncoding>
					<httpsTransport maxReceivedMessageSize="2147483647" allowCookies="false" authenticationScheme="Anonymous" bypassProxyOnLocal="true" hostNameComparisonMode="StrongWildcard" transferMode="Streamed" keepAliveEnabled="true" unsafeConnectionNtlmAuthentication="false" useDefaultWebProxy="true"/>
				</binding>
			</customBinding>
		</bindings>
		<extensions>
			<bindingElementExtensions>
				<add name="CompressionEncoder" type="Avontus.CustomGZipEncoder.GZipMessageEncodingElement, GZipEncoder"/>
			</bindingElementExtensions>
		</extensions>
	</system.serviceModel>

Thanks for posting!

Hi.
The sample code is now working - thank you.

How do the Invoices get updated to indicate that they have been synced ?

Hi, rhitch.

Can you see if the SyncStatus Property of Invoice Object solves it for you?

Thanks for posting!

I think that I have everything that I need except for the Sales Rep.
In the sample code, there is
AvontusUser.GetUser(theInvoice.JobSite.JobEmployee1ID)

this field is empty for the customer that I am working with.

On the printed invoice, there is a field labeled “Project Manager” that has the correct Sales Rep.
How do I reference this field in the code?

Thanks

Hi, rhitch.

this field is empty for the customer that I am working with.

Please note that this would be theInvoice.JobSite.ParentStockingLocation.JobEmployee1ID in case of Scaffold Tags. To further investigate this please link us or send a copy of your database through support noting the report in question.

Thanks for posting.

Thank you for the response.
I am working on getting the DB backup.

Now that the AR Invoice sync is completed, is there example code for Purchase Invoices?