How to call stored procedure from Dotnet Variable in NAV?

Process 1.

Add These Variables.

Name                 DataType                              Subtype Length

SQLConnection DotNet                              System.Data.SqlClient.SqlConnection.'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

SQLCommand DotNet                              System.Data.SqlClient.SqlCommand.'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

SqlParamCycleCode DotNet                      System.Data.SqlClient.SqlParameter.'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

SQLDbType                 DotNet                      System.Data.DbType.'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

CAL CODE :

SQLCommand := SQLCommand.SqlCommand();

SQLConnection := SQLConnection.SqlConnection();

SQLConnection := SQLConnection.SqlConnection('Server='+'172.16.18.158'+';'+ 'Database='+'DB NAME'+';'
+ 'Uid='+'userID'+';'
+ 'Pwd='+'Password'+';');

SQLConnection.Open;

SQLCommand.CommandText('Sp_UpdateWindowsAuthenticationFromNav');

SQLCommand.Connection := SQLConnection;

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();

SqlParamCycleCode.ParameterName:='@EmployeeId';

SqlParamCycleCode.DbType := SQLDbType.String;

SqlParamCycleCode.Value:=Rec.U_ID;

SQLCommand.Parameters.Add(SqlParamCycleCode);

SQLCommand.ExecuteNonQueryAsync();

SQLConnection.Close;

CLEAR(SQLCommand);

CLEAR(SQLConnection);

How to call stored procedure from MS Dynamics NAV 2018 using Automation Variable?

Automation Variable in NAV 2018

Frankly this is my first interaction with automation variable in my project. So I want to share with NAV community how can we connect our SQL Database and interact with stored procedures to process NAV database.

Step 1. Take the below variables.

Name                  DataType                          Subtype

lADOConnection Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection

lADOCommand Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Command

lADOParameter Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Parameter

lvarActiveConnection Variant

Step 2. Write the below code :-

IF ISCLEAR(lADOConnection) THEN

CREATE(lADOConnection,FALSE,TRUE);

//lADOConnection.ConnectionString := 'Driver={SQL Server};Server=Server Address;Database=Database NAme;Uid=sa;Pwd=Password;

Check with other users also but provide some admin rights on SQL.

  lADOConnection.Open;

  IF ISCLEAR(lADOCommand) THEN
  CREATE(lADOCommand,FALSE,TRUE);
           
  lvarActiveConnection := lADOConnection;
  lADOCommand.ActiveConnection := lvarActiveConnection; 

  lADOCommand.CommandText := 'Stored Procedure Name';

  lADOCommand.CommandType := 4;
  lADOCommand.CommandTimeout := 0; 


  lADOCommand.Execute;

  lADOConnection.Close;
  CLEAR(lADOConnection);
  CLEAR(lADOParameter);
  CLEAR(lADOCommand);

 MESSAGE('Done Successfully');

So NAV Readers just try this code if you face any issue , comment below. Also for some usage bookmark this page.

How to call Stored Procedure in MS Dynamics NAV?

How to Connect with SQl in NAV 2009?

This is for NAV 2009 using Automation Object For Above 2009 you can change these variables to Dotnet. The Link is provided below :- 

Direct Link For Reference in NAV 2013 and Above.

Name DataType Subtype Length

ADOConnection Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection 
LADOCommand Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Command 
LADOParameter Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Parameter 
lvarActiveConnection Variant  
SQLString Text  1024
ADORecordSet Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset 

Name ConstValue
pcodServerName Server NAME
pcodDatabaseName DBNAME
pcodUserID USERID
pcodPassword PASSWORD


Step 1 :- Create the Connection


ltxtConnectionString := 'Driver={SQL Server};'
                        + 'Server='+pcodServerName+';' 
                        + 'Database='+pcodDatabaseName+';' 
                        + 'Uid='+pcodUserID+';' 
                        + 'Pwd='+pcodPassword+';'; 

NameCompany := 'DEV Company';

EXIT(ltxtConnectionString);

