SQL Server Database ACME_POS Installation for the book “Oracle Warehouse Builder Getting Started 11g”
About
This article refer to the creation of the database ACME_POS in a SQL Server and the load of the data set that you need to perform the tutorial of the book : Oracle Warehouse Builder 11g: Getting Started (Paperback) by Robert Griesemer.
Prerequisites
- Download the ACME_POS database scripts from the Packt web site.
The original instructions can also be found in the files ending with the term instruction.
Steps
Start SQL Server Management Studio application as an administrative user
Verify that you launch the SQL Server Management Studio application connect as an administrative user with the “sysadmin” role.
Normally if you choose during the installation the Windows security configuration, you can connect with the same user or you must perform this operations to give the sysadmin server role to your Windows account :
- Launch the Microsoft SQL Server Management Studio application and connect as a sysadmin user. (note: if you're doing these steps, that must mean you have defined a local SQL Server account as the sysadmin user so connect as that account)
- In the SQL Server Management Studio application expand the 'Security' node in the tree.
- Right-click the 'Logins' node and select “New Login…” from the pop-up menu.
- In the resulting dialog, make sure “Windows Authentication” is checked and click on the “Search” button beside the 'Login Name' field.
- In the 'Select User or Group' dialog that appears, click the “Advanced…” button.
- Click on the “Find Now” button in the resulting dialog.
- In the search results window, find your Windows username, click on it and then click on the “OK” button.
- Click on the OK button again in the original “Select User or Group” dialog.
- In the new 'Login' dialog, your user name will now appear in the “Login name:” box.
- Click the “Server Roles” entry in the left window.
- In the list of server roles that appears, make sure the box is checked beside the “sysadmin” role.
- Click on the “OK” button to close the “Login” dialog and add your login name as a “sysadmin” user in the database.
Create the database
- Click on the 'File | Open | File' from the main menu.
- In the resulting file location dialog, navigate to where you have stored these ACME_POS scripts
- Select the file 'acme_pos_db_create_script.sql' and click on 'Open' to open it in the SQL Server Management Studio.
- Click on the 'Execute' button in the toolbar, or click on 'Query | Execute' from the main menu or just press the 'F5' key to run the script.
- This will report “Command(s) completed successfully.” in the messages window.
- To see the database in the 'Object Explorer', click on the 'Databases' node and then refresh. The ACME_POS database will now appear under Databases.
Create the object
Before the next step, as I add this problem Sql Server (see Sql Server section), I advise you to run this script before :
USE [ACME_POS]
GO
CREATE LOGIN [acme_dw_user] WITH PASSWORD=N'acme_dw_user', DEFAULT_DATABASE=[ACME_POS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
- Next step is to run the script to create the objects in the database. Open the SQL script named 'acme_pos_db_objects_script.sql'.
- Execute the script using the same command in database creation step
- That should report “Command(s) completed successfully.” in the message window. Refresh the 'Object Explorer' window to see the objects that have been created under the 'ACME_POS' Tables entry.
Load the data into the tables
- Open a command prompt window and navigate to the folder where you stored the downloaded files from the web site.
- Run the following command to bulk load the data into the database tables:
ACME_POS_Bulk_Load_Data.bat :
bcp ACME_POS.dbo.Employees in Employees.dat -f Employees-n..xml -T -S Localhost\SQLExpress
bcp ACME_POS.dbo.Items in Items.dat -f Items-n..xml -T -S Localhost\SQLExpress
bcp ACME_POS.dbo.Regions in Regions.dat -f Regions-n..xml -T -S Localhost\SQLExpress
bcp ACME_POS.dbo.Registers in Registers.dat -f Registers-n..xml -T -S Localhost\SQLExpress
bcp ACME_POS.dbo.Stores in Stores.dat -f Stores-n..xml -T -S Localhost\SQLExpress
bcp ACME_POS.dbo.Vendors in Vendors.dat -f Vendors-n..xml -T -S Localhost\SQLExpress
bcp ACME_POS.dbo.POS_Transactions in POS_Transactions.dat -f POS_Transactions-n..xml -T -S Localhost\SQLExpress
Start the script:
C:\ACME_POS_Data_Load_Script_Files>ACME_POS_Bulk_Load_Data.bat
- It will run individual bcp commands for each table to load the data. When it finishes, all the data will be loaded. You can scroll back through the output to verify.
Output :
..................
C:\ACME_POS_Data_Load_Script_Files>bcp ACME_POS.dbo.POS_Transactions in POS_Tran
sactions.dat -f POS_Transactions-n..xml -T -S Localhost\SQLExpress
Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
1000 rows sent to SQL Server. Total sent: 7000
1000 rows sent to SQL Server. Total sent: 8000
1000 rows sent to SQL Server. Total sent: 9000
1000 rows sent to SQL Server. Total sent: 10000
10026 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 406 Average : (24694.58 rows per sec.)
Support
acme_dw_user is not a valid login
Msg 15007, Level 16, State 1, Line 2
'acme_dw_user' is not a valid login or you do not have permission.
If you receive this message, execute this script :
USE [ACME_POS]
GO
CREATE LOGIN [acme_dw_user] WITH PASSWORD=N'acme_dw_user', DEFAULT_DATABASE=[ACME_POS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO