Troubleshooting Linked Server Error The OLEDB provider MSDASQL for linked server reported an error. The provider ran out of memory

Dear Friends,

Some months ago, I was working on a SSRS Report which sources data from a third party Oracle database. I had shared my experiences in this post http://daxdilip.blogspot.com.au/2013/03/ssrs-troubleshooting-error-im014.html

I have a Linked Server connection from my SQL box which will talk to the Oracle db and do a nightly refresh of the local data. Now, the source database (oracle db) got upgraded and some of the tables/fields were changed.

Error: I got this error in one of my data transfer jobs.

Solution:

You can refer to the Microsoft KB Article over here http://support.microsoft.com/kb/268520

Re-create the linked server to use the Microsoft OLE DB Provider for ODBC (MSDASQL).

• Use the ODBC driver for ORACLE that is provided by Microsoft:
  
  

  EXEC sp_addlinkedserver @server = 'ORACLEODBC', @srvproduct = 'MSDASQL', @provider = 'MSDASQL',
   @provstr = 'DRIVER={Microsoft ODBC for Oracle};SERVER=MyOracleServer;UID=USERNAME;PWD=Password;'
  go
  
  sp_addlinkedsrvlogin 'ORACLEODBC', false, NULL, 'USERNAME', 'Password'

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

Book Review - Dynamics AX Performance Optimization Guide

Dear Friends,

This post has been due from my end for a while now. First of all, I would like to thank Martin Zhen and Daniel Liao for shipping me the hard copy of the book from China.

Introduction of the Authors:

Martin Zhen started as a Dynamics AX developer in 2004. His focus has primarily been on performance, working on architecting, developing, and tuning Dynamics AX applications.

 

Daniel Liao has been working with AX since 2004.  Currently, he is managing an offshore development team in China that specialize in providing a complete set of offshoring services around the Microsoft Dynamics AX and .Net development services.

"Dynamics AX Perfomance Optimization Guide" - This book lives upto it's name and I would recommend it as an excellent resource and a handy guide to troubleshoot performance related issues to Dynamics AX and SQL. This book is targeted towards the technical audience.

Alongwith Dynamics AX, this book covers advanced troubleshooting tips with SQL Server which many of us might have not known before.

This book spans approx. 200 pages and it's divided into 8 chapters:

1. Understanding Dynamics AX - covering Dynamics AX Architecture, Sessions, Concurrency Control

2. Monitoring Hardware, Database and Dynamics AX - Touches on the key areas like Hardware Monitoring, Database Monitoring, Database I/O, Monitoring Dynamics AX

3. Setup and Configuration - This chapter extensively covers from a SQL Server perspective like Recovery models, Files and File Groups, SQL Server configuration, Database options

4. Common Dynamics AX performance problem - Disk I/O Bottlenecks, Memory Bottlenecks, Extensive Logging, Deadlocking

5. Optimizing Dynamics AX SQL Statement - Index Hints, Trace flags, Best Development Practices, Buffer and Caching mechanisms are covered in this chapter

6. SQL Server Performance and Compilation Optimizations for Dynamics AX - Resource Management, Auditing and Compliance, Performance Monitoring , Query Optimizations and Hardware Optimizations

7. Database Maintenance - Managing Indexes and Locking

8. Infrastructure and Hyper - V virtualization - Hardware Sizing, Sizing guidelines for Dynamics AX 2012 and 2009, Dynamics AX Server Virtualization benefits, Hyper-V best practices

As you might have already seen by now, this book covers extensively SQL Troubleshooting which i think is very critical to understand while dealing with performance issues. I think every technical person developing in Dynamics AX should have a good understanding of SQL Server from an administrative point of view and this book exactly does that. Whether you are a newbie in AX Development or an experienced developer, there's a lot to learn from this book.

I really appreciate Martin and Daniel for the effort they have put behind this book and I would recommend every technical person to grab a copy of the book if they could. Good job guys!

If you like to order a copy of this book, kindly go to this link http://www.amazon.com/Dynamics-Performance-Optimization-Guide-Microsoft/product-reviews/1481100750

How to Configure Kerberos Authentication for SQL and MOSS

Apart from AX, In my current role, I have been extensively working on MOSS/Sharepoint Administration and Troubleshooting, Configuring Kerberos Authentication for SQL, MOSS Server and SSRS, Planning for Disaster Recovery, Troubleshooting SSRS.


This post is on my experience on setting up Kerberos for our standalone MOSS, SQL and SSRS Servers and troubleshooting IIS for Kerberos.

1. Server Farm Topology

TEST-DEV2 Server: MOSS Server 2007 Farm  (Primary) – Central Administration, IIS

