Various issues on first test

Sep 2, 2009 at 7:39 PM

Thanks for a great tool. Most worked like a charm, but here are some issues I ran into:

  • Tables worked fine to a new empty database, but to an existing database I got several errors because a column default allready existed.
  • Table hints with the WITH keyword is no longer supported. In the ASP.NET membership stored procedures there a statemets like this: “…FROM dbo.aspnet_Users u(NOLOCK)” and this must be changed to “…FROM dbo.aspnet_Users u WITH (NOLOCK)”. Sometimes the source database has the WITH keyword and sometimes its missing, the table alias varies and there’s 15 or so different table hints to look for. Since I’m not the sharpest RegEx head, I fixed this in the source database instead of trying to invent a fancy regex to fix the generated script.
  • Data types ‘image’ and ‘ntext’ was replaced for tables, but not for stored procedures and since there’s no specific config section for stored procedures, I use generic TSQL section the following to the TSQL section in the “NotSupportedByAzureFile.Config” file:
    NotSupported Text="\sntext" ReplaceWith=" nvarchar(max)" SeverityLevel="0" ReplaceString="true" DisplayWarning="true" WarningMessage="Replaced column type [ntext] with [nvarchar](max)"<br/>
    NotSupported Text="\simage" ReplaceWith=" varbinary(max)" SeverityLevel="0" ReplaceString="true" DisplayWarning="true" WarningMessage="Replaced column type [image] with [varbinary](max)"<br/>
  • In my create table scripts I have command like this “…nvarchar(x) NOT NULL COLLATE Danish_Norwegian_CI_AS”. When SQL Azure is officially released, it’s expected to support non-us collations, but not at the column level. For now trying to set non-us collations will result in the following error when using string functions in stored procedures: “Cannot resolve collation conflict between 'Latin1_General_CS_AS' and ' Danish_Norwegian_CI_AS ' in equal to operation”. For now, until SQL  Azure is released, I solved this by adding the following to the TableStatement element in the “NotSupportedByAzureFile.Config” file:
    NotSupported Text="\sCOLLATE Danish_Norwegian_CI_AS\s" ReplaceWith=" " SeverityLevel="0" ReplaceString="true" DisplayWarning="true" WarningMessage="Removed COLLATE Danish_Norwegian_CI_AS to use database default collation"
Sep 3, 2009 at 12:18 PM

Great feedback.

  • On item number 1 (existing databases -- column default), can I get more detail? This app should work just fine with existing databases, so this is probably something I should look into.
  • Table hints. Good point and a hard one. I will put that on my todo list and see what I can come up with.
  • You are right. I really need to have a Stored Proc section. Maybe I can work on that this weekend.
  • I Collation ... I think you are on the right track there. Good job.