Job Queue and automation callback

madsmorre

Hi
I am running a codeunit in a Job queue (NAV 2016). This codeunit writes data to a SQL-database on another Server. I'm using Automation variables to do this.

I can run this codeunit manually and it work fine, but i get an error when it runs on Job Queue.

"Microsoft Dynamics NAV Server attempted to issue a client callback to create an Automation object: 00000514-0000-0010-8000-00aa006d2ea4 (CodeUnit xxxx). Client callbacks are not supported on Microsoft Dynamics NAV Server."

Any ideas?

Best Regards
Mads Morre

OnRun
IF ISCLEAR(Conn) THEN
CREATE(Conn,FALSE,TRUE);

ConnectionString :='PROVIDER=SQLOLEDB;Server=mssql.xxxxxx.dk;Database=XXXX_Stage;User Id=xxxx;Password=xxxxxxxx';
Conn.ConnectionString(ConnectionString);
Conn.Open;

Dimensionrec.SETRANGE(Dimensionrec."Dimension Code",'AFDELING');
IF Dimensionrec.FINDSET THEN REPEAT
SQLQueryStr1 := 'insert into Departments (Dataset, Code, Name) values (''dk'','''+Dimensionrec.Code+''','''+Dimensionrec.Name+''');';
Conn.Execute(SQLQueryStr1);
UNTIL Dimensionrec.NEXT = 0;

Company.SETFILTER(Name,'%1|%2|%3','COMP1','COMP2','COMP3');
IF Company.FINDSET THEN REPEAT
GLEntries.CHANGECOMPANY(Company.Name);
Recordset := Conn.Execute('exec usp_GetMaxLedgerEntry '''+Company.Name+''''); //Gets the last Entry No. from the external database - pr. COMP
MaxId := Recordset.Fields.Item('MaxId').Value;
GLEntries.SETFILTER("Entry No.",'%1..',MaxId+1);

IF GLEntries.FINDFIRST THEN REPEAT
SQLQueryStr1 := 'insert into LedgerEntries (Dataset, Id, AccountId, PostingDate, Description, Amount, DepartmentId, ShopId) values'+
'('''+Company.Name+''','''+FORMAT(GLEntries."Entry No.")+''','''+GLEntries."G/L Account No."+''','''+FORMAT(GLEntries."Posting Date",8,'<Year4><Month,2><Day,2>')+''','''+
CONVERTSTR(GLEntries.Description,'''','´')+''','''+FORMAT(GLEntries.Amount,0,2)+''','''+GLEntries."Global Dimension 1 Code"+''','''+GLEntries."Global Dimension 2 Code"+''');';
Conn.Execute(SQLQueryStr1);
UNTIL GLEntries.NEXT = 0;
UNTIL Company.NEXT = 0;

Conn.Close;
CLEAR(Conn);


Variables
Name DataType Subtype Length
Conn Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection
Recordset Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset
ConnectionString Text 250
SQLString Text 1024
SQLQueryStr1 Text 1024
Dimensionrec Record Dimension Value
GLEntries Record G/L Entry
Company Record Company
MaxId Integer

Slawek_Guzek

Use DotNET instead of automation.

You may need theese:

SQLCommand: DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlCommand";
SQLConnection : DotNet "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlConnection";

and then someting like this perpaps

SQLConnection := SQLConnection.SqlConnection(<YourConnectionString>);
SQLConnection.Open;
SQLCommand := SQLCommand.SqlCommand(<YourQuery>, SQLConnection);
SQLCommand.CommandTimeout(0); // wait indefinately for the command to finish.
FieldValue := FORMAT(SQLCommand.ExecuteScalar);
SQLConnection.Close;

Slawek

madsmorre

Hi Slawek

I get the following error when running with DotNet. I use the same connectionstring

ErictP

Take a look at codeunit 104050 'Upgrade - SQL Mgt.' how to setup and use SQLConnection and command.
You can find this codeunit in UPGTK8.00.00

Slawek_Guzek

https://www.connectionstrings.com/

madsmorre

I just changed the code to this:

//ConnectionString :='PROVIDER=SQLOLEDB;Server=mssql.xxxxx.dk;Database=XXXX_Stage;User Id=xxxx;Password=xxxxxxxx';
ConnectionString :='Server=mssql.xxxxx.dk;Database=XXXX_Stage;User Id=xxxx;Password=xxxxxxxx';

But now I get another error




It is an SQL server 2014 i'm trying to connect to

Slawek_Guzek

Have you checked the https://www.connectionstrings.com/? I guess not as every connection string published in SQL Server section starts from

Provider=...

madsmorre

Hi again

I'm new into using Automations and dotnet, so i'm trying to learn (if I don't explain correct )

I have read in the https://connectionsstrings.com and tried some of the different ways to connect.

It is correct that when I used ADO (Automation) I used

ConnectionString :='PROVIDER=SQLOLEDB;Server=mssql.xxxxx.dk;Database=XXXX_Stage;User Id=xxxx;Password=xxxxxxxx';
But when I changed to using dotnet I can't do this as I get the error "...keyword not supported: 'provider'"

I have then tried the following
ConnectionString :='Server=mssql.xxxxx.dk;Database=XXXX_Stage;User Id=xxxx;Password=xxxxxxxx';
and also
ConnectionString :='Data Source=mssql.xxxxx.dk;Initial Catalog=XXXX_Stage;User Id=xxxx;Password=xxxxxxxx';

I'm able to connect to the SQL server in Microsft SQL Server Managment Studio without any problems with the credentials in the Connectionstring

The reason why i'm changing from Automation (ADO) to DotNet is that I can't put the function in a Job queue with the Automation version. It says that
"Microsoft Dynamics NAV Server attempted to issue a client callback to create an Automation object: 00000514-0000-0010-8000-00aa006d2ea4 (CodeUnit xxxx). Client callbacks are not supported on Microsoft Dynamics NAV Server."

I hope that by changing to DotNet would solve that (I was advised to try this)

Slawek_Guzek

All the connection strings start from "Provider=..." Please observe the case. It is not PROVIDER=.. it may or may not make a difference, but if someting is not working it is worth to follow the example strictly

You may try first to connecto to your local NAV database with intergrated authentication:

ConnectionString := STRSUBSTNO('Data Source=%1;Initial Catalog=%2;Integrated Security=SSPI', yournavserver, yournavdatabase)'

Just try if your code can connect. Make a simple test codeunit which will only try to call the function preparing the connection

Once this is working add "Provider=..." in the front of connection string and try different drivers, SQLNCLI, SQLOLEDB etc. Go to your ODBC admin and see what drivers do you have in your system.

Once you have tested your preferred provider try replacing intergated authentication with user/password. All with your local NAV Database.

The last step would be to replace the local server/database with the correct one.