msp files in the MSDE 2000 Release A

Hi there,
After downloading MSDE2000A.exe and extacting the files,
in the Setup folder there are 16 msi files for instances,
a cab file and 17 msp files. I didn't find anything in the
ReadMeMSDE2000A.htm file about these msp files. Are they
used to patch a MSDE 2000 release A instance immediately
after installing it or are they for older versions of MSDE?
Thank you, I appreciate your help,
GDD1499
Hi,
The msp files are the Microsoft Installer Patch files. They are the Patch
Packages that are applied after the setup.
Each .msi file is a separate package that allows you to install an instance
of the Desktop Engine. And therefore there is a corresponding Path File for
each msi file.
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Ashish,
Thank you for your reply.
So, if I use any of the 16 msis to install an MSDE 2000
Release A instance - without using setup.exe - I need to
patch it immediately using the corresponding patch file?
Do you know what does the patch fix? Or could you point me
to a document that explaines what the patch does.
Thank you,
GDD1499

>--Original Message--
>Hi,
>The msp files are the Microsoft Installer Patch files.
They are the Patch
>Packages that are applied after the setup.
>Each .msi file is a separate package that allows you to
install an instance
>of the Desktop Engine. And therefore there is a
corresponding Path File for
>each msi file.
>
>Ashish
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>.
>
|||Hi,
Yes. You can run any one of the 16 msis to install an instance of MSDE 2000
Release A and need to patch it immediately with the corresponding patch
file.
I am not aware as to what is contained in the patch file. I tried to search
for resources to give details about the patches, but could not locate one.
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

MSO3-031 Patch for SQL Server

When loading patch with multiple instances of Sql and one
instance does not appear in the drop down list of choices?
How can I get this instance to appear in the drop down
list?Is the instance running and is it configured the same like the other
instances in terms of security (do you have permission to access it)?
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bart" <anonymous@.discussions.microsoft.com> wrote in message
news:551101c47420$e6235ff0$a501280a@.phx.gbl...
> When loading patch with multiple instances of Sql and one
> instance does not appear in the drop down list of choices?
> How can I get this instance to appear in the drop down
> list?

MSO3-031 Patch for SQL Server

When loading patch with multiple instances of Sql and one
instance does not appear in the drop down list of choices?
How can I get this instance to appear in the drop down
list?
Is the instance running and is it configured the same like the other
instances in terms of security (do you have permission to access it)?
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bart" <anonymous@.discussions.microsoft.com> wrote in message
news:551101c47420$e6235ff0$a501280a@.phx.gbl...
> When loading patch with multiple instances of Sql and one
> instance does not appear in the drop down list of choices?
> How can I get this instance to appear in the drop down
> list?
sql

MSO3-031 Patch for SQL Server

When loading patch with multiple instances of Sql and one
instance does not appear in the drop down list of choices?
How can I get this instance to appear in the drop down
list?Is the instance running and is it configured the same like the other
instances in terms of security (do you have permission to access it)?
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bart" <anonymous@.discussions.microsoft.com> wrote in message
news:551101c47420$e6235ff0$a501280a@.phx.gbl...
> When loading patch with multiple instances of Sql and one
> instance does not appear in the drop down list of choices?
> How can I get this instance to appear in the drop down
> list?

Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Latin1_

execute the following in any user database...

So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.

It appears that perhaps the system objects have different collation attributes..?!!

select name from sysusers where name not in ( select name from master..syslogins )

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

Oct 14 2005 00:35:21

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

(1 row(s) affected)

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This is because your master is in a different collation than your user database.

Try adding the COLLATE to your query.

selectnamefrom sysusers wherenamenotin(selectname COLLATE Latin1_General_CI_AIfrom master..syslogins )

Not sure which collation is your master's but just make sure they use the same collation for the comparison.

I personally like to keep all my collations the same over the entire server.

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:

select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'

Any thoughts? Thanks in advance.

|||

Hi,

Rewrite your join like this

on o.nameCOLLATE Latin1_General_CI_AS = e.objname

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why

Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.

Dave

|||

Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.

BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?

Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?

Catadmin

|||

Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).

Select colObjectName,Convert(varchar(1000),value)as colDescription

From(

Select table_name as colObjectName

FromOpenquery(yourservername,'yourdbname..sp_tables')

) procs

LeftJoin

::FN_LISTEXTENDEDPROPERTY (

'Description',

'User','dbo',

'Table',default,default,default

)

on colObjectName

-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server

= objname

Where colObjectName like'tbl%' --the prefix we use for in house developed tables

|||I did some research, and my guess is that fn_listextendedproperty returns the property of
mssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.

What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215

Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Latin1_

execute the following in any user database...

So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.

It appears that perhaps the system objects have different collation attributes..?!!

select name from sysusers where name not in ( select name from master..syslogins )

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

Oct 14 2005 00:35:21

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

(1 row(s) affected)

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This is because your master is in a different collation than your user database.

Try adding the COLLATE to your query.

select name from sysusers where name not in ( select name COLLATE Latin1_General_CI_AI from master..syslogins )

