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

That is only a warning, so the code should keep running.

As Naomi says, you get this error because you are dropping and adding a column all the time.

Rather than dropping and adding the column in the loop, do one of:

1) Drop the column before the loop, and restore it afterwards.
2) Use format file to map the file column to the table columns.

I would recommend the latter.

Thanks Naomi,

I checked my table and it didn't have indexes, so reinstated them and fixed the issue straight away. I would never have thought of this.

I must have done something to turn it off on Wednesday or Thursday causing it to stop.

My codes pretty rough and I'll work on improving it (as I build knowledge)

I've played around with format files and haven't been able to get one to work yet, but agree at some point best that I do, so I don't need unorthodox workarounds.

Thanks,

Bassmann

 Why do you need to dynamically add a new column (and also the same name co