Sqlbulkcopy utilizando ASP funciona en iis6 pero falla en iis7

iis-6 iis-7 records sqlbulkcopy

Pregunta

Puedo hacer que sqlbulkcopy funcione bien en iis6 (servidor de prueba) e iis7 (servidor en vivo) pero al cargar los datos en iis7, las filas se cortan. Solo importa ~ 190,000 de 250000 en iis7. En iis 6, utilizando el mismo código / página, carga todos los 250000 registros sin problemas. También ambos servidores están actualizando la misma base de datos SQL.

He buscado por todas partes una solución. Cualquier ayuda es muy apreciada.

    Sub UpdateData()

    Dim sSQLTable As String = "Table1"
    Dim sExcelFileName As String = savePath
    Dim sWorkbook() As String = GetExcelSheetNames(sExcelFileName)

    'Create our connection strings
    Dim sExcelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sExcelFileName & ";Extended Properties=""Excel 12.0;HDR=YES;"""

    'Execute a query to erase any previous data from our destination table
    Dim sClearSQL = "DELETE FROM " & sSQLTable
    Dim SqlConn As SqlConnection = New SqlConnection(ConnString)
    Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn)
    SqlConn.Open()
    SqlCmd.ExecuteNonQuery()
    SqlConn.Close()

    'Series of commands to bulk copy data from the excel file into our SQL table
    Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
    OleDbConn.Open()

    Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM [" & sWorkbook(0) & "]"), OleDbConn)
    Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
    Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(ConnString, SqlBulkCopyOptions.UseInternalTransaction)
    bulkCopy.BulkCopyTimeout = 2000
    bulkCopy.DestinationTableName = sSQLTable
    'DEMO bulkCopy.ColumnMappings.Add("Excel", "SQL")
    bulkCopy.ColumnMappings.Add("Material", "Material")
    bulkCopy.ColumnMappings.Add("Plnt", "Plant")
    bulkCopy.ColumnMappings.Add("SLoc", "SLoc")
    bulkCopy.ColumnMappings.Add("S", "S")
    bulkCopy.ColumnMappings.Add("Batch", "Batch")
    bulkCopy.ColumnMappings.Add("Special Stock Number", "SpecialStockNumber")
    bulkCopy.ColumnMappings.Add("Material Description", "MatDesc")
    bulkCopy.ColumnMappings.Add("Typ", "Type")
    bulkCopy.ColumnMappings.Add("StorageBin", "StorageBin")
    bulkCopy.ColumnMappings.Add("Available stock", "AvailStock")
    bulkCopy.ColumnMappings.Add("BUn", "BUn")
    'bulkCopy.ColumnMappings.Add("GR Date", "GRDate")
    bulkCopy.WriteToServer(dr)
    OleDbConn.Close()
End Sub

Respuesta popular

Gracias Andy por la sugerencia de ExcelDataReader!

Tengo que trabajar con ExcelDataReader . Parece que fue un problema de memoria con OleDB. Publicar mi código en caso de que ayude a otros en el futuro.

    Sub updateData()
    Dim sSQLTable As String = "Table1"

    'Execute a query to erase any previous data from our destination table
    Dim sClearSQL = "DELETE FROM " & sSQLTable
    Dim SqlConn As SqlConnection = New SqlConnection(ConnString)
    Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn)
    SqlConn.Open()
    SqlCmd.ExecuteNonQuery()
    SqlConn.Close()


    Dim stream As FileStream = File.Open(savePath, FileMode.Open, FileAccess.Read)

    '1. Reading from a binary Excel file ('97-2003 format; *.xls)
    'Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateBinaryReader(stream)

    '2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
    Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream)

    '3. DataSet - The result of each spreadsheet will be created in the result.Tables
    'Dim ds As DataSet = excelReader.AsDataSet()

     '4. DataSet - Create column names from first row
    excelReader.IsFirstRowAsColumnNames = True
    Dim ds As DataSet = excelReader.AsDataSet()
    Dim sourceData As New DataTable()
    sourceData = ds.Tables(0)

    ''5. Data Reader methods
    'Using destinationConnection As New SqlConnection(ConnString)
    ' open the connection

    'destinationConnection.Open()
    'Using bulkCopy As New SqlBulkCopy(destinationConnection.ConnectionString)

    ' column mappings
    Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(ConnString, SqlBulkCopyOptions.UseInternalTransaction)
    bulkCopy.BulkCopyTimeout = 2000
    bulkCopy.DestinationTableName = sSQLTable

    'DEMO bulkCopy.ColumnMappings.Add("Excel", "SQL")
    bulkCopy.ColumnMappings.Add("Material", "Material")
    bulkCopy.ColumnMappings.Add("Plnt", "Plant")
    bulkCopy.ColumnMappings.Add("SLoc", "SLoc")
    bulkCopy.ColumnMappings.Add("S", "S")
    bulkCopy.ColumnMappings.Add("Batch", "Batch")
    bulkCopy.ColumnMappings.Add("Special Stock Number", "SpecialStockNumber")
    bulkCopy.ColumnMappings.Add("Material Description", "MatDesc")
    bulkCopy.ColumnMappings.Add("Typ", "Type")
    bulkCopy.ColumnMappings.Add("StorageBin", "StorageBin")
    bulkCopy.ColumnMappings.Add("Available stock", "AvailStock")
    bulkCopy.ColumnMappings.Add("BUn", "BUn")
    'bulkCopy.ColumnMappings.Add("GR Date", "GRDate")

    'bulkCopy.DestinationTableName = sSQLTable
    bulkCopy.WriteToServer(sourceData)

    '6. Free resources (IExcelDataReader is IDisposable)
    excelReader.Close()
End Sub


Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué