Error publishing aspnetdb

Topics: Database Publishing Wizard
Mar 16, 2007 at 8:51 AM
Hi all, I'm a newbie in SQL Server and I'm building an asp.net website with Visual Web Developer. I noticed this cool tool, SQL Server Hosting Toolkit, to upload tables to the hoster (i couldn't find a way to do it), so I downloaded the italian version of the software and tried first to use it for my aspnetdb file, which at the moment contains just a few roles and users I inserted to test the site login procedure.
Then I used SQL Server Management Studio Express to connect to the hoster db space and executed the script generated by SQL SHT. My working environment is SQL Server 2005 Express, the hoster has SQL Server 2000, so I selected that option for scripting generation.
At the end of the query execution, these red lines were displayed:

Messaggio 446, livello 16, stato 9, procedura aspnetUsersInRolesAddUsersToRoles, riga 45
Cannot resolve collation conflict for equal to operation.
Messaggio 446, livello 16, stato 9, procedura aspnetUsersInRolesRemoveUsersFromRoles, riga 50
Cannot resolve collation conflict for equal to operation.
Messaggio 208, livello 16, stato 11, riga 1
Invalid object name 'dbo.aspnetUsersInRolesAddUsersToRoles'.
Messaggio 208, livello 16, stato 11, riga 1
Invalid object name 'dbo.aspnetUsersInRolesRemoveUsersFromRoles'.

Any ideas about that?

Thank you in advance,
JohnR
Mar 16, 2007 at 8:57 PM
Hi JohnR -

This message is caused by the fact that the database you are publishing into has a different database collation than the one you are publishing from. (A collation describes how text data should be sorted... see http://msdn2.microsoft.com/en-us/library/aa174903(SQL.80).aspx) We recommend that you change the collation of the source database to match that of the target database. You can find the collation of a database by running the following command against your hosted database:

SELECT DATABASEPROPERTYEX('database_name', 'Collation');

You can change the collation of the local database by executing the following command in an query execution window:

ALTER DATABASE database_name COLLATE collation_name

The next version of the Database Publishing Wizard will give a warning about this collation issue if you are choosing the "Publish to hosting provider" option.

Thanks,
alex
Mar 16, 2007 at 10:34 PM
Many thanks for your answer.

I'm not sure i did the right thing, well, I'll tell you what I found. I got my local tables into a aspnetdb.mdf file, not displayed into the environment in MS SQL Server Management Studio Express when I connect to the local area.
I connected both to my SQL Server 2005 Express (local) and SQL Server 2000 (hosted). I used the "master" db into "system database" to find the collation (again, the aspnetdb isn't there, it's into a .mdf file) and received
Latin1GeneralCI_AS
then I used the hosted db onto SQL Server 2000 and received
SQLLatin1GeneralCP1CI_AS

Are they different or it's just a name change due to the version change of SQL Server? Do I have to alter my local 2005 Express collation to SQLLatin1GeneralCP1CI_AS?

Thank you again,
JohnR