How to Create SQL Stored Prodedures/Views from NAV

gangabhushan

Hi,

I have to create SQL stored Procedures/Views from NAV 2015 at the time of new company creation.
Any one has any idea to do this in NAV.
Please help me.

Thanks in advance.

Regards,
Gangabhushan

bbrown

Go into SQL Server Management Studio (SSMS) and create them. Depending on the specific object you are creating you may need to do this before or after creating the company. Document your "create new company" process and make this one or more of the steps. Save the scripts so they can be reused.

IMHO creating a new company is usually not something that is done often enough to justify the expense of developing automation.

gangabhushan

Hi bbrown,

Thanks for the reply. But I have to create from NAV at the time of creation of new company from NAV only not manually or from SQL Server.

Thanks & Regards,
Gangabhushan

bbrown

gangabhushan wrote:

Hi bbrown,

Thanks for the reply. But I have to create from NAV at the time of creation of new company from NAV only not manually or from SQL Server.

Thanks & Regards,
Gangabhushan

Why?

Can you fill us in a little about your role and this request? What is your business relationship to the person making the request?

gangabhushan

bbrown wrote:

gangabhushan wrote:

Hi bbrown,

Thanks for the reply. But I have to create from NAV at the time of creation of new company from NAV only not manually or from SQL Server.

Thanks & Regards,
Gangabhushan

Why?

Can you fill us in a little about your role and this request? What is your business relationship to the person making the request?

We have our own Solution and we are using some stored procedures. Whenever we implement for a customer presently crating the Stored procedures manually. So, need to be automated whenever create a new company in NAV the stored procedures should be created in SSMS.

Thanks & Regards,
Gangabhushan

bbrown

I see. In that case you could look into doing this thru PowerShell. Both NAV and SQL are supported thru PowerShell. Just an idea. I'll leave you to sort out the details.

Marijn

Although I believe it's a very bad idea to spread business logic in a database layer, I suppose you could create stored procedures using an ODBC connector and fire your create procedure statements against SQL like any other SQL statement. This code could be triggerd using the OnInsert of the Company table. But then you need to install the ODBC connector and modify a std. NAV object. I am sure you have your reasons, but I think this architecture is gonna cause some headaches in the future when doing upgrades and restores.

bbrown

Marijn wrote:

Although I believe it's a very bad idea to spread business logic in a database layer, I suppose you could create stored procedures using an ODBC connector and fire your create procedure statements against SQL like any other SQL statement. This code could be triggerd using the OnInsert of the Company table. But then you need to install the ODBC connector and modify a std. NAV object. I am sure you have your reasons, but I think this architecture is gonna cause some headaches in the future when doing upgrades and restores.

I would agree here. Especially if you plan to apply for Microsoft "approval" of your add-on. While technically viable, these sort of "off the reservation" approaches will not pass muster with Microsoft.

PerJuhl

Hi

//Make connection to server
// Conn is Automation 'Microsoft ActiveX Data Objects 2.5 Library'.Connection
UserAdministrationSetup.GET;
UserAdministrationSetup.TESTFIELD("Server Name");
CREATE(Conn);
Session.SETRANGE("My Session",Session."My Session"::"1");
Session.FIND('-');
WITH Conn DO BEGIN
Provider := 'sqloledb';
Properties.Item('Data Source').Value := UserAdministrationSetup."Server Name";
Properties.Item('Initial catalog').Value := Session."Database Name";
IF UserAdministrationSetup."Windows authentication" THEN
Properties.Item('Integrated Security').Value := 'SSPI'
ELSE BEGIN
Properties.Item('User ID').Value := UserAdministrationSetup."User ID";
Properties.Item('Password').Value := UserAdministrationSetup.Decrypt(UserAdministrationSetup.Password);
END;
END;
Conn.Open;

// Run SQL String
TransCompanyName := CONVERTSTR(COMPANYNAME,'/\.','___');
SqlString:= 'Truncate Table ';
Conn.Execute(SqlString);

// Close
Conn.Close;
CLEAR(Conn);

BR Per

bbrown

PerJuhl wrote:

Hi

//Make connection to server
// Conn is Automation 'Microsoft ActiveX Data Objects 2.5 Library'.Connection

In NAV 2015, you should use .NET INTEROP and not ADO.

davmac1

Have you considered whether NAV query will address the fields you need in a view?
This is automatically multi-company, and part of standard NAV.