User-defined data type not being used in alter table

• Tom Wilson

  SSC Veteran

  Points: 248

  More actions

  March 10, 2007 at 4:38 pm

  #177117

  I am using the statement

  alter table AdvIndex add IndexID dtID null

  where dtID is defined as int.

  If I execute this from SQL Mgmt Studio, it works just fine - adds the table, sp_help shows it with type dtID.

  But if I run it through our corporate batch installer (OLEDB connnectino, reads SQL files, applies batch on each go, handles errors, isn't smart enough to do type replacement) the column is added as int.  This sounds like something is set to use the underlying data type instead of the user-defined data type; but I don't know of such as setting.  Also, when run through the same program for a create table, it is created with the correct dtID type.  Any idea what's causing the type translation?

• Tom Wilson

  SSC Veteran

  Points: 248

  More actions

  March 10, 2007 at 5:24 pm

  #694253

  Doh!  Found another place in my own upgrade script that was altering it back to an int.  Works every time - make a post and then find your mistake.