Server: Msg 446, Level 16, State 9 Cannot resolve collation conflict for equal to operation

Collation precedence conflict in SQL Server 2000

The collation precedence rules apply only to the character string data types, char, varchar, text, nchar, nvarchar, and ntext. Objects with other data types do not participate in collation evaluations.
link to article on msdn
A simple example is as follows

create table #TempRO
(
Id  int Identity (1,1),
App_No     NVarchar (100)
)
– insert some values in this table
Select *
from #TempRO T
Inner join App AO
On T.App_No = AO.App_No

We are having the App table using a different collation then the default that is “Arabic_CI_AS”

So the above select will give the following collation error, to remove it we just add the following statement while defining the temp table

create table #TempRO
(
Id  int Identity (1,1),
App_No     NVarchar (100)    collate Arabic_CI_AS
)

This not only happens in temp tables but also in the normal tables or at times if we are comparing columns so its better to use sp_help TableName or the syscolumns to find the collation of the two.

Happy Collating

Microsoft Enterprise Service Bus (EBS)

A glance at the core BizTalk system components used by the Microsoft ESB Guidance.

Microsoft Enterprise Service Bus (EBS)

 

Quick Links:

                    MSDN site: http://msdn.microsoft.com/esb

                    Community Extensions: http://codeplex.com/esb

Internet Explorer 8 RC1 is available.

Internet Explorer 8 RC1 will be available in 25 languages including: Arabic, Chinese (Traditional, Simplified and Hong Kong), Czech, Danish, Dutch, English, Finnish, French, German, Greek, Hebrew, Hungarian, Italian, Japanese, Korean, Norwegian, Portuguese (Brazil and Portugal), Polish, Russian, Spanish, Turkish and Swedish.

Yes you can download it from the following link.

http://www.microsoft.com/windows/Internet-explorer/beta/default.aspx

Well it’s been a long time Microsoft has been investing in Internet Explorer, so some major milestones have been achieved in this release as well. So the RC1 Release of Internet Explorer includes improvements to performance, stability and compatibility.  With this build, there are also enhancements to the fit and finish of the user interface, additional investment in CSS 2.1 and security and privacy updates to help address today’s evolving threat landscape.

Display My Computer Icon on the Desktop in Windows 7

Well its been a long time since i have written and shared any new tips and tricks with my fellow geeks.
But here I am with the basic really easy post on Windows 7. Well this new version of windows has really inspired me and I would like to keep writing on the new features available as I go on exploring them.

The easiest way is to right click on your desktop-> Personalize -> Change Desktop Icons  and here you land on the following screen.

Walla! You have your My Computer on the desktop.

Login failed error: (Msg 18456) SQL Server 2005 Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’

While I was migrating my database server from SQL 2000 to SQL Server 2005 I came across this error as the production and development environment were not quite identical.

Well the first thing to note in this error is non descriptive, preventing information disclosure to unauthenticated clients.

To determine the true reason for the failure, the administrator can look in the server’s error log where a corresponding entry will be written.  An example of an entry is:

2006-02-27 00:02:00.34 Logon     Error: 18456, Severity: 14, State: 8.

2006-02-27 00:02:00.34 Logon     Login failed for user ‘<user name>’. [CLIENT: <ip address>]

A great article with the complete description of this error and states is written by Il-Sung Lee here: http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

Happy working with your linked servers.

Adding reference to Project in Visual Studio 2005

In your Visual Studio IDE go to the projects (at the top) -> add references -> com (tab) -> add the Microsoft Excel library installed.

 

Or you can use the project explorer.

Right click on the references-> add references -> com (tab) -> add the library.

 

Please refer to the image below.

 

Then simply what you need to do is Import the Excel.

Imports Microsoft.Office.Interop.Excel for VB

using Microsoft.Office.Interop;              for C#

 

The “Microsoft Excel X Object Library” where X will depend on whatever version of Excel you have in the computer.
So the X depends on which version of Excel is installed on your machine.

Finding out if there are trailing spaces in a column

A lot of you might have come across this problem just like me.

 

Firstly we create a temporary table.

create table #temp1

(

            id Int Identity(1,1),

            invoice varchar (20)

)

insert into #temp1 values (’CR’)

insert into #temp1 values (’CR ‘)

insert into #temp1 values (’ CR ‘)

insert into #temp1 values (’ CR’)

 

Select *  From #temp1

            where DATALENGTH(invoice) <> len(invoice)

 

The len function in SQL gives you the length of the characters including the spaces white DataLength excludes the trailing not preceding spaces.

 

Here if you don’t have the column defined as varchar rather you have an nvarchar field then you simply convert then to varchar before calling the respective functions like this.

 

Select *  From #temp1

            where DATALENGTH(convert(varchar(20),invoice)) <> len(convert(varchar(20),invoice))

 

 

Happy searching trailing spaces in your db, please share with us if you have better options.

Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor. Server: Msg 8180, Level 16 ,Server: Msg 102, Level 15, Server: Msg 1018, Level 15

Server: Msg 8180, Level 16

Server: Msg 102, Level 15

Server: Msg 1018, Level 15, State 1, Procedure SendReview, Line 12

Incorrect syntax near ‘FASTFIRSTROW’. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor.

 

We were getting this error from the SQL server job that was running a store procedure inside which we were calling a table that was on a linked server running SQL Server 2005, while the major database was running on SQL Server 2000. We had the linked server setup and it was running fine. We could run queries to this table on the 2005 server without any difficulties.

One thing that I came to find out was the changes in the cursor. Please find details from the following link http://msdn.microsoft.com/en-us/library/ms143359.aspx.

 

Solution:

            1: Either you can have a temp table created out side the cursor to get all the values from across the linked server and then create the cursor over this table.

2: Secondly you can replace the Cursor with a simple loop.

3: Thirdly if possible and you can simply create the Cursor as “FOR Read Only”.

 

Hope this helps you people to migrate your databases happilyJ.

Well I found the solution from this URL:
http://www.eggheadcafe.com/software/aspnet/31278881/cannot-declare-a-cursor-t.aspx

Thought I should share it with everyone.

Showing Line numbers with Visual studio 2005

I have been changing machines quite frequently since the last 6 months. And each time I have a new machine I need to setup the environment as I prefer.

The tabs spaces the fonts the immediate window and the list goes on.

But one thing I always forget is where is the setting for the Line Numbers to appear so this blog is for me

Goto:

Tools -> Options -> Text editor- > All languages -> General -> Display (Line Numbers)

 

Hope this helps other fellow readers as well.

How to manually extend the evaluation period

As most of you technology geeks must be using the Windows Server 2008. And a lot might have already ran out of their allocated evaluation period of 60 days J.

 But don’t worry it’s not the end of your exploration with this new enhanced server from Microsoft. If you want to extend the evaluation period it’s really simple. I came across an email from a colleague of mine a great developer rather a master mind; thanks Naweed.

 

So if your 60 days initial evaluation period nears to end just follow these simple steps to increase the period using the Slmgr.vbs script.

 

1. Click Start, and then click Command Prompt.

 2. Type slmgr.vbs -dli, and then press ENTER to check the current status of your evaluation period.

 3. To reset the evaluation period, type slmgr.vbs –rearm, and then press ENTER.

 4. Restart the computer.

 

This resets the evaluation period to 60 days.

 

You can find more ways to of extending this evaluation here in this kb article:

1. How to install Windows Server 2008 without activating it
2. How to manually extend the evaluation period
3. How to automate the extension of the evaluation period

 

But keep in mind that you can only extend to a max of 240 days (i.e. 3 extensions)