GP Homepage Layout Mass Update – Thru’ SQL

I love SQL. Simply because, it eases lot of pain in doing some redundant work.

When you create a user, by default, GP creates the following segments on his/her homepage:

1. To Do (Reminders/Tasks)
2. My Reports
3. Microsoft Outlook
4. Quick Links
5. Connect (links to Online Resources such as CustomerSource, Forums, Blogs, etc.)
6. Metrics (quick dashboards for users)

All of the above work perfectly, but at some stages (especially when you are working on a GP Terminal Server) some of these components would halt GP for a while, not allowing users to do anything.

One classic example is Microsoft Outlook. We recently migrated our Email Services to Microsoft Office 365. For those users who use Thin Client to on their day-to-day job, Terminal Server is THE destination. Quite obviously, Terminal Server has got Outlook installed.

Before Office 365, Outlook profile used to connect to our on-premise Exchange Server. After migration, we had to change the profile from Exchange Server to Office 365 hosting server.

Whenever a user logs on to Terminal Server and opens GP, GP will halt for at least 3-5 mins to get this Outlook configured. I am getting calls from users (yes, even now) that their GP session does not work and they are not able to do anything. Following icon shows up on their taskbar when this happens:

It didn’t take me much time to realize that all these users had added Microsoft Outlook on their GP homepage.

“Do we really need Outlook on GP homepage?” is a question to be asked to oneself. Yes, Microsoft has integrated your ERP with one of the most used product by maximum business users. Agreed. But is that alone a reason for you to use it? Let’s not get into that argument in this post, anyways.

I had to take a stand and remove Microsoft Outlook from those users’ homepage. But then, I don’t know, right now, about how many users out of 100+ total users have added Outlook on their homepage.

Certainly not without the help of a SQL query.

The table where this information has got stored is SY08100 (Technical Name: syHomePageLayout). In this table, the column SectionID denotes the sections which are available for a user to add to his/her homepage. Following is the legend:

1 – To Do (Reminders/Tasks/Cues)
2 – Microsoft Outlook
3 – Metrics (quick dashboards for users)
4 – My Reports (links to users’ favorite reports and smartlists)
5 – Quick Links (links to users’ frequently used windows)
6 – Connect (links to Online Resources for GP such as Forums, Blogs, etc.)

Typically, for each user, this table will contain 6 rows, each row denoting each of above section. SY08100 also contains a column named Visible. This denotes whether or not to show a particular section on GP homepage.

For instance, if user SA has setup his GP homepage to show To Do, My Reports & Quick Links to show up, the following will be SY08100 records:

So, if I want to hide Microsoft Outlook from all users’ homepage, I just have to execute the following SQL query on DYNAMICS database:

UPDATE SY08100 SET Visible = WHERE SectionID = 2

We must also understand one thing. Users can always add this again thru’ Customize this page… option. So this is NOT A PERMANENT SOLUTION.

Whenever there is a necessity, as in my case, you can certainly rely on this SQL method to do a homepage layout mass update.


SQL Tip: Find Table(s) Having A Particular Column

This SQL query is my savior for a long time now. Thought I would just share it with all budding SQL developers.

Q: How to find the table(s) which contain a particular column which I know?
A: Below is the query that would do the trick.

SELECT name 
FROM sys.objects 
WHERE object_id IN 
SELECT object_id 
FROM sys.columns 
WHERE name = [columnname] –Column which you would want to locate

This query will return all tables which contains the specific column. And this query also would save your time immensely.


Perils of Using of DEX_ROW_ID on Customization / Integration – David

This one is certainly a nasty issue, if we are going to use DEX_ROW_ID value on customization or integration.

I have personally fallen into a trap by using DEX_ROW_ID on my customization. But now, it’s all wisdom out of that bad experience.

Only time when I normally depend on a DEX_ROW_ID is to sort records to understand which record was entered last in the table. That itself may not be always correct. So we may have to add other logical fields that are relevant to a particular record.

For instance, if I have to list out transactions entered on a particular day and check in the order in which the records are created, I would use the Transaction Date and DEX_ROW_ID as SORT ORDER.

There are many other reasons, but restrict yourself to use DEX_ROW_ID only for selecting records and analyze it.


