And I wondered if it was so easy as is looked. So I tried it out and indeed: It was so easy.
Now, I need something a little more complicated: I want to be able to send a parameter (I only need 1!) into the function. And I want to put the command in SQL Server Agent.
So I created a codeunit and I published it. Nothing new here.
Then I created a function in that codeunit that receives 1 parameter:
SomeParm(Parameter : Text) : Text
EXIT(’The Parameter=’ + Parameter);
Now I changed the powershell code a little to be able to pass that parameter:
$test = New-WebServiceProxy -Uri http://127.0.0.1:7047/DynamicsNAV/WS/Some Company/Codeunit/powershelltest -UseDefaultCredential true
and I got:The Parameter=xx
So that works!
Now I need this in SQL Server Agent!
So, I create a new SQL Server Agent Job.
I change the “Type” in the job step to “PowerShell” and put the previous powershell-code in it.
I save all, and run it and got some error. NNNNOOOO!!!!
But reading the error I noticed that the error was not as bad as it first looked. SQL Server Agent had still the standard Network System as user. And that user has no access-rights in my NAV DB. So changing the SQL Server Agent user, restarting the service, re-running the job gave me the correct result:job completed with success!
In my company, we developed a small C# program that did all this job, but we need Visual Studio to do it, compile the program and copy the executable to the server were we want to use it. Powershell on the other hand, sis a part of Windows (at least the more recent versions), so it is a lot easier to use.
I already had Powershell on my to-learn list, but it just got some more priority now.
Next on my list to try, is this:
An XML-file somewhere on the disk, a function with an XML-port as a parameter in the codeunit published as a webservice. The powershell must read the file and then call the webservice with the content of the XML-file.
But that is for another post.