CODEWITHSUNDEEP
sql-serversqlncli
Lys
Lys

Reputation: 631

SQLNCLI11 provider has the error of The fractional part of the provided time value overflows

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,

Upvotes: 2

Views: 3095

Answers (1)

Souza
Souza

Reputation: 110

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")

Upvotes: 0

Related Questions