XML formatting bug - v7.3.0.610

jmeyer

Just downloaded the new Adam Machanic's sp_whoisactive script from http://whoisactive.com/.

When using the traditional SQLPrompt re-formatting it will incorrectly format the followign code segment

UPDATE s
			SET
				additional_info.modify
				('
					insert <schema_name>{sql:column("b.schema_name")}</schema_name>
					as last
					into (/additional_info/block_info)[1]
				')
			FROM #sessions AS s
			INNER JOIN #blocked_requests AS b ON
				b.session_id = s.session_id
				AND b.request_id = s.request_id
				AND s.recursion = 1
			WHERE
				b.schema_name IS NOT NULL;

to

UPDATE  s
      SET     s.additional_info.modify('
					insert <schema_name>{sql:column("b.schema_name")}</schema_name>
					as last
					into (/additional_info/block_info)[1]
				')
      FROM    #sessions AS s
      INNER JOIN #blocked_requests AS b
      ON      b.session_id = s.session_id
              AND b.request_id = s.request_id
              AND s.recursion = 1
      WHERE   b.schema_name IS NOT NULL;

This then produces an "Incorrect syntax near 'modify'." error when I try to run the script.

It's well possible that this may actually a SQL Server 2008 R2 Management studio issue as the prefacing of the "s." alias would seem correct.

Aaron L

Hi Jens,

Thanks for your post - we can reproduce it here and will look into a fix.

On the surface it does look like it should be valid to me too although I've just tested it on a 2016 server and it displays the same error so it's not a 2008 R2 specific issue. I guess it's just a special case we need to accommodate for, but we hadn't seen it before so thanks for bringing it to our attention!

Thanks,
Aaron.

Aaron L

Hi Jens,

We've just uploaded a new beta build (7.3.0.632) which won't add the extra qualification if the modify method is being used. Please give it a try and let me know if you have any problems on the latest build.

Thanks!
Aaron.