Bug 2120713
Summary: | Replace simple mssql_input_sql_file with pre and post variables | |||
---|---|---|---|---|
Product: | Red Hat Enterprise Linux 8 | Reporter: | Sergei Petrosian <spetrosi> | |
Component: | ansible-collection-microsoft-sql | Assignee: | Sergei Petrosian <spetrosi> | |
Status: | CLOSED ERRATA | QA Contact: | Daniel Yeisley <dyeisley> | |
Severity: | unspecified | Docs Contact: | Alexandra Nikandrova <anikandr> | |
Priority: | unspecified | |||
Version: | 8.7 | CC: | dyeisley, gfialova, lmanasko | |
Target Milestone: | rc | Keywords: | Triaged | |
Target Release: | --- | |||
Hardware: | Unspecified | |||
OS: | Unspecified | |||
Whiteboard: | ||||
Fixed In Version: | ansible-collection-microsoft-sql-1.2.4-1.el8 | Doc Type: | Enhancement | |
Doc Text: |
The RN description for this BZ is covered as part of BZ#2066338.
link: https://bugzilla.redhat.com/show_bug.cgi?id=2066338
|
Story Points: | --- | |
Clone Of: | 2120712 | |||
: | 2129333 2129872 (view as bug list) | Environment: | ||
Last Closed: | 2022-11-08 09:47:20 UTC | Type: | Bug | |
Regression: | --- | Mount Type: | --- | |
Documentation: | --- | CRM: | ||
Verified Versions: | Category: | --- | ||
oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | ||
Cloudforms Team: | --- | Target Upstream Version: | ||
Embargoed: | ||||
Bug Depends On: | 2120712 | |||
Bug Blocks: | 2129333, 2129872 |
Description
Sergei Petrosian
2022-08-23 15:10:43 UTC
I'm not having a lot of luck with this. [root@isvqe-01 Certification]# rpm -qa | grep ansible-collection-microsoft-sql ansible-collection-microsoft-sql-1.2.3-1.el8.noarch I attempted to use the sql code from my test harness with the following yml. [root@isvqe-01 Certification]# cat site.yml --- - hosts: all vars: mssql_version: 2019 mssql_accept_microsoft_odbc_driver_17_for_sql_server_eula: true mssql_accept_microsoft_cli_utilities_for_sql_server_eula: true mssql_accept_microsoft_sql_server_standard_eula: true mssql_password: redhat123! mssql_edition: Developer mssql_enable_sql_agent: true mssql_install_fts: true mssql_enable_ha: true mssql_pre_input_sql_file: results/create_db.sql mssql_post_input_sql_file: results/load_tables.sql roles: - role: microsoft.sql.server [root@isvqe-01 Certification]# cat results/create_db.sql -- Create Script for Database DS2 -- DVD Store Database Version 2.1 Build, Load and Create Index Script - SQL Server version - Medium DB -- Copyright (C) 2007 Dell, Inc. <dave_jaffe> and <tmuirhead> -- Last updated 07/23/2010 IF EXISTS (SELECT * FROM SYSDATABASES WHERE NAME='DS2') DROP DATABASE DS2 GO CREATE DATABASE DS2 ON PRIMARY ( NAME = 'primary', FILENAME = '/var/opt/mssql/data/ds.mdf' --Added a placeholder by GSK --This placeholder will be replaced by perl script with the paths given by user ), FILEGROUP DS_MISC_FG ( NAME = 'ds_misc', FILENAME = '/var/opt/mssql/data/ds_misc.ndf', SIZE = 200MB ), FILEGROUP DS_CUST_FG ( NAME = 'cust1', FILENAME = '/var/opt/mssql/data/cust1.ndf', SIZE = 600MB ), ( NAME = 'cust2', FILENAME = '/var/opt/mssql/data/cust2.ndf', SIZE = 600MB ), FILEGROUP DS_ORDERS_FG ( NAME = 'orders1', FILENAME = '/var/opt/mssql/data/orders1.ndf', SIZE = 300MB ), ( NAME = 'orders2', FILENAME = '/var/opt/mssql/data/orders2.ndf', SIZE = 300MB ), FILEGROUP DS_IND_FG ( NAME = 'ind1', FILENAME = '/var/opt/mssql/data/ind1.ndf', SIZE = 150MB ), ( NAME = 'ind2', FILENAME = '/var/opt/mssql/data/ind2.ndf', SIZE = 150MB ) LOG ON ( NAME = 'ds_log', FILENAME = '/var/opt/mssql/data/ds_log.ldf', SIZE = 1000MB ) GO ; [root@isvqe-01 Certification]# cat results/load_tables.sql USE DS2 GO -- Tables CREATE TABLE CUSTOMERS ( CUSTOMERID INT IDENTITY NOT NULL, FIRSTNAME VARCHAR(50) NOT NULL, LASTNAME VARCHAR(50) NOT NULL, ADDRESS1 VARCHAR(50) NOT NULL, ADDRESS2 VARCHAR(50), CITY VARCHAR(50) NOT NULL, STATE VARCHAR(50), ZIP INT, COUNTRY VARCHAR(50) NOT NULL, REGION TINYINT NOT NULL, EMAIL VARCHAR(50), PHONE VARCHAR(50), CREDITCARDTYPE TINYINT NOT NULL, CREDITCARD VARCHAR(50) NOT NULL, CREDITCARDEXPIRATION VARCHAR(50) NOT NULL, USERNAME VARCHAR(50) NOT NULL, PASSWORD VARCHAR(50) NOT NULL, AGE TINYINT, INCOME INT, GENDER VARCHAR(2) ) ON DS_CUST_FG GO CREATE TABLE CUST_HIST ( CUSTOMERID INT NOT NULL, ORDERID INT NOT NULL, PROD_ID INT NOT NULL ) ON DS_CUST_FG GO CREATE TABLE ORDERS ( ORDERID INT IDENTITY NOT NULL, ORDERDATE DATETIME NOT NULL, CUSTOMERID INT NOT NULL, NETAMOUNT MONEY NOT NULL, TAX MONEY NOT NULL, TOTALAMOUNT MONEY NOT NULL ) ON DS_ORDERS_FG GO CREATE TABLE ORDERLINES ( ORDERLINEID SMALLINT NOT NULL, ORDERID INT NOT NULL, PROD_ID INT NOT NULL, QUANTITY SMALLINT NOT NULL, ORDERDATE DATETIME NOT NULL ) ON DS_ORDERS_FG GO CREATE TABLE PRODUCTS ( PROD_ID INT IDENTITY NOT NULL, CATEGORY TINYINT NOT NULL, TITLE VARCHAR(50) NOT NULL, ACTOR VARCHAR(50) NOT NULL, PRICE MONEY NOT NULL, SPECIAL TINYINT, COMMON_PROD_ID INT NOT NULL ) ON DS_MISC_FG GO CREATE TABLE INVENTORY ( PROD_ID INT NOT NULL, QUAN_IN_STOCK INT NOT NULL, SALES INT NOT NULL ) ON DS_MISC_FG GO CREATE TABLE CATEGORIES ( CATEGORY TINYINT IDENTITY NOT NULL, CATEGORYNAME VARCHAR(50) NOT NULL, ) ON DS_MISC_FG GO SET IDENTITY_INSERT CATEGORIES ON INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (1,'Action') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (2,'Animation') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (3,'Children') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (4,'Classics') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (5,'Comedy') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (6,'Documentary') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (7,'Drama') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (8,'Family') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (9,'Foreign') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (10,'Games') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (11,'Horror') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (12,'Music') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (13,'New') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (14,'Sci-Fi') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (15,'Sports') INSERT INTO CATEGORIES (CATEGORY, CATEGORYNAME) VALUES (16,'Travel') GO CREATE TABLE REORDER ( PROD_ID INT NOT NULL, DATE_LOW DATETIME NOT NULL, QUAN_LOW INT NOT NULL, DATE_REORDERED DATETIME, QUAN_REORDERED INT, DATE_EXPECTED DATETIME ) ON DS_MISC_FG GO -- This keeps the number of items with low QUAN_IN_STOCK constant so that the rollback rate is constant CREATE TRIGGER RESTOCK ON INVENTORY AFTER UPDATE AS DECLARE @changedPROD_ID INT, @oldQUAN_IN_STOCK INT, @newQUAN_IN_STOCK INT; IF UPDATE(QUAN_IN_STOCK) BEGIN SELECT @changedPROD_ID = i.PROD_ID, @oldQUAN_IN_STOCK = d.QUAN_IN_STOCK, @newQUAN_IN_STOCK = i.QUAN_IN_STOCK FROM inserted i INNER JOIN deleted d ON i.PROD_ID = d.PROD_ID IF @newQUAN_IN_STOCK < 3 -- assumes quantity ordered is 1, 2, or 3 - change if different BEGIN INSERT INTO REORDER ( PROD_ID, DATE_LOW, QUAN_LOW ) VALUES ( @changedPROD_ID, GETDATE(), @newQUAN_IN_STOCK ) UPDATE INVENTORY SET QUAN_IN_STOCK = @oldQUAN_IN_STOCK WHERE PROD_ID = @changedPROD_ID END END RETURN GO DECLARE @db_id int, @tbl_id int USE DS2 SET @db_id = DB_ID('DS2') SET @tbl_id = OBJECT_ID('DS2..CATEGORIES') DBCC PINTABLE (@db_id, @tbl_id) SET @db_id = DB_ID('DS2') SET @tbl_id = OBJECT_ID('DS2..PRODUCTS') DBCC PINTABLE (@db_id, @tbl_id) USE DS2 GO -- sqlserverds2_load_cust.sql use DS2 go go bulk insert CUSTOMERS from '/ds2/data_files/cust/us_cust.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert CUSTOMERS from '/ds2/data_files/cust/row_cust.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go -- sqlserverds2_load_orders.sql set dateformat ymd go bulk insert ORDERS from '/ds2/data_files/orders/jan_orders.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERS from '/ds2/data_files/orders/feb_orders.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERS from '/ds2/data_files/orders/mar_orders.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERS from '/ds2/data_files/orders/apr_orders.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERS from '/ds2/data_files/orders/may_orders.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERS from '/ds2/data_files/orders/jun_orders.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERS from '/ds2/data_files/orders/jul_orders.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERS from '/ds2/data_files/orders/aug_orders.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERS from '/ds2/data_files/orders/sep_orders.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERS from '/ds2/data_files/orders/oct_orders.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERS from '/ds2/data_files/orders/nov_orders.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERS from '/ds2/data_files/orders/dec_orders.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go -- sqlserverds2_load_orderlines.sql bulk insert ORDERLINES from '/ds2/data_files/orders/jan_orderlines.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERLINES from '/ds2/data_files/orders/feb_orderlines.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERLINES from '/ds2/data_files/orders/mar_orderlines.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERLINES from '/ds2/data_files/orders/apr_orderlines.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERLINES from '/ds2/data_files/orders/may_orderlines.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERLINES from '/ds2/data_files/orders/jun_orderlines.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERLINES from '/ds2/data_files/orders/jul_orderlines.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERLINES from '/ds2/data_files/orders/aug_orderlines.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERLINES from '/ds2/data_files/orders/sep_orderlines.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERLINES from '/ds2/data_files/orders/oct_orderlines.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERLINES from '/ds2/data_files/orders/nov_orderlines.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go bulk insert ORDERLINES from '/ds2/data_files/orders/dec_orderlines.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go -- sqlserverds2_load_cust_hist.sql bulk insert CUST_HIST from '/ds2/data_files/orders/jan_cust_hist.csv' with (TABLOCK, FIELDTERMINATOR = ',') go bulk insert CUST_HIST from '/ds2/data_files/orders/feb_cust_hist.csv' with (TABLOCK, FIELDTERMINATOR = ',') go bulk insert CUST_HIST from '/ds2/data_files/orders/mar_cust_hist.csv' with (TABLOCK, FIELDTERMINATOR = ',') go bulk insert CUST_HIST from '/ds2/data_files/orders/apr_cust_hist.csv' with (TABLOCK, FIELDTERMINATOR = ',') go bulk insert CUST_HIST from '/ds2/data_files/orders/may_cust_hist.csv' with (TABLOCK, FIELDTERMINATOR = ',') go bulk insert CUST_HIST from '/ds2/data_files/orders/jun_cust_hist.csv' with (TABLOCK, FIELDTERMINATOR = ',') go bulk insert CUST_HIST from '/ds2/data_files/orders/jul_cust_hist.csv' with (TABLOCK, FIELDTERMINATOR = ',') go bulk insert CUST_HIST from '/ds2/data_files/orders/aug_cust_hist.csv' with (TABLOCK, FIELDTERMINATOR = ',') go bulk insert CUST_HIST from '/ds2/data_files/orders/sep_cust_hist.csv' with (TABLOCK, FIELDTERMINATOR = ',') go bulk insert CUST_HIST from '/ds2/data_files/orders/oct_cust_hist.csv' with (TABLOCK, FIELDTERMINATOR = ',') go bulk insert CUST_HIST from '/ds2/data_files/orders/nov_cust_hist.csv' with (TABLOCK, FIELDTERMINATOR = ',') go bulk insert CUST_HIST from '/ds2/data_files/orders/dec_cust_hist.csv' with (TABLOCK, FIELDTERMINATOR = ',') go -- sqlserverds2_load_prod.sql bulk insert PRODUCTS from '/ds2/data_files/prod/prod.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go -- sqlserverds2_load_inv.sql bulk insert INVENTORY from '/ds2/data_files/prod/inv.csv' with (KEEPIDENTITY, TABLOCK, FIELDTERMINATOR = ',') go go -- sqlserverds2_create_ind.sql USE DS2 GO ALTER TABLE CATEGORIES ADD CONSTRAINT PK_CATEGORIES PRIMARY KEY CLUSTERED ( CATEGORY ) ON DS_MISC_FG GO ALTER TABLE CUSTOMERS ADD CONSTRAINT PK_CUSTOMERS PRIMARY KEY CLUSTERED ( CUSTOMERID ) ON DS_CUST_FG GO CREATE UNIQUE INDEX IX_CUST_UN_PW ON CUSTOMERS ( USERNAME, PASSWORD ) ON DS_IND_FG GO CREATE INDEX IX_CUST_HIST_CUSTOMERID ON CUST_HIST ( CUSTOMERID ) ON DS_IND_FG GO CREATE INDEX IX_CUST_HIST_CUSTOMERID_PRODID ON CUST_HIST ( CUSTOMERID ASC, PROD_ID ASC ) ON DS_IND_FG GO ALTER TABLE CUST_HIST ADD CONSTRAINT FK_CUST_HIST_CUSTOMERID FOREIGN KEY (CUSTOMERID) REFERENCES CUSTOMERS (CUSTOMERID) ON DELETE CASCADE GO ALTER TABLE ORDERS ADD CONSTRAINT PK_ORDERS PRIMARY KEY CLUSTERED ( ORDERID ) ON DS_ORDERS_FG GO CREATE INDEX IX_ORDER_CUSTID ON ORDERS ( CUSTOMERID ) ON DS_IND_FG GO ALTER TABLE ORDERLINES ADD CONSTRAINT PK_ORDERLINES PRIMARY KEY CLUSTERED ( ORDERID, ORDERLINEID ) ON DS_ORDERS_FG GO ALTER TABLE ORDERLINES ADD CONSTRAINT FK_ORDERID FOREIGN KEY (ORDERID) REFERENCES ORDERS (ORDERID) ON DELETE CASCADE GO ALTER TABLE INVENTORY ADD CONSTRAINT PK_INVENTORY PRIMARY KEY CLUSTERED ( PROD_ID ) ON DS_MISC_FG GO ALTER TABLE PRODUCTS ADD CONSTRAINT PK_PRODUCTS PRIMARY KEY CLUSTERED ( PROD_ID ) ON DS_MISC_FG GO CREATE INDEX IX_PROD_PRODID ON PRODUCTS ( PROD_ID ASC ) INCLUDE (TITLE) ON DS_IND_FG GO CREATE INDEX IX_PROD_PRODID_COMMON_PRODID ON PRODUCTS ( PROD_ID ASC, COMMON_PROD_ID ASC ) INCLUDE (TITLE, ACTOR) ON DS_IND_FG GO CREATE INDEX IX_PROD_SPECIAL_CATEGORY_PRODID ON PRODUCTS ( SPECIAL ASC, CATEGORY ASC, PROD_ID ASC ) INCLUDE (TITLE, ACTOR, PRICE, COMMON_PROD_ID) ON DS_IND_FG GO EXEC sp_fulltext_database 'enable' EXEC sp_fulltext_catalog 'FULLTEXTCAT_DSPROD', 'create', '/var/opt/mssql/data/' --Added a placeholder by GSK EXEC sp_fulltext_table 'PRODUCTS', 'create', 'FULLTEXTCAT_DSPROD', 'PK_PRODUCTS' EXEC sp_fulltext_column 'PRODUCTS', 'ACTOR', 'add' EXEC sp_fulltext_column 'PRODUCTS', 'TITLE', 'add' EXEC sp_fulltext_table 'PRODUCTS', 'activate' EXEC sp_fulltext_catalog 'FULLTEXTCAT_DSPROD', 'start_full' GO CREATE INDEX IX_PROD_CATEGORY ON PRODUCTS ( CATEGORY ) ON DS_IND_FG GO CREATE INDEX IX_PROD_SPECIAL ON PRODUCTS ( SPECIAL ) ON DS_IND_FG GO CREATE STATISTICS stat_cust_cctype_username ON CUSTOMERS(CREDITCARDTYPE, USERNAME) GO CREATE STATISTICS stat_cust_cctype_customerid ON CUSTOMERS(CREDITCARDTYPE, CUSTOMERID) GO CREATE STATISTICS stat_prod_prodid_special ON PRODUCTS(PROD_ID, SPECIAL) GO CREATE STATISTICS stat_prod_category_prodid ON PRODUCTS(CATEGORY, PROD_ID) GO -- sqlserverds2_create_sp.sql -- NEW_CUSTOMER USE DS2 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'NEW_CUSTOMER' AND type = 'P') DROP PROCEDURE NEW_CUSTOMER GO USE DS2 GO CREATE PROCEDURE NEW_CUSTOMER ( @firstname_in VARCHAR(50), @lastname_in VARCHAR(50), @address1_in VARCHAR(50), @address2_in VARCHAR(50), @city_in VARCHAR(50), @state_in VARCHAR(50), @zip_in INT, @country_in VARCHAR(50), @region_in TINYINT, @email_in VARCHAR(50), @phone_in VARCHAR(50), @creditcardtype_in TINYINT, @creditcard_in VARCHAR(50), @creditcardexpiration_in VARCHAR(50), @username_in VARCHAR(50), @password_in VARCHAR(50), @age_in TINYINT, @income_in INT, @gender_in VARCHAR(1) ) AS IF (SELECT COUNT(*) FROM CUSTOMERS WHERE USERNAME=@username_in) = 0 BEGIN INSERT INTO CUSTOMERS ( FIRSTNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, REGION, EMAIL, PHONE, CREDITCARDTYPE, CREDITCARD, CREDITCARDEXPIRATION, USERNAME, PASSWORD, AGE, INCOME, GENDER ) VALUES ( @firstname_in, @lastname_in, @address1_in, @address2_in, @city_in, @state_in, @zip_in, @country_in, @region_in, @email_in, @phone_in, @creditcardtype_in, @creditcard_in, @creditcardexpiration_in, @username_in, @password_in, @age_in, @income_in, @gender_in ) SELECT @@IDENTITY END ELSE SELECT 0 GO -- LOGIN USE DS2 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'LOGIN' AND type = 'P') DROP PROCEDURE LOGIN GO USE DS2 GO CREATE PROCEDURE LOGIN ( @username_in VARCHAR(50), @password_in VARCHAR(50) ) AS DECLARE @customerid_out INT SELECT @customerid_out=CUSTOMERID FROM CUSTOMERS WHERE USERNAME=@username_in AND PASSWORD=@password_in IF (@@ROWCOUNT > 0) BEGIN SELECT @customerid_out SELECT derivedtable1.TITLE, derivedtable1.ACTOR, PRODUCTS_1.TITLE AS RelatedPurchase FROM (SELECT PRODUCTS.TITLE, PRODUCTS.ACTOR, PRODUCTS.PROD_ID, PRODUCTS.COMMON_PROD_ID FROM CUST_HIST INNER JOIN PRODUCTS ON CUST_HIST.PROD_ID = PRODUCTS.PROD_ID WHERE (CUST_HIST.CUSTOMERID = @customerid_out)) AS derivedtable1 INNER JOIN PRODUCTS AS PRODUCTS_1 ON derivedtable1.COMMON_PROD_ID = PRODUCTS_1.PROD_ID END ELSE SELECT 0 GO USE DS2 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'BROWSE_BY_CATEGORY' AND type = 'P') DROP PROCEDURE BROWSE_BY_CATEGORY GO USE DS2 GO CREATE PROCEDURE BROWSE_BY_CATEGORY ( @batch_size_in INT, @category_in INT ) AS SET ROWCOUNT @batch_size_in SELECT * FROM PRODUCTS WHERE CATEGORY=@category_in and SPECIAL=1 SET ROWCOUNT 0 GO USE DS2 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'BROWSE_BY_ACTOR' AND type = 'P') DROP PROCEDURE BROWSE_BY_ACTOR GO USE DS2 GO CREATE PROCEDURE BROWSE_BY_ACTOR ( @batch_size_in INT, @actor_in VARCHAR(50) ) AS SET ROWCOUNT @batch_size_in SELECT * FROM PRODUCTS WITH(FORCESEEK) WHERE CONTAINS(ACTOR, @actor_in) SET ROWCOUNT 0 GO USE DS2 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'BROWSE_BY_TITLE' AND type = 'P') DROP PROCEDURE BROWSE_BY_TITLE GO USE DS2 GO CREATE PROCEDURE BROWSE_BY_TITLE ( @batch_size_in INT, @title_in VARCHAR(50) ) AS SET ROWCOUNT @batch_size_in SELECT * FROM PRODUCTS WITH(FORCESEEK) WHERE CONTAINS(TITLE, @title_in) SET ROWCOUNT 0 GO USE DS2 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'PURCHASE' AND type = 'P') DROP PROCEDURE PURCHASE GO USE DS2 GO CREATE PROCEDURE PURCHASE ( @customerid_in INT, @number_items INT, @netamount_in MONEY, @taxamount_in MONEY, @totalamount_in MONEY, @prod_id_in0 INT = 0, @qty_in0 INT = 0, @prod_id_in1 INT = 0, @qty_in1 INT = 0, @prod_id_in2 INT = 0, @qty_in2 INT = 0, @prod_id_in3 INT = 0, @qty_in3 INT = 0, @prod_id_in4 INT = 0, @qty_in4 INT = 0, @prod_id_in5 INT = 0, @qty_in5 INT = 0, @prod_id_in6 INT = 0, @qty_in6 INT = 0, @prod_id_in7 INT = 0, @qty_in7 INT = 0, @prod_id_in8 INT = 0, @qty_in8 INT = 0, @prod_id_in9 INT = 0, @qty_in9 INT = 0 ) AS DECLARE @date_in DATETIME, @neworderid INT, @item_id INT, @prod_id INT, @qty INT, @cur_quan INT, @new_quan INT, @cur_sales INT, @new_sales INT SET DATEFORMAT ymd SET @date_in = GETDATE() --SET @date_in = '2005/10/31' BEGIN TRANSACTION -- CREATE NEW ENTRY IN ORDERS TABLE INSERT INTO ORDERS ( ORDERDATE, CUSTOMERID, NETAMOUNT, TAX, TOTALAMOUNT ) VALUES ( @date_in, @customerid_in, @netamount_in, @taxamount_in, @totalamount_in ) SET @neworderid = @@IDENTITY -- ADD LINE ITEMS TO ORDERLINES SET @item_id = 0 WHILE (@item_id < @number_items) BEGIN SELECT @prod_id = CASE @item_id WHEN 0 THEN @prod_id_in0 WHEN 1 THEN @prod_id_in1 WHEN 2 THEN @prod_id_in2 WHEN 3 THEN @prod_id_in3 WHEN 4 THEN @prod_id_in4 WHEN 5 THEN @prod_id_in5 WHEN 6 THEN @prod_id_in6 WHEN 7 THEN @prod_id_in7 WHEN 8 THEN @prod_id_in8 WHEN 9 THEN @prod_id_in9 END SELECT @qty = CASE @item_id WHEN 0 THEN @qty_in0 WHEN 1 THEN @qty_in1 WHEN 2 THEN @qty_in2 WHEN 3 THEN @qty_in3 WHEN 4 THEN @qty_in4 WHEN 5 THEN @qty_in5 WHEN 6 THEN @qty_in6 WHEN 7 THEN @qty_in7 WHEN 8 THEN @qty_in8 WHEN 9 THEN @qty_in9 END SELECT @cur_quan=QUAN_IN_STOCK, @cur_sales=SALES FROM INVENTORY WHERE PROD_ID=@prod_id SET @new_quan = @cur_quan - @qty SET @new_sales = @cur_Sales + @qty IF (@new_quan < 0) BEGIN ROLLBACK TRANSACTION SELECT 0 RETURN END ELSE BEGIN UPDATE INVENTORY SET QUAN_IN_STOCK=@new_quan, SALES=@new_sales WHERE PROD_ID=@prod_id INSERT INTO ORDERLINES ( ORDERLINEID, ORDERID, PROD_ID, QUANTITY, ORDERDATE ) VALUES ( @item_id + 1, @neworderid, @prod_id, @qty, @date_in ) INSERT INTO CUST_HIST ( CUSTOMERID, ORDERID, PROD_ID ) VALUES ( @customerid_in, @neworderid, @prod_id ) SET @item_id = @item_id + 1 END END COMMIT SELECT @neworderid GO --Added by GSK Create Login and then add users and their specific roles for database USE [master] GO IF NOT EXISTS(SELECT name FROM sys.server_principals WHERE name = 'ds2user') BEGIN CREATE LOGIN [ds2user] WITH PASSWORD=N'', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF EXEC master..sp_addsrvrolemember @loginame = N'ds2user', @rolename = N'sysadmin' USE [DS2] CREATE USER [ds2DS2user] FOR LOGIN [ds2user] USE [DS2] EXEC sp_addrolemember N'db_owner', N'ds2DS2user' USE [master] CREATE USER [ds2masteruser] FOR LOGIN [ds2user] USE [master] EXEC sp_addrolemember N'db_owner', N'ds2masteruser' USE [model] CREATE USER [ds2modeluser] FOR LOGIN [ds2user] USE [model] EXEC sp_addrolemember N'db_owner', N'ds2modeluser' USE [msdb] CREATE USER [ds2msdbuser] FOR LOGIN [ds2user] USE [msdb] EXEC sp_addrolemember N'db_owner', N'ds2msdbuser' USE [tempdb] CREATE USER [ds2tempdbuser] FOR LOGIN [ds2user] USE [tempdb] EXEC sp_addrolemember N'db_owner', N'ds2tempdbuser' END GO The playbook fails. TASK [microsoft.sql.server : Input results/load_tables.sql with the sqlcmd command] *** fatal: [isvqe-02]: FAILED! => {"changed": false, "cmd": ["/opt/mssql-tools/bin/sqlcmd", "-U", "sa", "-P", "redhat123!", "-i", "/tmp/ansible.a5ipubm_", "-b"], "delta": "0:00:00.046361", "end": "2022-08-29 11:37:30.935507", "msg": "non-zero return code", "rc": 1, "start": "2022-08-29 11:37:30.889146", "stderr": "", "stderr_lines": [], "stdout": "Changed database context to 'DS2'.\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\nChanged database context to 'DS2'.\nDBCC execution completed. If DBCC printed error messages, contact your system administrator.\nDBCC execution completed. If DBCC printed error messages, contact your system administrator.\nChanged database context to 'DS2'.\nChanged database context to 'DS2'.\nMsg 4860, Level 16, State 1, Server isvqe-02, Line 1\nCannot bulk load. The file \"/ds2/data_files/cust/us_cust.csv\" does not exist or you don't have file access rights.", "stdout_lines": ["Changed database context to 'DS2'.", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "Changed database context to 'DS2'.", "DBCC execution completed. If DBCC printed error messages, contact your system administrator.", "DBCC execution completed. If DBCC printed error messages, contact your system administrator.", "Changed database context to 'DS2'.", "Changed database context to 'DS2'.", "Msg 4860, Level 16, State 1, Server isvqe-02, Line 1", "Cannot bulk load. The file \"/ds2/data_files/cust/us_cust.csv\" does not exist or you don't have file access rights."]} fatal: [isvqe-01]: FAILED! => {"changed": false, "cmd": ["/opt/mssql-tools/bin/sqlcmd", "-U", "sa", "-P", "redhat123!", "-i", "/tmp/ansible.rjtzobys", "-b"], "delta": "0:00:53.984980", "end": "2022-08-29 11:38:24.812649", "msg": "non-zero return code", "rc": 1, "start": "2022-08-29 11:37:30.827669", "stderr": "", "stderr_lines": [], "stdout": "Changed database context to 'DS2'.\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\n\n(1 rows affected)\nChanged database context to 'DS2'.\nDBCC execution completed. If DBCC printed error messages, contact your system administrator.\nDBCC execution completed. If DBCC printed error messages, contact your system administrator.\nChanged database context to 'DS2'.\nChanged database context to 'DS2'.\n\n(2000000 rows affected)\n\n(2000000 rows affected)\n\n(200000 rows affected)\n\n(200000 rows affected)\n\n(200000 rows affected)\n\n(200000 rows affected)\n\n(200000 rows affected)\n\n(200000 rows affected)\n\n(200000 rows affected)\n\n(200000 rows affected)\n\n(200000 rows affected)\n\n(200000 rows affected)\n\n(200000 rows affected)\n\n(200000 rows affected)\n\n(1000853 rows affected)\n\n(1000491 rows affected)\n\n(1001483 rows affected)\n\n(1000176 rows affected)\n\n(1000785 rows affected)\n\n(1000740 rows affected)\n\n(1000422 rows affected)\n\n(997819 rows affected)\n\n(999831 rows affected)\n\n(997938 rows affected)\n\n(998938 rows affected)\n\n(1001161 rows affected)\n\n(1000853 rows affected)\n\n(1000491 rows affected)\n\n(1001483 rows affected)\n\n(1000176 rows affected)\n\n(1000785 rows affected)\n\n(1000740 rows affected)\n\n(1000422 rows affected)\n\n(997819 rows affected)\n\n(999831 rows affected)\n\n(997938 rows affected)\n\n(998938 rows affected)\n\n(1001161 rows affected)\n\n(20000 rows affected)\n\n(20000 rows affected)\nChanged database context to 'DS2'.\nMsg 7609, Level 17, State 5, Server isvqe-01, Line 1\nFull-Text Search is not installed, or a full-text component cannot be loaded.", "stdout_lines": ["Changed database context to 'DS2'.", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "", "(1 rows affected)", "Changed database context to 'DS2'.", "DBCC execution completed. If DBCC printed error messages, contact your system administrator.", "DBCC execution completed. If DBCC printed error messages, contact your system administrator.", "Changed database context to 'DS2'.", "Changed database context to 'DS2'.", "", "(2000000 rows affected)", "", "(2000000 rows affected)", "", "(200000 rows affected)", "", "(200000 rows affected)", "", "(200000 rows affected)", "", "(200000 rows affected)", "", "(200000 rows affected)", "", "(200000 rows affected)", "", "(200000 rows affected)", "", "(200000 rows affected)", "", "(200000 rows affected)", "", "(200000 rows affected)", "", "(200000 rows affected)", "", "(200000 rows affected)", "", "(1000853 rows affected)", "", "(1000491 rows affected)", "", "(1001483 rows affected)", "", "(1000176 rows affected)", "", "(1000785 rows affected)", "", "(1000740 rows affected)", "", "(1000422 rows affected)", "", "(997819 rows affected)", "", "(999831 rows affected)", "", "(997938 rows affected)", "", "(998938 rows affected)", "", "(1001161 rows affected)", "", "(1000853 rows affected)", "", "(1000491 rows affected)", "", "(1001483 rows affected)", "", "(1000176 rows affected)", "", "(1000785 rows affected)", "", "(1000740 rows affected)", "", "(1000422 rows affected)", "", "(997819 rows affected)", "", "(999831 rows affected)", "", "(997938 rows affected)", "", "(998938 rows affected)", "", "(1001161 rows affected)", "", "(20000 rows affected)", "", "(20000 rows affected)", "Changed database context to 'DS2'.", "Msg 7609, Level 17, State 5, Server isvqe-01, Line 1", "Full-Text Search is not installed, or a full-text component cannot be loaded."]} File permissions shouldn't be a problem. [root@isvqe-01 Certification]# ls -lha /ds2/data_files/cust/us_cust.csv -rwxrwxrwx. 1 root root 304M Aug 29 11:34 /ds2/data_files/cust/us_cust.csv The same commands work from the command line until it hits the full text search commands. [root@isvqe-01 Certification]# /opt/mssql-tools/bin/sqlcmd -U sa -P redhat123! -i results/create_db.sql -b [root@isvqe-01 Certification]# /opt/mssql-tools/bin/sqlcmd -U sa -P redhat123! -i results/load_tables.sql -b Changed database context to 'DS2'. (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) ... Changed database context to 'DS2'. Msg 7609, Level 17, State 5, Server isvqe-01, Line 1 Full-Text Search is not installed, or a full-text component cannot be loaded. Restarting the service fixes that. [root@isvqe-01 Certification]# systemctl restart mssql-server.service [root@isvqe-01 Certification]# /opt/mssql-tools/bin/sqlcmd -U sa -P redhat123! -i results/create_db.sql -b [root@isvqe-01 Certification]# /opt/mssql-tools/bin/sqlcmd -U sa -P redhat123! -i results/load_tables.sql -b Changed database context to 'DS2'. (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) ... (20000 rows affected) Changed database context to 'DS2'. Changed database context to 'DS2'. Changed database context to 'DS2'. Changed database context to 'DS2'. Changed database context to 'DS2'. Changed database context to 'DS2'. [root@isvqe-01 Certification]# I got the latest rpm to work. [root@isvqe-01 Certification]# rpm -qa | grep ansible-collection-microsoft-sql ansible-collection-microsoft-sql-1.2.4-1.el8.noarch I used the following yml. [root@isvqe-01 Certification]# cat site.yml --- - hosts: all vars: mssql_version: 2019 mssql_accept_microsoft_odbc_driver_17_for_sql_server_eula: true mssql_accept_microsoft_cli_utilities_for_sql_server_eula: true mssql_accept_microsoft_sql_server_standard_eula: true mssql_password: redhat123! mssql_edition: Developer mssql_enable_sql_agent: true mssql_install_fts: true mssql_enable_ha: true pre_tasks: - name: Set facts to create a test DB on primary as a pre task set_fact: mssql_pre_input_sql_file: - results/create_db.sql mssql_post_input_sql_file: - results/load_tables.sql when: mssql_ha_replica_type == 'primary' roles: - role: microsoft.sql.server I used the same sql code from the previous comment. Since the problem described in this bug report should be resolved in a recent advisory, it has been closed with a resolution of ERRATA. For information on the advisory (ansible-collection-microsoft-sql bug fix and enhancement update), and where to find the updated files, follow the link below. If the solution does not work for you, open a new bug report. https://access.redhat.com/errata/RHBA-2022:7597 |