SqlDataAdapter - Internal connection fatal error.

Are you filling an existing datatable or a new dataset?

If it's an existing object it could be a type mismatch.

What if you do a full cast on the numeric in your stored procedure
eg:

select ...., cast (hours_worked as numeric(18,3)) hours_worked from table

Select allOpen in new window

Hi Kyle - I tried your suggestion and I still get the same error.   To answer your second question, this is a new object also.

Here is the stack trace if that helps.

  at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreResults(Boolean& moreResults)
   at System.Data.SqlClient.SqlDataReader.TryNextResult(Boolean& more)
   at System.Data.SqlClient.SqlDataReader.NextResult()
   at System.Data.ProviderBase.DataReaderContainer.NextResult()
   at System.Data.Common.DataAdapter.FillNextResult(DataReaderContainer dataReader)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at PPG.AC.NA.SS.DBAccess.SQLDatabase.ExecuteStoredProcedureWithReturn(String SQLCommand) in c:\projects\ppg\PAC_Store_Systems\New Store System\PPG.AC.NA.SS.DBAccess\SQLDatabase.cs:line 301

Can you post your code for the call?  (you can hide actual connection strings)

I don't think the issue is not related to the code making the call.  I can run the same code for another week and have no issues.

public DataSet ExecuteStoredProcedureWithReturn(String SQLCommand)
        {
            // Make sure the command exists
            if (SQLCommand == null)
            {
                throw new Exception("Can't execute stored procedure. Command object was never instantiated.");
            }

            // Make sure the command has been set
            if (SQLCommand == "")
            {
                throw new Exception("Can't execute stored procedure. Stored procedure name is missing.");
            }

            NamedSQLConnection sqlConnection = _connectionPool.FetchConnection();

            if (sqlConnection == null || sqlConnection.Connection.State != ConnectionState.Open)
                throw new Exception("DB connection not open");

            try
            {
                lock (_dataReaderLock)
                {
                    SqlCommand command = new SqlCommand(SQLCommand, sqlConnection.Connection);

                    // Now actually execute the stored procedure
                    command.CommandText = SQLCommand;

                    //command.Connection = sqlConn;

                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Clear();

                    SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                    dataAdapter.MissingMappingAction = MissingMappingAction.Passthrough;

                    dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                    DataSet dataSet = new DataSet();

                    // If the stored procedure requires input/output parameters
                    if (!(_parameterList == null))
                    {
                        for (int counter = 0; counter < _parameterList.Length; counter++)
                        {
                            command.Parameters.Add(_parameterList[counter]);
                        }

                        dataAdapter.Fill(dataSet);

                        _logger.Info("Succefully executed script '" + SQLCommand + "'");
                    }

                    //Let's dispose of the command
                    command.Dispose();

                    dataAdapter.Dispose();

                    return dataSet;
                }
            }
            catch (Exception er)
            {
                _logger.Debug("Error while attempting to execute '" + SQLCommand + "'");
                throw er;
            }
            finally
            {
                _connectionPool.ReturnConnection(sqlConnection);
            }

            #region Implementation Example

            //List<SqlParameter> sqlParamList = new List<SqlParameter>();

            //SqlParameter[] parameterArray = new SqlParameter[2];

            //parameterArray[0] = new SqlParameter("@MaxScheduleNum", newMaxSchedNum);
            //parameterArray[1] = new SqlParameter("@UserName", userName);

            //sqlParamList.Clear();

            //sqlParamList.AddRange(parameterArray);

            //m_DataBase.AddParameter(ref sqlParamList);

            //String SQLCommand = "SetMaxScheduleNum";

            //DataSet dataset = new DataSet();
            //dataset = m_DataBase.ExecuteStoredProcedureWithReturn(SQLCommand);

            #endregion
        }

Select allOpen in new window

Just a couple of notes:

no need to call this as you have a new command:
command.Parameters.Clear();


if you set these both to error
(EG:

 dataAdapter.MissingMappingAction = MissingMappingAction.Passthrough;
dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

//becomes:
    adapter.MissingMappingAction = MissingMappingAction.Error
    adapter.MissingSchemaAction = MissingSchemaAction.Error 

Select allOpen in new window

)
does it make a difference?


Doing more research on this a suggested solution was to turn the connection pooling off in the connection string.  ("pooling=false" for ADO and "OLE DB Services=-4;" for OLE DB)

I haven't experienced this issue before so trying different things.

Also, if you call a datareader and do it that way do you have the same results?  (You can do it with a few columns and throw in a datatable just to test).

This is a result from a stored procedure? If affirmative, what happens when you run the stored procedure for that week in particular and from SSMS directly?

doesn't define a root cause