mibuso.com
Home› NAV Three Tier
Options

How to Create SQL Stored Prodedures/Views from NAV

gangabhushangangabhushan Member Posts: 4
edited 2015-06-25 in NAV Three Tier
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
Gangabhushan

Comments

  • Options
    bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • Options
    gangabhushangangabhushan Member Posts: 4
    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
    Gangabhushan
  • Options
    bbrownbbrown Member Posts: 3,268
    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?
    There are no bugs - only undocumented features.
  • Options
    gangabhushangangabhushan Member Posts: 4
    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
    Gangabhushan
  • Options
    bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • Options
    MarijnMarijn Member Posts: 69
    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.
  • Options
    bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • Options
    PerJuhlPerJuhl Member Posts: 55
    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
  • Options
    bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • Options
    davmac1davmac1 Member Posts: 1,283
    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.
    David Machanick
    http://mibuso.com/blogs/davidmachanick/
Sign In or Register to comment.