Update Date with Today´s date where CustomerName = TextBox.text
Thanks in advance for your helpstring strSQL = "UPDATE [Customers] SET [Date] = GetDate() WHERE CustomerName = " + TextBox.Text
basic, basic, basic
I got the above error when I tried to execute the following query
delete from BUSINESS_ASSOCIATE
where ba_name is null
As you can see the query isn't exactly complicated.
What the table does have is lots of dependancies. I got SSMS to show me all the dependancies and there are literally hundreds (maybe thousands - I'm not going to count them all) of dependant tables and i suspect that is what is causing the problem.
So what do I do about it? This is a valid data-model (industry standard in fact) but SQL won't let me delete data from this table and that's a huge problem for me. Any advice about how to progress would be welcomed.
-Jamie
anyone?
|||
Hi Jamie,
I doubt there's a "quick fix" here, but just out of curiosity, if you set maxdop to 1 for this statement, does it make any difference (assuming you've a multi cpu machine)?
Aside from that there's the obvious workaround of throwing more memory at the server, which I'm sure you've considered anyway.
Cheers
Rob
|||Could you please file a bug at http://connect.microsoft.com/sqlserver with repro script? Can you post the estimated showplan results if the query optimizer can even generate a plan?|||Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
|||
Jamie Thomson wrote:
Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
OK, its up here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=227892
Its private though (cos I've uploaded a DB with data in it) so oly MSFT can see it.
-Jamie
|||Thanks for creating the bug and the repro. I assigned it to the right team and I was able to reproduce the problem on a later build also. They will get back to you soon.|||Is the max SQL memory setting too low? If it is fixed, try raising it. Also, I usually leave index creation memory set to 0. Is there a value for that server there?|||This is not related to the memory allocated to SQL Server or any of those settings.|||
This is not related to the memory allocated to SQL Server or any of those settings.
Thanks Uma. I was just about to go away and test that. Good job I checked here first
-Jamie
|||
It looks like you hit the documented limit on the maximum number of FK references that QP can handle on a single table. It is 253 according to BOL. See below topic:
http://msdn2.microsoft.com/en-us/library/ms143432.aspx
|||Was there ever a workaround or a resolution on this?|||There is no workaround other than simplifying your schema to reduce the number of FK references.
I got the above error when I tried to execute the following query
delete from BUSINESS_ASSOCIATE
where ba_name is null
As you can see the query isn't exactly complicated.
What the table does have is lots of dependancies. I got SSMS to show me all the dependancies and there are literally hundreds (maybe thousands - I'm not going to count them all) of dependant tables and i suspect that is what is causing the problem.
So what do I do about it? This is a valid data-model (industry standard in fact) but SQL won't let me delete data from this table and that's a huge problem for me. Any advice about how to progress would be welcomed.
-Jamie
anyone?
|||
Hi Jamie,
I doubt there's a "quick fix" here, but just out of curiosity, if you set maxdop to 1 for this statement, does it make any difference (assuming you've a multi cpu machine)?
Aside from that there's the obvious workaround of throwing more memory at the server, which I'm sure you've considered anyway.
Cheers
Rob
|||Could you please file a bug at http://connect.microsoft.com/sqlserver with repro script? Can you post the estimated showplan results if the query optimizer can even generate a plan?|||Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
|||
Hi guys,
I sure can. I'll do that from the office tomorrow when I see if the optimiser can produce anything..
-Jamie
OK, its up here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=227892
Its private though (cos I've uploaded a DB with data in it) so oly MSFT can see it.
-Jamie
|||Thanks for creating the bug and the repro. I assigned it to the right team and I was able to reproduce the problem on a later build also. They will get back to you soon.|||Is the max SQL memory setting too low? If it is fixed, try raising it. Also, I usually leave index creation memory set to 0. Is there a value for that server there?|||This is not related to the memory allocated to SQL Server or any of those settings.|||
This is not related to the memory allocated to SQL Server or any of those settings.
Thanks Uma. I was just about to go away and test that. Good job I checked here first
-Jamie
|||
It looks like you hit the documented limit on the maximum number of FK references that QP can handle on a single table. It is 253 according to BOL. See below topic:
http://msdn2.microsoft.com/en-us/library/ms143432.aspx
|||Was there ever a workaround or a resolution on this?|||There is no workaround other than simplifying your schema to reduce the number of FK references.
ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"
Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.
Any ideas? have you seen this before?
NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.
Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx
TO REPRODUCE:
* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))
* Data File Template:
Age<tab>Firstname<tab>Lastname<return>
* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:\datatest\exampledata-c.Dat'
WITH (FORMATFILE = 'C:\datatest\examplefmt.Fmt');
Thanks in advanced for any feedback.
Cheers!Hi,
plz can you show your XML format file "examplefmt.Fmt".
there must be something like this:
examplefmt.Fmt
...
2 SQLCHAR 0 100 "," 2 firstname SQL_Latin1_General_CP1_CI_AS
...
ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"
Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.
Any ideas? have you seen this before?
NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.
Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx
TO REPRODUCE:
* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))
* Data File Template:
Age<tab>Firstname<tab>Lastname<return>
* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:\datatest\exampledata-c.Dat'
WITH (FORMATFILE = 'C:\datatest\examplefmt.Fmt');
Thanks in advanced for any feedback.
Cheers!Hi,
plz can you show your XML format file "examplefmt.Fmt".
there must be something like this:
examplefmt.Fmt
...
2 SQLCHAR 0 100 "," 2 firstname SQL_Latin1_General_CP1_CI_AS
...
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 ofWhat should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215
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 ofWhat should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215
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 ofWhat should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215
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 ofWhat should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215
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 ofWhat should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215
