Issue inserting after upgrade to 2008

Question

Post reply

Issue inserting after upgrade to 2008

Simon D Richards

Ten Centuries

Points: 1048
More actions
December 6, 2010 at 7:42 am

#229848

Hi all, I have a problem on a newly upgraded (2005 to 2008) test machine. We have some legacy code (vb) that inserts into tables without defining the column names:

insert test_table

select 1, 2, 3

These inserts are now failing on incompatible data type as it looks like SQL 2008 applies some random order on the columns, if I change one of the procs to explicitly define the column names:

insert test_table col_1, col_2, col_3

select 1, 2, 3

then it works fine.

Unfortunately we have a lot of code like this, is this a roll back type of scenario or is there a database setting that will fix this?

Any advice appreciated!

Thanks

Simon

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

There's no random order applied. The order that the columns are defined in is the order that is used. This works fine on SQL Server 2008:

CREATE TABLE MyTable (col1 INT, col2 int, col3 int)



go

INSERT mytable SELECT 1, 2, 3

You have some other issue. Is there an identity or other column that is in there? Perhaps a property was changed when you upgraded?

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Simon D Richards Ten Centuries Points: 1048 More actions · Answer 2

Thanks for the reply, sorry I meant our instance not 2008 generally, wondering whether it was a bug or something.

weird thing is now it is doing exactly the opposite on the table, if I leave the column list off it inserts, when I leave it on it fails with : Operand type clash: date is incompatible with tinyint

just slightly baffled and this makes a good sounding board 🙂

Simon D Richards Ten Centuries Points: 1048 More actions · Answer 3

might be some form of corruption as now when I try and run the proc that does the insert I am getting:

Could not find database ID 4023. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.

and the database id changes everytime I run it. Our db IDs go up to 34 and that is it.