TEST-DEV1 Server:


a.   SQL Server 2008 Database Engine, SSRS 2008 (Sharepoint Integrated Mode)

b. MOSS WFE (Web Front End Part of Server Farm configured on TEST-DEV1)

2. Setup Kerberos for SQL Server (TEST-DEV1)

Before we setup Kerberos for Sharepoint Server, we need to configure SQL for Kerberos

a. Register SPN’s for SQL Server Service (one  with NETBIOS and other with FQDN) as shown below

Setspn -a http/<computer-name>.<domain-name>:<port> <domain-user-account>

Setspn Command line Reference - http://technet.microsoft.com/en-us/library/bb735885.aspx

2. Test  the connectivity on SQL Server is via Kerberos and not NTLM

Logon to SSMS (staying in the same box) and fire the below SQL, if SPN’s are properly setup, then you should see KERBEROS in auth_scheme field.

               

3. Another way to test if Kerberos is being used as Authentication scheme is to bring up Central Administration site and now go back to TEST-DEV1 (the host box where SQL is running). Bring up the Event Viewer (check Security tab)

4. In the Security Log, you should be able to see Success audit record for Logon/Logoff category event

Check the Detailed Authentication information and should be able to see the Login mechanism as Kerberos which confirms our test that MOSS is communicating with SQL via Kerberos

3. Setup Kerberos for MOSS Server (TEST-DEV2)

Register SPN’s for MOSS Server (Service account) in the similar fashion as we did for SQL:

1. Register with NETBIOS name
2. Register with FQDN name as shown below

3. Browse to Central Administration website and confirm by going to the Event Viewer of the hostmachine in Security log, (similar to the steps we performed for SQL) that the authentication package here is Kerberos

TROUBLESHOOTING:

A.

Troubleshooting IIS Authentication HTTP Error 401 after Kerberos is setup for MOSS Server Farm

Note: If you are running IIS 7.0 server in a Web farm the KDC will not know in advance which individual server the request may go to and hence ticket decryption may fail

Bring up IIS Manager, Go to each of your websites and turn off the Kernel Mode Authentication under Windows Authentication Advanced Settings option

OR

Let Kernel mode authentication be enabled and the Application pool's identity be used for Kerberos ticket decryption. The only thing you need to do here is:

1. Run the Application pool under a common custom domain account.

2. Add this attribute "useAppPoolCredentials" in the ApplicationHost.config file.

<system.webServer>

  <security>

     <authentication>

        <windowsAuthentication enabled="true" useKernelMode="true" useAppPoolCredentials="true" />

     </authentication>

  </security>

</system.webServer>

Remember there is no GUI setting for this. You need to modify the ApplicationHost.config file from

<%SystemDrive%>/Windows/System32/inetsrv/config folder on the IIS 7.0 machine.

Note:
Tried both the options but Option A works fine with Option B, there’s this frequent system crash as I reckon it’s changing the applicationhost.config file settings. Need to check this behaviour in Production Server as Option B is more performance friendly and recommended approach

B.

Configuring Kerberos for SSRS (running in Sharepoint Integrated Mode):

1. Register SPN for SSRS Server Instance  (both with NETBIOS and FQDN)

Setspn -a http/<computer-name>.<domain-name>:<port> <domain-user-account>

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

2. Open the RsReportServer.config file and locate the <AuthenticationTypes> section. Add <RSWindowsNegotiate/> as the first entry in this section to enable Kerberos

3. Test: Deploy a Report through BIDS to  a Report Center site, http://test-dev2/rc/

Troubleshooting tip: You may run into error rsaccessdenied: The account SHarepoint/System doesn’t have permissions to deploy the report. In order to troubleshoot this error, check if spn’s are properly registered with the sharepoint service account and there’s no duplicity of SPN’s.

SQL Tip - List all databases file path

Today, @ work, I needed to find out the physical file location for .mdf and .ldf files of all our databases on SQL

Just fire this query in SSMS Query Editor

SELECT name, physical_name FROM sys.master_files

Output:

It will return you the database names alongwith physical paths

To read more about sys.master_files and the additional columns, read here http://msdn.microsoft.com/en-us/library/ms186782.aspx

Step by Step Guide How to Install Dynamics AX2012 Enterprise Portal

How to Install Dynamics AX2012 Enterprise Portal

This is the second post in the series of installing Dynamics AX2012 Components. You can refer to the first post here - (Installing the base Dynamics AX Components - Databases, AOS, Client, Debugger, Office Addins)

http://daxdilip.blogspot.com/2011/08/step-by-step-guide-how-to-install.html

