Changed datatype in table but still thinks it's the old 1

[bigracefan]

I used Enterprise Manager to change a datatype from smallDateTime to nVarchar(100). The dates that where in that table are still there and I can change the dates with no problems. When I try to put a string in it, it gives me a conversion error. Telling me that it can't change the string to smalldatetime. I've even rebooted the server, with no change. I then changed the name of the field, and now I can't even change dates. It's telling me that a table or view is accessing the table. What can I do in this case? I don't see the view, if there is one. We have over 200 in that database. Thanks

 

[SQLDenis]

sp_depends 'TableNameHere' will list all the objects that use this table (not foolproof)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

How big a table? And did you refresh in Enterprise Manager?
What is the exact error message you are getting?

Incidentally it is not recommended to make a change like this through Enterprise Manager as it what is does is completely recreate the table and then populate it from the old table and then drop the old table and rename the new one. This is a very time-consuming process. If you have a lot of records, perhaps it wasn't finished yet or something happened in the middle to corrupt the table. Alter table command in QA is a much better choice usually.

If it has gotten corrupted, do you have a good backup? From before the first change?

Changing from a datetime datatype to a string datatype is not normally a good practice. Are you sure that no one is doing any datemath on that field?


"NOTHING is more important in a database than integrity." ESquared

 

[bigracefan]

I think it was because this table had two triggers.

 

[SQLSister]

Ah yes, triggers expecting one datatype might not be happy with a differnt one.

Just out of curiously why were you changing it from a datetime field to a string type of field?

"NOTHING is more important in a database than integrity." ESquared

 

[bigracefan]

The change was mandated. I wanted to add a field but we'll see what the repercussions are next week.