Multi-part names of xml types mistakenly reported as not supported.

Oct 22, 2009 at 7:31 PM

CREATE PROCEDURE [usp_Client_Load]

(

      @ClientID bigint

)

AS

BEGIN

      SET NOCOUNT ON

      DECLARE @Err int

 

      SELECT

            [ClientID],

            [ClientName],

            Client.CustomFields.value('(/ClientFields/IsSuperClient)[1]', 'bit') AS IsSuperClient

      FROM [Client]

      WHERE

            ([ClientID] = @ClientID)

 

      SET @Err = @@Error

 

      RETURN @Err

END

 Client.CustomFields.value('(/ClientFields/IsSuperClient)[1]' reported as “ Multi-part names with Server or Database specifier is not supported in this version of SQL Azure.” , while it executes fine.

Coordinator
Oct 23, 2009 at 6:05 PM

Hi,

Yes, I see your point.  I have ran into the same problem.  I am not quite sure how to resolve it because things like dbo.table.column or database.dbo.table do not work.  This is a simple Regex lookup and it does not do multi pass evaluation (not a real SQL parser).  So for now, you can make note of it and say, yep, that is ok or if you know that is not an issue in your database, you can just remove that Regex expression from NotSupportedByAzureFile.config.

Sorry I have nothing better at this time.  Suggestions are welcome.

Thanks

Coordinator
Nov 7, 2009 at 8:56 PM

Hi,

In v1.9.1, put a NotSubStr.  That means when I find the multi-part name, I make sure that the last part of the three part name is not .value.  So, I think I have a solution for the issue you mentioned.  If you are still testing, let me know if this worked for you.

Thanks!