Not sure which collation is your master's but just make sure they use the same collation for the comparison.

I personally like to keep all my collations the same over the entire server.

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:

select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'

Any thoughts? Thanks in advance.

|||

Hi,

Rewrite your join like this

on o.name COLLATE Latin1_General_CI_AS = e.objname

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why

Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.

Dave

|||

Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.

BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?

Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?

Catadmin

|||

Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).

Select colObjectName, Convert(varchar(1000),value) as colDescription

From (

Select table_name as colObjectName

From Openquery (yourservername,'yourdbname..sp_tables')

) procs

Left Join

::FN_LISTEXTENDEDPROPERTY (

'Description',

'User','dbo',

'Table', default, default, default

)

on colObjectName

-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server

= objname

Where colObjectName like 'tbl%' --the prefix we use for in house developed tables

|||I did some research, and my guess is that fn_listextendedproperty returns the property of
mssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.

What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215

Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Latin1_

execute the following in any user database...

So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.

It appears that perhaps the system objects have different collation attributes..?!!

select name from sysusers where name not in ( select name from master..syslogins )

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

Oct 14 2005 00:35:21

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

(1 row(s) affected)

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This is because your master is in a different collation than your user database.

Try adding the COLLATE to your query.

selectnamefrom sysusers wherenamenotin(selectname COLLATE Latin1_General_CI_AIfrom master..syslogins )

Not sure which collation is your master's but just make sure they use the same collation for the comparison.

I personally like to keep all my collations the same over the entire server.

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:

select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'

Any thoughts? Thanks in advance.

|||

Hi,

Rewrite your join like this

on o.nameCOLLATE Latin1_General_CI_AS = e.objname

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why

Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.

Dave

|||

Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.

BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?

Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?

Catadmin

|||

Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).

Select colObjectName,Convert(varchar(1000),value)as colDescription

From(

Select table_name as colObjectName

FromOpenquery(yourservername,'yourdbname..sp_tables')

) procs

LeftJoin

::FN_LISTEXTENDEDPROPERTY (

'Description',

'User','dbo',

'Table',default,default,default

)

on colObjectName

-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server

= objname

Where colObjectName like'tbl%' --the prefix we use for in house developed tables

|||I did some research, and my guess is that fn_listextendedproperty returns the property of
mssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.

What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215

Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Latin1_

execute the following in any user database...

So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.

It appears that perhaps the system objects have different collation attributes..?!!

select name from sysusers where name not in ( select name from master..syslogins )

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

Oct 14 2005 00:35:21

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

(1 row(s) affected)

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This is because your master is in a different collation than your user database.

Try adding the COLLATE to your query.

selectnamefrom sysusers wherenamenotin(selectname COLLATE Latin1_General_CI_AIfrom master..syslogins )

Not sure which collation is your master's but just make sure they use the same collation for the comparison.

I personally like to keep all my collations the same over the entire server.

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:

select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'

Any thoughts? Thanks in advance.

|||

Hi,

Rewrite your join like this

on o.nameCOLLATE Latin1_General_CI_AS = e.objname

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why

Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.

Dave

|||

Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.

BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?

Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?

Catadmin

|||

Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).

Select colObjectName,Convert(varchar(1000),value)as colDescription

From(

Select table_name as colObjectName

FromOpenquery(yourservername,'yourdbname..sp_tables')

) procs

LeftJoin

::FN_LISTEXTENDEDPROPERTY (

'Description',

'User','dbo',

'Table',default,default,default

)

on colObjectName

-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server

= objname

Where colObjectName like'tbl%' --the prefix we use for in house developed tables

|||I did some research, and my guess is that fn_listextendedproperty returns the property of
mssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.

What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215

Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Lat

execute the following in any user database...

So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.

It appears that perhaps the system objects have different collation attributes..?!!

select name from sysusers where name not in ( select name from master..syslogins )

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

Oct 14 2005 00:35:21

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

(1 row(s) affected)

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This is because your master is in a different collation than your user database.

Try adding the COLLATE to your query.

select name from sysusers where name not in ( select name COLLATE Latin1_General_CI_AI from master..syslogins )

Not sure which collation is your master's but just make sure they use the same collation for the comparison.

I personally like to keep all my collations the same over the entire server.

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:

select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'

Any thoughts? Thanks in advance.

|||

Hi,

Rewrite your join like this

on o.name COLLATE Latin1_General_CI_AS = e.objname

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why

Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.

Dave

|||

Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.

BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?

Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?

Catadmin

|||

Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).

Select colObjectName, Convert(varchar(1000),value) as colDescription

From (

Select table_name as colObjectName

From Openquery (yourservername,'yourdbname..sp_tables')

) procs

Left Join

::FN_LISTEXTENDEDPROPERTY (

'Description',

'User','dbo',

'Table', default, default, default

)

on colObjectName

-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server

= objname

Where colObjectName like 'tbl%' --the prefix we use for in house developed tables

|||I did some research, and my guess is that fn_listextendedproperty returns the property of
mssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.

What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215