August 30, 2010 at 4:48 pm
I've created the following table variable and populated it from a CTE named CPERecovered:
DECLARE @EquipmentRecovery_Stage TABLE (
[JobID] [bigint] NOT NULL,
[AccountNo] [varchar](50) NULL,
[SerialNumber] [varchar](25) NOT NULL,
[EquipmentType] [varchar](10) NOT NULL,
[EquipmentTypeDescription] [varchar](30) NULL
)
--...
/********************
CTE Manipulation goes here
*********************/
--...
INSERT INTO @EquipmentRecovery_Stage(
JobID
, AccountNo
, SerialNumber
, EquipmentType
, EquipmentTypeDescription
)
SELECT
JobID
, AccountNo
, SerialNumber
, EquipmentType
, EquipmentTypeDescription
FROM
CPERecovered
ORDER BY
AccountNo
,SerialNumber
,EquipmentType
When I do this, I get the following error:
The conversion of the varchar value '8200600593306666' overflowed an int column. Maximum integer value exceeded.
The statement has been terminated.
I know from my data that the value in the error message is an AccountNo so the problem, I'm guessing, is with the AccountNo column. The error SEEMS to indicate that the destination column's data type is an INT and the source is a VARCHAR, and the VARCHAR value is too large to fit in an INT. However, the data type of the AccountNo column in the table variable is clearly NOT an INT, it is a VARCHAR(50).
Ultimately, the SOLUTION for this was simply to change from a table variable, to a temp table:
CREATE TABLE #EquipmentRecovery_Stage (
[JobID] [bigint] NOT NULL,
[AccountNo] [varchar](50) NULL,
[SerialNumber] [varchar](25) NOT NULL,
[EquipmentType] [varchar](10) NOT NULL,
[EquipmentTypeDescription] [varchar](30) NULL
)
--...
/********************
CTE Manipulation goes here
*********************/
--...
INSERT INTO #EquipmentRecovery_Stage(
JobID
, AccountNo
, SerialNumber
, EquipmentType
, EquipmentTypeDescription
)
SELECT
JobID
, AccountNo
, SerialNumber
, EquipmentType
, EquipmentTypeDescription
FROM
CPERecovered
ORDER BY
AccountNo
,SerialNumber
,EquipmentType
...and now it works fine. But can anybody explain this?
I considered that the problem could be with the population of the CPERecovered CTE in the omitted section, but I ran through the omitted section line-by-line and couldn't duplicate the error as-is. The only time I got an error was with the final INSERT shown in the above code snippets.
One other note, when I look at the contents of the temp table, I don't even SEE that AccountNo value....which again makes me think the problem is actually with the omitted CTE section. But again, that section runs fine in isolation from the above INSERT statements....AND....the problem goes away by simply changing from a table variable to a temp table.
FWIW: I'm using SQL Server Management Studio 2008 (on 32-bit XP) against SQL Server 2005 Standard (on 32-bit 2K3). All recent updates applied.
Edit: Changed text "CPE Manipulation goes here" to "CTE Manipulation goes here."
August 30, 2010 at 4:59 pm
I don't see anything here either. Can you post the CTE?
Edit: There might be an implicit conversion going on somewhere.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 30, 2010 at 5:10 pm
I'm not sure if I can. Let me see if I can duplicate this problem with a simple example that doesn't disclose anything my company might get antsy about (yeah, I'm paranoid...I really need my job). Maybe I should have done that first...sorry.
While I work on that, let's suppose that there is some sort of implicit conversion going on in the omitted CTE section. Can you think of anything that would explain why the problem goes away by simply changing the INSERT destination from a table variable to a temp table?
August 30, 2010 at 5:40 pm
Uncle Moki (8/30/2010)
I'm not sure if I can. Let me see if I can duplicate this problem with a simple example that doesn't disclose anything my company might get antsy about (yeah, I'm paranoid...I really need my job). Maybe I should have done that first...sorry.While I work on that, let's suppose that there is some sort of implicit conversion going on in the omitted CTE section. Can you think of anything that would explain why the problem goes away by simply changing the INSERT destination from a table variable to a temp table?
No I can't.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 30, 2010 at 5:54 pm
I would have to agree with wayne. Maybe in your CTE you are pulling information from an Int column and then pulling info from a bigint column. The bigint column will overflow the int column in your cte.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 31, 2010 at 10:22 am
Jason - I hear you. But even if there was some implicit type conversion problems with the omitted CTE section, I don't see how it would explain why the problem goes away simply by changing the INSERT destination from a table variable to a temp table. I mean, even if there was a place where I have two CTE's, CTEOne creating an AccountNo column of type INT, CTETwo creating an AccountNo column of type VARCHAR(50) (or BIGINT), and I try to insert CTETwo.AccountNo into CETOne.AccountNo, then the error would still exists even after I change the INSERT destination from a table variable to a temp table, particularly since both types of table (variable or temp) explicitly define the AccountNo column as VARCHAR(50), which works for BIGINT, INT, or VARCHAR(50).
I'm trying to figure out how best to duplicate this error without having to post a bunch of irrelevant TSQL. Once I do (assuming I do), I'll post the TSQL.
August 31, 2010 at 11:25 am
I appreciate your trying to get something that will duplicate the issue. However, as you've mentioned, it isn't making sense why the table variable won't work with the temp table does. At this point, I'd just want to see the CTE code - can you post just that?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 31, 2010 at 4:04 pm
:unsure:I'm not trying to be difficult, and I know not seeing the CTE setion would give a more complete picture, but I just can't post it....a) it's very long and would just cause confusion and b) I'm not sure my company would appreciate it (the column names are somewhat revealing). I promise to try to assemble a more sanitary sample that duplicates the problem...if I can create one successfully.
HOWEVER...I think I've pinpointed cause of the error. One of the CTEs has a join that mixes datatypes like...
ON t1.ColumnA BETWEEN t2.RangeStart AND t2.RangeEnd
...ColumnA is a VARCHAR(50) and sometimes contains a 16-digit AccountNo but t2.RangeStart and t2.RangeEnd are an INT. This seems to match the error description previously posted.
So now there are TWO solutions, either of which make the error go away:
1. CAST the t2.RangeStart and t2.RangeEnd values as BIGINT
2. Change the INSERT destination to a temp table.
But I still don't get solution #2....seems like the error should occur regardless of whether the destination is a table variable or a temp table.
Having identified the above, maybe it'll be easier to duplicate the error with a simple example. If/When I get one, I'll be sure to post it.
August 31, 2010 at 4:09 pm
I agree with Wayne. i believe the issue resides in the CTE.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 31, 2010 at 4:16 pm
Uncle Moki (8/31/2010)
:unsure:I'm not trying to be difficult, and I know not seeing the CTE setion would give a more complete picture, but I just can't post it....a) it's very long and would just cause confusion and b) I'm not sure my company would appreciate it (the column names are somewhat revealing). I promise to try to assemble a more sanitary sample that duplicates the problem...if I can create one successfully.HOWEVER...I think I've pinpointed cause of the error. One of the CTEs has a join that mixes datatypes like...
ON t1.ColumnA BETWEEN t2.RangeStart AND t2.RangeEnd...ColumnA is a VARCHAR(50) and sometimes contains a 16-digit AccountNo but t2.RangeStart and t2.RangeEnd are an INT. This seems to match the error description previously posted.
So now there are TWO solutions, either of which make the error go away:
1. CAST the t2.RangeStart and t2.RangeEnd values as BIGINT
2. Change the INSERT destination to a temp table.
But I still don't get solution #2....seems like the error should occur regardless of whether the destination is a table variable or a temp table.
Having identified the above, maybe it'll be easier to duplicate the error with a simple example. If/When I get one, I'll be sure to post it.
Thanks. That would be very helpful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 31, 2010 at 5:51 pm
Uncle Moki (8/31/2010)
:unsure:I'm not trying to be difficult, and I know not seeing the CTE setion would give a more complete picture, but I just can't post it....a) it's very long and would just cause confusion and b) I'm not sure my company would appreciate it (the column names are somewhat revealing). I promise to try to assemble a more sanitary sample that duplicates the problem...if I can create one successfully.HOWEVER...I think I've pinpointed cause of the error. One of the CTEs has a join that mixes datatypes like...
ON t1.ColumnA BETWEEN t2.RangeStart AND t2.RangeEnd...ColumnA is a VARCHAR(50) and sometimes contains a 16-digit AccountNo but t2.RangeStart and t2.RangeEnd are an INT. This seems to match the error description previously posted.
So now there are TWO solutions, either of which make the error go away:
1. CAST the t2.RangeStart and t2.RangeEnd values as BIGINT
2. Change the INSERT destination to a temp table.
But I still don't get solution #2....seems like the error should occur regardless of whether the destination is a table variable or a temp table.
Having identified the above, maybe it'll be easier to duplicate the error with a simple example. If/When I get one, I'll be sure to post it.
I thought that there was an implicit conversion going on. I would suggest option 1. However, you still have an implicit conversion going on, so you might want to convert to varchar(50). Furthermore, converting either column to anything will impede the ability to use an index on that column, and I believe that you were saying these are large tables?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 31, 2010 at 6:59 pm
WayneS (8/31/2010)I thought that there was an implicit conversion going on.
I hope it didn't seem like I was arguing that there wasn't an implicit conversion going on. The error clearly states there was one, I was just having trouble seeing it. The "oil and water" issue for me is understanding why the error (regardless of where it was) would go away when changing a seemingly unrelated line of code from using a table variable to a temp table. That makes no sense to me.
WayneS (8/31/2010)...you still have an implicit conversion going on, so you might want to convert to varchar(50).
I didn't want to use VARCHAR(50) because the goal is to compare a range of numbers not varchars (a different topic...characters vs numeric comparison). But to your point, I could explicitly CAST all three to BIGINT if there was a risk that the implicit conversion would be a problem.
WayneS (8/31/2010)Furthermore, converting either column to anything will impede the ability to use an index on that column, and I believe that you were saying these are large tables?
The tables are large, but the code in the omitted CTE section filters them down to managable sizes so the performance hit of using CAST is not a big deal. Plus those particular fields aren't indexed...and one is from another CTE so indexes aren't going to be a factor...etc...the point, not a big deal, but thanks for the tip...good to keep thinking about performance.
BTW: Not trying to sound snarky :crazy:, but this is part of the reason why I didn't want to post the CTE. i.e. Because I don't want to detract from the main question and start discussing the approach (good/bad approach is a separate issue). The primary question I can't seem to answer is why an error would occur with a table variable, but not a temp table with the same definition.
Maybe I should rephrase my question given the new info - Any idea why the type-conversion problem in the omitted CTE Section throws an error message when my final INSERT destination is a Table Variable, but not when the final INSERT destination is a Temp Table?
August 31, 2010 at 7:09 pm
Uncle Moki (8/31/2010)Maybe I should rephrase my question given the new info - Any idea why the type-conversion problem in the omitted CTE Section throws an error message when my final INSERT destination is a Table Variable, but not when the final INSERT destination is a Temp Table?
Until it can be identified what exactly is causing this issue, I don't think that anyone can definitively answer that.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply