CODEWITHSUNDEEP
t-sqlopenrowset
Maury Markowitz
Maury Markowitz

Reputation: 9293

SQL that works fine when read in OPENROWSET fails when the same code is a string in T-SQL

I'm writing some basic testing code to try out a couple of ways to address UPDATE performance. I made 10000 lines of UPDATES and saved that to a textfile. It's about 3 MB in total. An example of the lines in the file is:

UPDATE Extras SET [Name]='Note',[Table]='Details',
[Row]=5954211,[Value]='No fee for this customer',
[Type]='String' WHERE [Key]=355 AND [ProjKey]=4

I then opened that text file in SMS and ran it, and it takes about 3 seconds. I then used this code:

DECLARE @sql nvarchar(MAX)
SET @sql = 
(
  SELECT *
  FROM OPENROWSET(BULK 'C:\the file.txt', SINGLE_CLOB) AS data
)
EXEC sp_executsql @sql

As I hoped, this ran much faster, about three times. But it has the downside that it needs a file. So I then re-wrote the UPDATE file to escape the quotes on strings:

UPDATE Extras SET [Name]=''Note'',[Table]=''Details'',
[Row]=5954211,[Value]=''No fee for this customer'',
[Type]=''String'' WHERE [Key]=355 AND [ProjKey]=4

And removed the OPENROWSET and simply put the resulting string in as a constant:

SET @sql='UPDATE...'

SMS displays this correctly, the UPDATEs are a big red string and the DECLARE, SET and EXEC are seen as commands.

This does not run, you get random errors about syntax in lines that are clearly fine. Removing all the lines after a point about 10 lines above the error allows it to run without error. As does removing all the lines above this point and running the next batch.

Now I am aware that there is an 8000 character limit in strings fed to EXEC, but this error occurs ~800k, and not at all when I use OPENROWSET. I'm well below the size limit for a varchar(max). When I tried clipping out various parts, it always happened around the 2000 line mark. But I am unaware of any limits that might apply around this size.

Can anyone offer some insight here?

Upvotes: 0

Views: 38

Answers (0)

Related Questions