.NET

NAV 2013 SQL Query Codeunit Tool (SQL From NAV Part II)


NAV 2013 SQL Query Codeunit tool

i found this nice demo tool by Dynamic Manufacturing Solutions on Mibuso.

Source http://mibuso.com/downloads/nav-2013-sql-query-codeunit
nice tool, based on .NET integration (AD), usefull if you don’t like Powershell.

Purpose of this tool
“Simple codeunit allows you to execute an SQL query to populate any NAV (temporary) table using one call.  Test page is included to demonstrate functionality with a simple query.”

… Nice tool (with demo page & codeunit objects) tu run SQL Statements directly from NAV using .NET integration (ADODB); you can run SQL Select statements and any sql commands (if you have rights).

My Modifications

I did some modifications to load a generic table using dynamics fields.. example field1. field2, fiels3 etc. and possibility to load a saved SQL Statement from “.SQL” Script file or from BLOB field in “SQL Commands Table” etc. ..

Example of utilization
..you can retrieve Intercompany “Contacts” running a simple “SELECT with UNION SQL statement”…
.. also you can publish this page “as a web service” to retrive\expose data from multi-sources database using only a web service connection!

SQL RUN COMMAND

query 2

“How-to use”

SIMPLE QUERY
SELECT SUM(Quantity) AS QuantityTot, [Item No_], [Location Code], [Lot No_], [Serial No_]
FROM dbo.[CRONUS Italia S_p_A_$Item Ledger Entry]
GROUP BY [Item No_], [Location Code],[Lot No_],[Serial No_]
HAVING ([Location Code]<>” AND (SUM(Quantity)) >0)

MORE COMPLEX QUERY WITH HAVING FILTERS
SELECT SUM(Quantity) AS Field1,  [Item No_] as Field2, [Location Code] as Field3,
[Lot No_] as Field4, [Serial No_] as Fileld5 FROM dbo.[CRONUS Italia S_p_A_$Item Ledger Entry]
GROUP BY [Item No_], [Location Code],[Lot No_],[Serial No_]
HAVING ([Location Code]<>” AND (SUM(Quantity)) >0)

SQL STATEMENT TO “CREATE A NEW TABLE”
CREATE ACTIVITY 2 TABLE
CREATE TABLE [dbo].[CRONUS Italia S_p_A_$Activity_2](
[timestamp] [timestamp] NOT NULL,
[Code] nvarchar NOT NULL,
[Description] nvarchar NOT NULL,
CONSTRAINT [CRONUS Italia S_p_A_$Activity$10] PRIMARY KEY CLUSTERED
(
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ANY SQL COMMAND EX  BACKUP…..

etc. etc. Great tool!

Source of original release http://mibuso.com/downloads/nav-2013-sql-query-codeunit

Download My Customized Objects from here (ZIP File)  SQL RunCommand

Annunci

6 risposte »

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...