SQL Express 2014 Installation - [BONUS] How to Convert from CE to SQL Express

Jesse · September 8, 2015, 12:57pm

Microsoft SQL Express 2014

[SQL Express 2014 Download Link][1]

Versions Available

Express 32BIT WoW64\SQLEXPR32_x86_ENU.exe
Express 32BIT\SQLEXPR_x86_ENU.exe
Express 64BIT\SQLEXPR_x64_ENU.exe
ExpressAdv 32BIT\SQLEXPRADV_x86_ENU.exe
ExpressAdv 64BIT\SQLEXPRADV_x64_ENU.exe
ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe
ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe
LocalDB 32BIT\SqlLocalDB.msi
LocalDB 64BIT\SqlLocalDB.msi
- MgmtStudio 32BIT\SQLManagementStudio_x86_ENU.exe
- MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe

:heavy_exclamation_mark: Last 2 versions are not the actual SQL Express Database they are only the Management Studio Tool used to browse and administer the Database. This tool is included in the ExpressADV and the ExpressAndTools versions.

Versions Recommended

ExpressAdv 32BIT\SQLEXPRADV_x86_ENU.exe
ExpressAdv 64BIT\SQLEXPRADV_x64_ENU.exe
ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe
ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe

Version and OS Used For Tutorial

ExpressAdv 64BIT\SQLEXPRADV_x64_ENU.exe
Windows 10

##SQL Express 2014 Advanced 64bit Installation##

It will run various checks to ensure it can install correctly if it finds a service or windows feature not installed that it needs it will recommend you install the windows feature before it will continue.

.net framework is the most common Windows Feature it may tell you to enable.

###New Installation / Add Features

Choose New Installation or Add features to existing install. For this example we are performing a new installation.

###Accept the License terms

###Installation options

The options chosen are all you need for SambaPOS. You can read about the other options if you are curious but they are not required.

Reporting Services is useful if you have more advanced knowledge of SQL; if not, you can de-select this and it will not affect SambaPOS functionality in any way. SQL Reporting Services has nothing to do with SambaPOS Reporting features.

Management Tools is not required but highly recommended so it is selected. Choose the instance directory: Default is fine.

You will notice it lists Prerequisites for selected installation features. These are typically just Windows Features you can install through control panel / programs.

###Instance Configuration

You can choose default instance (SQLEXPRESS) or rename it to whatever you want.

The Instance is not the same as the Database. Do not confuse the 2 terms. You can choose to have many Databases running under a single Instance.

###Service Accounts

This tells it what to name the service that it runs as in the background and how it should start up. You should leave this default.

###Authentication mode and User Accounts

For Database Engine Configuration you can leave everything default and allow Windows authentication mode it will run just fine.

If you plan to run multiple Terminals, it is recommended to enable Mixed-mode Authentication. In that case, you need to set a password for the administrative account. The initial admin account is named sa and you can set the password to whatever you like. We recommend sambapos as the password. You can optionally set up more admin users if you wish, but there is rarely a need to do so.

This concludes the installation you now have SQL Express 2014 installed correctly and ready for SambaPOS to connect to it.

###Testing the Installation

Lets open up Management Tools and test our installation. This program should be installed if you followed the above Tutorial steps. SQL Server Management Studio (or SSMS as it is often referred to as) is the tool we use to check if everything installed properly.

Jesse · September 8, 2015, 11:48pm

Screen shots for exporting SQL CE data to SQL Server Express.

Download SQL Server Compact Toolbox tool from http://sqlcetoolbox.codeplex.com/.

Visit http://sqlcetoolbox.codeplex.com/releases and choose Standalone Version 4.0.

Right click on left pane and choose Add SQL Server Compact 4.0 Connection... menu item.

A toolbox window will appear. Click Browse button to choose your sdf file.

Under [MyDocuments]\SambaPOS4 folder find SambaPOS4.sdf file or the sdf file you’re actually using.

Click Test Connection button. If it succeeds click Close button.

SambaPOS4.sdf file should appear here.

Right Click on SambaPOS4.sdf and choose Script > Script Database Schema and Data menu item.

File Save dialog will appear. Enter a name to the export file (I’ve named is MySambaPOSData) and click Save button.

Table Selection Dialog will appear. Don’t change anything on this screen and click OK. __MigrationHistory table is unselected by default and there is no need to export it.

Few seconds later export will complete and you’ll see this confirmation message.

Tool Created a file named as MySambaPOSData.sqlce. This file contains required SQL scripts to recreate your database under SQL Express. If you edit that file with a text editor like notepad you should see some auto generated SQL code similar to that.

