Msg 105 Unclosed quotation mark after the character string '(data[@name="is_recompi'. about darlingdata HOT 6 CLOSED

@gflpcantor31 thanks for reporting this. It didn't come up in any of my testing.

It's sort of an unfortunate state of things with dynamic SQL where at different times in different places string concatenation sometimes implicitly converts to lower byte lengths, causing these truncation errors. There's no great workaround for it other than to add a convert to nvarchar max around every string fragment, which is pretty tedious.

In the meantime, if you find which concatenation point in the dynamic SQL causes this, please let me know.

from darlingdata.

@gflpcantor31 can you try the version in the dev branch and let me know if that resolves the error for you?

from darlingdata.

Hello @erikdarlingdata
I've just tested the latest version, I'm sorry, but the error still occurs identically.

from darlingdata.

Hello @erikdarlingdata

To correct the error, I had to make the following two types of changes:
1 - Because of data type precedence (1) , I added the following:

@table_sql =  CAST(N'' AS nvarchar(max)) + 
.... 
....

2 - Again because of data type precedence and doubts about the data types returned by CASE expressions (?), as a precaution I applied a CAST to CASE expressions like this:

CAST ( CASE 
               WHEN …. 
               WHEN … 
               ELSE …. END  AS nvarchar(max)) 

(1) Data type precedence https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver16
(2)
sp_HumanEvents__#273_gflpCantor31.zip
Maybe I've missed a few, but the only type 1/ change I made didn't resolve the error. That's why, to remove any doubts, I applied type 2/ modifications

Attached you'll find the script sp_HumanEvents__#273_gflpCantor31.zip where I've applied these modifications. With these modifications, I no longer reproduce the error. I'll leave it up to you to check and validate these modifications.

from darlingdata.

@gflpcantor31 If you open a proper pull request with these changes, I'm happy to take a look. It's a bit much to ask me to scroll through 3000 lines looking for your changes and then copying them over into my local file. Thanks!

from darlingdata.

Bonjour @erikdarlingdata
As desired, I've opened a pull request incorporating the changes.
I went back to your last CONVERT modifications (nvarchar(max), ..) and replaced them with a CAST, but only on the CASE expressions that caused the problem.
I've run a few tests. As far as I'm concerned, everything's OK and I don't see the error anymore.
I hope I haven't forgotten anything. I'm not very familiar with Git and Github procedures :)

from darlingdata.