This post will talk about installing Dynamics AX2012 Enterprise Portal.
The basic pre-requisite for Installing Enterprise Portal is Sharepoint 2010 Foundation/Sharepoint 2010 Server

Since this is sandpit/training box, I would be just installing Sharepoint Foundation 2010.

i. Install the Sharepoint 2010 Pre-Requisites shown below

ii. Pre-Requisites Installed Successfully

iii. Now, start with the Foundation Installation

iv. Run the Sharepoint Configuration Wizard

v. After all the tasks are completed successfully, you are welcomed to the Sharepoint Team site

Tip - If you are planning to Install Sharepoint 2010 on Windows 7, please see the steps here which I posted a while ago - http://daxdilip.blogspot.com/2009/11/how-to-install-and-configure-sharepoint.html

Installing Dynamics AX2012 Enterprise Portal

i. Now, let's go ahead and install Enterprise Portal Component. Run the setup and click Next

ii. Check the Pre-Requisite Validation runs fine and then move next

iii. Enter the .Net BC Connector Information

iv. Configure the website, I just left the defaults as it is and moved next

v. Move next with the installation

vi. Encountered an error at this step

Looked at the error log file which came up, and found the below logs:

Error: Windows cannot find the local profile and is logging you on with a temporary profile. Changes you make to this profile will be lost when you log off.

Installation completed successfully, but Setup could not create the Enterprise Portal website. You can create the website by running Setup and installing Enterprise Portal again. You can also manually create a website using SharePoint Central Administration.

An error occurred during setup of Enterprise Portal (EP).

Registering tracing manifest file "C:\Program Files\Microsoft Dynamics AX\60\Server\Common\TraceProviderCrimson.man"

WEvtUtil.exe install-manifest "C:\Users\itadmin.PASPALEY\AppData\Local\Temp\tmpA5A1.tmp

**** Warning: Publisher {8e410b1f-eb34-4417-be16-478a22c98916} is installed on

Troubleshooting:

Didn't spent much time on troubleshooting this one as I have seen this error of logging with temporary profile before, but not with EP. The simplest solution which worked for me was to restart the box , login back and run the Enterprise Portal Setup again and that fixed the issue.

Finally, the Role center page arrives :-)... I can recall my pervious experience with  Dynamics AX 4.0 and 2009 version, never I was able to install MOSS and Enterprise Portal at the first go successfully without too many hiccups.

Additional Tip/Note -

Installation of Sharepoint Foundation 2010 Creates a new SQL Server named instance for e.g. In this case TEST-DEMOAX2012/Sharepoint where all sharepoint related databases can be found

SQL Server Collations Explained

Couple of weeks back, I was working on SQL Collations (Troubleshooting Chinese Characters in AX) and had collected some notes on the way. Thought, will post it over here.

Overview

The physical storage of character strings in Microsoft® SQL Server™ 2000 is controlled by collations. A collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared

Read more here -

http://msdn.microsoft.com/en-us/library/aa214408(v=sql.80).aspx

A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages).

The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.

The code page used to store non-Unicode character data.http://msdn.microsoft.com/en-us/library/aa174903(v=sql.80).aspx

SQL Server collations can be specified at many levels.

• Define Collation at Instance Level
• Collation at Database Level (Defaulted to Instance)
• At Column Levels (Defaulted to database)

List of SQL Server Collations

http://msdn.microsoft.com/en-us/library/ms144250.aspx

Storage and Performance Effects of Unicode
Note - It's very important to understand how Unicode/non-Unicode  storage affects the performance and size of the database, In the below para, I have highlighted especially languages e.g. Thai uses 2 times the space than Chinese chars.

http://msdn.microsoft.com/en-us/library/ms189617.aspx

SQL Server stores Unicode data by using the UCS-2 encoding scheme. Under this mechanism, all Unicode characters are stored by using 2 bytes

The difference in storing character data between Unicode and non-Unicode depends on whether non-Unicode data is stored by using double-byte character sets. All non-East Asian languages and the Thai language store non-Unicode characters in single bytes. Therefore, storing these languages as Unicode uses two times the space that is used specifying a non-Unicode code page. On the other hand, the non-Unicode code pages of many other Asian languages specify character storage in double-byte character sets (DBCS). Therefore, for these languages, there is almost no difference in storage between non-Unicode and Unicode

The following table shows the non-Unicode code pages that specify character data storage in double-byte character sets.

http://blogs.msdn.com/b/qingsongyao/archive/2009/04/10/sql-server-and-utf-8-encoding-1-true-or-false.aspx?PageIndex=4#comments

   

Choosing the correct collation while installing SQL Server 2008
Note - It's very important to choose a proper collation for your instance during the installation time, as later on going and changing it can prove costly

