Microsoft SQL Server 2019 Express Docker image Example

Hi Folks,

Today I am going to explain the procedure for connecting to a Microsoft SQL Server 2019 Express edition docker image

Pre-requisites

1. Docker Desktop
2. Windows OS
3. Powershell/command prompt

Procedure

Get and run docker image

> docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<Your_Password>" -e "MSSQL_PID=Express" --name "<Your_SQL_Server_Name>" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

Command information

Remove the "MSSQL_PID=Express" to run other version of SQL Server

Replace 2019 with required SQL Server version

Password should be atleast 8 characters with capital, small, numeric, special character combination

Use different port if you already have a local sql server

Name should not contain spaces

Check for docker container

> docker ps

You should able to see the container with your SQL Server name at the end in running status

Connect to SQL Server

docker exec -it "<Your_SQL_Server_Name>" /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<Your_Password>"

You should be able to see "1>" prompt

Command information

Use the SQL Server name or user the container ID

Use database and play around with table(s)

Important

Multiple commands can be entered one after one, but to execute the set of command(s), you need give "GO" command.

Create Database

CREATE DATABASE SampleDB

GO

List all databases

SELECT Name from sys.Databases

GO

Start using the database

USE SampleDB

GO

Create table

CREATE TABLE UserInfo ( Id INT, Name VARCHAR(64))

GO

Insert values

INSERT INTO UserInfo (1, 'ABC')

GO

Retrieve table contents

SELECT * FROM UserInfo

GO

Exit from SQL Server

QUIT

Hope you are able to run the SQL Server Docker image.

Please send your feedback and comments to psrdotcom@gmail.com

Bulk import of csv, text file data into MS SQLServer 2012

Hi friends,

Today we have done bulk import of data using csv/txt format into MS SQLServer 2012.

Procedure

1. Right click on your database and select “Tasks -> Import Data”
2. Click the “Next>” button
3. For the Data Source, select “Flat File Source”.
4. Browse and select the CSV/TXT file
5. For the Destination, select the correct database provider (e.g. for SQL Server 2012, you can use SQL Server Native Client 11.0).  Enter the Server name; check Use SQL Server Authentication, enter the User name, Password, and Database before clicking on the Next > button. 
6. Change the options like delimiter, carriage return(new line), First row as header
7. Spend some time configuring how you want the data to be imported before clicking on the Next > button.
8. Change the table name
9. Change the columns datatype
10. Preview your table
11. On the Select Source Tables and Views window, you can Edit Mappings before clicking on the Next > button
12. Check Run immediately and click on the Next > button. 
13. Click on the "Finish" button to run the package
14. You data with the table will be selected

Enjoy the simple and useful bulk data import.

Please send your feedback and comments to psrdotcom@gmail.com

Blogger Labels: Bulk,text,data,SQLServer,Procedure,database,Tasks,Import,Click,Source,Flat,File,Browse,Change,options,carriage,header,Spend,columns,Preview,Finish,Enjoy

Manually Adding Dependency in Spring MicroSoft SQLServer JDBC Driver

Hi Friends,

Today, I’ve spent more than 40 minutes in understanding the process and setting up the SQLServer JDBC Driver in Spring manually

Pre-requisites

Maven (Download from http://maven.apache.org/download.cgi and add the maven path to environment variables)

Procedure

1. Download the latest Microsoft JDBC driver for SQL Server from the following official link http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774
2. I’ve selected sqljdbc jar version 4.0
3. Navigate to the extracted folder where .jar files available in command prompt
4. Execute the following command
5. cmd> mvn install:install-file -DgroupId=com.microsoft.sqlserver.jdbc -DartifactId=sqljdbc -Dversion=4.0 -Dpackaging=jar -Dfile="sqljdbc4.jar"
    
6. Now you should be able to see the downloading and moving the corresponding .jar and .pom files. Then a message “BUILD SUCCESS” will appear in command prompt.
7. Add the following dependency to your pom.xml

<!-- Database -->
<dependency>
<groupId>com.microsoft.sqlserver.jdbc</groupId>
<agtifactId>sqljdbc</agtifactId>
<version>4.0</version>
</dependency>

Now you run the project and enjoy the usage of SQLServer Database with Spring.

Please send your feedback and comments to psrdotcom@gmail.com

Blogger Labels: Dependency,MicroSoft,SQLServer,JDBC,Driver,Friends,Procedure,Download,Server,version,Navigate,folder,Execute,DgroupId,DartifactId,Dversion,Dfile,message,BUILD,Database,usage,feedback,sqljdbc,groupId,artifactId