July 21, 2014 at 11:24 am
Since it'll break all of a sudden and then work as usual I'd add a TRY CATCH block to the sproc where the CATCH block would call a separate sproc that would perform the tests I posted, again wrapped in a separate TRY CATCH block to identify the one that fails.
Remember: If you're forced to work against the three-letter-word-that-must-not-be-named you'll have to add double and triple checks down to the level of pure stupidity to lower the risk of your code crashes due to changes on their side...
Been there. Done that. Glad I left it behind me. Still have nightmares occasionally.
July 21, 2014 at 11:25 am
ChrisM@home (7/21/2014)
Lynn Pettis (7/21/2014)
DevNate (7/21/2014)
Lined server is connected via Symbols.So you are saying the Linked Server is hidden by using a synonym? Well, not obvious from what you posted. What is the synonym used so we can tell which table in the query is actually a linked server.
Can you confirm with your DBA that this is actually the case? I can't see how this object naming convention of schema.linkedserver can work.
The beauty there is that the linked server is obfuscated through the use of synonyms. So in this case, they are referencing schema.synonym.
Obfuscating it like what has been done is only going to compound problems when trying to troubleshoot it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 21, 2014 at 11:28 am
Lynn Pettis (7/21/2014)
Issues.Three, and I may be wrong here, but you are going to be pulling the data from all the tables referenced across your network to the server running the query where the respective joins and filtering will then be completed.
That is the way it appears to me as well. That is a recipe for problems.
Far more efficient to perform the join query on the remote server and then pull the resultant data across if you truly must pull it across to a different server.
That can be done in many different ways. In the end, just join the data on the source server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 21, 2014 at 11:34 am
Just a thought, but I was wondering if the following would work:
SELECT WorkOrder, TakTime
INTO
[plc].[TaktTime]
from openquery([SAPETL],'
WITH TaktValues ([Counter], NODE, PLNNR) AS
(
SELECT
MAX(plpo1.ZAEHL) AS [Counter], MAX(plpo1.PLNKN) AS NODE, plpo1.PLNNR
FROM
[ERP].[erp].PLPO plpo1
GROUP BY plpo1.PLNNR
)
SELECT
s022.AUFNR AS WorkOrder,
(CASE
WHEN plpo.SPLIM = 0 THEN 0
ELSE plpo.VGW01/plpo.SPLIM
END) AS TaktTime
FROM
[ERP].[erp].S022 s022
INNER JOIN
[ERP].[erp].AFPO afpo on afpo.AUFNR = s022.AUFNR
INNER JOIN
[ERP].[erp].AFKO afko on afko.AUFNR = s022.AUFNR
INNER JOIN
[ERP].[erp].PLPO on plpo.PLNNR = afko.PLNNR
INNER JOIN
TaktValues ON plpo.PLNNR = TaktValues.PLNNR
AND plpo.ZAEHL = TaktValues.[Counter]
AND plpo.PLNKN = TaktValues.NODE
WHERE
s022.AUFNR IN (SELECT DISTINCT WorkOrder FROM plc.AlarmData)
ORDER BY
s022.AUFNR DESC
');
July 21, 2014 at 11:42 am
Lynn Pettis (7/21/2014)
Just a thought, but I was wondering if the following would work:
SELECT WorkOrder, TakTime
INTO
[plc].[TaktTime]
from openquery([SAPETL],'
WITH TaktValues ([Counter], NODE, PLNNR) AS
(
SELECT
MAX(plpo1.ZAEHL) AS [Counter], MAX(plpo1.PLNKN) AS NODE, plpo1.PLNNR
FROM
[ERP].[erp].PLPO plpo1
GROUP BY plpo1.PLNNR
)
SELECT
s022.AUFNR AS WorkOrder,
(CASE
WHEN plpo.SPLIM = 0 THEN 0
ELSE plpo.VGW01/plpo.SPLIM
END) AS TaktTime
FROM
[ERP].[erp].S022 s022
INNER JOIN
[ERP].[erp].AFPO afpo on afpo.AUFNR = s022.AUFNR
INNER JOIN
[ERP].[erp].AFKO afko on afko.AUFNR = s022.AUFNR
INNER JOIN
[ERP].[erp].PLPO on plpo.PLNNR = afko.PLNNR
INNER JOIN
TaktValues ON plpo.PLNNR = TaktValues.PLNNR
AND plpo.ZAEHL = TaktValues.[Counter]
AND plpo.PLNKN = TaktValues.NODE
WHERE
s022.AUFNR IN (SELECT DISTINCT WorkOrder FROM plc.AlarmData)
ORDER BY
s022.AUFNR DESC
');
That's the one. EXECUTE() AT linkedserver is good too.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 21, 2014 at 11:56 am
Thanks all, appreciate the feedback.
I did try pulling out the synonyms to simplify the troubleshooting and still ran into the same issue while it was broken. For now, my code is working though and it difficult to troubleshoot.
I am going to add in the TRY CATCH as describe by LutzM (Thanks) and see what parts are breaking when it does fail.
LutM - do you have a recommendation for how to capture the output?
I will also look into the openquery function as suggested by Lynn Pettis (Thanks) and the followup with EXECUTE AT linkedserver from ChrisM@home
Thanks all for helping this newb 😉 oh now bumped to Grasshopper status
July 21, 2014 at 12:01 pm
ChrisM@home (7/21/2014)
Lynn Pettis (7/21/2014)
Just a thought, but I was wondering if the following would work:
SELECT WorkOrder, TakTime
INTO
[plc].[TaktTime]
from openquery([SAPETL],'
WITH TaktValues ([Counter], NODE, PLNNR) AS
(
SELECT
MAX(plpo1.ZAEHL) AS [Counter], MAX(plpo1.PLNKN) AS NODE, plpo1.PLNNR
FROM
[ERP].[erp].PLPO plpo1
GROUP BY plpo1.PLNNR
)
SELECT
s022.AUFNR AS WorkOrder,
(CASE
WHEN plpo.SPLIM = 0 THEN 0
ELSE plpo.VGW01/plpo.SPLIM
END) AS TaktTime
FROM
[ERP].[erp].S022 s022
INNER JOIN
[ERP].[erp].AFPO afpo on afpo.AUFNR = s022.AUFNR
INNER JOIN
[ERP].[erp].AFKO afko on afko.AUFNR = s022.AUFNR
INNER JOIN
[ERP].[erp].PLPO on plpo.PLNNR = afko.PLNNR
INNER JOIN
TaktValues ON plpo.PLNNR = TaktValues.PLNNR
AND plpo.ZAEHL = TaktValues.[Counter]
AND plpo.PLNKN = TaktValues.NODE
WHERE
s022.AUFNR IN (SELECT DISTINCT WorkOrder FROM plc.AlarmData)
ORDER BY
s022.AUFNR DESC
');
That's the one. EXECUTE() AT linkedserver is good too.
Haven't used EXECUTE() AT linkedserver, so I can't really say. I have and do use the OPENQUERY method of pulling data from one server to another, just not on a regular basis.
July 21, 2014 at 12:07 pm
Hi Lynn, EXECUTE() AT allows you to run a batch of statements, including for instance running results into a temp table for further processing.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 28, 2014 at 11:34 am
Once again this morning, the job failed and has continued to fail. I tried the below as well as a try catch and both are yielding the same result. I'm am talking with our SAP analyst in regards to those tables as it appears that it the error is coming from that server.
Lynn Pettis (7/21/2014)
Just a thought, but I was wondering if the following would work:
SELECT WorkOrder, TakTime
INTO
[plc].[TaktTime]
from openquery([SAPETL],'
WITH TaktValues ([Counter], NODE, PLNNR) AS
(
SELECT
MAX(plpo1.ZAEHL) AS [Counter], MAX(plpo1.PLNKN) AS NODE, plpo1.PLNNR
FROM
[ERP].[erp].PLPO plpo1
GROUP BY plpo1.PLNNR
)
SELECT
s022.AUFNR AS WorkOrder,
(CASE
WHEN plpo.SPLIM = 0 THEN 0
ELSE plpo.VGW01/plpo.SPLIM
END) AS TaktTime
FROM
[ERP].[erp].S022 s022
INNER JOIN
[ERP].[erp].AFPO afpo on afpo.AUFNR = s022.AUFNR
INNER JOIN
[ERP].[erp].AFKO afko on afko.AUFNR = s022.AUFNR
INNER JOIN
[ERP].[erp].PLPO on plpo.PLNNR = afko.PLNNR
INNER JOIN
TaktValues ON plpo.PLNNR = TaktValues.PLNNR
AND plpo.ZAEHL = TaktValues.[Counter]
AND plpo.PLNKN = TaktValues.NODE
WHERE
s022.AUFNR IN (SELECT DISTINCT WorkOrder FROM plc.AlarmData)
ORDER BY
s022.AUFNR DESC
');
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply