Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

CTE with Linked Server: Mulitpart Identifier could not be bound Expand / Collapse
Author
Message
Posted Thursday, July 17, 2014 2:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:14 PM
Points: 12, 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.

Post #1593835
Posted Saturday, July 19, 2014 9:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #1594318
Posted Saturday, July 19, 2014 9:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 17,824, Visits: 15,756
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
Post #1594319
Posted Monday, July 21, 2014 9:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:14 PM
Points: 12, 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.
Post #1594642
Posted Monday, July 21, 2014 9:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Post #1594645
Posted Monday, July 21, 2014 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:14 PM
Points: 12, Visits: 61
Lined server is connected via Symbols.
Post #1594664
Posted Monday, July 21, 2014 10:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 20,739, Visits: 32,525
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.



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)
Post #1594673
Posted Monday, July 21, 2014 10:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:14 PM
Points: 12, 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.


Post #1594674
Posted Monday, July 21, 2014 10:35 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 20,739, Visits: 32,525
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.



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)
Post #1594676
Posted Monday, July 21, 2014 10:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:49 AM
Points: 1,034, Visits: 6,812
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
Post #1594678
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse