2

I have the following connection string:

provider=SQLNCLI11;Server=[server];Database=[db];uid=[uid];pwd=[pwd]

and I have the following code:

                OleDbCommand oComm = new OleDbCommand();
                oComm.Connection = OleConnection;
                oComm.Transaction = m_oleTran;
                oComm.CommandText = sSQL;
                oComm.CommandTimeout = TimeOut;
                BuildParams(ref oComm, sCols, (object [])oVals);
                if (oComm.Connection.State == ConnectionState.Closed)
                    oComm.Connection.Open();
                m_RowsAffected = oComm.ExecuteNonQuery();
                if (m_oleTran == null)
                    oComm.Connection.Close();
                oComm.Dispose();



    private void BuildParams(ref OleDbCommand oComm, string [] sCols, object [] oVals)
    {
        for (int i = 0; i< sCols.Length; i++)
        {
            if (sCols.Length > 0)
                oComm.Parameters.AddWithValue(sCols[i], oVals[i]);
        }
    }

when I executed a simple update SQL statement, I got the following error

The fractional part of the provided time value overflows the scale of the corresponding SQL Server parameter or column. Increase bScale in DBPARAMBINDINFO or column scale to correct this error.    at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

Any ideas?

Thanks,

Lys
  • 591
  • 2
  • 9
  • 19
  • What is the source of values? There are 2 different datetimestamps DT_DBTIMESTAMP and DT_DBTIMESTAMP2, one of hem supports 3 digits in fractional part, the another 7 (https://learn.microsoft.com/en-us/sql/integration-services/data-flow/integration-services-data-types) From your source code i can't determine types and values for parameters – vitalygolub Nov 07 '17 at 10:40
  • I'm using question mark as the parameters placeholder instead of named ones. Does that matter? – Lys Nov 08 '17 at 02:16
  • Most probably, no. But if you use DateTime.Now as one of oVals it can matter – vitalygolub Nov 08 '17 at 08:30
  • oVals has some System.DateTime. It seems like I need conversion? – Lys Nov 09 '17 at 07:44
  • convert to odbc canonical string https://technet.microsoft.com/en-us/library/ms190234(v=sql.90).aspx or just round ticks, like here https://stackoverflow.com/questions/1004698/how-to-truncate-milliseconds-off-of-a-net-datetime – vitalygolub Nov 09 '17 at 12:53

1 Answers1

0

Solution used in DTSX Script Task with VB language.

For compliance reasons our customer asked us to change the SQL Server provider in a database version upgrade. And we started getting the same error when trying to save the dates. The date sent to the database was not on the correct scale. We could have changed the data type in the database, but we chose not to do so after we tested this very simple solution that worked. We just use a

.ToString("yyyy-MM-dd HH:mm:ss")

and send a string instead of a date with the correct size and it went through the provider and the database and saved without problems.

Provider=SQLNCLI11;

SOLUTION:

com.Parameters.AddWithValue("@COD_INTERFACE", SqlDbType.Int)
com.Parameters.AddWithValue("@COD_SEQUENCIAL", SqlDbType.Int)
com.Parameters.AddWithValue("@DTA_GERACAO", SqlDbType.DateTime)
com.Parameters.AddWithValue("@DTA_IMPORTACAO", SqlDbType.DateTime)

com.Parameters("@COD_INTERFACE").Value = CInt(Dts.Variables("User::intCodigoInterface").Value)
com.Parameters("@COD_SEQUENCIAL").Value = CInt(Dts.Variables("User::intSequencialArquivo").Value)
com.Parameters("@DTA_GERACAO").Value = CDate(Dts.Variables("User::dtaGeracaoArquivo").Value).ToString("yyyy-MM-dd HH:mm:ss")
com.Parameters("@DTA_IMPORTACAO").Value = Now.ToString("yyyy-MM-dd HH:mm:ss")
Souza
  • 110
  • 11