Step 2. :- Calling the SQL Connection.


IF ISCLEAR(ADOConnection) THEN
  CREATE(ADOConnection);

ADOConnection.ConnectionString := GetLiveConnectionString;

ADOConnection.Open;

Step 3. :- Write the SQL Querry


SQLString := 'select CAST(sum(SIL.Amount) as float) as InvoiceAmount, '
            + '((SUM(SIL.Amount)-SUM(SIL.[Unit Cost (LCY)]*SIL.Quantity))/SUM(SIL.Amount)*100) as GrossProfit '
            + 'from dbo.['+NameCompany+'$Sales Invoice Line] as SIL '
            + 'join dbo.['+NameCompany+'$Sales Invoice Header] as SIH '
            + 'on SIL.[Document No_] = SIH.[No_] '
            + 'where (SIH.[Posting Date] between '+ ConvertedStartDate + ' and ' + ConvertedEndDate + ');';

Step 4. :- Execute the SQL Querry

ADORecordSet := ADOConnection.Execute(SQLString);

Step 5 :- Manuplate if you want the record set if you want the SQL Record Set.

ADORecordSet.MoveFirst;

Step 6 :- Close All Connection and Clear the Variable.


ADORecordSet.Close;
ADOConnection.Close;
CLEAR(ADORecordSet);
CLEAR(ADOConnection);

Killing an Idle Session Automatically in NAV 2016

Hi All,

This is the requirement of one of our client using MS Dynamics NAV with 20 Users License so they want to kill sessions with idle time of 10 min.

In NAV 2009 R2 we can achieve it via SQL Stored procedure.

But from NAV 2013 we can achieve it via defining time on NAS Application Server.

You may have a look on Screen for Reference but I had faced issue with this solution that

Some users are getting logged out after 10 min. However , this solution has resolved problem upto an Extent if any body has resolved it more perfectly then please do comment.

 

Thanks.

How to create USER in MS Dynamics NAV 2013 R2 ?

This post is to resolve the error while restoring a new database in SQL Server and connecting it with NAV development environment.

The error we Get while connecting with SQL from NAV development Environment.

You do not have access to Microsoft Dynamics NAV. Verify that you have been set up as a valid user in Microsoft Dynamics NAV.

Solution :-

 You can delete all users from SQL Database using the Querry.

DELETE FROM [dbo].[User]; DELETE FROM [dbo].[Access Control]; delete From [dbo].[User Property]; delete FROM [dbo].[Page Data Personalization]; Delete FROM [dbo].[User Default Style Sheet]; Delete FROM [dbo].[User Metadata]; DELETE FROM [dbo].[User Personalization];

You may check it in my Blog Post for NAV 2016 also.

how-to-create-user-and-open-rtc-client

Check if you can connect with RTC Client.

If still error exists , then you can create user from powershell , check here to create user with powershell.

creating-multiple-users-with-powershell

To create user in NAV 2013 R2 , you have to execute the querry, just copy paste this querry in SQl and replace the 

DB Name and domain name.

