Microsoft RMS and SQL Server 2000 (MSDE) or Server

The RMS db is just another SQL Server Database, so any techniques that you are familiar with in SQL Server are certainly feasible. I'm not sure Replication makes sense for a data load - I'd lean more toward DTS, but whatever technique you like...

The primary tables required to load items are: Item Supplier SupplierList (All suppliers for the item get an entry in this table - the supplier in Item.SupplierID is the "Primary supplier") Department Category ItemTax

Thanks Glenn,

My knowledge of SQL is from about 10 years ago and I had no clue that Data Transformation Service was so powerful. After I read your post, I called for support and a Microsoft SQL Server Engineer walked me through loading data from an excel spreadsheet directly into the 'item' table. There were about

4500 items that loaded sucessfully.

Here is a portion of the transcript the engineer created to document the approach.

I believe this is a safe procedure because columns that are declared "not null" will have a default data value of the correct type inserted in each case where the source excel data is empty or null. Glen, thanks for your tip. If you or other RMS gurus see a problem with RMS database/product integrity due to this technique, please advise.

Thanks again for a terrific pointer. Kent Smith Consulting Systems Engineer Columbus Park, LLC

Here is the transcript... ===============================As we discussed, the issue you're experiencing is that you wanted to import data from Excel to SQL Server. The data will be coming in from different excel files with different columns which will be consolidated into a single table called Item in SQL server. Once we resolve this issue we will consider this support incident completed and closed. We'll be working to resolve this specific issue through the course of the case. If I have misunderstood any aspect of the issue, please let me know.

Criteria for Resolution:

To successfully import data from Excel file to SQL server that acts as a back end database for retail management server.

SUPPORT INCIDENT: An incident is a problem that cannot be broken down into subordinate problems. For a request with subordinate problems, each problem is considered a separate incident, and therefore must be submitted as a separate support request.

Right now, we have agreed to do the following:

I have delivered you the solution and you will be checking on it?s feasibility for the next two days. Here is a summary of the steps we took to create the package.

1 > Open up SQL server Enterprise Manager. Select your database , right click select All Tasks> Import data.

2> In the wizard select Data Source as Microsoft Excel 97-2000. Specify the input excel file.

3> For the destination server select the SQL Server database where you will be importing the data.

4> Select Copy tables and views from the source database option. Click Next.

5> Select the table (Item) Click next. (Make sure you remove the $ sign else the table will be created with the $ sign.

6> If you are creating the item table for the first time select Run Immediately and Save the DTS package.

7> If the table is already present, do not select run immediately option, Save the package and exit of the wizard.

8> Go to the folder Data Transformation Services > Local Pacakages. Open the package that we just saved.

9> Remove the Create table task from the designer. Select the Data Pump Task between the SQL and Excel connection.

10> Here you can Modify the column mappings from source to destination.

Also you wanted to know how can you insert data into a column which does not allow NULL value. I informed you that we cannot issue a insert statement to a table and skip the column value if it is defined as NOT NULL. The work around I suggested was modifying the table schema to allow NULLS and also if necessary supply a default value to be inserted in case NULL value is not acceptable.

Also the column named ID which was the primary key needed to be incremental for each insert statement. I guided you to convert the datatype of this coulmn to Identity.

==================================

"Glenn Adams [MVP - Retail Mgmt]" wrote:

Hi Glenn and Kent, I am from the Microsoft SQL Server Support team who did support Kent on the RMS solution with DTS . I should thank you guys for redirecting the SQL team to look into RMS which can potentially provide solutions for RMS using DTS.

Thanks aga> Thanks Glenn,

Hi Rayan

I saw your posting under (Re: Microsoft RMS and SQL Server 2000 (MSDE) or Server) on the MSRMS NG. I have been discussing with two different VARs who are competing to provide us with a connector for plugging our webstores onto the MS RMS DB, and their opinions in regards to the use of DTS in this application differ. Would you care to read about their positions and weigh in your .2?

Thanks, Eddie B

"Rayan" wrote:

import

Their concerns are security (port 1433 needs to be open to the internet OR if you tunnel that port the VPN layer becomes an extra point of failure) and scalability (taking the business logic out of SQL server allows the whole process to scale better).

Eddie B

"Rayan" wrote:

import

If you ask two more VARs, you'll probably get two more completely different approaches to the problem. Look at the features and usability of the solution more than the underlying technology - there are many ways to approach this solution and trying to pick the "best" approach is almost meaningless - any of them can succeed or fail. Really the most important thing is to get a solution that's already working for others if possible...

If I am not mistaken the webstore will connect to the SQL server which obviously requires port 1433 to be open so that the web application connect to the SQL server. DTS does not explicity require the port to be opened unless you are executing the package from a remote location..

Moreover using windows authentication for your DTS you can force extra security through Kerberos so that all the packets sent across are encrypted.

Regarding scalability I guess you need to ret hink on what scalability you are looking for because from the post it looks like you are going to import data from webstore which can be easily taken care by DTS. I agree that business logic should be outside SQL server , but importing data is not a business logic moreover it is a task which will be best if integrated with SQL.

I strongly recommend you to go through Books Online for SQL and browse through the DTS functionality provided in SQL.

Thanks Ryan. I agree with what you say and i believe there is an added advantage to using DTS: it makes it a lot easier to modify the connector to integrate with other DBs.

Eddie B

"Rayan" wrote:

Is it also possible to write a script using php that will sync the SQL database running my RMS (POS) to my online SQL database that runs my ecommerce cart? I dont need the solution, I can pay my programmer, I just need to know if it is possible so I can feel confident about paying him development time to get the two databases sync'd.

Thanks Dax

Dax,

Sure. Go into an item and on the Options tab, checkmark the box that says Item is available on website. Have your programmer look at the Webitem column in the Item table.