Using Stored Procedure and Web Service

lagerberg

Hi,
I have a three tier setup with Nav, and I want to use Rashed's method of starting jobs in Nav via Web Services, using a stored procedure call in SQL.

The setup uses domain accounts, and I believe I have delegations correctly set up.
The RTC is working, and so is direct calls to the Nav WS with a browser.

However, when I try to call from a stored procedure in exactly the way Rashed has described, I get error 500 - like this:
Msg 6522, Level 16, State 1, Procedure NavJobScheduler, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "NavJobScheduler":
System.Net.WebException: The remote server returned an error: (500) Internal Server Error.
System.Net.WebException:
at System.Net.HttpWebRequest.GetResponse()
at StoredProcedures.NavJobScheduler(String ObjectType, Int32 ObjectID, String Login, String Password, String Domain, String WebServiceURL)
.

I would really like to get this going - has anyone got ths to work?

/Peter

lagerberg

Hi,
I'm gonna answer this myself, after some trials I found the problem, and it stems from two factors:
First: The config file for webservices could not be changed while either the Nav service or the web service was running.
Second: There was an error in the actual codeunit I was trying to execute.
The funny this is that I found this out when trying to use SSIS web service task, which provided me with enough error information to trace this down!

/Peter

ssingla

lagerberg wrote:

Hi,
I'm gonna answer this myself, after some trials I found the problem, and it stems from two factors:
First: The config file for webservices could not be changed while either the Nav service or the web service was running.
Second: There was an error in the actual codeunit I was trying to execute.
The funny this is that I found this out when trying to use SSIS web service task, which provided me with enough error information to trace this down!

/Peter

Can you further clarify how you solved the problem. I am still facing the same problem even after modifying the NTLM setting in config file while the NAV Server Service and NAV Business Web service was stopped. I am using the exact codeunit provided by Rashed but still cannot solve the problem

ssingla

I have tried to deploy the solution on Cronus database and it worked but the on Clients database it is giving me error. The only important difference between the client and cronus database is that the client database is 2009 with SP1 executables. Will it make a difference? Do we need to upgrade before using Rashed's solution?

lagerberg

Hi,
I am not very experienced with this, but for what it's worth here is what I found:
I needed to set the database that the web service was running against, and in that procedure I probably made some mistake regarding changes to the config file while the services was running. They have to be stopped before you change the config file.

The other error I got was that I could not execute the actual code (RunJob) in the codeunit 50000.
For my purposes I changed that codeunit to do other things, which worked for me.
I would suggest you replace the code in codeunit 50000, RunJob, with something that should work for sure, like just doing a simple update to a record. When you got that working, you could move on.

What I did to verify the error was to create a call in the onrun trigger and just test the code in my RunJob trigger by running the codeunit from object designer.
I hope this will help you.

ssingla

The basic problem is that the application is working fine in CRONUS and not in my client's database. Meanwhile the codeunit function is working fine when called from form/"process only report".

lagerberg

Could it be a license issue?
Is the license stored in the database correct?

ssingla

lagerberg wrote:

Could it be a license issue?
Is the license stored in the database correct?

The license is developer license (I am doing testing on my laptop) so this problem is ruled out. I further tested it on another client database and it worked without any problem. The only problem I see now is that the current DB is 2009 with 2009 SP1 executable and this could be the problem. I have forwarded the issue to our technical team and evaluate any code/object change in 2009 and 2009 SP1 for web services.

lagerberg

I think they have changed some small things in SP1 regarding web services, like the name of the service or something similar.
Also, the web service exposed name of the company and the Codeunit/Page to be called is case sensitive, I'm not sure how that was in he first version.
It could be worth checking as you say!
/P

ssingla

Updating a bit late but the code has worked. The error was due to problem with NAV custom code in Codeunit 1 (Somebody wrote CustomForm.run on function "CompanyOpen"). We were able to catch the problem by using debugger in Visual Studio.

We have used NAV 2009 db with 2009 sp1 executable and it is working without any problem. The solution has been successfully deployed on client site.