Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #863 > unrolled thread

Collation error

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Collation error Lackey <lackeysc@gmail.com> - 2011-12-13 09:21 -0800
    Re: Collation error Erland Sommarskog <esquel@sommarskog.se> - 2011-12-13 23:32 +0100
      Re: Collation error Lackey <lackeysc@gmail.com> - 2011-12-13 14:48 -0800
        Re: Collation error Erland Sommarskog <esquel@sommarskog.se> - 2011-12-14 08:22 +0000
          Re: Collation error Lackey <lackeysc@gmail.com> - 2011-12-14 06:23 -0800
            Re: Collation error Erland Sommarskog <esquel@sommarskog.se> - 2011-12-14 23:53 +0100
      Re: Collation error Lackey <lackeysc@gmail.com> - 2011-12-13 14:53 -0800

#863 — Collation error

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!

[toc] | [next] | [standalone]

#864

Lackey (lackeysc@gmail.com) 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@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

[toc] | [prev] | [next] | [standalone]

#865

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.

[toc] | [prev] | [next] | [standalone]

#867

Lackey (lackeysc@gmail.com) 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. :-)

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@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

[toc] | [prev] | [next] | [standalone]

#868

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.

[toc] | [prev] | [next] | [standalone]

#869

Lackey (lackeysc@gmail.com) 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@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

[toc] | [prev] | [next] | [standalone]

#866

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!

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web