Problem with stored procedure using temp table after upgrading from sql 2000 to 2008

I have a stored procedure that works fine in sql server 2000, but, after migrating to 2008 is now failing with an error 'conversion of the varchar value '263' overflowed an INT1 column.
Also, the sp uses a temp table to first load data and then extract from the temp table. In SSMS the intelisense is flagging the 2nd usage of the temp table and stating invalid object name (but, not sure that's related to the first issue.

The code is like:
Select field1, field2, etc
Into #Temp
From tblTest
Where field_date1 In (Select Max(field_date1) from tblTest)  <<<<<error msg report this line as failing
exec('select * from #Temp') <<<this test line executes fine
Insert Into tblTest1 ( fields listed here )
Select a.field1, a.field2, etc  <<<<intelisense not recognizing
From #Temp as a  <<<intelisense error, invalid object name

any ideas?
thanks
alan
avoorheisAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
you dont have to care about the error messages that the Intellisense is giving (it wont recognise the temp table you are going to create using the select into statement)

Now as far as the other error, can you provide the exact error message ...
0
 
avoorheisAuthor Commented:
thanks, I did finally solve it, one of fields was too small, had to change from tinyint to smallint. The error msg wasn't much help, it did give the value which was the problem, but, not the field name, which made it more difficult to find.

Is there a way to get more details about the error?

msg was:
Msg 244, Level 16, State 1, Procedure sp_SRExtractUpdate, Line 47
The conversion of the varchar value '263' overflowed an INT1 column. Use a larger integer column.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> The conversion of the varchar value '263' overflowed an INT1 column. Use a larger integer column.

You can check for the columns which holds the value 263 to track it down..
Error says that you have tinyint datatype column..
INT1 - tinyint
INT2 - smallint
INT - Int
Bigint - Bigint

Hence you need to use smallint datatype column to get this resolved..
0
All Courses

From novice to tech pro — start learning today.