Syntax error when executing OPENROWSET Command

Question

SET @fullPath = 'D:\Hosting\Project\Content\Order\ImportTest.txt'

SET @command = N'select @data_count =(select len(BulkColumn) - len(replace(BulkColumn, 
char(10), '')) + 1
from OPENROWSET(BULK ''' + @fullPath + ''',
SINGLE_CLOB) ROW_SET'

EXEC(@command)

The commands above are generating the following error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'D:'.

Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string ', SINGLE_CLOB) ROW_SET'.

How do I fix the above issue?

How do I fix the above issue? You can't fix what you cannot see. To debug dynamic sql you must print or select the string that your code generates so you can actually examine the query, see the incorrect syntax, and then fix your code. — SMor
– SMor, Commented Nov 2, 2021 at 11:20

Gert Arnold · Accepted Answer · 2021-11-02 07:51:10Z

1

Just as you escaped the single quotes in BULK ''' + @fullPath + ''' you have to do it here: char(10), ''''.

answered Nov 2, 2021 at 7:51

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2021-11-02 13:05:02Z

0

As well as the double-quotes, you also need to properly escape the filename using QUOTENAME

SET @fullPath = 'D:\Hosting\Project\Content\Order\ImportTest.txt';

SET @command = N'
select
  @data_count = len(BulkColumn) - len(replace(BulkColumn, char(10), '''')) + 1
from OPENROWSET(BULK ' + QUOTENAME(@fullPath, '''') + ',
              SINGLE_CLOB) ROW_SET
';

EXEC sp_executesql @command;

answered Nov 2, 2021 at 13:05

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

Syntax error when executing OPENROWSET Command

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related