Open SQL Management Studio. Create new Database, Create new Query, Copy & Paste everything that file contains inside Query editing window and execute Query. That will create tables and data.

Jesse · September 8, 2015, 11:49pm

Well what kind of errors? It would help if you can show us the error.

restaurant34 · September 9, 2015, 12:01am

when I executed in management studio it was some errors, like this ( one information down ). I didn’t see. I don’t know if the way I did was the good one, because i copy file by file from notepad and put in management studio.
when I enter new engine database, then new query?, I don’t know if it created. the name of the file was sqlsomething3.sql
May be because I don’t understand nothing of this , sql, the reason I ask for your help.
You already helped me today, or… many times in this last year. Thanks

greencyber · October 22, 2015, 4:21pm

Hello, I miss the last part…when i open the Management Studio i create a new database and then ,i donn’t know what to do…Plz help me

Jesse · October 22, 2015, 4:25pm

What are you trying to do? if your just installing SQL Express to work with SambaPOs you dont need to create the database SambaPOS will do that for you. If you are converting from CE then can you explain more what you mean because its a very detailed tutorial.

greencyber · October 22, 2015, 4:25pm

i migrate from ce to sqlexpress

Jesse · October 22, 2015, 4:27pm

The tutorial tells you what to do… did you read it?

Did you create the new query and copy and paste all of the file into it and execute the query?

greencyber · October 22, 2015, 4:27pm

i have the file sambapos.sqlce, but i don’t kbnow how to convert to sqlexpress

Jesse · October 22, 2015, 4:28pm

Did you read that part?

greencyber · October 22, 2015, 4:28pm

i miss the part"Create new Query, Copy & Paste everything that file contains inside Query editing window and execute Query. That will create tables and data."

greencyber · October 22, 2015, 4:29pm

i created the new database…

Jesse · October 22, 2015, 4:30pm

So like My screenshot says select your newly created database then click New Query button and copy the content of the file you created and paste it into the query then select execute query.

If it made multiple files then you need to do separate queries for each file. Execute each one.

greencyber · October 22, 2015, 4:32pm

ok, i will try then tell you?thanks!

greencyber · October 22, 2015, 4:36pm

when you said copy the content of the file you created, you means sambapos.sqlcfile?

cawoody1 · January 4, 2016, 10:24pm

Hi, loving the work on this guys…
I have SambaPOS all setup and working all customized.
I am attempting to install SQL to have more control over database backups and to be able to add more terminals. I have installed SQL 2014, when it comes to the SQL server compact toolbox, i right click but Add SQL SERVER COMPACT 4.0 CONNECTION is greyed out, why is this?
I’m starting to give up after 3 days solid on this so your help would be much grateful!! Thank you!!!

Jesse · January 5, 2016, 2:53am

Typically means you don’t have compact SQL engine installed. Install compact SQL then try

eadauto · September 13, 2016, 5:05am

Hi,
I am trying to do a fresh install of sambapos 5 to use SQL express with no luck, I tried:

Install SQL express 2014, install samba pos with no LocalDB options, the result I cant start Samba POS to change the connection string
Install SQL express 2014, install samba pos with LocalDB, the result I cant start Samba POS I get an error of connection string after a while

How should I proceed to do this? On this tutorial it mentions to use certain values but when I run my Sambapos the error it shows refers to other values:

Data Source=SAMBAPOSSERVER\SQLEXPRESS; User Id=sa; Password=sambapos#1; Database=SAMBAPOS5;

Is there a procedure to do this clean install?

Jesse · September 13, 2016, 11:06am

Uninstall SQL Local DB, Delete the sambasettings.txt file from C\ProgramData\SAMBAPOS\SambaPOS5\ (Must Enable show hidden folders to see ProgramData)

Restart SambaPOS and then Enter your connection string.[quote=“eadauto, post:18, topic:6956”]
Data Source=SAMBAPOSSERVER\SQLEXPRESS; User Id=sa; Password=sambapos#1; Database=SAMBAPOS5;
[/quote]

Your 100% sure thats your connection string settings? Your computername is SAMBAPOSSERVER? Your SQL instance you installed was SQLEXPRESS and did you enable the sa user for that instance?

ryantambay08 · March 11, 2017, 2:18am

hello there! just a bit of question did i miss something? at first i open the SQL Server Management Studio it did worked at first and then i closed it, i restart my pc and i opened again the server i started receiving this message…