Category: Web development

This week, I’m altering functions and stored procedures in a SQL Server 2008 database so that it can be migrated to Azure. The Data Migration Assistant does a great job of generating reports identifying MigrationBlockers, but one type of error was vague enough to confuse me for a few minutes. Here’s an example of that error:

    {
      "Recommendations": [
        {
          "ApplicableCompatibilityLevels": [
            "CompatLevel100",
            "CompatLevel110",
            "CompatLevel120",
            "CompatLevel130",
            "CompatLevel140"
          ],
          "ChangeCategory": "MigrationBlocker",
          "RuleId": "46010",
          "Title": "One or more objects contain statements that are not supported in Azure SQL Database [46010]",
          "Impact": "While assessing the schema on the source database, one or more syntax issues were found. Syntax issues on the source database indicate that some objects contain syntax that is unsupported in Azure SQL Database.",
          "ImpactDetail": "Function: [dbo].[GalleryXML] contains a statement that is not supported on Microsoft Azure SQL Database v12. The specific error is: Incorrect syntax near AS.",
          "Recommendation": "Note that some of these syntax issues may be reported in more detail as separate issues in this assessment.  Review the list of objects and issues reported, fix the syntax errors, and re-run assessment before migrating this database.",
          "MoreInfo": ""
        }
      ],
      "IsSelectedForMigration": true,
      "Eligibility": {
        "IsEligibleForMigration": true,
        "Explanation": "OK"
      },
      "ObjectName": "GalleryXML",
      "SchemaName": "dbo",
      "ObjectType": "UserDefinedFunction"
    },

The error text, “Incorrect syntax near AS,” was just not a strong clue as to what was wrong with this function. The purpose of the function is to create structured XML that can be de-serialized into an instance of an object in C#. I use PATH Mode to accomplish this, and it is one of my favorite maneuvers when operating in SQL Server/.Net. Here is an abbreviated version of the query to help you understand the required changes to continue using this method in an Azure database:

SELECT 
ID,

...

--Subquery for photos
(
	SELECT 
	ID,

	...

	FROM GalleryPhotosTbl 
	WHERE GalleryID = GalleryTbl.ID
	FOR XML PATH('GalleryPhoto'), TYPE
) AS Photos
FROM GalleryTbl
WHERE GalleryTbl.ID = @ID
FOR XML PATH('Gallery'), TYPE

The syntax that Azure doesn’t support is “AS Photos.” Here is the solution:

SELECT 
ID,

...

--Subquery for photos
(
	SELECT 
	ID,

	...

	FROM GalleryPhotosTbl 
	WHERE GalleryID = GalleryTbl.ID
	FOR XML PATH('GalleryPhoto'), root ('Photos'), TYPE
)
FROM GalleryTbl
WHERE GalleryTbl.ID = @ID
FOR XML PATH('Gallery'), TYPE

I needed to detect a mixed case string in classic ASP. I define a mixed case string as containing both upper and lower case characters, like AuxagGsrLpa. I could not find a free function on the web to make this decision, so I wrote one.

<%
	Function isMixedCase( str )
		'detects mixed case strings using the english alphabet
		'ignores spaces and other non-alphabetic characters
		str = trim( str )
		isMixed = false
		lastCase = ""
		for i=1 to len( str )
			currentChar = mid( str, i, 1 )
			if asc( currentChar ) >= 65 and asc( currentChar ) <= 90 then
				if lastCase <> "" and lastCase <> "upper" then
					isMixed = true
					exit for
				else
					lastCase = "upper"
				end if
			else
				if asc( currentChar ) >= 97 and asc( currentChar ) <= 122 then
					'lower
					if lastCase <> "" and lastCase <> "lower" then
						isMixed = true
						exit for
					else
						lastCase = "lower"
					end if
				else
					lastCase = ""
				end if
			end if
		next
		if isMixed then
			isMixedCase = true
		else
			isMixedCase = false
		end if
	End Function
%>

Here are a few test strings to demonstrate the functionality:


<% response.write "Hello " & isMixedCase( "Hello " ) & " <br>" response.write "hello! " & isMixedCase( "hello!" ) & " <br>" response.write "HELLO " & isMixedCase( "HELLO " ) & " <br>" response.write "hello there " & isMixedCase( "hello there" ) & " <br>" response.write "hellothere " & isMixedCase( "hellothere" ) & " <br>" response.write "hellotherE " & isMixedCase( "hellotherE" ) & " <br>" %>

Here is the resulting HTML output of the tests:

Hello True
hello! False
HELLO False
hello there False
hellothere False
hellotherE True