SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CTE with Linked Server: Mulitpart Identifier could not be bound


CTE with Linked Server: Mulitpart Identifier could not be bound

Author
Message
DevNate
DevNate
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 61
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.


LutzM
LutzM
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24387 Visits: 13559
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"....



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68297 Visits: 18570
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

DevNate
DevNate
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 61
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.
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42629 Visits: 20015
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?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
DevNate
DevNate
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 61
Lined server is connected via Symbols.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97575 Visits: 38988
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
DevNate
DevNate
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 61
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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97575 Visits: 38988
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5382 Visits: 10609
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.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search