Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Collation error

 
   Database Forums (Home) -> General Discussions RSS
Next:  AUTO: Douglas Dailey/Lenexa/IBM is out of the off..  
Author Message
Lackey

External


Since: Dec 13, 2011
Posts: 4



(Msg. 1) Posted: Tue Dec 13, 2011 2:21 pm
Post subject: Collation error
Archived from groups: comp>databases>ms-sqlserver (more info?)

I can ussually resolve this issue but I'm looking for help this time. I'm running the following select statement on one server and pulling the information from a second server.

SELECT CustomerName
from [SQL-STL].FSDBPE.dbo.FS_Customer

Results: Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 446, Level 16, State 1, Line 1
Cannot resolve collation conflict for replace operation.

The odd thing is I can run the following select statement without errors.

SELECT *
from [SQL-STL].FSDBPE.dbo.FS_Customer

Result: returns all fields including CustomerName

Thanks for your help!

 >> Stay informed about: Collation error 
Back to top
Login to vote
Erland Sommarskog2

External


Since: May 30, 2004
Posts: 1658



(Msg. 2) Posted: Tue Dec 13, 2011 11:25 pm
Post subject: Re: Collation error [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Lackey ( ) writes:
> I can ussually resolve this issue but I'm looking for help this time.
> I'm running the following select statement on one server and pulling the
> information from a second server.
>
> SELECT CustomerName
> from [SQL-STL].FSDBPE.dbo.FS_Customer
>
> Results: Server: Msg 8180, Level 16, State 1, Line 1
> Statement(s) could not be prepared.
> Server: Msg 446, Level 16, State 1, Line 1
> Cannot resolve collation conflict for replace operation.
>
> The odd thing is I can run the following select statement without errors.
>
> SELECT *
> from [SQL-STL].FSDBPE.dbo.FS_Customer
>
> Result: returns all fields including CustomerName

Interesting.

Is FS_Customer.CustomerName a physical column, or is it a computed
column in a table or a view?

Can you post the output of the following queries:

SELECT is_collation_compatible, uses_remote_collation, collation_name
FROM sys.servers
WHERE name = 'SQL-STL'

SELECT serverproperty('ProductVersion'), serverproperty('Collation'),
databasepropertyex(db_name(), 'Collation')

Please run the latter query both in the local database and in
the remote database.


--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

 >> Stay informed about: Collation error 
Back to top
Login to vote
Lackey

External


Since: Dec 13, 2011
Posts: 4



(Msg. 3) Posted: Tue Dec 13, 2011 11:25 pm
Post subject: Re: Collation error [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Thanks for your help. Here are the results:

SELECT is_collation_compatible, uses_remote_collation, collation_name
FROM sys.servers
WHERE name = 'SQL-STL'

Result:Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.servers'.

SELECT serverproperty('ProductVersion'), serverproperty('Collation'),
databasepropertyex(db_name(), 'Collation')

Local machine result: 8.00.2055 SQL_Latin1_General_CP1_CI_AS SQL_Latin1_General_CP1_CI_AS

Remote maching result: 8.00.2039 SQL_Latin1_General_CP1_CI_AS Latin1_General_BIN

I am using SQL Server 2000 by the way.
 >> Stay informed about: Collation error 
Back to top
Login to vote
Lackey

External


Since: Dec 13, 2011
Posts: 4



(Msg. 4) Posted: Tue Dec 13, 2011 11:25 pm
Post subject: Re: Collation error [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

I forgot to answer your first question. FS_Customer is a view. The CustomerName field is based on a physical field which is not calculated in any way.

Thanks again!
 >> Stay informed about: Collation error 
Back to top
Login to vote
Erland Sommarskog2

External


Since: May 30, 2004
Posts: 1658



(Msg. 5) Posted: Wed Dec 14, 2011 9:25 am
Post subject: Re: Collation error [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Lackey ( ) writes:
> Thanks for your help. Here are the results:
>
> SELECT is_collation_compatible, uses_remote_collation, collation_name
> FROM sys.servers
> WHERE name = 'SQL-STL'
>
> Result:Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'sys.servers'.

Moral: always say which version of SQL Server you are using up front.
Particularly if you are still on SQL 2000. Smile

The settings I'm asking for are available on SQL 2000 as well, but I
don't on the top of my head to find the information.

In the meanwhile, can you post the view definition?


--
Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Collation error 
Back to top
Login to vote
Lackey

External


Since: Dec 13, 2011
Posts: 4



(Msg. 6) Posted: Wed Dec 14, 2011 11:23 am
Post subject: Re: Collation error [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Create view FS_Customer as

select
T1.CMCUSTID as CustomerID,
T1.CMCUNAME as CustomerName,
T1.CMCUAD1 as CustomerAddress1,
T1.CMCUAD2 as CustomerAddress2,
T1.CMCUCITY as CustomerCity,
T1.CMCUSTAT as CustomerState,
T1.CMCUZIP as CustomerZip,
T1.CMCUCNTR as CustomerCountry,
T1.CMCONTCT as CustomerContact,
T1.CMPHONE as CustomerContactPhone,
T1.CMFAX as CustomerContactFax,
T1.CMBILLTO as BillToCustomerID,
T1.CMB2NAME as BillToName,
T1.CMB2AD1 as BillToAddress1,
T1.CMB2AD2 as BillToAddress2,
T1.CMB2CITY as BillToCity,
T1.CMB2STAT as BillToState,
T1.CMB2ZIP as BillToZip,
T1.CMB2CNTR as BillToCountry,
T1.CMACTCNT as AccountingContact,
T1.CMACPOS as AccountingContactPosition,
T1.CMACFONE as AccountingContactPhone,
T1.CMACFAX as AccountingContactFax,
T1.CMCSR as CSR,
T1.CMREGION as SalesRegion,
T1.CMCOMCD as CommissionCode,
T1.CMCLASS as CustomerClass1,
T1.CMCLASS$2 as CustomerClass2,
T1.CMCLASS$3 as CustomerClass3,
T1.CMCLASS$4 as CustomerClass4,
T1.CMCLASS$5 as CustomerClass5,
T1.CMCLASS$6 as CustomerClass6,
T1.CMCLASL as CustomerClass7,
T1.CMCLASL$2 as CustomerClass8,
T1.CMTOPLVL as IsTopLevel,
T1.CMCLVL as CustomerLevel,
T1.CMTRCLAS as TradeClassName,
T1.CMCNTCUR as CustomerControllingCode,
T1.VCCMCURCOD as CustomerCurrencyCode,
T1.CMOKCURR as CurrencyConversionProcessed,
T1.CMCRREP as CreditRepresentative,
T1.CMCREDIT as CreditLimitControllingAmount,
T1.CMCRAUTH as CreditLimitMaintainedUserID,
T1.CMCRDATE as CreditLimitMaintainedDate,
T1.CMAR as ARCustomerCode,
T1.CMOEHOLD as OrderEntryCreditHoldCode,
T1.CMSPHOLD as ShipmentCreditHoldCode,
T1.CMLCROVA as MaximumPastDueControllingAmount,
T1.CMCROVDY as MaximumPastDueDays,
T1.CMCROVP as MaximumPastDuePercent,
T1.CMCBOORD as IncludeOpenOrdersInCreditConsumed,
T1.CMCBRORD as IncludeReleasedOrdersInCreditConsumed,
T1.CMCBCM as IncludeOpenCreditMemosInCreditConsumed,
T1.CMCRUPPR as CheckCreditAtUpperLevels,
T1.CMACASGN as IsCreditHoldAssignmentAutomatic,
T1.CMCRHOLD as IsCustomerOnCreditHold,
T1.CMACREMV as IsCreditHoldRemovalAutomatic,
T1.CMDBID as DunBradstreetNumber,
T1.CMDBSIC as DunBradstreetClass,
T1.CMDBRATE as DunBradstreetRating,
T1.CMDBDATE as DunBradstreetMaintainedDate,
T1.CMBANK1 as BankReference1,
T1.CMBANK2 as BankReference2,
T1.CMPRTFMT as PrintFormat,
T1.CMPRTLNG as PrintLanguage,
T1.CMPRNT as ItemPrintCode,
T1.CMARTYPE as AROpenItemBalanceForwardCode,
T1.CMSTMTCD as PrintStatement,
T1.CMSTMTCY as StatementCycle,
T1.CMINTRST as ServiceChargeInterestRate,
T1.CMPAYHOW as TermsCode,
convert(float,T1.CMTRMPC1) / 10 as TermsDiscountPercent1,
T1.CMTRMDA1 as TermsDiscountDays1,
convert(float,T1.CMTRMPC2) / 10 as TermsDiscountPercent2,
T1.CMTRMDA2 as TermsDiscountDays2,
convert(float,T1.CMTRMPC3) / 10 as TermsDiscountPercent3,
T1.CMTRMDA3 as TermsDiscountDays3,
convert(float,T1.CMTRMPC4) / 10 as TermsDiscountPercent4,
T1.CMTRMDA4 as TermsDiscountDays4,
T1.CMTRMNET as TermsDiscountNetDays,
T1.CMPAYWEN as TermsMonthlyPaymentDay,
T1.CMCUTOFF as TermsMonthlyCutoffDay,
T1.CMDELAY as TermsMonthlyDelay,
T1.CMSACNO as SalesAccount,
T1.CMCGACNO as CostOfGoodsSoldAccount,
T1.CMARACNO as AccountsReceivableAccount,
T1.CMFTACNO as FreightAccount,
convert(float,T1.CMTRADE) / 10 as DefaultSurchargeDiscountPercent, T1.CMPRIOR as CustomerPriority,
T1.CMENDUSR as EndUserCustomerID,
T1.CMFOB as FOBPoint,
T1.CMFRTCD as FreightChargeCode,
T1.CMCARR as CarrierName,
T1.CMRTCODE as DistributionZone,
T1.CMSHCODE as DistributionRegion,
T1.CMMULSHP as AllowMultipleOrderShipments,
T1.CMMITSHP as AllowMultipleLineItemShipments,
T1.CMPALLOC as AllowPartialAllocations,
T1.CMPSHPIT as AllowPartialShipments,
T1.CMPARTL as CustomerAcceptsPartialShipments,
T1.CMCLEXP as IsCOAutoclosedAfterExpiration,
T1.CMCLCOM as AutocloseAfterShipment,
T1.CMCLEXPD as DaysAfterExpirationToAutocloseCO,
T1.CMCLQTY as AutocloseQuantityVariance,
T1.CMTRLT as TransportationLeadTimeDays,
T1.CMSHIPLT as ShipmentLeadTimeDays,
T1.CMONTIME as DaysEarlyConsideredOnTimeShipment,
T1.CMONTIML as DaysLateConsideredOnTimeShipment,
convert(float,T1.CMRCPTVU) / 10 as UnderShipmentQuantityTolerancePercent,
convert(float,T1.CMRCPTVO) / 10 as OverShipmentQuantityTolerancePercent,
T1.CMOTCOMP as ForceOnTimeShipment,
T1.CMQTCOMP as ForceOnTimeShipmentVariance,
T1.CMSTKRM as PreferredShipmentStockroom,
T1.CMBIN as PreferredShipmentBin,
T1.CMEXMPT as FederalPrimaryTaxExemptReason,
T1.CMTXCERT as FederalPrimaryTaxExemptCertificateNumber,
T1.CMEXS as StateSecondaryTaxExemptReason,
T1.CMSTXCER as StateSecondaryTaxExemptCertificateNumber,
T1.CMEXC as CountyTaxExemptReason,
T1.CMCTXCER as CountyTaxExemptCertificateNumber,
T1.CMEXM as MunicipalTaxExemptReason,
T1.CMMTXCER as MunicipalTaxExemptCertificateNumber,
T1.CMFTAXCD as FederalPrimaryTaxCode,
T1.CMSTAXCD as StateSecondaryTaxCode,
T1.CMCTAXCD as CountyTaxCode,
T1.CMMTAXCD as MunicipalTaxCode,
T1.CMVATCUS as VATCustomerCode,
T1.CMEDI as IsEDITradingPartner,
T1.CMLSTMNT as LastMaintainedDate,
T1.CMEMAIL as CustomerContactEmail,
T1.CMCADATE as CustomerStartDate,
T1.CMSTATUS as CustomerStatus,
T1.CMBKCUST as TradingPartnerBank,
T1._CMSTRUC_OwnRow as ParentCustomerKey,
T1._CSTCLASS_OwnRow as TradeClassKey,
T1._Ts as CustomerRowVersion,
T1._Row as CustomerKey
from Mfg_CUSTMAST T1

AS you can see, it is a pretty straight forward view.

Thanks again.
 >> Stay informed about: Collation error 
Back to top
Login to vote
Erland Sommarskog2

External


Since: May 30, 2004
Posts: 1658



(Msg. 7) Posted: Wed Dec 14, 2011 11:25 pm
Post subject: Re: Collation error [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Lackey ( ) writes:
> Create view FS_Customer as
>...
> from Mfg_CUSTMAST T1
>
> AS you can see, it is a pretty straight forward view.

And Mfg_CUSTMAST is a table and not another view?

Is there any computed columns in this table/view?

What I'm getting at is that the replace() operation mentioned in
the error message must be hiding somewhere. It cannot come out of
the blue.

Meanwhile, the query for linked servers on SQL 2000 is:

select srvcollation, useremotecollation, collationcompatible
from master.dbo.sysservers
where srvname = 'SQL-STL'

--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
 >> Stay informed about: Collation error 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
SQL2005 collation vs Oracle - Hi there, Is it posible to set the collation of SQL server 2005 like Oracle, which is that objects names and column names are case-insensitive, but data is sensitive;e.g: In Oracle: --create lower-case table and column name create table t1(c1..

Cannot resolve collation conflict for equal to operation. - I've stumbled across the above error and am a little stuck. I've just installed SQL2000 (sp3) on my PC and restored a database from our Live server. On a simple Update statement on a #temp table, it fails with the above message. I think I understand wha...

sql Cannot resolve collation conflict equals - comparing r.. - Hello, I currently have Table1 and View1. View1 is a query from 2 or 3 tables that works fine on its own. However in my current query if I try to use it...something like... SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3 FROM View1 a JOIN...

SQL Duplicate Error - Hi! I am trying to do a simple udpate on a table and I am getting the error: "Cannot insert duplicate key row in object 'UserInfo' with unique index 'UserInfo_Login'.The statement has been terminated." Here is the command I am trying to run...

Error using MSSQL Server - Hi I am new here, so Hello to everyone! I am WIndows Xp Pro user. I am getting this message "An error 6 -(the handle is invalid. ) occured while performing the service operation on the MSSQLServer service.". Can someone help me to solve this. ...
   Database Forums (Home) -> General Discussions All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum

Warning: fopen(/home/adsense_reject.txt) [function.fopen]: failed to open stream: Permission denied in /home/autoforu/public_html/Giga/GigaFunctions.php on line 1142

Warning: fwrite(): supplied argument is not a valid stream resource in /home/autoforu/public_html/Giga/GigaFunctions.php on line 1143

Warning: fclose(): supplied argument is not a valid stream resource in /home/autoforu/public_html/Giga/GigaFunctions.php on line 1144



[ Contact us | Terms of Service/Privacy Policy ]