Search

MS SQL's Tags

How to install and connect MS SQL server with PHP in Ubuntu
SQL or MS SQL is relational database server developed by Microsoft. Though MS SQL is not open-source, there is also SQL server 2019 Express free version avaialble for small businesses. In this article, we will go through how to install MS SQL server in Ubuntu. We will also connect MS SQL server with PHP application. prerequisite: You should be logged in Ubuntu system with sudo permission. You should also have PHP installed in server. The system minimum memory should be at least 2 GB. Installation First of all, connect to the server Terminal and add the Microsoft Linux repositories keys. wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - And register the Microsoft SQL Server Ubuntu repository for MS SQL Server 2019: sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)" Run the beow commands to install SQL Server: sudo apt-get update sudo apt-get install -y mssql-server After the mssql-server is installed, setup SQL password for sa user. sudo /opt/mssql/bin/mssql-conf setup It will ask to accept Terms and login password for main user. Now check mssql-server is running. systemctl status mssql-server --no-pager Server tools installation In order to connect with mssql-server, you will also need to install server-tools and ODBC driver. To install mssql-tool, add repository key with below command. curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - And then register Microsoft Ubuntu repository. curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list Now update Ubuntu repository list and install required drivers and repository. sudo apt-get update sudo apt-get install mssql-tools unixodbc-dev Add /opt/mssql-tools/bin/ to your PATH environment variable in a bash shell. echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile To access sqlcmd/bcp bash shell for non-login sessions, add the PATH in the ~/.bashrc file with the below command: echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc SQL server is installed. If you want to connect server with Terminal, run the command: sqlcmd -S localhost -U sa -P '<password>' If everything is ok, it will open sql command promp. Connect with PHP First step is to create database connection with PHP. <?php $serverName = "localhost,1433"; $connectionInfo = array(     "Database"=>"Laravelcode",     "UID"=>"sa",     "PWD"=>"root" ); $conn = sqlsrv_connect($serverName, $connectionInfo); if($conn == false) {     die(print_r(sqlsrv_errors(), true)); } You can use $conn variable to query into SQL server. $sql = "SELECT first_name, last_name, email FROM users"; $sql_query = sqlsrv_query($conn, $sql); if($sql_query === false) {     die(print_r(sqlsrv_errors(), true) ); } $row = sqlsrv_fetch_array($sql_query, SQLSRV_FETCH_ASSOC); while($row) {     echo $row['first_name'].", ".$row['last_name'].", ".$row['email']."<br />"; } sqlsrv_free_stmt($sql_query); This way you can interact PHP application with SQL server.
sqlcmd: command not found.
After installing MS SQL server on your local machine, you might want to connect with MS SQL server with command line. sqlcmd -S localhost -U sa -P "<password>" But instead of connecting with MS SQL server, you might get the error "sqlcmd: command not found." There might be mssql-tools or symlink issue. To resolve this error, use below commands. The first method you should try is check mssql-tools is installed. Run the below command in Terminal. sudo apt-get install mssql-tools If mssql-tools is already installed, you might get below output. Reading package lists... Done Building dependency tree        Reading state information... Done mssql-tools is already the newest version (17.7.1.1-1). 0 upgraded, 0 newly installed, 0 to remove and 7 not upgraded. If it is not already installed, it will start installing. You might still get the error after installation complete. You need to create symlinks. Run the below command to check where mssql-tools/bin/sqlcmd is installed. sudo ls /opt/mssql-tools/bin/sqlcmd* It will output the path something like: /opt/mssql-tools/bin/sqlcmd Now create symlink of the command to /user/bin folder with below command. sudo ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd This option should fix the error and you can now access MS SQL server using that above command.