July 17, 2014 at 2:13 pm
Interesting issue. I have the following CTE that JOINs some tables from a Linked Server which is our SAP data. This CTE is in a stored procedure and then executed via a SQL Server Agent Job on a timer (every 10 minutes). This ran fine for almost 20 hours and then dies with a multipart identifier could not be bound error (exact error below CTE).
Server running the job: SQL Server 2008 R2 (no SP)
Linked Server: SQL Server 2005 SP3 housing SAP
CTE:
WITH TaktValues ([Counter], NODE, PLNNR) AS
(
SELECT
MAX(plpo1.ZAEHL) AS [Counter], MAX(plpo1.PLNKN) AS NODE, plpo1.PLNNR
FROM
etl.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
INTO
[plc].[TaktTime]
FROM
etl.S022 s022
INNER JOIN
etl.AFPO afpo on afpo.AUFNR = s022.AUFNR
INNER JOIN
etl.AFKO afko on afko.AUFNR = s022.AUFNR
INNER JOIN
etl.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
Error:
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1008.AUFNR" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1008.AUFNR" could not be bound.
July 19, 2014 at 9:43 am
Seems like there are some views involved since there's no reference to Tbl1008 in the code you've posted.
Most probably the underlying data structure has changed without notice (as usual when dealing with that "software").
Since you're forced to deal with the three-letter cussword you might need to spend a few thousand bucks for a "consultant"....
July 19, 2014 at 9:45 am
LutzM (7/19/2014)
Seems like there are some views involved since there's no reference to Tbl1008 in the code you've posted.Most probably the underlying data structure has changed without notice (as usual when dealing with that "software").
Since you're forced to deal with the three-letter cussword you might need to spend a few thousand bucks for a "consultant"....
+10
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 9:04 am
Thanks for the response.
While working with that "software" has its... um, challenges, we are forced to work with it. 🙂
The interesting part now is that the Job has run all weekend with 0 errors. I'm at a loss as to why it would work, break, and then work again. For now I am forced to monitor it and hope that at the next break I can pull some more details out of it.
It may be in the views and a changing data structure. Do you know of article describing how views affect linked servers or possibly related to CTEs?
Once again, thanks in advance.
July 21, 2014 at 9:08 am
The query you posted - the opening post - doesn't contain the three-part names which are commonly associated with linked servers. How are you running this query?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 21, 2014 at 9:56 am
Lined server is connected via Symbols.
July 21, 2014 at 10:23 am
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.
July 21, 2014 at 10:29 am
Sorry yes, synonyms were used.
etl is the schema.
table synonyms are used for the following tables.
S022
PLPO
AFPO
AFKO
synonyms are pointed to the linked server.
July 21, 2014 at 10:35 am
DevNate (7/21/2014)
Sorry yes, synonyms were used.etl is the schema.
table synonyms are used for the following tables.
S022
PLPO
AFPO
AFKO
synonyms are pointed to the linked server.
Still confused. Are you saying that all four of the above tables are actually referenced through a Linked Server? Are they all on the same Linked Server or are there multiple Linked Servers? How about posting the DDL for the synonyms in order to make things clearer.
July 21, 2014 at 10:40 am
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.
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 10:41 am
I don't think it will help at all to know the linked server that is referenced.
The OP should try a
SELECT TOP 1 AUFNR FROM afpo
SELECT TOP 1 AUFNR FROM afko
SELECT TOP 1 AUFNR FROM s022
to verify which one fails.
Then use SELECT * FROM the_table_in_question
Finally, take a Baseball bat and practice at the "Submit-And-Pray"-Dptmt....
July 21, 2014 at 10:53 am
Once again I apologize.
All synonyms are pointed to the same server and created as follows. Of course replacing the appropriate table name where required.
CREATE SYNONYM [etl].[AFKO] FOR [SAPETL].[ERP].[erp].[AFKO]
July 21, 2014 at 10:54 am
Did you try what I proposed to find the table in question?
July 21, 2014 at 11:00 am
Yes, all queries went through. The interesting part is the query I am using is working. but its intermittent.
July 21, 2014 at 11:03 am
Issues.
One, you only posted the DDL for one synonym, but enough on that.
Two, it is looking like the "tables" may actually be views not actual tables.
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.
At this point we really can't do much until you provide the underlying DDL for objects referenced in the query.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply