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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 & Architect, EE Solution GuideCommented:
>> 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.