2

Apologies if this has been answered elsewhere but I couldn't find anything here or online.

Does openrowset have a file size limit? I'm trying to write a stored procedure, part of which checks if certain characters exist within the file and if they do I'd have to skip to the next section of the stored procedure. So far I've simply bulk inserted the entire file into a one column table then did:

IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%}%')>0 GOTO NEXTSECTION
IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%~%')>0 GOTO NEXTSECTION
IF(SELECT COUNT(*) FROM #fulltable WHERE fulltable LIKE '%#%')>0 GOTO NEXTSECTION

It's reliable but very slow when dealing with large files (sometimes over 10GB). I'm thinking the below query would be quicker for large files

DECLARE @FILE NVARCHAR(MAX)
select @FILE = BULKCOLUMN from (
select * from openrowset(BULK N'filpath', single_clob) [a]
)a

IF(SELECT IIF(@FILE LIKE '%{%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%}%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%~%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%@%',1,0)) = 1 GOTO NEXTSECTION
IF(SELECT IIF(@FILE LIKE '%£%',1,0)) = 1 GOTO NEXTSECTION

But as I say Bulk insert is reliable and I'd hate to risk the file being truncated when using OPENROWSET if there is some sort of file size limit.

Any advice on the matter would be appreciated.

  • Why you do not calculate all COUNTs in one statement? it will be one file fullscan, file will be readed once (it must be readed once anycase). COUNTs may be stored in variables for future use without one more file access. – Akina Oct 15 '19 at 12:06
  • surely it would be slower in the long run as it would have to search through the file for every character. Notice I have a GOTO statement if any of the characters do exist in the file. – Anonymous Oct 15 '19 at 12:14
  • I don't understand why (schematically) SELECT COUNT('}') INTO "cnt}", COUNT('#') INTO "cnt#", ... ; IF "cnt}" > 0 THEN ... will be slower then a lot of separate queries... – Akina Oct 15 '19 at 12:21
0

I don't believe OPENROWSET has a limit, at least not one I have come across - and there isn't one documented, so to answer that question, NO.

However, SSMS is a 32 bit application so I think that limits it's memory on a 64 bit OS to 4gb - so whatever you do, your limited by that. That doesn't mean the max file you can import is 4gb, but it would stream it through the application with what ram it has available.

Also, If your running OPENROWSET on your laptop/client SSMS into a remote server, that will also slow you down. Consider running OPENROWSET on the server - I have seen massive speed improvements by doing the work on the server, not from the client.

From Microsoft:-

Note SSMS is a 32-bit process. Therefore, it is limited to 2 GB of memory. (https://support.microsoft.com/en-za/help/2874903)

  • are you sure about this 4GB limit? I'm using a 64-bit laptop and i managed to query a 10gb fiie – Anonymous Oct 16 '19 at 9:20
  • It's not a limit in that sense. e.g. your laptop might only have 8gb of RAM but you can still pull in a 100gb file into a database. note where I said "That doesn't mean the max file you can import is 4gb, but it would stream it through the application with what ram it has available." – gareth Oct 18 '19 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.