Stored Procedure as Data Source in PowerPivot – Issue & Solution

I am working on a PowerPivot based analysis design and my data source is a SQL Stored Procedure which does the following:

1. Inserts set of records from one DB into a Table Variable.
2. Inserts similarly structured set of records from another DB into the same Table Variable.
3. Finally retrieves records by SELECT…GROUP BY… statement based on necessary criteria.

The above is to ensure that I don’t end up troubleshooing Temp table issues or data redundancy or performance issues for that matter.

PowerPivot understands the above stored procedure very clearly and it also validates the execute query without any issues. It even shows the result set in preview. But it throws the following error message after all the steps involved in setting up SP as data source:

The above error message is quite less informative. I initially thought Stored Procedures are not properly supported by PowerPivot (how silly I am…!!!). But I just gave myself some more time to search forums as usual. After all, I spent almost a day in getting that Stored Procedure completed with 100% accurate results.
The following is the SIMPLE FIX to that menacing issue:
Yeah that’s it. SET NOCOUNT ON is that simple fix. Now my PowerPivot understands this SP and works merrily.
The Post I got this answer from: PowerPivot and Stored Procedure as a SQL Source.
The answer is given by Microsoft Product Team and that was conveyed by Lisa Liu CSS, a Microsoft Moderator.
The reason for why we ought to set NOCOUNT ON is given in the same post by Devarajan KM. The reason is: Set NOCOUNT to ON so that you get only one result set arrived after execution.

Getting Month Numbers between two dates in T-SQL

I literally had to spend around an hour to crack this code, with some little help from my best geek friend, GOOGLE, of course.

Issue is this: I wanted to get Month in Numbers (Jan = 1, Feb = 2, etc.) that exist between two dates. For instance, if my date range is 1-Jan-2011 to 31-Mar-2011, then I should find and retrieve the months Jan, Feb and Mar as 1, 2 and 3 respectively.

After much much toiling and reading some stuff online, the following is what I got for myself:


;WITH Numbers (Number) AS
SELECT MONTH(DATEADD(MONTH, Number – 1, ‘2011-01-01’)) Month_Number
FROM Numbers
WHERE Number – 1 <= DATEDIFF(MONTH, ‘2011-01-01’, ‘2011-03-31’)


IMPORTANT: Make sure that you have more than 12 records in sys.all_objects table, which by default SHOULD have.

I hope this code piece is useful for some who have got similar requirement.


T-SQL "UNION" Vs "TABLE variable"

Very recently I learned this. And what a change it has made to all of my SQL based projects! I am sure people who are hardcore SQL programmers would already know this. Thought of sharing this for those who would want to know something interesting.

I had a SQL Stored Procedure that is about to retrieve records of a same table from two different databases. The usual approach was something like below:

SELECT [column_name_1], [column_name_2], [column_name_3], …
FROM [db_name_1]..[table_name]


SELECT [column_name_1], [column_name_2], [column_name_3], …
FROM [db_name_2]..[table_name]

This was working till these tables contained records less than 30000 rows. And it started taking significantly unacceptable time (like 10-15 seconds). I then thought of creating a temporary table and dump records from one DB then from other. There is a potential issue in that, in the form MULTI-USER environment. When two different users invoke this stored procedure at the same time, this temporary table must cater for both. I was not really sure about how I can address this.

Then came this idea of using TABLE VARIABLE. It’s like a Structure in C++. This is how I modified my stored procedure:

Column_1 DATATYPE,
Column_2 DATATYPE,


INSERT INTO @TABLE_NAME (Column_1, Column_2, …)
SELECT [column_name_1], [column_name_2], …
FROM [db_name_1]..[table_name]

INSERT INTO @TABLE_NAME (Column_1, Column_2, …)
SELECT [column_name_1], [column_name_2], …
FROM [db_name_2]..[table_name]

SELECT Column_1, Column_2, …

After this, the program started taking around 2-5 seconds with more than 50000 records to handle. Not to forget, this program contained several grouping and calculations.

I don’t have to worry about delay and also TEMP TABLE issues that are potentially disastrous. I am still not sure how this is handled in terms of memory allocation. What happens after this program completes it’s process, is also a factor that I am yet to figure out.

But, so far, I have found this as one of the best methods in recent times.