VSTS Build fails: 'trim' is not a recognized built-in function name

MarkPhsa

I am using a UDF newly available with SQL 2017 called dbo.TRIM.   From Visual Studio 2017 I can build the project without issue and deploy the individual stored procedures that I use it in.

Please note that I have tried this with the project "Target Platform" set to both of these values: 

• SQL Server 2017
• Microsoft Azure SQL Database V12

When I use the VSTS task "Redgate SQL Change Automation:Build" I get the following errors during the build:

<div>2018-09-18T20:01:32.1343049Z Validating project 'D:\a\1\s\db-fhir-store-redgate\db-fhir-store-redgate\db-fhir-store-redgate.sqlproj'.</div><div>2018-09-18T20:01:55.4958454Z ##[warning]&nbsp;&nbsp;&nbsp;&nbsp;DbBuildTask: ERROR D:\a\1\s\db-fhir-store-redgate\db-fhir-store-redgate\Programmable Objects\fhir\Stored Procedures\PatientMutation.sql(0,0): An error occurred while attempting to verify project scripts: </div><div>2018-09-18T20:01:55.4969737Z ##[warning]&nbsp;&nbsp;&nbsp;&nbsp;Msg 195, Level 15, State 10, Server factoryvm-az69\LOCALDB#EAD7A53C, Procedure PatientMutation, Line 30</div><div>2018-09-18T20:01:55.4971175Z ##[warning]&nbsp;&nbsp;&nbsp;&nbsp;'trim' is not a recognized built-in function name.</div><div>2018-09-18T20:01:55.4972364Z ##[warning]&nbsp;&nbsp;&nbsp;&nbsp;Msg 195, Level 15, State 10, Server factoryvm-az69\LOCALDB#EAD7A53C, Procedure PatientMutation, Line 35</div><div>2018-09-18T20:01:55.4973515Z ##[warning]&nbsp;&nbsp;&nbsp;&nbsp;'trim' is not a recognized built-in function name.</div><div>2018-09-18T20:01:55.4975190Z ##[warning]&nbsp;&nbsp;&nbsp;&nbsp;Msg 319, Level 15, State 1, Server factoryvm-az69\LOCALDB#EAD7A53C, Procedure PatientMutation, Line 35</div><div>2018-09-18T20:01:55.4976771Z ##[warning]&nbsp;&nbsp;&nbsp;&nbsp;Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.</div><div>2018-09-18T20:01:55.4978431Z ##[warning]&nbsp;&nbsp;&nbsp;&nbsp;Msg 319, Level 15, State 1, Server factoryvm-az69\LOCALDB#EAD7A53C, Procedure PatientMutation, Line 38</div><div>2018-09-18T20:01:55.4979983Z ##[warning]&nbsp;&nbsp;&nbsp;&nbsp;Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.</div><div>2018-09-18T20:01:55.8970810Z ##[error]RedGate.Versioning.Automation.Shared.Errors.TerminatingException: Schema validation failed: </div><div>2018-09-18T20:01:55.8974835Z    at RedGate.Versioning.Automation.MsBuild.MsBuildTaskRunner.PerformBuild(String projectPath, IDictionary`2 globalProperties)</div><div>2018-09-18T20:01:55.8976212Z    at RedGate.Versioning.Automation.Compare.Projects.ScaProjectBuilder.Build(ScaProjectPath scaProject, IDatabase database)</div><div>2018-09-18T20:01:55.8977064Z    at RedGate.Versioning.Automation.Compare.SchemaValidation.DiskBackedProjectValidator.ValidateProject(IProjectPath projectPath, IDatabase database, CreateDatabaseParameters createDatabaseParameters)</div><div>2018-09-18T20:01:55.8977967Z    at RedGate.Versioning.Automation.Compare.SchemaValidation.ProjectValidator.ValidateProject(String projectPath, CreateDatabaseParameters createDatabaseParameters)</div><div>2018-09-18T20:01:55.8978644Z    at RedGate.Versioning.Automation.PowerShell.Commands.InvokeDatabaseBuildCommandImpl.Process()</div><div>2018-09-18T20:01:55.8979226Z    at RedGate.Versioning.Automation.PowerShell.DlmAutomationCommandImpl`1.ProcessRecord()</div><div>2018-09-18T20:01:55.8981595Z    at RedGate.PowerShell.CommandBase`2.DelegateTo(Action doImplementation)</div>

way0utwest

What's the build target? The only compiler the build process has is the SQL Server engine. If you've targeted LocalDB or a version that doesn't support TRIM(), then things might not work.

MarkPhsa

As I mentioned above, I have tried both of the following target platforms within the project:

• SQL Server 2017
• Microsoft Azure SQL Database V12

The actual target is an Azure SQL database.   

Is there somewhere else that I specify the target?  I did not see it on the VSTS build task

way0utwest

Apologies, the target is really a release term. That's where you pick the end database. For build, you need a temporary place to compile the code.  The temporary server is where the code would run. This needs to support TRIM()

MarkPhsa

Ok, since this is VSTS against an Azure environment, I can only build using the LocalDB.  It looks like the VSTS servers do not yet have SQL Server 2017 LocalDB installed.  Here is the list of installed software on those servers.  Heavy sigh....

http://listofsoftwareontfshostedbuildserver.azurewebsites.net/