Question

CData SQL Gateway: Problem to start multiple services on the SQL Gateway

  • 22 March 2024
  • 4 replies
  • 70 views
J
Badge

Hi Tech Support,

I am currently testing your ODBC Connector to Business Central. I managed to connect to Business Central. What I noticed was that the connection was returning all the different divisions that we have on the server. We use the connector to link an SQL Server (as a linked server) but it created a problem. We have multiple tables with the same name which I think it is normal all the different divisions have exactly the same structure tables and field names.

So I decided to create multiple ODBC connections, each of them pointing at a different division (on the company field of the connector). This filtered out perfectly the different divisions.

The problem is that on the “CData SQL Gateway” I can only start the connection with one of them (that happens to have the default name of “CData D365BusinessCentral Sys”) They are configured exactly the same, using different ports but pointing at different divisions (Company).

On the users tab I use also exactly the same user.

When I hit on the Edit button on the SPGCAN service name (see first screen) It opens the screen of the ODBC connection and I when I test the connection it says successful. But when I try to start the service for that connection it fails.

Please help. I really need to buy this connector but I am stopped by the capability of segregating the different companies.

The error that I get is as it follows:

[2024-03-22T08:19:55.876-07:00] SPGCAN Win32 status did not change within the expected time interval

[2024-03-22T08:19:55.876-07:00] The SPGCAN expected status was Running, but the actual status is Stopped.

[2024-03-22T08:19:55.876-07:00] Service SPGCAN status: Failed.

 

 

Thanks in advance.

 

4 replies

J
Badge

I just found case similar to mine, I tried it but it did not work. I modified my second odbc connector, it returned the right schema but the Gateway refuses to start the service.

 

 

Ethem Q
Userlevel 5
Badge

Hi @Julio Ravazzano 

Based on the description of the issue you have shared, I would like to point out that the error message you are getting "Win32 status did not change within the expected time interval" is actually a very generic one and usually comes up when SQL Gateway service is ran as a Windows service which can be enabled by checking the 'Run as a Windows Service' option in the Status tab. When unchecking this option and also in the SQL Gateway logfiles you should see an underlying error which is the actual error.

2024-03-25T12:12:25.733-04:00	0	[ |Q-Id]	*[2024-03-25T12:12:25.724-04:00][ERROR] The service 'D365BC2ndOrg' can't be started. Error: '[500] The registry token store location 'registry://%DSN%' is invalid'.

 

The error "Error [500] The registry token store location ‘registry://%DNS%’ is invalid’." can occur when we haven't established a connection with the ODBC Data Source we are connecting to, as the registry does not currently have the registered tokens.

We have a post on our Community Form outlining how to resolve this issue, linked below which you might want to check:

https://community.cdata.com/editions-90/sql-gateway-error-the-service-configured-service-can-t-be-started-546 

 

If the above does not help, my assumption for this particular scenario would be that the second System DSN that you have created might not have been saved in the registry due to lack of Administration rights. Running both the ODBC driver and SQL Gateway as an Administrator seems to resolve the issue for me. Can you give this a try and let me know if this works for you?

Please feel free to reach out to the CData support team at support@cdata.com if you have any other questions.

J
Badge

Thanks for the information. However, I have tried following the exact same path (From within the CData SQL Gateway) and the application shows me Successful. Here is my latest attempt. Once we get this resolved, we are buying the connector. 

 

Ethem Q
Userlevel 5
Badge

Hi @Julio Ravazzano 

To recap your situation, you have created two System DSNs for two different connections where you have defined a different Organization in each connection. Also, there are two separate Services in SQL Gateway where you are selecting each System DSN accordingly and the problem occurs when trying to start both services at the same time, is that right? Now, from the screenshot you provided in your last comment I am not seeing the name of the Company being specified. As you might have already know, connections to Linked Server via SQL Gateway do not support connecting to multiple schemas at the same time so you will need to go with the approach mentioned above by making use of two separate System DSNs.

Returning, to the initial response, can you confirm you tried running the ODBC and the SQL Gateway as an Administrator as per the previous suggestion?

If that does not help, the next step would be to try changing the OAuthSettingsLocation from the default value to registry to a local text file. You can set the OAuthSettingsLocation to a local path directory where the driver has permission to write to. See example below:

 

 

After this change, test the connection again and hit OK. Kindly close SQL Gateway if it is already running and make sure to restart it by running it as an administrator.

 

If any of the above does not resolve the issue, please contact the support team at support@cdata.com or submit a support ticket here  with the details of the issue and someone from our team will help you further troubleshoot the issue.

 

Reply


Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=
Terms and ConditionsCookie settings