Dentaku

Installing ASP.NET Providers on SQL Server 2000 without default collation

Do you need to install ASP.NET providers on a SQL Server with a non default collation? This blog is run on a SQL Server 2000 that uses "Finnish Swedish". So when I tried to install the ASP.NET providers for SQL Server I got the error: "Cannot resolve collation conflict for equal to operation.". To get it running you need to edit the script before running it.

1. Generating the script

Generate the script with aspnet_regsql in your .NET 2.0 folder. In my case it's C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727. Use this to create a file in C:
aspnet_regsql -sqlexportonly "c:\i nstall providers.sql" -A all -d <YourDataBaseName>
Using the -d option includes the name of your database in the script.

2. Fix the collation

The stored procedures aspnet_UsersInRoles_AddUsersToRoles and aspnet_UsersInRoles_RemoveUsersFromRoles uses temporay tables to store user names. To make the script work, search for DECLARE @tbNames and add COLLATE <your database collation> to row as shown below:

1
2
3
4

DECLARE @tbNames  table(Name nvarchar(256) 
    COLLATE Finnish_Swedish_CI_AI_KS_WS NOT NULL PRIMARY KEY)

3. Install the providers

Open up the script in a SQL Query Analyzer and execute it.

4. Set the connection string

ASP.NET uses a default connectionstring named LocalSqlServer, remove it and add it again in Web.config:

1
2
3
4
5
6
7
8
9
10
11

<connectionStrings>
    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" 
        connectionString="Data Source=<your server>;
            Initial Catalog=<database name>;
            User Id=<user>;
            Password=<password>;" 
    providerName="System.Data.SqlClient"/>
</connectionStrings>

2006-06-09 23:20:00 | Posted in ASP.NET | Link | digg this