Collation Conflicts and Settings in SQL Server

System.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the UNION operation.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 m

---------------------------------------------------------------------------------------------------------------------------------------

Solution: The issue is with the collation settings of the database Right click on the database options and change the collation. Your query will work like a charm.

:-)

Cheers,

Venkatesan Prabu .J

Head, KaaShiv InfoTech

Error with MODI in C#

Here is one of the sample error received while using MODI library.

Retrieving the COM class factory for component with CLSID {40942A6C-1520-4132-BDF8-BDC1F71F547B} failed due to the following error: 80040154.

Solution:

  1. May be the libraries were not registered properly in the system (Interop.Modi)
  2. Microsoft document Imaging software is not installed properly in your system. Run your Microsoft office setup  -> go to add/remove features -> Microsoft office features- > Microsoft document imaging. Right click and set the options to install in the local machine.


Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

Cross join feature in SQL Server

One of the superb feature of sql server is application of cross joins. 

It provides an option of multiple select statement into a single insert statement. I've happened to work in sql server for getting lot of informations from different select statement and I need to insert into a single insert statement.. In this case, my cross join helps me a lot.

 Here is the syntax for the same.

SELECT
  f1.col1, f2.col2, f3.col3, f4.col4
FROM
  (select count(1)  AS col1
 from SystemBasedUser() where ID like '%MS_SQL%') AS F1
 CROSS JOIN
 (select count(1) AS col2
 from SystemBasedUser() where ID like '%MS_POLI%') AS F2
 CROSS JOIN
 (select count(1) AS col3
 from SystemBasedUser() where ID like '%MS_SMO%') AS F3
 CROSS JOIN
 (select count(1) AS col4
 from SystemBasedUser() where ID like '%MS_AGEN%') AS F4

Cheers,

Venkatesan Prabu .J

Head, KaaShiv InfoTech

Security related information - System logins in sql server

Here is the query to fetch security related information from the master database:

The below query will fetch and retrieve the security related information from the database. It will provide the list of users and their access nature. Execute it and see the magic :-)

SELECT
[name] as LoginName,
LOGINPROPERTY ([name] , 'DefaultDatabase') as DefaultDatabase,
LOGINPROPERTY ([name] , 'DaysUntilExpiration') as DaysUntilExpiration,
(CASE ISNULL(LOGINPROPERTY ([name] , 'IsExpired'),0) WHEN 0 THEN 'False' ELSE 'True' END) as IsExpired,
(CASE ISNULL(LOGINPROPERTY ([name] , 'IsLocked'),0) WHEN 0 THEN 'False' ELSE 'True' END) as IsLocked,
LOGINPROPERTY ([name] , 'PasswordLastSetTime') as PasswordLastSetTime,
LOGINPROPERTY ([name] , 'PasswordHashAlgorithm') as PasswordHashAlgorithm
FROM master..syslogins

Cheers,
Venkatesan Prabu .J 
Head, KaaShiv InfoTech  

Fix: f5 is not working in windows forms

Problem f5 is not working in windows forms - Unable to start the windows forms :

   Recently, I've faced this problem while trying to access my windows forms.

  1. Created windows forms

  2. Run the application

  3. Stopped

  4. Re-run the application.

Oops, I couldn't run my app once again. Wait for another 2 minutes. Now, it's running perfectly.

Possible Solution:

  1. May be, Ur windows forms exe is still in the running process. Go and kill the process. 

  2. Check for the Application Experience service. It should run.

Cheers,

Venkatesan Prabu .J

Head, KaaShiv InfoTech

www.kaashivinfotech.com

Just for Fun

I've happened to see a joke series in facebook. Wish to share with my blog readers.

Smart Doctor!
Q: Doctor, I've heard that cardiovascular exercise can prolong life. Is this true?
A: Heart only good for so many beats, and that it... Don't waste on exercise. Everything wear out eventually. Speeding up heart not make you live longer; it like saying you extend life of car by driving faster. Want to live longer? Take nap.

Q: Should I reduce my alcohol intake?
A: Oh no. Wine made from fruit. Brandy distilled wine, that mean they take water out of fruity bit so you get even more of goodness that way. Beer also made of grain. Bottom up!

Q: How can I calculate my body/fat ratio?
A: Well, if you have body and you have fat, your ratio one to one. If you have two body, your ratio two to one.

Q: What are some of the advantages of participating in a regular exercise program?
A: Can't think of single one, sorry. My philosophy: No pain...good!

Q: Aren't fried foods bad for you?
A: YOU NOT LISTENING! Food fried in vegetable oil. How getting more vegetable be bad?

Q : Will sit-ups help prevent me from getting a little soft around the middle?
A: Oh no! When you exercise muscle, it get bigger. You should only be doing sit-up if you want bigger stomach.

Q: Is chocolate bad for me?
A: You crazy?!? HEL-LO-O!! Cocoa bean! Another vegetable! It best feel-good food around!

Q: Is swimming good for your figure?
A: If swimming good for figure, explain whale to me.

Q: Is getting in shape important for my lifestyle?
A: Hey! 'Round' is shape!

Well... I hope this has cleared up any misconceptions you may have had about food and diets.

And remember:
Life should NOT be a journey to the grave with the intention of arriving safely in an attractive and well-preserved body, but rather to skid in sideways - Chardonnay in one hand - chocolate in the other - body thoroughly used up, totally worn out and screaming "WOO-HOO, what a ride!!"

AND.....

For those of you who watch what you eat, here's the final word on nutrition and health. It's a relief to know the truth after all those conflicting nutritional studies.

1. The Japanese eat very little fat and suffer fewer heart attacks than Americans.

2. The Mexicans eat a lot of fat and suffer fewer heart attacks than Americans.

3. The Chinese drink very little red wine and suffer fewer heart attacks than Americans.

4. The Italians drink a lot of red wine and suffer fewer heart attacks than Americans...

5. The Germans drink a lot of beer and eat lots of sausages and fats and suffer fewer heart attacks than Americans.

CONCLUSION: Eat and drink what you like. Speaking English is apparently what kills you...............

[FIX]Current thread must be set to single thread apartment (STA) mode before OLE calls can be made. Ensure that your Main function has STAThreadAttribute marked on it. This exception is only raised if a debugger is attached to the process.

While working with Windows Application, I've come up with a small error while executing the code. Below is the error string

Current thread must be set to single thread apartment (STA) mode before OLE calls can be 
made. Ensure that your Main function has STAThreadAttribute marked on it. This exception is only raised if a debugger is attached to the process.

Solution 1: Add the key word [STAThread] before the main function
To over come the above specified error. 


   [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Login());
        }
Solution 2: Delete the files available in the bin\debug folder and rebuild the solution.

Solution 3: Check the task manager for the exe running in the background. Kill it. Your application will work as usual.

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech 
www.kaashivinfotech.com

Get File Extension from C#

While working in C#, I came to know about finding the file extension and wish to share the same with my blog readers.....

///// Sample code to get the file extension.

Add the namespace using system.io;

        string filename = @"C:\Users\venkat\Documents\venkat.doc";

 string extension = Path.GetExtension(p);
 if (extension  == ".doc")
 {
      messagebox.show ( " It's a word document ");
 }

Note: Please check the case sensitiveness

Cheers,

Venkatesan Prabu .J

Director - Kaashiv InfoTech - www.kaashivinfotech.com