Fetched Row Count Not Match With Source Table Row Count

[mathiasyeremiaaryadi]

Issue Description Hi, I have a Go service that act as a cron job to check all data from the database table once every week. The logic is very simple, the Go service fetches all rows from the source table and does some logic with the data there. However, there is some issue where I have 3371351 or ~ 3million rows and 400 columns from the source table and query only gave me 138912 or ~ 100k rows (stored in Go slice) which indicates several rows didn't fetched properly by the query. In other word, the fetched rows aren't complete, even though the query process is successfully executed.

Go Code Here my Go code that perform a simple SELECT statement:

func (repository Repository) GetAllData() ([]entity.ExampleStruct, error) {
    sql := fmt.Sprintf("SELECT * FROM [%s].[schema_data].[%s]", viper.GetString("SQL_SERVER_DBNAME"), viper.GetString("SQL_SERVER_TABLE"))

        rows, err := repository.sqlServerConnection.Query(sql)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    if rows.Err() != nil {
        return nil, rows.Err()
    }

    var exampleStructList []entity.ExampleStructList 
    for rows.Next() {
        var exampleStruct entity.ExampleStruct
        err := rows.Scan(// scan struct here)
        if err != nil {
            return nil, err
        }

        exampleStructList = append(exampleStructList , exampleStruct)
    }

    return exampleStructList, nil
}

Before, I have used the WITH (NOLOCK) clause to speed up the SELECT statement and resulting in unmatched row count with the row count on the source table. So I removed the WITH (NOLOCK) clasue because I thought it affects the row fetch behavior.

Expected Behaviour The SELECT statement should return the complete data that has same count of rows as the source table rows If source table has 3million rows of data, my SELECT statement should also fetch 3million data in slice

Specification Go version: 1.20.7 MS SQL Server version: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000

[shueybubbles]

thx for opening an issue! The implications of this issue are pretty bad, considering querying rows is the primary use of the driver. Have you tried reverting to any older versions of the driver, including the old denisenkom versions, to see if this bug is relatively new or old?

[mathiasyeremiaaryadi]

Hi, I haven't tried the older version, but I'll try it out. Which is the older version should I try? And something that I suspect is, there is a frequent restore activity on the table from my other team, is it affecting the rows that I fetched ?

[shueybubbles]

i'd assume ongoing restores would disrupt a multimillion row query, depending on what kinds of locks are in use. They'd probably be upset if your query grabbed a lock on the entire content of the table...

[mathiasyeremiaaryadi]

Somtimes my SELECT query failed with error like this:

"Error": {
  "Number": 927,
  "State": 6,
  "Class": 14,
  "Message": "Database 'xxxx' cannot be opened. It is in the middle of a restore.",
  "ServerName": "xxxx",
  "ProcName": "",
  "LineNo": 1,
  "All": [
   {
    "Number": 927,
    "State": 6,
    "Class": 14,
    "Message": "Database 'xxxx cannot be opened. It is in the middle of a restore.",
    "ServerName": "xxxx",
    "ProcName": "",
    "LineNo": 1,
    "All": null
 }

So I can tell my SELECT query was interrupted with the restore actitivy. I think it is make sense that the restore acitvity cause I can't retrieve a complete record from the table.