title: Error converting data type varchar
body: <p>I currently have a table with a column as <code>varchar</code>. This column can hold numbers or text. During certain queries I treat it as a <code>bigint</code> column (I do a join between it and a column in another table that is <code>bigint</code>)</p>
  
  <p>As long as there were only numbers in this field had no trouble but the minute even one row had text and not numbers in this field I got a "Error converting data type <code>varchar</code> to <code>bigint</code>." error even if in the WHERE part I made sure none of the text fields came up.</p>
  
  <p>To solve this I created a view as follows:</p>
  
  <pre><code>SELECT     TOP (100) PERCENT ID, CAST(MyCol AS bigint) AS MyCol
  FROM         MyTable
  WHERE     (isnumeric(MyCol) = 1)
  </code></pre>
  
  <p>But even though the view shows only the rows with numeric values and casts Mycol to bigint I still get a <em>Error converting data type varchar to bigint</em> when running the following query: </p>
  
  <pre><code>SELECT * FROM MyView where mycol=1
  </code></pre>
  
  <p>When doing queries against the view it shouldn't know what is going on behind it! it should simply see two bigint fields! (<a href="http://www.getprice.com.au/view.gif" rel="noreferrer">see attached image</a>, even mssql management studio shows the view fields as being bigint)</p>