May 31, 2011 at 9:36 am
Note: The server and linked server in question are both SQL Server 2008 (10.0.4000.0)
Firstly here is what's causing me problems:
DECLARE @EDB_PK_OFFSET INT
SET @EDB_PK_OFFSET = 100000000
INSERT INTO WHS_COR_EMPLOYER_OPERATION
( WHS_COR_KEY,
WHS_COR_STATUS_KEY,
EMPLOYER_OPERATION_KEY,
WHS_COR_EMPLOYER_OPERATION_COUNT,
CREATED_DATE,
AUDIT_DATE,
EFFECTIVE_DATE,
EXPIRY_DATE,
EXPIRED_DATE,
RECERTIFICATION_DATE)
SELECT distinct C.CERTIFICATION_ID + @EDB_PK_OFFSET AS WHS_COR_KEY,
Case CS.CERTIFICATION_STATUS_REASON_ID
when 1 then 1 --cancelled
when 2 then 2 --expired
else 3
end AS WHS_COR_STATUS_KEY,
EO.EMPLOYER_OPERATION_KEY AS EMPLOYER_OPERATION_KEY,
1 AS WHS_COR_EMPLOYER_OPERATION_COUNT,
C.FROM_DATE AS CREATED_DATE,
C.CERTIFICATION_AUDIT_DATE AS AUDIT_DATE,
C.ISSUE_DATE AS EFFECTIVE_DATE,
C.CERTIFICATION_EXPIRE_DATE AS EXPIRY_DATE,
C.THRU_DATE AS EXPIRED_DATE,
C.RECERTIFICATION_DATE AS RECERTIFICATION_DATE
FROM [LINKED_SERVER].[DB_NAME].dbo.CERTIFICATION C
INNER JOIN [LINKED_SERVER].[DB_NAME].dbo.CERTIFICATION_STATUS CS
ON CS.CERTIFICATION_ID = C.CERTIFICATION_ID
INNER JOIN [LINKED_SERVER].[DB_NAME].dbo.ORGANIZATION_ACCOUNT_CERTIFICATION OAC
ON OAC.CERTIFICATION_ID = C.CERTIFICATION_ID
INNER JOIN [LINKED_SERVER].[DB_NAME].dbo.ORGANIZATION_ACCOUNT OA
ON OAC.ORGANIZATION_ACCOUNT_ID = OA.ORGANIZATION_ACCOUNT_ID
INNER JOIN [LINKED_SERVER].[DB_NAME].dbo.OPERATION_PARTICIPANT OP
ON OP.ORGANIZATION_ACCOUNT_ID = OA.ORGANIZATION_ACCOUNT_ID
INNER JOIN [LINKED_SERVER].[DB_NAME].dbo.PARTICIPANT P
ON OP.PARTICIPANT_ID = P.PARTICIPANT_ID
INNER JOIN [LINKED_SERVER].[DB_NAME].dbo.[OPERATION] O
ON P.OPERATION_ID=O.OPERATION_ID
INNER JOIN [LINKED_SERVER].[DB_NAME].dbo.OHS_FILE OHF
ON OHF.ORGANIZATION_ACCOUNT_ID = OA.ORGANIZATION_ACCOUNT_ID
INNER JOIN EMPLOYER_OPERATION EO
ON P.OPERATION_ID=EO.EDB_EMPLOYER_OPERATION_ID
WHERE C.CERTIFICATION_TYPE_ID = 1
AND C.CERTIFICATION_ID IS NOT NULL
AND O.INDUSTRY_CLASSIFICATION_TYPE_ID=OHF.INDUSTRY_CLASSIFICATION_TYPE_ID
I changed the linked server name and the database name but nothing else. When I run it this is what happens:
[font="Courier New"]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 "Tbl1007.CERTIFICATION_STATUS_REASON_ID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1005.THRU_DATE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1005.FROM_DATE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1005.ISSUE_DATE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1005.RECERTIFICATION_DATE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1005.CERTIFICATION_EXPIRE_DATE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1005.CERTIFICATION_AUDIT_DATE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1005.CERTIFICATION_ID" could not be bound.[/font]
However, if I comment out the "insert" part and just run the select, it works fine. This is what I can't figure out. Also this query has been working fine for a long time, until now.
Also it isn't a problem with the data being inserted. I can actually change it to a "select into" and put the data into a new local table, then insert the data from there. It works fine when I do that.
Does anyone have any idea what the problem could be?
June 1, 2011 at 8:01 am
bump
June 1, 2011 at 9:05 am
Is there a trigger on the table you're trying to insert into?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2011 at 8:41 am
GSquared (6/1/2011)
Is there a trigger on the table you're trying to insert into?
No, there is no trigger.
Also I was wrong about one other thing, this query is new and never worked in this environment. It was successful in UAT, going from SQL 2008 to SQL 2000.
November 10, 2011 at 9:05 am
Hi, this problem has been popping up again in multiple places. It's happening on both SQL 2008 and 2008 R2, and also in queries that used to work.
Is this a known problem at all?
November 10, 2011 at 9:13 am
Was a transaction explicitly started for the INSERT?
November 10, 2011 at 9:23 am
Is one of the servers or dbs in case sensitive mode?
November 10, 2011 at 9:27 am
In terms of transactions present, i meant to also ask about whether the "Require distributed transactions for server-to-server communication" is selected on the server of the insert? If so, you will have to employ the use of BEGIN DISTRIBUTED TRANSACTION. If I am mistaken, someone can correct me.
November 10, 2011 at 3:38 pm
To answer the questions:
Regarding Collation: The same collation is used for every server and every database involved - SQL_Latin1_General_CP1_CI_AS (case insensitive)
Regarding "Require distributed transactions for server-to-server communication":
This is not enabled for any of the servers, and no transactions are used
As mentioned, the main thing is that the query works, just not when used with "insert into" or "select into".
Another weird thing is that leaving out various combinations of items in the "select" portion also allows the query to work... but no single items seem to be the source of the problem.
November 10, 2011 at 9:33 pm
Based on what you describe, it sounds like a distributed transaction issue. Would you check to see if the MS DTC service is running on the server? Select statements do not invoke the use of the MS DTC service, but inserts do.
November 10, 2011 at 11:01 pm
Each distributed query can reference multiple linked servers and can perform either update or read operations against each individual linked server. A single distributed query can perform read operations against some linked servers and update operations against other linked servers. In general, the Database Engine requires distributed transactions support from the corresponding OLE DB provider whenever data from more than one linked server are likely to be updated in a transaction. Hence, the types of queries that are supported against linked servers depend on the level of support for transactions present in the OLE DB providers. OLE DB defines two optional interfaces for transaction management:
ITransactionLocal supports local transactions in the OLE DB data source.
ITransactionJoin lets the provider join a distributed transaction that includes other resource managers.
Any provider that supports ITransactionJoin also supports ITransactionLocal.
If a distributed query is executed when the connection is in autocommit mode, these rules apply:
Only read operations are allowed against providers that do not support ITransactionLocal.
All update operations are allowed against any providers that support ITransactionLocal.
The controlling instance of the Database Engine automatically calls ITransactionLocal in each linked server participating in an update operation to start a local transaction. It commits them when the statement succeeds or rolls them back if the statement fails.
Distributed Queries and Distributed Transactions
March 21, 2014 at 4:22 am
So what is the solution?
March 21, 2014 at 4:43 am
It's an old thread, Wesley - so it's quite likely that none of the respondents will reply. Are you experiencing a similar issue? If so, your best option might be to begin a new thread, since no single problem was either identified or solved on this one.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply