How can we help you today? How can we help you today?

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

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:
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'.
2018-09-18T20:01:55.4958454Z ##[warning]    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:
2018-09-18T20:01:55.4969737Z ##[warning]    Msg 195, Level 15, State 10, Server factoryvm-az69\LOCALDB#EAD7A53C, Procedure PatientMutation, Line 30
2018-09-18T20:01:55.4971175Z ##[warning]    'trim' is not a recognized built-in function name.
2018-09-18T20:01:55.4972364Z ##[warning]    Msg 195, Level 15, State 10, Server factoryvm-az69\LOCALDB#EAD7A53C, Procedure PatientMutation, Line 35
2018-09-18T20:01:55.4973515Z ##[warning]    'trim' is not a recognized built-in function name.
2018-09-18T20:01:55.4975190Z ##[warning]    Msg 319, Level 15, State 1, Server factoryvm-az69\LOCALDB#EAD7A53C, Procedure PatientMutation, Line 35
2018-09-18T20:01:55.4976771Z ##[warning]    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.
2018-09-18T20:01:55.4978431Z ##[warning]    Msg 319, Level 15, State 1, Server factoryvm-az69\LOCALDB#EAD7A53C, Procedure PatientMutation, Line 38
2018-09-18T20:01:55.4979983Z ##[warning]    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.
2018-09-18T20:01:55.8970810Z ##[error]RedGate.Versioning.Automation.Shared.Errors.TerminatingException: Schema validation failed:
2018-09-18T20:01:55.8974835Z at RedGate.Versioning.Automation.MsBuild.MsBuildTaskRunner.PerformBuild(String projectPath, IDictionary`2 globalProperties)
2018-09-18T20:01:55.8976212Z at RedGate.Versioning.Automation.Compare.Projects.ScaProjectBuilder.Build(ScaProjectPath scaProject, IDatabase database)
2018-09-18T20:01:55.8977064Z at RedGate.Versioning.Automation.Compare.SchemaValidation.DiskBackedProjectValidator.ValidateProject(IProjectPath projectPath, IDatabase database, CreateDatabaseParameters createDatabaseParameters)
2018-09-18T20:01:55.8977967Z at RedGate.Versioning.Automation.Compare.SchemaValidation.ProjectValidator.ValidateProject(String projectPath, CreateDatabaseParameters createDatabaseParameters)
2018-09-18T20:01:55.8978644Z at RedGate.Versioning.Automation.PowerShell.Commands.InvokeDatabaseBuildCommandImpl.Process()
2018-09-18T20:01:55.8979226Z at RedGate.Versioning.Automation.PowerShell.DlmAutomationCommandImpl`1.ProcessRecord()
2018-09-18T20:01:55.8981595Z at RedGate.PowerShell.CommandBase`2.DelegateTo(Action doImplementation)
MarkPhsa
0

Comments

4 comments

Sort by
  • 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.
    way0utwest
    0
  • 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
    MarkPhsa
    0
  • 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()


    way0utwest
    0
  • 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/
    MarkPhsa
    0

Add comment

Please sign in to leave a comment.