May 21, 2005 at 3:10 pm
Given: an existing website with a MSSQLServer7 backend; a test instance of MSSQLServer 2000 database (testing for possible migration). I took a backup from the SQLServer 7 db and "restored" to the SQLServer 2000 instance. The "restore" completed without issues - database is in the 2000 instance, data are there, etc.
Problem: One of my SELECT statements includes this as part sql statement: "WHERE (registrations.status='submitted')". SQLServer 2000 claims it can't do the select, saying "Syntax error converting the varchar value 'submitted' to a
column of data type int" Now, I've looked at the database table
'registrations' in the 2000 instance and the column 'status' is a varchar column.... so I don't know
why SQL Server 2000 thinks it's an int column.... if I switch the code to look back at the production db (SQL Server 7) it executes fine. So, apparently SQL Server 2000 is confused about something. Any hints???
Thanks,
Scott
May 22, 2005 at 9:04 am
Is the registrations table the only one throwing up this error ? Have you queried the other tables with varchar columns with no problem ?
**ASCII stupid question, get a stupid ANSI !!!**
May 23, 2005 at 10:02 am
Scott - I came across this while I was reading an article on dynamic sql - this may be completely irrelevant but it'd be interesting to see what happens if you change the column name from 'status' to something else...
"If you permit yourself to send bare SQL statements from the application, you face a much greater problem. You need to search a much larger amount of code, and if the column has a common name like 'status' you are basically lost. And sysdepends becomes completely irrelevant."
**ASCII stupid question, get a stupid ANSI !!!**
May 23, 2005 at 10:35 pm
well.... I don't know what the deal was exactly except that it appears to have been an incomplete or corrupted restore from the production db to the "migration" db.... tonight I did another "restore" and the thing started working... aren't migrations fun?!
May 24, 2005 at 5:12 am
Glad that got sorted out...
Migrations CAN BE fun...when everything works...once in a bluemoon ?! 😉
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply