execute sql query from within navision

mrQQ
mrQQ Member Posts: 239
edited 2008-05-12 in NAV/Navision Classic Client
Hello,

i want to create a report, which would connect to current sql server and execute sql statement(s), which would create some triggers. basicly, i want to make an installation report.

has anyone tried something like this?

Comments

  • kine
    kine Member Posts: 12,562
    Search for ADO. There are some examples how to use it to connect to current DB...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • ara3n
    ara3n Member Posts: 9,258
    Here is a how to

    http://www.mibuso.com/forum/viewtopic.php?t=23038
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • mrQQ
    mrQQ Member Posts: 239
    amazing, thank you.
  • mrQQ
    mrQQ Member Posts: 239
    hmm, i couldnt exactly find how to get current server hostname..
  • garak
    garak Member Posts: 3,263
    use the Hostname and SQL Server Instance Name you can use the table Server.

    Here you have the Server Name (Host + SQL Instanc if named instance).

    Than you can open an connection to the sever with using ADO (Search the forum for this)
    Do you make it right, it works too!
  • mrQQ
    mrQQ Member Posts: 239
    thanks, got that sorted out :)

    now another problem - i've scripted the trigger as sql script, and it works fine in query window, but i'm not sure how to execute multiple statement query using ADO, it complains for example about GO statement..
  • garak
    garak Member Posts: 3,263
    how complex is your statement. if it is more than 1024 char, make a sql scalar function with parameters and call this function with params from navision (also with ado)
    Do you make it right, it works too!
  • mrQQ
    mrQQ Member Posts: 239
    well, i can't make a function in sql, because the whole purpose of my report is to simplify deployment, that is to automate trigger creation, so that end user doesn't have to open management studio and do things manually :)

    anyway, i think i've sorted it out already :)
  • dspasov
    dspasov Member Posts: 33
    We have this solution.
    This simple code is in a button in Sales Order form and good thing is we can pass dynamicly parameters to the select statement like "Order No.", "posting date" e.t.c

    sqlfile.CREATE('c:\Cargo\Cargo4.sql');
    batfile.CREATE('c:\Cargo\sql-test4.bat');

    sqlfile.WRITEMODE:=TRUE;
    sqlfile.TEXTMODE:=TRUE;

    batfile.WRITEMODE:=TRUE;
    batfile.TEXTMODE:=TRUE;
    sqlfile.WRITE('SET NOCOUNT ON');
    sqlfile.WRITE('SELECT dbo.[XXXXXXX$XXXXXXXXXXXX].[XXXXXXXXX] );




    Name:='C:\XXXX\XXXX;
    batfile.WRITE('sqlcmd -S SERVER5 -U reprt -P s0g00d -d Orbit -s '+" "+' -k 2 -W -m 10 -i C:\Cargo\Cargo4.sql -o '+Name+'.txt');

    sqlfile.CLOSE;
    batfile.CLOSE;
    SLEEP(3000);
    SHELL('c:\Cargo\sql-test4.bat');

Categories