title: Call to stored procedure fails from Web Service
body: <p>I have a weird issue where I have a stored procedure that is inserting data into a database, it was working fine when we had the database in SQL Server 2005, but we have upgraded to SQL Server 2008 R2 and now it fails after two successful entries. </p>
  
  <p>The error being returned from the SQL exception is </p>
  
  <blockquote>
    <p>string or binary data would be truncated<br>
    the statement has been terminated</p>
  </blockquote>
  
  <p>Has anyone come across this sort of issue before ? As I say this has only started since we migrated to SQL Server 2008.</p>
  
  <p>I should also say, that I know what the truncation error means, but this is the issue, I have checked the values and lengths being sent to the database and none of them exceed the field sizes that are being inserted. Also I can run this multiple times from Management Studio and it never fails. So I'm thinking its something to do with the connection or sql command object maybe ??</p>
  
  <p>Code for the connection to the database is below</p>
  
  <pre><code> Public Function AddDataToDatabase(ByVal ModuleName As String, ByVal ModuleKey As Integer, ByVal ShapeDescription As String, ByVal ShapeNotes As String, ByVal ShapeType As String, ByVal ShapeStatus As String, ByVal FeatureType As String) As AttributeEditingResult Implements IDataService.AddDataToDatabase
  
      Dim sqlConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MDC_Reference").ConnectionString)
  
      Dim sqlCmd As SqlClient.SqlCommand = New SqlCommand
      sqlCmd.CommandText = ConfigurationManager.AppSettings.Get("sp_Insert")
      sqlCmd.CommandType = CommandType.StoredProcedure
      sqlCmd.Connection = sqlConn
  
      Dim myReader As SqlClient.SqlDataReader
      Dim TableKey As Integer = 0
  
      Dim Result As New AttributeEditingResult With {
          .Success = False, _
          .TableKey = TableKey}
  
      sqlCmd.Parameters.Clear()
  
      sqlCmd.Parameters.Add(New SqlParameter("@ModuleName", SqlDbType.VarChar, 50))
      sqlCmd.Parameters.Add(New SqlParameter("@ModuleKey", SqlDbType.Int))
      sqlCmd.Parameters.Add(New SqlParameter("@ShapeDescription", SqlDbType.VarChar, 255))
      sqlCmd.Parameters.Add(New SqlParameter("@ShapeNotes", SqlDbType.VarChar, 1024))
      sqlCmd.Parameters.Add(New SqlParameter("@ShapeType", SqlDbType.VarChar, 10))
      sqlCmd.Parameters.Add(New SqlParameter("@ShapeStatus", SqlDbType.VarChar, 50))
      sqlCmd.Parameters.Add(New SqlParameter("@FeatureType", SqlDbType.VarChar, 50))
  
  
      sqlCmd.Parameters("@ModuleName").Value = ModuleName
      sqlCmd.Parameters("@ModuleKey").Value = ModuleKey
      sqlCmd.Parameters("@ShapeDescription").Value = ShapeDescription
      sqlCmd.Parameters("@ShapeNotes").Value = ShapeNotes
      sqlCmd.Parameters("@ShapeType").Value = ShapeType
      sqlCmd.Parameters("@ShapeStatus").Value = ShapeStatus
  
  
      If FeatureType = "NULL" Then
          sqlCmd.Parameters("@FeatureType").Value = DBNull.Value
      Else
          sqlCmd.Parameters("@FeatureType").Value = FeatureType
      End If
  
      Try
          sqlConn.Open()
          myReader = sqlCmd.ExecuteReader()
          While myReader.Read
              Result.TableKey = CInt(myReader(0))
          End While
          sqlConn.Close()
          sqlCmd.Dispose()
          Result.Success = True
      Catch ex As SqlException
          sqlConn.Close()
          sqlCmd.Dispose()
          Result.Success = False
          Result.ErrorMessage = ex.Message &amp; vbNewLine &amp; ex.StackTrace
      End Try
  
     Return Result
  
  End Function
  </code></pre>