http://blogs.msdn.com/b/nickmac/archive/2008/07/21/choosing-the-correct-collation-on-sql-server-2008.aspx

 

Change Collation at server level or database level

http://www.db-staff.com/index.php/microsoft-sql-server/69-change-collation

http://sql-articles.com/articles/dba/how-to-change-server-collation-in-sql-server-2008/

Windows Collation sorting style
http://msdn.microsoft.com/en-us/library/ms143515.aspx

Check constraint Collation Dependency

Note - There are some action items for Microsoft to make it easy with respect to changing collations, Check Constraint Dependency with collations in their future releases of SQL

https://connect.microsoft.com/SQLServer/feedback/details/384157/check-constraint-collation-dependency

Make it easy to change Collation on Database

https://connect.microsoft.com/SQLServer/feedback/details/358320/make-it-easy-to-change-collation-on-a-database

Troubleshooting AOS Service Startup Error Object Server 01 The specified schema name DBO either does not exist

Few days back, one of my AOS Services won't start. In the Event Viewer, I found this error message:


Error 1 - Object Server 01 The database reported session 1 AOS [Microsoft][ODBC SQL Server Driver][SQL Server]The specified schema name DBO either does not exist


Solution - After some troubleshooting, Found that this was due to the Collation change at SQL Server level which was made sometime back for testing Chinese characters.


Changed the Collation back to the default collation of SQL Server and now AOS Service started.


SQL Collations is a whole new world which I explored a while ago. Will be writing a detailed post soon on that. Watch out this space!

Tip - List all databases on the server EXEC sp_helpdb

Doing a quickie here...

I had a need to extract all the databases into an excel to report on them. Here's a cool SQL which came handy for me:

Open SSMS (SQL Server Mgmt Studio) and fire this sql in the query window:

EXEC sp_helpdb
This will list all the databases name, alongwith some other useful info like size, owner when the db was created, db statust etc. Quite handy. Love SQL :-)

Tip - How to avoid Overriding of SQL Indexes from AX when a Full Synchronization is done

There's this stored procedure written in SQL which retrieves historical order data and displays it on the Commerce Server Website. This SP uses almost 13 tables to retreive the data from (which includes custom and standard AX tables).

We added few indexes from AX to boost performance but in vain as when we run the Query Execution Plan, the Subtree cost of the index still shows more than 80%. SQL Server 2008 suggests new indexes which can be added via SQL to improve the performance of the query. I tried the indexes suggested by SQL and immediately the performance boost can be observed.

After some investigation, I found that the index added by SQL is different from index added via AX as AX doesn't have the capability to add the INCLUDE keyword when we create a new non-clustered index which SQL does as shown in the screenshot below





Here's something more about the include keyword from MSDN


Index with Included Columns
You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:
•    They can be data types not allowed as index key columns.
•    They are not considered by the Database Engine when calculating the number of index key columns or index key size.
An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.


Issue

Whenever a synchronization is done, AX overwrites the indexes which we put through SQL

Resolution

a. Created the index via AX as a table method

b. Modified the Application Class/dbsynchronize method so that after synchronization it invokes the table method to create index (mentioned above in Step a)

So, by doing this we have the SQL Indexes intact as we are recreating the index once AX finishes it's synchronization thereby avoiding the overwritten part

Latest update - Dynamics AX 2009 Intelligent Data Management Framework

Came across Brandon's blog about the latest update to Dynamics AX IDMF Tool, Worth a try..

http://dynamics-ax.blogspot.com/2010/11/intelligent-data-management-framework.html

Object Server 01: Internal Error occured executing stored procedure when creating session for the AOS

We were getting this error when trying to start the AOS Service after we restored an AX Database from one of our existing environments:

Error : Object Server 01: Internal Error occured executing stored procedure when creating session for the AOS

By seeing this error, I was quickly able to conclude that it has to do something related to the stored procedures which creates server sessions and client sessions, I compared the permissions of the sp's with our other AX databases and found that the aos service account didn't have "Execute" permissions on these 2 sp's and hence the errors, After assigning the permissions, we could start the service without any hiccups

I had a similar experience troubleshooting these stored procedures in the past which has been blogged http://daxdilip.blogspot.com/2009/02/sysserversessions-and-sysclientsessions.html

 

Back Up and Restore Userinfo table across AX environments

Usually we come up with the requirement of replicating AX users across  multiple Dynamics AX Environments, These are the following steps which we perform manually inorder to do this task:

But, this can be done in a smarter way with this SQL Script.

Dynamics AX Retail/POS Deployment

