-
I can't complete the connection between the tables
HELLO! Can someone please advise on how to now link to the purchase invoice? How to complete this chain? SELECT * FROM [Training$Sales Invoice Header$437] AS sih LEFT JOIN [Training$Sales Invoice Line$437] AS sil ON sil.[Document No_] = sih.[No_] JOIN [Training$Value Entry$437] AS ve ON ve.[Document No_] = sih.[No_] AND…
-
how to clear all entries and transaction from Navision 2016 using sql query
if any one have sql query for deleting all the entries from navision --- i have to create one company so i copied it from preivious company but along with all entries copied ,, i only need master data
-
sys.sysobjvalues
Hi, I am getting this error. The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'sys.sysobjvalues' and the index name 'clst'. The duplicate key value is (60, 808494059, 1, 0). I have restored the database in different servers but no use. DAC is giving a big problem Does…
-
Business Central Telemetry
Hello, I would like to better understand Telemetry capabilities within Business Central. Does anyone have any useful resources I could read / watch to get this information. Thank you!
-
Updating Data in a SQL-Database with DotNet DataAdapter and Data Rows
Hello, I'm provided with the task to update a programm that transfers data to a SQL-Database. It is from an older version and uses ADO recordsets, which simply does not work with NAV Business Central any more. So, I wanted to use System.Data.SqlClient.SqlDataAdapter, System.Data.DataTable, System.Data.DataRow and so on.…
-
Using Transparent Data Encryption (TDE) with Dynamics NAV
In case you have an urgent need for encryption at rest for your Dynamics NAV installation, we just declare support for Transparent Data Encryption... https://blogs.msdn.microsoft.com/nav/2018/04/26/using-sql-server-transparent-data-encryption-tde-with-dynamics-nav/
-
Quickly read company specific table data for each company with SQL
Sometimes I need to check table data from a company specific table in each company. A quick lookup from MSSQL can make live easier. I've made an easy query for this. In this example I want a quick overview from all the job queues and entries in the NAV database. DECLARE @TableName varchar (50) = 'Job Queue'
DECLARE…
-
BackUp Automatically DATABASE NAV
Hi am working in NAV 2017 and i need to saved database automatically ? how can do this ?
-
How I can data copy from one table to another table in vidual studio 2010
How I can data copy from one table to another table in vidual studio 2010 by navision database
-
Updated MSDN documentation on SQL Server setup and configuration
We updated the MSDN documentation on SQL Server setup and configuration two months ago: https://msdn.microsoft.com/en-us/dynamics-nav/installation-considerations-for-microsoft-sql-server Hopefully, this covers most of the topics you need to consider when optimizing a SQL Server instance for optimal performance for Dynamics…
-
Import between Dynamics nav databases
Hello all, I need an advise from our experienced and valuable members, we have a task exchange data between sql servers. 1. From server /MS SQL 2012 standard/to POS database import basic information like customer, item, sales price etc. /no updates on POS/ 2. From 4-5 POS databases /MS SQL 2012 express/ to the server…
-
MySQL Database!!!
If I decide to make a website with a database, why would I need to know SQL but not just knowing how to use MySQL?
-
Using ADO.Net to Read and Update an External Table
I am trying to read and update a table in an external SQL Server. I am able to read the data ok, but I have not been able to figure out how to update the record I read from that table. I am including the Code Example I'm using to read. The statement to update the record is also included. Can someone please help me with an…
-
Deny object changes in NAV
I blogged about it here, but I came accross this forum, so why not just post it here as well :-). Internally at iFacto, we have been working quite heavily on our infrastructure. And if I say "heavily" .. I mean unbefreakinglievable heavily. In a way: we threw everything out .. and we placed a lot in. But we only did this…
-
"Nicely" formatted dates in SQL queries
I would expect that many of us wish formatting dates (to remove the "time" portion of the date field) in Transact-SQL or other queries to the NAV database was easier. I found that a commonly-quoted solution of using convert(varchar, <datefield> ,101) causes problems in Excel if you are querying the SQL databae directly…
-
log file backup
Hi expert, is there any way to schedule the automatic back up for the log file from the SQL server enterprise manager? Please adivse! Appreciate it!
-
How to rename a database including db files
Hi After renaming a sql db in the ssms, the data files are not affected. What is the best approach do rename also the data files. In our case, it wouldnt be a problem if the database is not online during the rename. Thanks in advance Thomas
-
KILL inactive users
Because active users are quite expensive, I prefer to kill sessions of idle users I first log the user activity. I only kill sessions when they are close to our limmit of 33 concurrent users. and because some users are working with batches that may not be killed we can exlude them.. and the script is limmitted to 5 kill…
-
SQL Server Query Hash Statistics
In short: -it is a new feature in SQL2008 that came in late and therefore generated little fuss. -It has very little impact on SQL server performance but can give you a lot of info about slow queries. -there is a tool to monitor Hash stats on 1 or more SQL servers. Here are some links with more detailed info:…
-
Free eBook on SQL Server Maintenance Plans from Brad M. McGe
I downloaded it and read it. Definitely a good book for beginning DBA's. You can download it here: http://www.bradmcgehee.com/2010/01/free-ebook-on-sql-server-maintenance-plans-now-available/
-
Poor performance Dynamics NAV 500SP1 + SQL Server 2008
After migrating SQL Server from 2000 to 2008 I had a lot of problems with performance and locks. ](*,) I found some information that helped me to solve this problems \:D/ and hope it can help any others. First I needed to identify the resources consuming queries:SELECT TOP 30 -- You can change number of lines st.text,…
-
Import Object - SQL Error 15333 -
Hi All, I am trying to import an object to Navsion but I encountered a below error. “The following SQL server error(s) occurred while accessing the AvailabilityTable: 15333, “42000”, [Microsoft][ODBC SQL Server Driver][SQL Server] Error: the qualified @oldname references database (Databasename) other than the current…
-
Some useful links on SQL-installation, disk-configurati, SAN
http://sqlblog.com/blogs/joe_chang/archive/2008/03/04/storage-performance-for-sql-server.aspx http://sqlcat.com/top10lists/archive/2007/11/21/storage-top-10-best-practices.aspx http://kendalvandyke.blogspot.com/2009/02/disk-performance-hands-on-series-recap.html…
-
Using ADO to kick-off SQL jobs from NAV
The sample code below is an example of how ADO can be used to kick-off a SQL Agent job from NAV. This can be helpful when you need to run things but they don't fit into a fixed schedule. This particular code is called from Adjust Cost, but I'm sure there are other uses. It allows us to run nightly SQL maintenance following…
-
Backup compression in SQL Server 2008 R2 Standard
Backup compression is available in SQL Sever 2008 R2 Standard! Read more : http://www.microsoft.com/sqlserver/2008/en/us/R2-editions.aspx
-
NAV Date table in SQL-statement
And now that I am busy with recursion: -- recursive CTE to generate numbers like the NAV date tableDECLARE @from AS DATE;DECLARE @to AS DATE;SET @from = '2009-01-16';SET @to = '2009-03-20';WITH DateTable ([The Date]) AS(SELECT @to UNION ALL SELECT DATEADD(DAY,-1,[The Date]) FROM DateTable WHERE DATEADD(DAY,-1,[The Date])…
-
NAV integer table in SQL-statement
If we want to loop on integers in NAV, we can use the integer table and put some filters on it. The problem is that in SQL, we don't have such a table. It is possible to create the table with a recursive CTE (Common Table Expression). If you want, you can also put the select into a stored procedure and use it. This is the…
-
How to find out if you have sufficient memory on sql server
I saw this blog that had the query. http://blog.sqlauthority.com/2009/10/09 ... o-execute/
-
Get I/O Stats directly from the SQLServer
Here two little scripts, to get some I/O Stats from the SQLServer2005. For the script we use the "sys.dm_io_virtual_file_stats" which provides I/O statistics about database file The two little script are based on this article --> http://www.sqlmag.com/Articles/Print.cf ... leID=93327 The First script calcs. the Percentage…
-
sql server introduction to joins basic of joins
This link gives you a good overview what the different joins do.