execute sql query from within navision

mrQQ

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?

kine

Search for ADO. There are some examples how to use it to connect to current DB...

ara3n

Here is a how to

http://www.mibuso.com/forum/viewtopic.php?t=23038

mrQQ

amazing, thank you.

mrQQ

hmm, i couldnt exactly find how to get current server hostname..

garak

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)

mrQQ

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

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)

mrQQ

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

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