A busy long weekend with various project deployments coming my way. The best pick out of those was AX Retail/POS Deployment as it had various components and various teams working on the deployment and it was a long one which spanned over more than 7 hours.

We deployed the new POS app onto 2 stores. This was the second release and apart from the normal Dynamics AX AOD Deployment cycle, I had a chance to deploy some new retail components like installing Transaction, Data Director Services, installing LS POS .Net Retail Scheduler Service, Pushing the Data Director jobs onto the stores, The deployment went cool without any hiccups apart from a few network glitches.

Dynamics AX 2009 Enterprise Portal Troubleshooting Blank Page Issue

We have implemented Dynamics AX 2009 EP portal for one of our business. Users log on to the EP to create/edit/view their timesheets. Recently, we got this issue where suddenly the Edit timesheet page got disappeared. Whenever the user goes to view/edit timesheet, the page was blank.

Solution:

a. Checked the Event viewer and security logs but couldn't find anything.
b. Restarted IIS but with no luck.
c. Restarted AOS, restarted the web server but still the same error persisted.
d. Checked in sharepoint virtual folder and found that the Edit Timesheet.cs (C# page) file was not there.
e. Finally, went to AOT > Web > Web Files > Page Definitions > EditTimeSheetpage and right-clicked to deploy the same to the web server.

This solved the issue, but still wondering how the page got disappeared!

How to identify SQL Server version and edition

This comes in handy when you need to check the SQL Server Version, Service Pack which your Enterprise is running, It shows the different versions and sp's for SQL Server rite from version 6.5 to SQL Server 2008, A handy link to be stored in your favorites and this is the query :

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

You can read more here,

http://support.microsoft.com/kb/321185

Free E-book from Microsoft Press on Microsoft SQL Server 2008 and Windows Server 2008

SQL Server 2008

http://blogs.msdn.com/microsoft_press/archive/2009/11/16/free-e-book-introducing-microsoft-sql-server-2008.aspx

Windows Server 2008

http://ow.ly/Hbjy

How to Install and Configure Sharepoint Server 2010 on Win 7

I thought I will blog about my recent experience on configuring Sharepoint 2010.

So, here it goes..

a. Downloaded Sharepoint Server 2010 from Technet. If you are a Technet subscriber, you can download it from here

https://technet.microsoft.com/en-us/subscriptions/securedownloads/default.aspx

b. This was the first issue I encountered when running the installation setup

After some search on www, I found that it's mandatory to have Windows Server 2008 for Sharepoint Server running on Production, but there's a way to install it on a Developer OS which runs on Win 7.


c. I went through this msdn link http://msdn.microsoft.com/en-us/library/ee554869(office.14).aspx which showed me the initial steps for installing the Pre-Requisites for Win 7 before running the Sharepoint Server 2010 Setup. I installed the manual pre-requisites mentioned in the link (Point 7 to 9 in Step2: Install the Prerequisites for SharePoint 2010 ). After completing the pre-requisites setup, I tried running the Sharepoint Setup and again encountered the same Operating System error shown above.


d. Luckily, this post from Sandrino - http://www.codeproject.com/KB/sharepoint/Install_SP2010_on_Win_7.aspx
came to my rescue. Thanks Sandrino for the wonderful post.


e. I followed the steps in Sandrino's post except the install Process explorer as the installer had unpacked the setup files in the same location which is mentioned in Sandrino's post.

f. I ran into one more minor issue as I was unable to edit the config file as it was giving me Access Denied Error, Then I had to go to the parent folder MSECache and right-click Security > Permissions and give my logged-in account full permissions on the folder.

g.  Now, I was able to run the Sharepoint setup program and it got installed successfully. A sigh of relief, which didnt last long though ! :-)

h. Now, I went back to the msdn link and installed the SQL Hotfix before running the Sharepoint Configuration Wizard. (See Point 5 in Step 3: Install Sharepoint 2010)

i. You need to request for the SQL Server 2008 hotfix link and an email would be sent to you with the hotfix link and password.

j. Once the hotfix got installed, I ran the Sharepoint 2010 Configuration Wizard, but stumbled upon this error

Seeing the error description, i figured out it was becoz I was not connected to my office network as it was a domain validation exception. I bought my laptop back to my office network and again ran the Sharepoint 2010 Configuration Wizard and this time it created the database and Central Administration successfully. :-), Wow! What a relief :-)

This was my brief journey on installing and configuring Sharepoint 2010. Next, watch out this space to see how Dynamics AX integrates with Sharepoint 2010

References:


http://msdn.microsoft.com/en-us/library/ee554869(office.14).aspx
http://www.codeproject.com/KB/sharepoint/Install_SP2010_on_Win_7.aspx