Personal Finance

RMSrookie · Guest

Is it feasible to use SQL Server Replication or Merge processing to:
1. Do the initial load of a RMS items from a given suppliers
set of 'Item' and 'Suppliers' records?
2. Maintain 'cost' or other data changes via SQL Server Replicaition or
Merge.

3. Using SQL Server (bcp, utilities, etc) to do a correctly matched
import of
records into RMS as part of the initial install.

4. What tables record entries are needed to import bulk records into the
'item' table?

(I know that the supplier table must have a record that matches the
vendornumber field, but are their other tables that also have a
pre-requisite
or dependent record requirements?)

I admit, I am a RMS novice.

Sincerely
Kent Smith
bcentral@kc.rr.com

Glenn Adams [MVP - Retail · Guest

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

--
--
Glenn Adams
Tiber Creek Consulting
http://www.tibercreek.com
glenn@tibercreek.com
----------------------------------------------
Please DO NOT respond to me directly but post all responses here in the
newsgroup so that all can share the information

"RMSrookie" <RMSrookie@discussions.microsoft.com> wrote in message
news:A96B61DB-645A-499F-B1C6-40A16F570B55@microsoft.com...

RMSrookie · Guest

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.
http://support.microsoft.com/Directory/directory/policies.asp

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:

Rayan · Guest

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 again
rayan.

"RMSrookie" wrote:

	Personal Finance Forum Index -> POS	All times are GMT
Page 1 of 1