title: SQL: Specified cast is not valid
body: <p><strong>Note: I am looking for why this is happening and how to fix it, I am not looking for a workaround. This appears to be a server(SQL Server or Connection string) issue.</strong></p>
  
  <p>I have a program that is connected to a sql 2008 database (Database A) and I have inline sql that runs that has ints and strings returned and it works fine. But I have been asked to switch to another 2008 database (Database B) and now everything is coming back as a string and I am getting a specified cast is not valid from C# where when I am connected to the sql 2008 (Database A) it does not say this. This is a inline sql statement so the sql statement is not changing and the table schema of the database is the same. Its doing this on int primary keys Anyone have any ideas?</p>
  
  <p>I originally thought the was a 2000 to 2008 issue but I now have the some problem on 2008 as well. Both databases are on the same instance of sql server these are the connection strings</p>
  
  <p>Connection Strings</p>
  
  <pre><code>  Server=Server01\instance;Database=Fraud_Micah; Trusted_Connection=yes &lt;- Server 2008 (this one does not)
    Server=Server02\instance;Database=Fraud; Trusted_Connection=yes &lt;- Server 2008 (this one works)
  </code></pre>
  
  <p>Both databases are at DB compatibility_level of 100 </p>
  
  <p>The select Statement</p>
  
  <pre><code>select *, delimeter, file_filetype.LocalPath, ArchiveDir, EmailList
  from file_importtable 
  join file_filetype on file_importtable.FileTypeID = file_filetype.ID
  where importsuccessdate is null and transferdate is not null
  and remotediscoverdate is not null 
  and OriginalFileName in ('Test987.xml.pgp')
  </code></pre>
  
  <p><code>fileTypeID</code> is where its breaking -> <code>InvalidCastException: Specified cast is not valid.</code></p>
  
  <p>C# Code (Note reader is type SQLDataReader)   </p>
  
  <pre><code>if (!(reader.IsDBNull(reader.GetOrdinal("FileTypeID"))))
  {
      file.FileTypeID = reader.GetInt32(reader.GetOrdinal("FileTypeID"));
  }
  </code></pre>
  
  <p>Here is the column definition: <code>[FileTypeID] [int] NULL</code>, there is no null values in the table.</p>
  
  <p>I don't think the C# code comes from this, its a int? <code>public int? FileTypeID { get; set; }</code></p>
  
  <p>In debug mode: <code>reader["FileTypeID"]</code> -> "1" it is in fact a string but why when I connect to a 2008 database would it return a 1 instaed of a "1"</p>
  
  <p>2008 Table A Def</p>
  
  <pre><code>[ProcessSuccessDate] [datetime] NULL,
  [ProcessSuccessUser] [datetime] NULL,
  [FileTypeID] [int] NULL,
  [HoldDate] [datetime] NULL,
  </code></pre>
  
  <p>2008 Table B Def</p>
  
  <pre><code>ProcessSuccessDate] [datetime] NULL,
  [ProcessSuccessUser] [datetime] NULL,
  [FileTypeID] [int] NULL,
  [HoldDate] [datetime] NULL,
  </code></pre>
  
  <p><code>file.FileTypeID = (int)reader["FileTypeID"];</code> yields the same result.</p>
  
  <p>Doing a  </p>
  
  <pre><code>     file.FileTypeID (int)reader.GetInt32(reader.GetOrdinal("FileTypeID"));
  </code></pre>
  
  <p>does work but I don't want to do that for every column that already should be coming back as a int also writing sql like this </p>
  
  <pre><code>     select Convert(int, FileTypeID) as FileTypeId, delimeter, file_filetype.LocalPath, ArchiveDir, EmailList
  </code></pre>
  
  <p>can get around the issue as well, however I want to know why I have to do this if I already set the type as a int in the table. I might as well put all the types as strings in the table. At this point I am not looking for a workaround I want to understand why its not working like it should be.</p>