A Visual SQL Query Designer – Code project (SQL From NAV Part III)


A Visual SQL Query Designer – Code Project (SQL From NAV Part III)

If some of your customers asks you to gain access to SQL data directly from NAV (as other systems with integrated query editors..) can use this nice tool to run SQL queries (or others T-SQL statements…).
This tools recalls some typical features of SQL Studio Management studio like Visual SQL Query Designer; currently is available in 2 releases (VB.NET and C #), i tested the C# solution that works well.

Tool is under “CPOL Licence”

“Source Code can be modified to create derivative works”, …  you can modify the solution’s source to create derivates. For example you can create a class for NAV .NET integration or create an addin for RTC Client, the tool also can be used directly by calling him from Wshell in NAV (.EXE); you can however also pilot the numbers of records extracted etc. etc. .. nice tool anyway.

Tool features

“This article describes the implementation of a QueryDesignerDialog class that allows users to create SQL queries based on a given OLEDB connection string.

query1

query2

query3

Code Download

Source http://www.codeproject.com/Articles/43171/A-Visual-SQL-Query-Designer

Advertisements

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).

Modifications

I did some modifications to load a generic table using dynamics fields.. example field1. field2, fiels3 etc. and possibility lo 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

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 http://mibuso.com/downloads/nav-2013-sql-query-codeunit

Automate backup on SQL Express and Send Mail Powershell Script


235 download on Technet…

Roberto Stefanetti NAV Blog

sql express backup

Sql Server Backup Automation and send-email
Solution to automate backup on SQL Express and send mail based on first Microsoft SQL BackupScript “sp_SQLBackupDatabases”

Scheduling
NB: SQL Server Agent “is not available” on this SQL release
You can schedule jobs with standard “Windows Jobs“ !

How System Works:
3 Objectsd based procedure:
1) batch file > SQL-Backup.bat (Execute ALL Procedure)
2) backup procedure > SP_SQLBackupDatabase.sql (original Microsoft SQL Backup Script)
3) Send mail > Send-email.ps1 (a send email routine based on Powershell Script)

Example:
sqlcmd -U sa -P xxxx -S .SQL2012EXPRESS -Q “EXEC sp_SQLBackupDatabases @backupLocation = ‘C:SQLBackups’, @backupType = ‘F’”

Original Microsoft Solution: https://support.microsoft.com/en-us/kb/2019698

MSDN Post Link https://gallery.technet.microsoft.com/Automate-backup-on-SQL-21eb452d
Download link https://gallery.technet.microsoft.com/Automate-backup-on-SQL-21eb452d/file/142818/1/SQL-BackupEmail.zip

View original post

Top 10 SQL Server Counters for Monitoring SQL Server Performance — DatabaseJournal.com


Greg Larsen lists his top 10 SQL Server counters for maintaining database performance.

sql journal

Sorgente: Top 10 SQL Server Counters for Monitoring SQL Server Performance — DatabaseJournal.com

Great post by Greg Larsen.

BOOST NAV – How to show most 50 heavy queries on SQL SERVER


query

BOOST NAV – How to show most 50 heavy queries on SQL SERVER

… very nice SQL Script to discover slow Sessions !!

SQL SCRIPT

SELECT TOP 50 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN 1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset

END qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.total_logical_writes, qs.last_logical_writes,

qs.total_worker_time,

qs.last_worker_time,

qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

qs.last_elapsed_time/1000000 last_elapsed_time_in_S,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_logical_reads DESC  /* logical reads */

/* ORDER BY qs.total_logical_writes DESC — logical writes

ORDER BY qs.total_worker_time DESC — CPU time */

 

You are Welcome !