Backport to branch(3.9) : Fix CI issue SQL Server 2022 failed (#2090)

Co-authored-by: kota2and3kan <47254383+kota2and3kan@users.noreply.github.com>
scalar-labs · Jul 26, 2024 · 01fee78 · 01fee78
1 parent ded9834
commit 01fee78
Showing 1 changed file with 17 additions and 10 deletions.
diff --git a/ci/no-superuser/create-no-superuser-sqlserver.sh b/ci/no-superuser/create-no-superuser-sqlserver.sh
@@ -1,5 +1,5 @@
 #!/bin/bash
-set -u
+set -ue
 
 # Get container name and password from arguments
 SQL_SERVER_CONTAINER_NAME=$1
@@ -8,6 +8,12 @@ MAX_RETRY_COUNT=$3
 RETRY_INTERVAL=$4
 COUNT=0
 
+if [[ "$SQL_SERVER_CONTAINER_NAME" = "sqlserver22" ]]; then
+  SQLCMD=/opt/mssql-tools18/bin/sqlcmd
+else
+  SQLCMD=/opt/mssql-tools/bin/sqlcmd
+fi
+
 echo "INFO: Creating no superuser start."
 
 # A SQL Server container takes a few seconds to start SQL Server process
@@ -21,7 +27,8 @@ do
 
     echo "INFO: Retry count: ${COUNT}"
 
-    docker exec -t ${SQL_SERVER_CONTAINER_NAME} /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d master -Q "SELECT 1"
+    docker exec -t ${SQL_SERVER_CONTAINER_NAME} ${SQLCMD} -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d master -C -Q "SELECT 1"
+
     if [[ $? -eq 0 ]]; then
         break
     else
@@ -40,40 +47,40 @@ echo "INFO: sqlcmd command succeeded. Continue creating no superuser."
 
 # Create login
 echo "INFO: Create login start"
-docker exec -t ${SQL_SERVER_CONTAINER_NAME} /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d master -Q "CREATE LOGIN no_superuser WITH PASSWORD = 'no_superuser_password', DEFAULT_DATABASE = master , CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF"
+docker exec -t ${SQL_SERVER_CONTAINER_NAME} ${SQLCMD} -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d master -C -Q "CREATE LOGIN no_superuser WITH PASSWORD = 'no_superuser_password', DEFAULT_DATABASE = master , CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF"
 echo "INFO: Create login end"
 
 # Create database
 echo "INFO: Create database start"
-docker exec -t ${SQL_SERVER_CONTAINER_NAME} /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d master -Q "CREATE DATABASE test_db COLLATE Japanese_BIN2"
+docker exec -t ${SQL_SERVER_CONTAINER_NAME} ${SQLCMD} -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d master -C -Q "CREATE DATABASE test_db COLLATE Japanese_BIN2"
 echo "INFO: Create database end"
 
 # Create no_superuser
 echo "INFO: Create no_superuser start"
-docker exec -t ${SQL_SERVER_CONTAINER_NAME} /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d test_db -Q "CREATE USER no_superuser FOR LOGIN no_superuser"
+docker exec -t ${SQL_SERVER_CONTAINER_NAME} ${SQLCMD} -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d test_db -C -Q "CREATE USER no_superuser FOR LOGIN no_superuser"
 echo "INFO: Create no_superuser end"
 
 # Add roles
 echo "INFO: Add role db_ddladmin start"
-docker exec -t ${SQL_SERVER_CONTAINER_NAME} /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d test_db -Q "EXEC sp_addrolemember @rolename = 'db_ddladmin', @membername = 'no_superuser'"
+docker exec -t ${SQL_SERVER_CONTAINER_NAME} ${SQLCMD} -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d test_db -C -Q "EXEC sp_addrolemember @rolename = 'db_ddladmin', @membername = 'no_superuser'"
 echo "INFO: Add role db_ddladmin end"
 
 echo "INFO: Add role db_datawriter start"
-docker exec -t ${SQL_SERVER_CONTAINER_NAME} /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d test_db -Q "EXEC sp_addrolemember @rolename = 'db_datawriter', @membername = 'no_superuser'"
+docker exec -t ${SQL_SERVER_CONTAINER_NAME} ${SQLCMD} -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d test_db -C -Q "EXEC sp_addrolemember @rolename = 'db_datawriter', @membername = 'no_superuser'"
 echo "INFO: Add role db_datawriter end"
 
 echo "INFO: Add role db_datareader start"
-docker exec -t ${SQL_SERVER_CONTAINER_NAME} /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d test_db -Q "EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'no_superuser'"
+docker exec -t ${SQL_SERVER_CONTAINER_NAME} ${SQLCMD} -S localhost -U sa -P ${SQL_SERVER_PASSWORD} -d test_db -C -Q "EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'no_superuser'"
 echo "INFO: Add role db_datareader end"
 
 # Check the collation of test_db (for debugging purposes)
 echo "INFO: Check collation start"
-docker exec -t ${SQL_SERVER_CONTAINER_NAME} /opt/mssql-tools/bin/sqlcmd -S localhost -U no_superuser -P no_superuser_password -d test_db -Q "SELECT name, collation_name FROM sys.databases" -W
+docker exec -t ${SQL_SERVER_CONTAINER_NAME} ${SQLCMD} -S localhost -U no_superuser -P no_superuser_password -d test_db -C -Q "SELECT name, collation_name FROM sys.databases"
 echo "INFO: Check collation end"
 
 # Check if no_superuser can access SQL Server (for debugging purposes)
 echo "INFO: SELECT @@version start"
-docker exec -t ${SQL_SERVER_CONTAINER_NAME} /opt/mssql-tools/bin/sqlcmd -S localhost -U no_superuser -P no_superuser_password -d test_db -Q "SELECT @@version"
+docker exec -t ${SQL_SERVER_CONTAINER_NAME} ${SQLCMD} -S localhost -U no_superuser -P no_superuser_password -d test_db -C -Q "SELECT @@version"
 echo "INFO: SELECT @@version end"
 
 echo "INFO: Creating no superuser succeeded."