In iis6, sqlbulkcopy with ASP works, but not in iis7.

iis-6 iis-7 records sqlbulkcopy

Question

On both iis6 (my testing server) and iis7 (my live server), I was able to get sqlbulkcopy to function properly; however, the rows get chopped off when I upload data in iis7. In iis7, only 190,000 out of 250 000 files are imported. With the identical code/page on IIS 6, all 250000 records load without any problems. The same SQL DB is being updated by both servers as well.

I've looked everywhere for a solution. I really appreciate any assistance.

    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
1
1
12/12/2011 9:34:20 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

I appreciate Andy's ExcelDataReader suggestion.

Zzz-9-Zzz made it work. It seems like OleDB has a memory problem. I'm sharing my code in case it might assist someone else in the future.

    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
2
12/13/2011 4:28:01 PM

Related Questions

Why is sqlbulkcopy behaving strangely in an asp.net website based on IIS?

Do you have any sample data?...It seems to be one of the cellphone numbers. I would isolate the precise column first, and then I would look at the original data. Additionally, the DataTable seems to contain just string values; try typing the dataset.
asp.net c# iis sqlbulkcopy
asked by Doobi

Attempting to duplicate a row with significant content in an XML column using SqlBulkCopy fails.

I'm attempting to move a record across two SQL Server tables. The structure of both tables is the identical, and one of the columns is of the xml type. About 2.5Mb worth of xml data is included in each row of the source table....See the attached map.zip file for the content of the xml column that I saved, or go to ...https://docs.google.com/leaf?id...
sql sqlbulkcopy sql-server xml
asked by stask

Using sqlbulkcopy in C# asp.net to write xml data to a SQL server

On my system, there are two XML files named customers.xml and customerdetails.xml. I'm trying to write this data straight to an asp.net-made sql database. My whole code is done in C#. CustomerID, CustomerN, CustomerLN, CustomerAdd, CustomerTelNo, and Comments are the columns found in the CustomerDetails table in the sql database....My update page i...
c# sqlbulkcopy sql-server xml
asked by Rose

SqlBulkCopy Failed to obtain column collation information for the destination table

When I use a DataTable object and SqlBulkCopy to write rows to a table, I see this problem....Let me first state that I am aware of the Microsoft KB article below before moving on. Every article that has been written on this inaccuracy has cited that one. However, I don't have any dots in the name of my table or schema. The table name should be suf...
collation sqlbulkcopy
asked by Mike K.

Excel SqlBulkCopy works differently on local pc, and on application server?

My program truncates a database table and inserts rows from an excel file into it....I used an excel file with 2000 rows, and the upload code below entirely inserted all of the rows into the database on both my local computer (my development environment) and the server, so I assumed I had successfully completed the operation....However, after addin...
c# excel oledbconnection sqlbulkcopy
asked by HOY

Stop SqlBulkCopy from rolling back transaction when one insert fails

Use SqlBulkCopy not....Directly....Inserting first into a temporary table (which also avoids SqlBulkCopy's terrible locking behavior) and then MERGING with the correct rules into the final table are both acceptable techniques....This is how I do all of my bulk updates: quick and easy.
sqlbulkcopy sql-server
asked by TomTom

SqlBulkCopy into table that Default column values fails when source DataTable row has DBNull.Value

You shouldn't even be transmitting the field for part 1, "field that is NOT NULL with a DEFAULT," in the first place. You shouldn't map it. It is not necessary to modify that field to allow NULL values alone for this....In order to get the default value when handing in DbNull, part 2's "field that is NULL with a DEFAULT" requires that ...will... wo...
c# default-constraint null sqlbulkcopy sql-server
asked by Solomon Rutzky

Excel File upload in asp.net using SqlBulkCopy

Welcome to the interesting and enjoyable world of dealing with Excel's peculiarities and the reasons why using an Excel spreadsheet as a data source is not a good idea. Sadly, getting this one to operate properly will need a little more effort on your side....Each row must be examined, and type conversions must be made for each column as necessary....
asp.net c# excel sqlbulkcopy
asked by Corey

SQL Trigger Update to Uppercase doesn't work using SQLBulkCopy Insertion

I'm utilizing.NET...SQLBulkCopy... data into a database in bulk. When data is added into the table, I'm attempting to convert all given strings to uppercase. My trigger script is as follows:...ALTER TRIGGER [dbo].[EmployeeTable_UpperCase] ON [dbo].EmployeeTable AFTER INSERT AS BEGIN UPDATE EmployeeTable SET [CompanyCode] = UPPER...
sqlbulkcopy sql-server-2008-r2 triggers
asked by Koo SengSeng

SqlBulkCopy with ObjectReader - Failed to convert parameter value from a String to a Int32

I'm doing an import from an XML-based file using SqlBulkCopy (.NET) and ObjectReader (FastMember). The appropriate column mappings have been added....I sometimes see the message "Failed to convert parameter value from a String to an Int32."...I'd want to learn how to: 1. Track down the problematic table column. 2. Get the ObjectReader's "current"..
fastmember sqlbulkcopy
asked by Mr767267

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow