Migrate/Import entire database from one SQL instance to another

General
5

@QMcKay That is a much more elegant solution, thanks! I’m being forced to dig into SQL management on the fly and I’m learning as I go.

Good point about not knowing the implications of manually modifying individual tables. @emre, would it cause problems if I were to build my InventoryItems table, Recipes table, and MenuItems table manually in excel and import them into the database with management studio, circumventing SambaPOS? I’m trying to find a more efficient way to enter thousands of items other than the POS GUI. I’ve imported 20 rows of inventory items this way, and they appear in SambaPOS normally; I just don’t know if I’ll be disrupting references between tables within SambaPOS.

6

@steet81 if you properly build your data from database side that should work. However since SambaPOS uses an ORM tool inside I even don’t have an idea about what database tables we have or how data stored inside.

7

Thanks @emre. I’m going to try to do my whole product/inventory build from the database side. I’ll report back when I get it up and running.

8

I wrote a bulk importer for V3 a while back http://forum2.sambapos.org/index.php/topic,1591.0.html.
It should work on V4.

9

Thanks @JohnS. I saw your thread a couple of days ago, but I couldn’t figure out how to download your attachment. Maybe I’m totally missing it, but I don’t see a download link on any post on this forum that shows the paper clip attachment icon (I’m new here). But the main reason I didn’t pursue your importer just yet is that I figured I needed to roll up my sleeves and get into SQL to teach myself the hard way. I would like to check out your importer though if you can help me get the file.

10
11

Thanks @JohnS. I just opened load_products.php to take a look and… wow, you’ve put a lot of work into this! I’m not even very familiar with SQL scripting, much less using php. I’m doing all my database editing with the import/export tool in SSMS and Excel, and I’m starting to see how the tables interact with each other not-so-simply in some instances.

12

@steet81, I have just updated the scripts as I found issues running on V4 databases.

13

@JohnS, awesome, thanks for making the changes so quickly! I’ve done all the steps, but I’m getting an error when I try to run the php file (screenshot below). I’m trying to track down the error, and I think it might be the connection string on line 62. I’m running SQL Server 2012, and I’m not sure which Native Client version was installed with that.

41fe1436745cfcb1.PNG1158×217 13.4 KB

edit: I was able to get past the line 62 error by changing the Native Client version number to 11.0, but I just encountered about 40 errors after that (below). Any suggestions?

ffb3f7d144387a37.PNG1152×533 34 KB

14

Looks like you didn’t config the SQL connection properly in the script.

Can you post your Data connection string from SambaPOS and also the SQL settings in the load_products.php script

15

from load_products.php:

$myServer = “LOCALHOST\SAMBAPOS3”;
$myUser = “sa”;
$myPass = “samba”;
$myDB = “SambaPOS4”;

from sambaPOS:

Data source=LOCALHOST\SAMBAPOS3; User Id=sa; Password=samba

Thanks,
Matt

16

And the SambaPOS4 database exists for SQL Instance SAMBAPOS3 ?

I’m running SQL Express 2012 without any issues. The errors are from the fact that the script cannot connect to the database.
You running it on the same computer as SQL Express ?

17

Yeah I didn’t stay consistent with my naming. I’m running everything locally.

18

I’m able to connect now. I should have tried this at the beginning, but I cleared the inventory, products, menus etc. from my database, and I was able to connect and run the php file.

side note: To clear the database, I had to run your “DELETE V3 Prod Inv Recipe MenuItems.sql” file in my own query cause I was getting errors with the batch file.

All of my items seem to have populated correctly when in SambaPOS, but I did receive some errors in the web browser after running the php (cap below). Maybe I can ignore the errors, since the POS seems to be running correctly so far.

c0cd4b496b239199.PNG1245×632 40.6 KB

19

Can you provide a screen shot if the error in the batch file please

20

I just got the batch file to run. I didn’t notice that line 8 of “DELETE V3 All Transactions.bat” says “localhost\SAMBAPOS” instead of “localhost\SAMBAPOS3”. The database is also named import, which I changed to SAMBAPOS4 for my instance.

Maybe line 8 of the batch file should say: SQLCMD -S localhost[SQL INSTANCE NAME] -d [DATABASE NAME] -i V3_del_Prod_Inv_Recipe_MenuItems.sql

and you could include a line in your tutorial to change the [SQL INSTANCE NAME] and [DATABASE NAME] in the batch file.

Thanks for all the help btw!!
Matt

21

Some if the errors in the php script seem to be due to punctuation in the product names.
Make sure you remove all (") (’) quotes, etc from names.

22

Good catch! Now I can run the php with zero errors; I just deleted the (’) from two items in the csv file.

SambaPOS looks just fine on the backend, but I’m running into a problem on the POS end. In the POS, whenever I click on “new customer”, type in a name, and click “select customer”, SambaPOS crashes without an error message.

I do not have this problem whenever I use your csv file, so it might be a formatting issue.

I’ve attached my csv file so maybe you can replicate the problem.

I’m going to try running your script on a completely fresh database in the meantime.

update: problem persists on fresh database.

update: i deleted all but 3 rows of my csv file and the POS does not crash. must be a formatting issue, or an issue with the amount of rows. i’ll update when i track it down.

importcsv.zip (1.8 KB)

23

Do you use commas (,) in your prices ?

24

Barcode should be blank. You cannot have duplicates.
InventoryQty should be your starting stock level
CostPrice can be zero or blank