tt-615680 (4/12/2012)
Thank you for your reply!This is part of the stored procedure:
........................
...................
CREATE TABLE Data
(
Total_TY decimal(10,2), --this is where is gives the error
Total_LY decimal(10,2),
variance decimal(10,2)
)
INSERT INTO #Data
SELECT
CASE WHEN ISNULL(ISNULL(Total_TY,0)/ISNULL(Total_LY,1), 0) = ISNULL(Total_TY, 0) THEN 100
WHEN ISNULL(ISNULL(Total_LY,0)/ISNULL(Total_TY,1), 0) = ISNULL(Total_LY, 0) THEN -100
ELSE ISNULL((ISNULL(Total_TY,0)/ISNULL(Total_LY,1)), 0) END AS var
FROM#TYear c
LEFTJOIN #LYear pON c.countryCode = p.countryCode
What I'm confused about is that when I run the same stored procedure on a different Server it runs fine and the setting for the Collation are the same.
Thank you!
What is the collation of your SQL Server instance, Tempdb objects will use the server collation and that's undoubtedly where your issue is. Post the results from the following run against your SQL instance
select serverproperty('collation')
select databasepropertyex('tempdb', 'collation')
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