Avoiding “Cannot resolve the collation conflict exception in SQL Server”

31 12 2015

Avoiding “Cannot resolve the collation conflict exception in SQL Server”

This exception is usually thrown when a comparison between character data (‘strings’) is done and both the source have different collation. This can occur with temporary as well as permanent tables.

There are various ways to fix this but it purely depends on how and what kind of tables\query you are writing.

  • While comparing complex #Temp tables you can specify “COLLATE DATABASE_DEFAULT” with character based column
    E.g
create table #TempTable1 (ID int, Name varchar(100)COLLATE DATABASE_DEFAULT)
  • While dumping data into #Temp and then comparing you may like to utilize “into” clause. This would create the #Temp table with the collation of the database in which TestTable1 resides.
    E.g.
select * into #TempTable1 from TestTable1
  • While comparing permanent tables we dodnot have much choice of changing the declaration of character column and so we may like to use “COLLATE DATABASE_DEFAULT” directly within the query in where caluse.
    E.g.
select t1.*
from TestDatabase2.dbo.TestTable1 t1
inner join TestTable1 t2 on t1.Name COLLATE DATABASE_DEFAULT = t2.Name COLLATE DATABASE_DEFAULT

Note:
The rule behind the creation of #Temp table is that it gets created in System Databases >> tempdp database. Now this tempdb database can have a different collation than the database where stored procedure\another table to compare resides. You can use any of the above pattern to avoid this situation





Overriding Save Button Behavior in SharePoint

15 12 2015

Overriding Save Button Behavior in SharePoint

There are multiple ways to hookup a customized behavior with SharePoint “SaveButton”. Below are two options that can make your day.  

Option 1

Inherit from ListFieldIterator class and hookup OnSaveHandler with your custom method. Below is the simple code to customize SaveButton behavior while utilizing ListFieldIterator class.

public class CustomListFieldIterator : ListFieldIterator
{
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
SPContext.Current.FormContext.OnSaveHandler += new EventHandler(CustomOnSave);
}
protected void CustomOnSave(object sender, EventArgs e)
{
//custom code
// returns true if item saved else false
Boolean returnValue = SaveButton.SaveItem(SPContext.Current, false, "comments if any");
//custom code
}
}

Note:

For this approach static method SaveButton.SaveItem with parameters is available. Make sure to pass true for DocumentLibrary list and false for the normal list. It won’t have any issues saving the items but some stuff like “Check Out \ Check In” might not work as expected.

Option 2

Inherit from SaveButton class and override the virtual method SaveItem from base class.

public partial class CustomSaveButton : SaveButton
{
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
}
protected override bool SaveItem()
{
//custom code
bool result = base.SaveItem();
//custom code
return result;
}
}




My winning script for a play @my organization

15 12 2015

Topic: Teach & Protect

Pitch: A small IT company striving through recession period.

Play Members:

  • 1 Prospect Client (ABC)
  • 1 Manager (CEO) (Dhaval Upadhyaya 🙂 )
  • 2 Leads (DEF & GHI)
  • 3 Developers (JKL, MNO & PQR)

Play:

It all began with a phone call of a new prospect customer attracted by a small yet powerful portfolio of our IT Company. A ray of hope to surpass the recession and continue our journey towards growth enlightened.

During the short conference call the customer briefed about the situation and gave a deadline of a month. The customer was also striving through the financial crises due to market conditions and technology wilderness. His business would almost crash if the existing system was not transferred to the new technology that would help to retain his customers and company alive.

It was all upto the company to decide whether to go for a shot or not. As a Manager the decision was taken to go for it considering the collapsing business and market condition. A milestone was kept at 15th day and final deliver after another 15 days.

Manger briefed the leads about the project and the achievable situations. One of the lead was domain expert and the other complimented with great technical knowledge. Developers were very smart but didn’t have expertise in domain nor the technology.

It all started with little skepticism and fear about failing but the moral of the entire team was up. Leads taught and protected developers when mistakes were made. Due to sharp learning curve and technical competency the schedule was running behind. The first milestone meeting came. The manager explains the client about the situation of being late by 5 days. Client with a short silence asked, Are you guys still going to make it? The manager with confidence and statistical data once again brings in the client’s hope. The last 10 days entire team work very smartly and collaboratively by teaching and protecting each other’s while making mistakes. On the final day the system was made live on time with reasonably acceptable gaps. Soon within a month client retained his end customers and also started accumulating new customers, because many other competitors went out of market.

It was a time to reward, relax and witness the accomplishment made.

There was a sign of prosperity each members felt when a prospect client was converted to permanent business partner. Money, even in recession started flowing. Both the customer and IT Company expanded.