Tip Dynamics AX Batch Execution and Using the NOLOCK hint to read SQL Server uncommitted data

Dear All,

Hope you are doing well.

I thought to share here a quick handy tip which I have been using for quite a while now.

Scenario:

I have a batch job running in AX which loads thousands of rows into some AX tables based on a csv/txt file. Quite often, I would like to know how many rows have been inserted into the tables.
There are many ways to get this but a quick way without much development effort is to use SQL Server Query Hint NOLOCK

Explanation:

For e.g. I'm loading some data into smmActivites table via an AX Batch and I would like to know ant any point of time how many rows have been inserted via my batch.

So, I execute the below query via SQL Management Studio

select COUNT(*) from SMMACTIVITIES WITH (NOLOCK) where DATAAREAID = 'n000'


The above statement will show me the no. of rows inserted into the table

What is the role of NOLOCK?

NOLOCK is a SQL Table hint which reads uncommitted data from database - meaning

Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data.

You can read more about NOLOCK hint from the MSDN Documentation here http://technet.microsoft.com/en-us/library/ms187373.aspx

Note - Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.

EOMONTH function in SQL Server 2012

Dear Friends,

A few weeks ago, I was writing some stored procs in SQL Server and I found this EOMONTH function handy.

This is a new function in SQL Server 2012

EOMONTH function takes a date argument/parameter and returns the last day of that month.

Quite handy.

Please refer to this technet article below for detailed syntax and some examples

http://technet.microsoft.com/en-us/library/hh213020.aspx

And if you would like to know how to achieve this in previous versions of SQL, have a look at the below link.

http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=49ffa5c2-03a3-4888-aac2-a80320f2faf4&tkw=last-day-of-the-month-function

Btw, in AX, we have an equivalent dateEndMth function in Global class for the above purpose.


Happy Querying :)

Dynamics AX 2012 and SQL Server 2012 SP1 compatibility announced

Dear Friends,

Sharing a quick note.

They Dynamics Sustained Engineering Team in Microsoft has published the compatibility between Dynamics AX 2012 and SQL Server 2012 SP1.

Please read on ....

https://community.dynamics.com/ax/b/dynamicsaxsustainedengineering/archive/2013/02/19/announcing-compatibility-certification-of-sql-server-2012-sp1-with-dynamics-ax-2012.aspx#.USfZmKWnqxV

Configuring and Implementing Sharepoint 2010 RBS (Remote Blob Storage) with SQL Server 2012 - Part 2

Good day Friends!

A while ago, I blogged on Configuring and Implementing Sharepoint 2010 RBS (Remote Blob Storage) with SQL Server 2012 - Part 1

In Part1, I gave an introduction on RBS, Benefits of implementing RBS in your enterprise, Installing the required components of RBS, Configuring and Setting up RBS for Sharepoint Content database.

In this post, I will try and cover the steps involved in Enabling RBS on a Second content database, Troubleshooting RBS, General Links and References and finally how it can be a part of your enterprise (along with Dynamics AX ERP)

A. Enabling RBS on a Second Content database

While enabling RBS for a second content database, Follow the similar steps which I mentioned in Part 1 except for the msiexec part, run the following command:

msiexec /qn /i rbs.msi REMOTEBLOBENABLE=1 FILESTREAMPROVIDERENABLE=1 DBNAME=WSS_Content_IntranetBlob FILESTREAMSTORENAME=FilestreamProvider_1 ADDLOCAL=EnableRBS,FilestreamRunScript DBINSTANCE=CD

Finally we need to run this Powershell Script to activate RBS on Second content database

            $cdb = Get-SPContentDatabase –WebApplication  <URL>
            $rbss = $cdb.RemoteBlobStorageSettings
            $rbss.Installed()
            $rbss.Enable()
            $rbss.SetActiveProviderName($rbss.GetProviderNames()[0])

B. Troubleshooting


Error# 1
Initially, for testing purpose I installed and configured RBS with Sharepoint Foundation 2010 on one of my test vm's, Everything went well but when I try to upload a document, it throws the below exception:

Exception: Microsoft.Data.SqlRemoteBlobs.BlobStoreException: There was a generic database error.

Solution:

Tried completely uninstalling RBS from Sharepoint Foundation and re-configured once again the Content database for RBS but still no luck :(.

Then I turned on to my ultimate weapon ;-) SQL profiler to identify the queries running behind the scenes whilst uploading a document to sharepoint library.

As usual, SQL Profiler didn’t let me down and it showed me the stored proc which was getting executed:

exec [mssqlrbs_filestream].[rbs_fs_sp_initialize] @schema_suffix=N'1',@filegroup_name=N'RBSFileStreamProvider',@data_filegroup_name=N'PRIMARY',@allow_remote=0,@schema_version=@p5 output,@share_prefix=@p6 output

I copied this statement from the Profiler and opened a new query window via SSMS and fired the query and I got the same error, I debugged the stored proc and found that the stored proc definition just took 3 input parameters, while sharepoint was sending 4 parameters, modified the stored proc to cater for @allow_remote parameter and that did the trick!!! :). Not a pretty way to fix it up, but I guess this would be a bug with Foundation 2010 version running on my VM.

C. References

Thought to post some useful links/references which can be handy while configuring RBS:

http://technet.microsoft.com/en-us/library/ee748631%28v=office.14%29.aspx
http://blogs.technet.com/b/pramodbalusu/archive/2011/07/09/rbs-and-sharepoint-2010.aspx
http://stevemannspath.blogspot.com.au/2012/07/bang-two-pound-four-remote-blob-storage.html
http://sharepoint.nauplius.net/2011/03/enabling-rbs-on-multiple-content.html
http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=174
 

D. Conclusion

To summarize, I believe RBS is the answer if you want to externalize your storage rather than cluttering your database and even this concept can be extended to Microsoft Dynamics AX (Document Management) wherein we end up storing huge BLOB type data in SQL.

Installing Dynamics AX 2012 on SQL Server 2011/2012 Denali CTP 3

This post was sitting in my drafts box for a while now, so thought to flush it out :-)

Few months ago, out of curiousity, I had installed SQL Server 2011 "Denali" CTP3 (now called SQL Server 2012) on my local box

Yes, you can have a side-by-side install, In my case I have SQL Server 2008 along with "Denali".

The next step was to try out and see if Dynamics AX 2012 can run on it. Although, Dynamics AX 2012 Setup/Installer complains about the new version, but there's a workaround to set this up.

- Backup the existing AX 2012 database (Baseline and Transactional db's) from SQL Server 2008
- Restore the db onto SQL Server 2012 "Denali"
- Install a new AOS and make it point to the restored DB

And, now your Dynamics AX 2012 is ready and up on SQL Server 2012 :-)

If you are interested to know "What's New in SQL Server 2012" maybe you can have a look at the below links or just "google" it.

http://www.windowsitpro.com/article/sqlserverdenali/sql-server-denali-new-features-140115

http://www.windowsitpro.com/article/sqlserverdenali/sql-server-denali-new-features-140115

I will be exploring the new features of SQL 2012 as and when I get time, I will do some posting on how Dynamics AX can leverage the new and improved features in SQL Server 2012.

Till then, Njoi :-)