SQL: First and Last Day of Current Year

The following SQL code is an example of retrieving the first day and the last day of the current year:
-- first day of current year 

-- last day of current year





Microsoft Dynamics Nav: Adding a Record through Web Services

Microsoft Dynamics Nav Web Services can not only read data, but they can also write data to a Microsoft Dynamics Nav database. This example demonstrates adding a record into a Microsoft Dynamics Nav database through Web Services using an XMLPort.

Microsoft Dynamics Nav: Adding a Record through Web Services

  1. Create and XMLPort for transferring the data. Note: For this example the MaxOccurs property of the element should be set to ‘One’ to set a “one record limit”. It is possible to set multiple records
  2. 1.Create and XMLPort for transferring the data.

  3. Create a CodeUnit and add functions that will be used to through the Web Service
  4. GetCustomers(CustomerCode : Code[10];VAR CustXML : XMLport GetSetCustomer) Customer.SETRANGE("No.", CustomerCode); CustXML.SETTABLEVIEW(Customer); SetCustomers(VAR CustXML : XMLport GetSetCustomer) : Integer // Return 1 if import was successful IF CustXML.IMPORT THEN  EXIT(1) ELSE  EXIT(0);

  5. Publish the CodeUnit as a Web Service
  6. 3.Publish the CodeUnit as a Web Service

  7. Create a .NET Application and register the Microsoft Dynamics Nav Web Service
  8. Create the code that interacts with the registered Web Service (Note: This code is for basic demonstration only and additional coding practices should be applied for production code)
    GetSetCustomers_Binding ws;
            private void Form1_Load(object sender, EventArgs e)
                ws = new GetSetCustomers_Binding();
                ws.UseDefaultCredentials = true;
                ws.Url = "http://localhost:7047/DynamicsNAV/WS/CRONUS%20USA,%20Inc./Codeunit/GetSetCustomers";
            private void btnGet_Click(object sender, EventArgs e)
                Customer cust = new Customer();
                Customers cs = new Customers();
                // in nav the XML should be set to maxoccurence of 1 so that we only return one 
                ws.GetCustomers(txtNo.Text, ref cs);
                cust = cs.Customer[0];
                txtName.Text = cust.Name;
                txtPhone.Text = cust.Phone_No_;
                txtContact.Text = cust.Contact;
            private void btnSet_Click(object sender, EventArgs e)
                Customers cs = new Customers();
                Customer cust = new Customer();
                List<Customer> custlist = new List<Customer>();
                cust.No_ = txtNo.Text;
                cust.Name = txtName.Text;
                cust.Phone_No_ = txtPhone.Text;
                cust.Contact = txtContact.Text;
                cs.Customer = custlist.ToArray();
                if (ws.SetCustomers(ref cs) == 0)
                    MessageBox.Show("No Good.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

The sample application referenced in this post can be downloaded >>>here<<<.

View the following posts for additional examples:

Microsoft Dynamics Nav: Retrieving a set of Records through Web Services

Microsoft Dynamics Nav: Using an XMLPort as a .NET DataSource


Microsoft Dynamics Nav: Retrieving a set of Records through Web Services

In a previous post ( Microsoft Dynamics Nav: Using an XMLPort as a .NET DataSource and Microsft Dynamics Nav: Export XMLPort to File) I had demonstrated how to return a set of records, using Web Services, from Microsoft Dynamics Nav with an XMLPort as a dataset.

Microsoft Dynamics Nav: Retrieving a set of Records through Web Services

The following example demonstrates returning a filtered set of records from Microsoft Dynamics Nav through Services. 

public partial class Form1 : Form
        public Form1()

        GetCustomers_Binding ws;
        Customers customers;
        BindingSource bs;

        private void Form1_Load(object sender, EventArgs e)
            ws = new GetCustomers_Binding();
            ws.UseDefaultCredentials = true;
            ws.Url = "http://localhost:7047/DynamicsNAV/WS/CRONUS%20USA,%20Inc./Codeunit/GetCustomers";

            customers = new Customers();
            bs = new BindingSource();


        private void BindData(string salespersoncode)
            ws.GetCustomers(salespersoncode, ref customers);
            dataGridView1.DataSource = bs;
            bs.DataSource = customers.Customer;

        private void button1_Click(object sender, EventArgs e)

GetCustomers(SalesPerson : Code[10];VAR CustXML : XMLport Customer_Export) IF SalesPerson <> '' THEN Customer.SETRANGE("Salesperson Code",'PS'); CustXML.SETTABLEVIEW(Customer);

The sample application referenced in this post can be downloaded >>>here<<<.


SQL: Rebuild Indexes Online

In the SQL Server Enterprise and Developer versions of SQL Server you can rebuild indexes online. When rebuilding an index online long-term table locks are not used during the index rebuild operation. To rebuild an index online use the REBUILD WITH ONLINE = ON option in the index command.

Online index operations fail if the table contains LOB (Large OBject) data types as part of the index definition as key or nonkey columns. LOB (Large OBject) data types are: image (data type 34), text (data type 35), ntext (data type 99), varchar(max), nvarchar(max), varbinary(max), and xml (data type 241).
One note, rebuilding an index drops and re-creates an index. Rebuilding an index offline forces a scan of clustered indexes and may remove some inconsistency issues.

The following SQL script will rebuild the individual indexes on tables (additionally the ALL option could be used) that do not contain LOB column types, which is what is needed for my situation.
Additionally, this script could be altered to analyze the index columns and adjust the online operation accordingly.
set nocount on;
set ansi_padding on;
set ansi_nulls on;
set quoted_identifier on;

-- declare variables.
declare @objectid as int
declare @tablename as varchar(255)
declare @indexname as varchar(255)
declare @cmd as varchar(1024)
declare @rowcount as int

-- declare a cursor.
declare indexes insensitive scroll cursor
    -- retrieve a listing of database indexes
		,object_name(i.object_id) as [objectname]
		,i.name as [indexname]
		sys.indexes i
	-- where
	    -- if desired filter for certain objects
	    -- i.object_id = #####
	    -- (object_name(i.object_id) like '%$%entry') 

-- open the cursor.
open indexes

-- loop through all the indexes.
while (1=1)
		fetch next from indexes into @objectid, @tablename, @indexname;
		if @@fetch_status <> 0 break;
		-- indexes cannot be rebuilt online if the table contains large data types
		-- check if the table has a large datatype field
			@rowcount = count(c.system_type_id)
			sys.all_objects ao left join sys.columns c 
				on (ao.object_id = c.object_id)
			left join sys.types s 
				on (c.system_type_id = s.system_type_id)
			c.system_type_id in (34,35,99,241)
			and ao.object_id = @objectid;
        -- if a large data type was found then do not rebuild the index
		if @rowcount = 0 begin
			set @cmd = 'alter index [' + rtrim(@indexname) + '] on [' + @tablename +'] rebuild partition = all with (online=on, sort_in_tempdb = on, maxdop =0);'
			exec (@cmd);
			print @cmd;
        end else begin
			set @cmd = 'index skipped for table ' + @tablename + ' index: ' + @indexname;
			print @cmd;

-- close cursor
close indexes;
deallocate indexes;



What's this EXIF data?

Did you know that almost all digital cameras (including SmartPhones) save specific metadata, such as camera settings and scene information, within the image (photo) files that they create?

This information is called EXIF (Exchangeable Image File) data. Information such as the camera or phone make, model and image resolution is stored within the photo. GPS enabled cameras may also store location information from where the picture was taken.

whatmyphotosays.com lets you view the EXIF data for photos found on your computer or the internet.

Follow this link to whatmyphotosays.com and see what EXIF data is saved in your pictures.