ReadyRoll issue with data formatted like an SQLCMD parameter

rutteger

I have a database with data in a table like so:

INSERT INTO ProblemData
(IntValue,CharValue,Command) VALUES (1,'abc','$(PackageVersion)')

This table is flagged "Include Table Data".
A Refresh, will always see this data as changed, even after you import an generate script.
You can see in the View Differences, the existing pane has no data for the Cmd column, and the new pane shows $(PackageVersion) as new data being added.
This will continue infinitely, I can never get this table synced.
Is there a work-around?

dnlnln

Thanks for reporting that issue. This is a known defect in ReadyRoll and the current recommended workaround is to edit the script after import, replacing all instances of the string "$(" with "$' + '(" minus the double-quotes. If you happen to import data from a table that includes the reserved characters, then you should typically get the following error during verification:

$(mydata) scripting variable not defined

However, in the example you mentioned, the variable $(PackageVersion) is one of a handful of SqlCmd variables that are built into ReadyRoll. So the behavior is a bit different: since the variable is defined, but because its value differs, the column values can never be synchronized. Nevertheless, the same workaround as above should resolve the issue.

An alternative solution is to encode any columns that may contain these reserved characters so that the values are not misinterpreted by the SqlCmd utility (thus, no editing of the file is required). One option is to use Base64 encoding for the data.

The defect tracking number is #204. Unfortunately I don't have an ETA for a fix, however I have noted this case, and will post again here when there is an update.

rutteger

Thank you Daniel.

Yes, the $(PackageVersion) is intentionally a SQLCMD parameter. This is a table where that column is supposed to show what version the data changed. We didn't want to manually edit the data for every deployment. Our hope was that by putting $(PackageVersion) in the table, it would "magically" show the current version after deployment. It is fine if SQLCMD processes that data as a parameter, we were counting on it.

The only problem is that it seems to also confuse ReadyRoll when trying to check for data changes. I will report to the project team that we have to manually update the version for now until there is a fix.

Cheers,
-Len

dnlnln

Hi Len,

Thanks for clarifying that. Given that SQLCMD variables are a function of the build engine rather than the script generation engine, I'd recommend switching to an offline approach to setting the PackageVersion column in your table (the column called Command in your code sample). This can be done by having a Post-Deployment script that conditionally sets the value after each deployment. For example, add a script called /Post-Deployment/01_Set_StaticData_Version.sql with the following content:

UPDATE ProblemData
SET PackageVersion = '$(PackageVersion)'
WHERE PackageVersion IS NULL;

To prevent the data comparison process from erroneously detecting changes on that column, edit your .sqlproj file and add the following:

<PropertyGroup>
    <ExcludeColumnsFromDataSync>
      Table=[dbo].[ProblemData].[PackageVersion];
    </ExcludeColumnsFromDataSync>
</PropertyGroup>

This will exclude the column from static data script generation. Note that when you deploy within Visual Studio, the PackageVersion will be set to (undefined); the column will only be populated properly as part of CI build.

I've attached a sample project that demonstrates this. Any questions please let me know