This Stored Proc works in SQL 2000 by fails in SQL 2005

[MisterMo]

could someone give me a clue of why I get a conversion error in SQL 2005

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--Created by Mo for POS Informes
ALTER PROCEDURE [dbo].[XLRSP_DailyProductionByShift]
@TerminalId varchar(200),  -- (till in Restaurant, Supermarket, Bar.....)
@StartDate datetime, -- Starting date range.
@EndDate datetime -- End Date range

AS

BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	create table #TempTbl(FechaCierre datetime, Descripcion varchar(100), PuntoDeVenta Varchar(100), 
	DescripcionFB varchar(100), Totallinea numeric(10,2), Turno varchar(100), IdCierre int, Desde datetime,
	Hasta datetime, TiposPago varchar(100) )
	
	insert into #TempTbl exec ControlesHistoricosCentral.dbo.XLRSP_DailyProductionByShift @TerminalId,@StartDate,@EndDate

	SELECT TC.FechaCierre, FBE.Descripcion, FBT.Descripcion AS [Punto de Venta], ZTFB.DescripcionFB, CONVERT(numeric(10, 2), 
	SUM(HTTL.Cantidad * HTTL.Precio - HTTL.Cantidad * HTTL.Precio * HTTC.Descuento / 100)) AS Totallinea, FBTu.Descripcion AS Turno, 
	TC.Id AS IdCierre, FBTu.Desde, FBTu.Hasta, FBTP.Descripcion AS TiposPago
	FROM HIST_TPV_Tickets_C AS HTTC INNER JOIN
	HIST_TPV_Tickets_L AS HTTL ON HTTC.ID = HTTL.IDTicket AND HTTC.Id_Explotacion = HTTL.Id_Explotacion AND 
	HTTC.IDTerminal = HTTL.IDTerminal INNER JOIN
	TPV_Cierres AS TC ON HTTC.IdCierre = TC.Id AND HTTC.Id_Explotacion = TC.Id_Explotacion AND HTTC.IDTerminal = TC.IdTerminal INNER JOIN
	HIST_TPV_Tickets_Cobros AS HTTCo ON HTTC.Id_Explotacion = HTTCo.Id_Explotacion AND HTTC.ID = HTTCo.IDTicket AND 
	HTTC.IDTerminal = HTTCo.IDTerminal INNER JOIN
	ControlesCentralPBC.dbo.[F&B_TiposPago] AS FBTP ON HTTCo.IDTipoPago = FBTP.Id INNER JOIN
	ControlesCentralPBC.dbo.[F&B_ArticulosVenta] AS FBAV ON FBAV.Id = HTTL.IDArticulo INNER JOIN
	ControlesCentralPBC.dbo.ZVTipoFB AS ZTFB ON ZTFB.FB = FBAV.FAB INNER JOIN
	ControlesCentralPBC.dbo.[F&B_Explotaciones] AS FBE ON HTTC.Id_Explotacion = FBE.Id INNER JOIN
	ControlesCentralPBC.dbo.[F&B_Terminales] AS FBT ON FBT.Id = HTTC.IDTerminal LEFT OUTER JOIN
	ControlesCentralPBC.dbo.[F&B_Turnos] AS FBTu ON HTTC.IdTurno = FBTu.Id INNER JOIN
	dbo.SplitIDs(@TerminalId) AS TID ON FBT.Id = TID.vID
	WHERE (TC.FechaCierre BETWEEN @StartDate AND @EndDate)
	GROUP BY TC.FechaCierre, FBE.Descripcion, FBT.Descripcion, ZTFB.DescripcionFB, FBTu.Descripcion, TC.Id, FBTu.Desde, FBTu.Hasta, FBTP.Descripcion
	union all
	select FechaCierre, Descripcion, PuntoDeVenta, DescripcionFB, 
	Totallinea, Turno, Idcierre, Desde, Hasta, TiposPago
	from #TempTbl
END

I am running a stored Proc within a stored proc and all is fine with SQL 2000.

When I try to run the same on a SQL 2005 server I get

Msg 457, Level 16, State 1, Procedure XLRSP_DailyProductionByShift, Line 18
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

TIA

-Mo

 

[arrowhouse]

How was your 2005 database created?

It looks like there is some difference in collation either between your ControlesCentralPBC database and the one you are altering the procedure on, or possibly between the one you are altering the procedure on and the tempdb.

I would imagine on your 2000 box they are all the same collation.

 

[MisterMo]

all databases sits on the same server and they've been converted to 2005,

as a quick fix I now insert the second query into the temp table and all works fine.

I just don't understand why is doing this.

-Mo

 

[arrowhouse]

I think it is because the collation of your server is different to that of your databases. The temp tables are created with the colloation of your tempdb which is likely to be the default collation of the server. If your other databases have come from backups & been converted to 2005 it is possible that the server they originated from had a different default collation, or they were themselves created with a different collation.

Look at your 2005 server properties. What is the server collation? Look at the properties of the two databases involved what is their collation. Finally look at the properties of the tempdb - what is its collation. I think you will find there is a difference somewhere in there.

If you have been defining column level collations you may even have to go to looking at that level to find the missmatch.

By moving your second query into a temp table when the union happens all the varchar columns are in the same collation & you don't get the error.

 

[mrdenny]

I would have to agree with arrowhouse here.

Check the collation on tempdb and your database to see if they are the same or different.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com