When creating a stored procedure and it contains a SELECT statement, you may get the following error message even if the collation of your database seems to be correct.
Cannot resolve collation conflict for column n in SELECT statement.
where n is the column number.
This can be easily fixed by appending the following portion of SQL to the column n in your SELECT statement:
COLLATE <your database collation name>
For example, COLLATE SQL_Latin1_General_CP1_CI_AS.
When trying to change the collation of a database, you may get the following error message:
The database could not be exclusively locked to perform the operation.
To workaround this problem, we first need to set the database for single user. And change it back for multi user after changing the collation.
ALTER DATABASE <database_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE <database_name> COLLATE <target_collation_name e.g. SQL_Latin1_General_CP1_CI_AS>
ALTER DATABASE <database_name> SET MULTI_USER
By default, Microsoft SQL Server 2008 doesn't allow any remote connection. What we'll need to do is to enable TCP/IP protocol in SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <InstanceName> and enable the TCP/IP protocol as shown in the picture below. An example of InstanceName is MSSQLSERVER.
After restarting SQL Server (InstanceName) service (as shown below), it should work without any problem. You can quickly access the services windows by going to start menu > run > type services.msc. Locate the SQL Server (InstanceName) service, right click on it, and choose restart.
In my case, completing these steps enables me to connect from a remote machine successfully. However, someone reported that we also need SQL Server Browser service running too. Therefore, if completing the steps above doesn't help you get connected, you may try setting SQL Server Browser service to Automatic and start the service.
Created by Train Horns
