Hi there,

I have some code I build 2 weeks ago which Ive been running daily but its suddenly stopped working with the following error.

The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit

When I google this there seems to be a related to tables with vast numbers of columns.

My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit Im puzzled with is it was working and stopped. I dont recall changing anything but I wouldnt rule that out. I ve inspected the source files and I dont believe they have changed either.

Im a bit stuck on how to troubleshoot this. Any tips appreciated.

       DECLARE

              @FileName varchar(50),

              @Path varchar(50),

              @SqlCmd varchar(1000) = '',

              @ASXCode varchar(5),

              @Offset decimal(18,0),

              @LastDateValue date;

              Set @Offset = 36000

              SET @Path = 'M:\Data\ASX\Download\Quotes\Intraday\'

             

--Temp Cleanup

       DELETE FROM tbl_Intraday_tmp 

       Declare

              @files table(filenames varchar(255),depth int,isfile bit)

              insert into @files

              EXEC xp_dirtree  @path, 10, 1

              Declare cur Cursor for

       SELECT filenames FROM @files WHERE isfile=1

       OPEN cur

       FETCH NEXT FROM cur INTO @filename

       while @@FETCH_STATUS=0

                             BEGIN    

                                 

                                         ALTER TABLE tbl_Intraday_Tmp

                                         DROP COLUMN ASXCode

                                         SET @ASXCode = LEFT(@filename, charindex('.', @filename) - 1)                               

                                         SET @SqlCmd = 'BULK INSERT tbl_Intraday_tmp From ''' + @Path  + @FileName + ''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', FIRSTROW = 0)'                        

                                          BEGIN TRY

                                                EXEC(@SqlCmd)

                                                PRINT 'OK: ' + @FileName

                                          END TRY

                                          BEGIN CATCH

                                                PRINT ' '

                                                PRINT 'Error: ' + @FileName

                                           END CATCH                             

                          

                          

                                         ALTER TABLE tbl_Intraday_Tmp

                                         ADD ASXCode varchar(5)

                                         UPDATE tbl_Intraday_tmp

                                         SET ASXCode = @ASXCode

       FETCH NEXT FROM cur INTO @filename

                            

                             END

                             CLOSE cur

                             DEALLOCATE cur

Check advice from Erland in this thread https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5ee4ac6c-03e3-4ae2-b75c-c6389b14a338/run-2-cursors?forum=transactsql about proper cursor declaration.

Does the table have indexes? If yes. what are their definition. Also, in your cursor loop you're adding new column ASXCode varchar(5). So, you may exceed 8060 bytes per row easily this way. Why do you need to dynamically add a new column (and also the same name co