I tried your script and recreated your environment completely and i didn't see any error.
Ran perfectly and created all tables and transferred all data successfully
i tried running under a sql agent job and still no error was generated
but i guess you can update your insert script by making sure the column gets converted under all conditions
[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END
or
[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE Null END
change your script to
INSERT INTO OPENQUERY (
ZMMS_ORA
'SELECT
"COMPANYID",
"BO_ID",
"AS CM",
"AS RSM",
"AS SE",
"TS CM",
"TS RAE",
"TS RSM",
"TS SE",
"BBONE AM",
"ASM",
"WCT ASM",
"LS TSM",
"WCT TSM",
"TS TSM",
"AS PM",
"AS DEV",
"TS PM",
"TS DEV",
"TS TECH",
"BB EXEC",
"CHM",
"ANGEL Client Manager (CM)",
"ANGEL Engagement Team",
"BbOne Client Manager (CM)",
"Blackboard Executive",
"CE/Vista Hosting Mgr (ASM)",
"CE/Vista Tech Support (TSM)",
"Complex Hosting Manager (CHM)",
"Learn Client Manager (CM)",
"Learn Hosting Mgr (ASM)",
"Learn Reg Sales Mgr (RSM)",
"Learn Sales Engineer (SE)",
"Learn Services Developer",
"Learn Services ICM Mgr",
"Learn Services Project Manager",
"Learn Tech Support (TSM)",
"Transact Client Manager (CM)",
"Transact Project Manager",
"Transact Reg Acct Exec (RAE)",
"Transact Reg Sales Mgr (RSM)",
"Transact Sales Engineer (SE)",
"Transact Services Technician",
"Transact Tech Support (TSM)",
"Xythos Tech Support (TSM)" FROM ZMMSUSER.PSRPT_TEAM_ROLES_PSFT'
)
SELECT "COMPANYID"
,[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END
,"AS CM"
,"AS RSM"
,"AS SE"
,"TS CM"
,"TS RAE"
,"TS RSM"
,"TS SE"
,"BBONE AM"
,"ASM"
,"WCT ASM"
,"LS TSM"
,"WCT TSM"
,"TS TSM"
,"AS PM"
,"AS DEV"
,"TS PM"
,"TS DEV"
,"TS TECH"
,"BB EXEC"
,"CHM"
,"ANGEL Client Manager (CM)"
,"ANGEL Engagement Team"
,"BbOne Client Manager (CM)"
,"Blackboard Executive"
,"CE/Vista Hosting Mgr (ASM)"
,"CE/Vista Tech Support (TSM)"
,"Complex Hosting Manager (CHM)"
,"Learn Client Manager (CM)"
,"Learn Hosting Mgr (ASM)"
,"Learn Reg Sales Mgr (RSM)"
,"Learn Sales Engineer (SE)"
,"Learn Services Developer"
,"Learn Services ICM Mgr"
,"Learn Services Project Manager"
,"Learn Tech Support (TSM)"
,"Transact Client Manager (CM)"
,"Transact Project Manager"
,"Transact Reg Acct Exec (RAE)"
,"Transact Reg Sales Mgr (RSM)"
,"Transact Sales Engineer (SE)"
,"Transact Services Technician"
,"Transact Tech Support (TSM)"
,"Xythos Tech Support (TSM)"
FROM CRMRPT..B_RPT_B_TEAM_ROLE;
Best Answer
This is normal. If you set a parameter without specifying
sid
, then it will create/modify the entry in spfile withsid=*
. But if you have another value set with the actualsid
(instance name), that takes precedence over*
, and the value remains unchanged.Demonstration:
My instance is called
MIN19
, so even though 20G is set for*
, it will start with 10G:I may change it to whatever I want, it will not have effect:
Make sure to clean up overlapping parameters and use only 1 of them to avoid confusion. Example: