September 6, 2006 at 10:37 am
I'm having syntax error with the following openquery statement:
UPDATE OPENQUERY(BHOBHT01,
'Update BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = 'Y'
where UNIQUE_TRANS_NO IN
(select UNINO from MOMTRX_Pull)')
I'm getting
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'Y'.
I've tried:
UPDATE OPENQUERY(BHOBHT01,
"Update BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = 'Y'
where UNIQUE_TRANS_NO IN
(select UNINO from MOMTRX_Pull)")
Results:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'Update BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = 'Y'
where UNIQUE_TRANS_NO IN
(select UNINO from MOMTRX_Pull)'.
I've tried:
UPDATE OPENQUERY(BHOBHT01,
'Update BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = "Y"
where UNIQUE_TRANS_NO IN
(select UNINO from MOMTRX_Pull)')
Results:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ')'.
Use of four-part name does not work on this particular server
Update BHOBHT01..BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = 'Y'
where UNIQUE_TRANS_NO IN
(select UNIQUE_TRANSACTION_NUMBER from MOMTRX_Pull)
Results:
Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01426: numeric overflow
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80040e57].
HELP!!
September 6, 2006 at 11:16 am
Have you tried changing the Sub query to JOIN?
Update BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = 'Y'
From MOMTRX_Pull
where UNIQUE_TRANS_NO =MOMTRX_Pull.UNINO
September 6, 2006 at 11:36 am
Results from:
update openquery(BHOBHF02,'Update BHDBO.T_ERP_MOM_TRANS_OUTGOING
set RECORD_PROCESSED = 'Y'
From MOMTRX_Pull
where UNIQUE_TRANS_NO =MOMTRX_Pull.UNINO')
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'Y'.
September 6, 2006 at 11:54 am
I think the string is breaking near Y so try changing it to "Y".
September 6, 2006 at 12:00 pm
I've tried that also...see 4th try in original posting
September 6, 2006 at 12:03 pm
I guess the syntax itself is wrong in your tries.. try this
update
openquery(LS01,'SELECT A.RECORD_PROCESSED FROM OPR..T_ERP_MOM_TRANS_OUTGOING A
INNER JOIN OPR..MOMTRX_Pull B ON A.UNIQUE_TRANS_NO =B.UNINO'
)
set
RECORD_PROCESSED = 'Y'
The only Change here I used database Name as 'OPR' and linked server as LS01
September 6, 2006 at 12:27 pm
Results from:
update openquery(BHOBHT01,'SELECT A.RECORD_PROCESSED FROM BHDBO.T_ERP_MOM_TRANS_OUTGOING A
INNER JOIN db1_Porini_Rep.dbo.MOMTRX_Pull B ON A.UNIQUE_TRANS_NO =B.UNINO')
set RECORD_PROCESSED = 'Y'
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00933: SQL command not properly ended
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare:repare returned 0x80040e14].
T_ERP_MOM_TRANS_OUTGOING is on an Oracle 8.1.6 instance and MOMTRX_Pull is on SQL Server 2000.
September 6, 2006 at 12:31 pm
Why can't you run the update directly in the openquery or using a linked server??
September 6, 2006 at 2:12 pm
I assumed that both the tables are in linked server only.
ok try this.. this has to be run with the connection to the MOMTRX_Pull's Database
update
openquery(PAERSCBBLD0104,'SELECT UNIQUE_TRANS_NO,RECORD_PROCESSED FROM OPR..T_ERP_MOM_TRANS_OUTGOING')
set
RECORD_PROCESSED = 'Y'
WHERE
UNIQUE_TRANS_NO IN (SELECT UNINO FROM MOMTRX_Pull)
September 6, 2006 at 2:48 pm
Thanks, this seems to work!
September 2, 2009 at 1:36 pm
I am getting the same error today . Did you get any resolution on this?
here is the query..and the tale is in linked tables.
declare @query1 varchar(8000)
/* truncate table upon new load of inIRNs to be run */
--truncate table DUP_PHONE
/* populate the table */
insert into LEADSTATUSDM_STAGE.dbo.DUP_PHONE
Select * from openquery(Campusodsprd ,'
SELECT ph2.Parent_Oid AS PERSON_OID, PH.phone_number AS formatted_phone_number, ph2.Area, ph2.Phone_Number, ph2.Type,
ph.person_count, SYSDATE AS DATE_CREATED, ROW_NUMBER() OVER(PARTITION BY PH2.Parent_Oid, PH.Phone_Number
ORDER BY PH2.Type) AS PERSON_COUNTER
FROM (SELECT upper(trim(translate(ph.Phone_Number, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1''))) AS phone_number,
upper(trim(translate(ph.Area, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1''))) AS area, COUNT(DISTINCT p.Oid)
AS person_count
FROM galaxy.Person p JOIN
galaxy.Phone ph ON ph.Parent_Oid = p.Oid AND ph.Phone_Number IS NOT NULL AND ph.Legacy_Type IN (''HM'', ''HM2'', ''CE'')
WHERE p.IRN IN (9027122388,9031824159,9032129480,9031929100,9032051753)
AND
length(translate(upper(ph.Phone_Number), ''qABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''q'')) >= 7 AND
LENGTH(TRIM(SUBSTR(upper(translate(ph.Phone_Number, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1'')), 1, 1)
FROM upper(translate(ph.Phone_Number, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1'')))) > 0
GROUP BY upper(trim(translate(ph.Phone_Number, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1''))), upper(trim(translate(ph.Area,
''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1'')))) PH JOIN
galaxy.Phone ph2 ON upper(translate(ph2.Phone_Number, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1'')) = ph.Phone_Number AND
upper(trim(translate(ph2.Area, ''1ABCDEFGHIJKLMNOPQRSTUVWXYZ-*+#?,./()'''' '', ''1''))) = ph.Area AND ph2.Legacy_Type IN (''HM'', ''HM2'', ''CE'')) PH
WHERE PH.PERSON_COUNTER = 1;
')
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy