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
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
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.
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
- Query to find position in a string before keyword and extract data before that position
- Correlate these tables dynamically (or automated)
- Manage the order of updates when using a filtered unique index?
- How to use an UPDATE Query with an INNER JOIN to update fields within a table
- Can we use SQL Server STRING_AGG() in queries using GROUP BY ROLLUP ()
- What are row, page and table locks? And when they are acquired?
- Aggregate into custom groups
- Work with multiple where statements with different conditions
- Excel VBA connection to SQL Server with Microsoft Entra MFA authentication
- How can I find sql server port number from windows registry?
- SSIS OLE DB Destination Editor - Create New Table not an option?
- How to Apply Gridify Filtering on an ICollection<T> in EF Core?
- SQL Server Cursor vs WHILE loop
- How do I control parameter sniffing and/or query hints in entity framework?
- Select with like is not returning the correct results
- I am trying to get the start date ONLY from the column below using a SUBSTRING. Can someone tell me what I'm doing wrong?
- using Object_id() function with #tables
- Conditionally filter results of query based on a single flag
- Fill down from a specific row based on criteria
- Spark sending LIMIT to SQL Server on display function
- database restore failing with move
- Join column from a table in SQL Server without write permissions to a data.frame in R using dbplyr
- different deleting OUTPUT behaviour based on source table
- How to simulate a deadlock in SQL Server in a single process?
- Is "SET NOCOUNT OFF" necessary in a stored procedure?
- EF Core takes a lot of time, sometimes, to perform SELECT query
- Dynamic T-SQL - Alter column definition to max length of field
- SQL Server: Update table based on JSON
- Best index(es) to use for an OR Statement in SQL Server
- In a select with a group by, get the last value of a column not in the group by