I have a Bash script that creates a build machine for an Azure DevOps project. This build machine then creates a single-server instance of SAP Hybris Commerce, builds our customisations, runs tests, creates a deployable Hybris artefact, then destroys itself. I have been tasked with switching the MySQL Database for MS SQL, as that's what our project is now using. The build machine OS is RHEL 7.5.
I used the MS SQL Unattended Install script from the Microsoft site to set up the SQL server, and install the SQL tools, and the Unix ODBC driver.
I use SQLCMD to set up the database and the user:
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P MySAUserPassword -C -e -m-1 -i config/initmssql/mssqlinit.sql
And this is the content of mssqlinit.sql:
USE master;
DROP DATABASE IF EXISTS dbname;
CREATE DATABASE dbname;
ALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON;
USE dbname;
CREATE LOGIN dbuser WITH PASSWORD = 'MyStrongPassword';
CREATE USER dbuser FOR LOGIN dbuser;
GRANT CONTROL ON DATABASE dbname TO dbuser;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
sp_configure 'max server memory', 12884;
RECONFIGURE;
This gives me the error:
Msg 911, Level 16, State 1, Server BUILDMACHINENAME, Line 6
Database 'dbname' does not exist. Make sure that the name is entered correctly.
I'm confused because if dbname really doesn't exist, then shouldn't this script fail on line 4, the first of the ALTER DATABASE commands? And if the database wasn't created, shouldn't the CREATE DATABASE command have thrown an error? I'm using the parameter -m-1 in the SQLCMD which I think should be showing me all output.
Then, later in the process, when the build script is attempting to connect to the database, I see the following error:
[java] ERROR [main] [DataSourceImpl] error connecting to DataSource having url jdbc:sqlserver://localhost:1433;databaseName=dbname;responseBuffering=adaptive;loginTimeout=10;encrypt=true;trustServerCertificate=true;: Login failed for user 'dbuser'. ClientConnectionId:fc64117f-b794-490a-81f3-8b0cd489597a
[java] com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'dbuser'. ClientConnectionId:fc64117f-b794-490a-81f3-8b0cd489597a
I'm guessing this could be a bit of a red herring because dbuser is associated with the database dbname which my earlier use of SQLCMD reported wasn't created.
It's difficult for me to inspect the build machine because it only lives as long as the script takes to run and build the artefacts.
The SQL script looks really simple to me, and I can't figure out why it isn't working. Can any of you lovely people give me a hint, please?
T-SQL is a compiled language. You CREATE the database dbname in the same batch you try to USE it, however, (in some contexts) databases aren't an object type where the compilation is deferred. As a result when the script is parsed the USE statement produces the error and no batch is run at all.
You need to add batch separators to your script. I'm a "little" zealous with my use below, but this is likely how I would write such a script:
USE master;
GO
DROP DATABASE IF EXISTS dbname;
GO
CREATE DATABASE dbname;
GO
ALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
USE dbname;
GO
CREATE LOGIN dbuser WITH PASSWORD = 'MyStrongPassword';
CREATE USER dbuser FOR LOGIN dbuser;
GRANT CONTROL ON DATABASE dbname TO hnkdot; --Should this not be dbuser?
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
USE master; --PErsonally I prefer to do server configurations in master
GO
EXEC sys.sp_configure 'max server memory', 12884;
GO
RECONFIGURE;
GO