How do I convince my PM that this is the way to go? What rationale should be used? (My PM has managed enterprise type data migration but only with SSIS/tools. I think some of the other developers managed the offshore team and either guided them to the scrub in code scheme or at least allowed it.)
Keep in mind this, scrub/translate in SQL Server, might be a very hard sell as I'm not experienced in writing SQL scripts, and have written C++ and C# code for 10+ yrs.
Thanks so much for the information.
p.s. There are a number of files which make up the ISAM database.
scrub/translate in SQL Server, might be a very hard sell as I'm not experienced in writing SQL
My recommendation is based on my experience and I'm afraid I DO have extensive experience in SQL. Seriously, I would recommend getting in a consultant to do this job, it is not trivial and screwing it up will (and already has once) totally ruin any app built on it!
The main reason not to use SSIS for the transformation is my own lack of skill with the tool, I find it particularly difficult to audit a process that happens on the server in what is essentially a black box. Another is that we recently did some comparisons between using SSIS to transform the data and stored procs and the procs were 3 times faster than SSIS. When you process over 2k files per day it is important.
Never underestimate the power of human stupidity
I know I'm asking a lot and you should be paid for this kind of info. So thanks again.
Don't get me wrong, if it were up to me I would more than follow your guidance. The only issue here is that I'm going to be a mouthpiece for your ideas and I don't have much standing in the company. I'm a new hire.
Given that, what concrete examples/reasons can I give my PM to convince him of the folly of the current approach and the correctness of the 'move data to SQL Server and then do translation into the new schema' strategy?
You've recieved some excellent info from Mycroft. And I agree with him fully.
I'd just like to add that memory considerations is a major reason to do the job on the database using SQL scripts.
A database is optimised for handling large sets of data.
"When did ignorance become a point of view" - Dilbert
I am trying to call Oracle function via Linked Server, while executing it return the error. Can some one tell what is going wrong with this code, keep in mind that the parameter passed are of exactly same type defined in Oracle function except the date type parameter which I am passing as string because there is no equivalent.
-- returned error --
OLE DB provider "MSDAORA" for linked server "ERPUDEV" returned message "ORA-06550: line 1, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
-- calling part --
DECLARE @source_code VARCHAR(30)
DECLARE @source_lineid INT
DECLARE @feeder_itemcode VARCHAR(40)
DECLARE @sales_price INT
DECLARE @fdr_org_code VARCHAR(10)
DECLARE @fdr_subinv_code VARCHAR(10)
DECLARE @txn_qty INT
DECLARE @txn_date VARCHAR(30)
DECLARE @feeder_txntype VARCHAR(30)
DECLARE @fdr_subinvcode VARCHAR(10)
DECLARE @fdr_trnsubinvcode VARCHAR(10)
DECLARE @item_desc VARCHAR(10)
DECLARE @ErrMsg VARCHAR(100)
DECLARE @RetVal INT
SET @source_code = 'SSP-SALES'
SET @source_lineid = 8728
SET @txn_no = '15059'
SET @txn_no2 = '29193'
SET @feeder_itemcode = 'SSP1032'
SET @sales_price = 12
SET @fdr_org_code = 'S'
SET @fdr_subinvcode = 'S-SALES'
SET @txn_qty = 1
SET @txn_date = '13/MAR/2010'
SET @feeder_txntype = 'S-SALES_ISSUE'
SET @fdr_trnsubinvcode = ''
A little more to understand the problem, I'v found that the error message was because of the returned type boolean (@RetVal).
-- oracle implementation
CREATE OR REPLACE FUNCTION TEST_ERP_INSERT (
P_SOURCE_CODE IN VARCHAR2
, P_SOURCE_LINE_ID IN NUMBER
, P_TRANSACTION_NUMBER IN VARCHAR2
, P_FEEDER_ITEM_CODE IN VARCHAR2
, P_SALES_PRICE IN NUMBER
, P_FEEDER_ORGANIZATION_CODE IN VARCHAR2
, P_FEEDER_SUBINVENTORY_CODE IN VARCHAR2
, P_TRANSACTION_QUANTITY IN NUMBER
, P_TRANSACTION_DATE IN VARCHAR2
, P_FEEDER_TRANSACTION_TYPE IN VARCHAR2
, P_FEEDER_TRANSFER_SUBINV_CODE IN VARCHAR2
, p_errmesg OUT VARCHAR2
p_errmesg := 'Error:'
I Wonder if it possible to retrieve from an SQL server a recored plus its 5 recored before anf after.
for example: When I search a product with serial-number "100" i would like to retrieve 11 recorods from serial-number "095" until "105", of course after a "ORDER BY" statement of the serial-number field
I have three primary tables:
Each customer master has a one to many relationship with contacts.
Each contact has a possible 1:1 relationship with the AddressList. (ie. null addresses ok)
Each address in the AddressList has a 1:1 relationship with CityEnumerators and StateEnumerators.
I'm trying to get a view working that shows all contacts that are active whose CustomerMaster record is active. Unfortunately I only get the contacts that have addresses and not the ones without addresses. I thought the following query would do it....but no go. (BTW also tried RIGHT OUTER JOIN with no luck also)
Any help would be appreciated as to what I'm doing that is screwing the pooch.
SELECT TOP (100) PERCENT dbo.CustomerMaster.BSACustomerKey, dbo.ContactMaster.ContactName, dbo.ContactMaster.BusinessPhone, dbo.ContactMaster.MobilePhone,
dbo.ContactMaster.Email, dbo.ContactMaster.FirstName, dbo.ContactMaster.LastName, dbo.ContactMaster.EmergencyMaintenance,
dbo.ContactMaster.NormalMaintenance, dbo.ContactMaster.NetworkAbuse, dbo.ContactMaster.IssuedSwipeCard, dbo.ContactMaster.CardNumber,
dbo.AddressList.CustomerAddressLine1, dbo.AddressList.CustomerAddressLine2, dbo.AddressList.CustomerAddressLine3, dbo.AddressList.CustomerPostBox,
dbo.CityEnumerator.City, dbo.StateEnumerator.State, dbo.AddressList.CustomerCounty, dbo.AddressList.CustomerCountry, dbo.AddressList.CustomerZipcode,
FROM dbo.ContactMaster INNER JOIN
dbo.CustomerMaster ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity LEFT OUTER JOIN
dbo.AddressList ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity INNER JOIN
dbo.CityEnumerator ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity INNER JOIN
dbo.StateEnumerator ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE (dbo.CustomerMaster.CustomerStatus = 1) AND (dbo.ContactMaster.Status = 1)
ORDER BY dbo.CustomerMaster.BSACustomerKey
So now with my question - how can I create a stored procedure that will produce results in the same row. I am getting duplicates for each row (which I know the way it is setup I would). But is there a way in my Stored Procedure to have the output look like: