There are no such limitations in the operating system (the kernel and the filesystem) itself. The SDHC format seems to be limited to 32GB, so there's one limit. The FAT32 filesystem type probably has another limit, so there's one more. But if there were a kind of SD card available with a size of 2 TB[*] then there's no reason it wouldn't work just fine in the N800, if you formatted it to use the ext2 filesystem instead of FAT32.

There have been some problems with (in particular) SDHC cards, which could cause data/card corruptions. This has been resolved in the latest OS revision, but technically speaking the problems were caused by cards not fully following the SDHC specifications. Thus, Nokia is presumably a bit conservative with what they 'officially' support, but as mentioned above all types of cards should work as far as the filesystem/addressing is concerned.

[*] a 2TB card may however need another type of card entirely, with different number of connectors etc. This was just for illustration. I would be fine with a 120TB card in my internet tablet though, I have applications in mind already.