Sample Header Ad - 728x90

SSMS - Openrowset maximum file size

2 votes
2 answers
1330 views
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.
Asked by Anonymous (155 rep)
Oct 15, 2019, 12:01 PM
Last activity: Feb 25, 2020, 09:11 PM