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);

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.

How to create user and open RTC Client Of MS Dynamics NAV 2016 and above?

---------------------------

Microsoft Dynamics NAV

---------------------------

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

---------------------------

OK   

---------------------------

Requirement :-

 This Error occurs when we install a new Database in SQL and after creating the instance we are going to run RTC from Development Environment.

Cause:-

New database does not contains the Windows Login ID through which we are connecting.

Solution :

Step1.

To resolve this error , Open SQL Server Management Studio SSMS from Start and in your Login assign the USER MAPPING for database. For reference you may check it.

Step2.

Now if your DB is new installed then you need to Run This Querry. It will Delete old user and their data.

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];

Step3.

Now Run this Querry to create Your USERID in User table and other Table. 

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],[Contact Email])   VALUES(@UserGUID, @UserID, '', 0, '1/1/1753', @StringSID, 0, 0 ,'VE00AC832@yamaha-motor-india.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],[App ID],[Scope])   VALUES(@UserGUID, 'SUPER', '','2de23703-bbb9-4542-970d-84b6e5597f53','0')   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

Step4.

Now run the RTC Client From Dev Environment. You can also Download both the Querry From below mentioned link.


Link 1 Delete Querry :- 

Link2 SQL User Creation In NAV 2016 :- 

Hope you enjoyed my Post and this solution helped. If you Liked it then please do share and comment below your new requirement and errors , We will revert you back soon.