sql-serverazure-devopscontinuous-integrationlocaldbbuild-agent

Using wrong version of SQLLocaldb on Windows-Latest Devops Build Agent - possibly?


My Azure DevOps CI build pipeline is configured to run on windows-latest build agent

pool:
vmImage: windows-latest

The SQLLocalDb version on window-latest build agents as stated here is v17

enter image description here

When I run the following as part of the yaml CI pipeline, it shows v15.0 (SQLServer 2019)

- script: |
    sqllocaldb versions
    sqllocaldb info
  displayName: sqllocaldb versions on build VM

enter image description here

How do I get to install/upgrade to the latest version so that I can use the ordinal parameter that was introduced for string_split in SQL Server

Is SQllocalDb for SQLserver 2022 available, if so, how do I get it?

*** Could not deploy package.
Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 8144, Level 16, State 3, Procedure <procedure_name>, Line 42 Procedure or function STRING_SPLIT has too many arguments specified.
Error SQL72045: Script execution error.

Solution

  • Upon checking the SQL Server installation path C:\Program Files\Microsoft SQL Server\ on the Microsoft-hosted windows-latest pipeline agent, there is only the sub folder of /LocalDB/Binn in 150 not 160.

    enter image description here

    To install SQllocalDb for SQL Server 2022, you may refer to the sample PowerShell script below running on the MS-hosted agent, which works for me.

    pool:
      vmImage: windows-latest
    
    steps:
    - checkout: none
    - powershell: |
        sqllocaldb versions
        sqllocaldb info
        tree "C:\Program Files\Microsoft SQL Server\" /F /A
      displayName: Check SQLLocalDB verion and SQL Server installation path
    - powershell: |
        Write-Host "================ Download SQL2022 installer==============="
        mkdir SQL2022
        $url = "https://go.microsoft.com/fwlink/?linkid=2215160"
        $outputFilePath = "$(System.DefaultWorkingDirectory)\SQL2022\SQL2022-SSEI-Expr.exe"
        Invoke-WebRequest -Uri $url -OutFile $outputFilePath
        tree $(System.DefaultWorkingDirectory) /F /A
        
        Write-Host "================Download and extract SqlLocalDB.MSI from installation media==============="
        & "$(System.DefaultWorkingDirectory)\SQL2022\SQL2022-SSEI-Expr.exe" /Action=Download /MediaType=LocalDB /Quiet /MEDIAPATH=$(System.DefaultWorkingDirectory)\SQL2022
        Start-Sleep -s 60.00 # Wait for the downlaoding to complete
        tree $(System.DefaultWorkingDirectory) /F /A
        
        Write-Host "================Run the SqlLocalDB.MSI installer non-interactively=============="
        msiexec.exe /qb /i $(System.DefaultWorkingDirectory)\SQL2022\en-US\SqlLocalDB.msi IAcceptSqlLocalDBLicenseTerms=YES
        Start-Sleep -s 60.00 # Wait for the installation to complete
        
        Write-Host "================Check SQLLocalDB verion and SQL Server installation path again==============="
        sqllocaldb versions
        sqllocaldb info
        tree "C:\Program Files\Microsoft SQL Server\" /F /A
      displayName: Install SQllocalDb for SQL Server 2022
    

    enter image description here