SET NOCOUNT ON GO USE [DATABASE Name]  -- NAV 2013 database you want to use GO DECLARE @UserID varchar(100) SET @UserID = 'Domain\Username'  -- Windows Login you want to add -- Get security identifier (SID) for specified user.  Login must be setup in SQL Server first. DECLARE @BinarySID binary(100) SELECT @BinarySID = sid FROM sys.syslogins WHERE name = @UserID IF @BinarySID IS NULL   RAISERROR('SQL Server login not found for User %s.', 10, 1, @UserID) -- SID is stored in the User table as a formatted string.  Need to convert it. DECLARE @StringSID varchar(238) DECLARE @i AS int DECLARE @j AS int DECLARE @Grp AS int SELECT @StringSID = 'S-'     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinarySID, 1, 1)))) SELECT @StringSID = @StringSID + '-'     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinarySID, 3, 6)))) SET @j = 9 SET @i = LEN(@BinarySID) SET @Grp = 1 WHILE (@j < @i) AND (@Grp <= 5) BEGIN   SET @Grp = @Grp + 1   DECLARE @val BINARY(4)   SELECT @val = SUBSTRING(@BinarySID, @j, 4)   SELECT @StringSID = @StringSID + '-'     + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))))   SET @j = @j + 4  END -- Check to see if User record already exists DECLARE @UserGUID uniqueidentifier SELECT @UserGUID = [User Security ID] FROM [User] WHERE [Windows Security ID] = @StringSID IF @UserGUID IS NOT NULL   PRINT 'User ID ' + @UserID + ' already exists in User table.'   ELSE BEGIN   -- Generate new GUID for NAV security ID   SET @UserGUID = NEWID()     -- Create User record   INSERT INTO [User]   ([User Security ID], [User Name], [Full Name], [State], [Expiry Date], [Windows Security ID], [Change Password],[License Type],[Authentication Email])   VALUES(@UserGUID, @UserID, '', 0, '1/1/1753', @StringSID, 0, 0 ,'EmailID@Domain.com')    PRINT 'Created User record for User ID ' + @UserID + '. - ' + CAST(@@ROWCOUNT AS varchar) + ' row(s) affected.' END -- Check to see if user is assigned to SUPER role  IF EXISTS(SELECT * FROM [Access Control] WHERE [User Security ID] = @UserGUID AND [Role ID] = 'SUPER' AND [Company Name] = '')   PRINT 'User ID ' + @UserID + ' is already assigned to SUPER role.'   ELSE BEGIN    -- Create Access Control record to add user to SUPER role   INSERT INTO [Access Control]   ([User Security ID], [Role ID], [Company Name])   VALUES(@UserGUID, 'SUPER', '')   PRINT 'Added User ID ' + @UserID + ' to SUPER role. - ' + CAST(@@ROWCOUNT AS varchar) + ' row(s) affected.' END -- User Property record required to allow login IF EXISTS(SELECT * FROM [User Property] WHERE [User Security ID] = @UserGUID)   PRINT 'User Property record already exists for User ID ' + @UserID + '.'   ELSE BEGIN   INSERT INTO [User Property]   ([User Security ID], [Password], [Name Identifier], [Authentication Key], [WebServices Key], [WebServices Key Expiry Date],[Authentication Object ID])   VALUES(@UserGUID, '', '', '', '', '1/1/1753','')   PRINT 'Created User Property record for User ID ' + @UserID + '. - ' +  CAST(@@ROWCOUNT AS varchar) + ' row(s) affected.' END SET NOCOUNT OFF GO

And execute the Querry. Now you will be able to connect with NAV development.

You can also download the Querry For NAV 2013 R2 from Here.

Download SQL Querry Directly For MS Dynamics NAV

You can also refer to other website here :-

Link For Other Blog

I hope I had Explained the steps clearly if you like the post and it helped you then please share our post and subscribe to our blog post.

Thanks.

sql server - How to delete large data of table from SQL without lagging? - MS Dynamics Nav

Deleting large chunks of data from querry in SQL?

Requirement :- While deleting G/L Entry and Item ledger entry for creating an activity in Upgradation I came across this requirement.This post is just containing the best SQL Querry which I managed to find out and Test to my Level.

I had 120 millions of Lines in GL entry which was almost impossible to delete using C/AL Language.

Thanks to Stack Over Flow in finding me this querry.

Stackoverflow Link

Please follow these Links for more detailed querry in Sql:-

Link 1

Link 2

Link 3

Link 4

The Best querry which I can find is :-

WHILE (1=1)
BEGIN
DELETE TOP(1000) FROM [Yamaha Music India Private Ltd$Change Log Entry]
IF @@ROWCOUNT < 1 BREAK
END
You can adjust the TOP value according to your need and speed. This is a tested querry you should put it in Querry page of SQL. and Please use with high priority as it will delete all data from table.

Best Of  Luck