Update/Insert Issue with OpenRowset/BulkInsert
Hello,
We have a Windows 2003 Server with SqlServer 2000 Standart with sp4, We have the latest versions of MDAC 2.8 sp1 and JET 4.0 sp8. Client service is working on the server (VB.NET with 1.1.4322. framework). The service runs on the server and reads from files (tab delimited) and updates values to tables. The problem is, the service doesn't properly update "some" of the big files. It only updates 1-2 rows. The table that we update is a rather big table (about 12.000.000 rows).
We execute the following sp
SET @sp = @sp + 'SELECT TMP_GsmNo, TMP_Status FROM OPENROWSET('
SET @sp = @sp + '''MICROSOFT.JET.OLEDB.4.0'','
SET @sp = @sp + '''' + @pathTotal + ''','
SET @sp = @sp + '''SELECT * FROM [' + CONVERT(nvarchar, @gatewayMessageID) + '#txt] WHERE TMP_Status IN(3,5)'')'
INSERT #TEMP EXEC(@sp)
UPDATE Reports SET RPT_Status=TMP_Status FROM
(SELECT TMP_Status, TMP_GsmNo FROM #TEMP) TempTable
WHERERPT_MessageID=@messageID
AND RPT_GsmNo COLLATE database_default=TMP_GsmNo
We tried to find out, if the problem is because of Reports table and added the following code before update statement.
if @gatewayMessageID = 119044
INSERT TempTable EXEC(@sp)
It only inserts 2 rows to TempTable. We tried the same code with BULKINSERT too
SET @sp = @sp + 'BULK INSERT #TEMP FROM '
SET @sp = @sp + '''' + @pathTotal + ''''
SET @sp = @sp + 'WITH (FIELDTERMINATOR = ''\t'')'
EXEC(@sp)
UPDATE Reports SET RPT_Status=TMP_Status FROM
(SELECT TMP_Status, TMP_GsmNo FROM #TEMP) TempTable
WHERERPT_MessageID=@messageID
AND RPT_GsmNo COLLATE database_default=TMP_GsmNo
the results were the same.
We tried to call the sp from the code (dynamic sql) but the results were the same. But when we debug the code and call the insert or update portion of sql for an incorrect message, from the query analyzer it properly updates the db.
Thanks.
