March 11, 2008 at 3:40 pm
Here is my sql
insert into dbo.PayerContracts (PUID, ContractID, PyrContractUID, Effective, Termination, InsuranceType, Entity, Facility, InsuranceCo, EmployerCode, DateType, InsuranceGroupID, FinClass, timestamp)
select * from OPENQUERY(ATLADS07PCM,'select PUID, ContractID, PyrContractUID, Effective, Termination, InsuranceType, Entity, Facility, InsuranceCo, EmployerCode, DateType, InsuranceGroupID, FinClass, NULL from stlukes.dbo.PayerContracts')
go
I get the following error message:
Server: Msg 273, Level 16, State 1, Line 1
Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column.
I've also tried this:
insert into dbo.PayerContracts (PUID, ContractID, PyrContractUID, Effective, Termination, InsuranceType, Entity, Facility, InsuranceCo, EmployerCode, DateType, InsuranceGroupID, FinClass, timestamp)
select *, NULL from OPENQUERY(ATLADS07PCM,'select PUID, ContractID, PyrContractUID, Effective, Termination, InsuranceType, Entity, Facility, InsuranceCo, EmployerCode, DateType, InsuranceGroupID, FinClass from stlukes.dbo.PayerContracts')
go
And got this error message:
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any idea! I am flummoxed! I've also tried putting null in my openquery select statement to no avail.
always get a backup before you try that.
March 11, 2008 at 3:50 pm
Just skip mentioning the timestamp column in both the INSERT and the embedded OPENQUERY. I'm thinking that will fix the issue.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 11, 2008 at 3:52 pm
meaning:
insert into dbo.PayerContracts (PUID, ContractID, PyrContractUID,
Effective, Termination, InsuranceType,
Entity, Facility, InsuranceCo,
EmployerCode, DateType, InsuranceGroupID,
FinClass)
select *
from
OPENQUERY(ATLADS07PCM,'select PUID, ContractID, PyrContractUID,
Effective, Termination, InsuranceType,
Entity, Facility, InsuranceCo,
EmployerCode, DateType, InsuranceGroupID,
FinClass
from stlukes.dbo.PayerContracts')
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 2, 2008 at 6:12 am
I've been meaning to come back and post my solution but never got around to it.
I couldn't get the previous suggestions to work.
So.
I inserted the data into a temp table in my target database and then inserted the data from the temp table to the permanent table by using a list of value in my select statemnt and adding NULL as the last part of the select.
select * into #PayerContracts
from OPENQUERY(%LINKEDSERVER%,'select PUID, ContractID, PyrContractUID, Effective, Termination, InsuranceType, Entity, Facility, InsuranceCo, EmployerCode, DateType, InsuranceGroupID, FinClass, UBClaimType, DisplayWebInd from %copyfromdb%.dbo.PayerContracts')
go
then
insert into %copytodbname%.dbo.PayerContracts
(PUID, ContractID, PyrContractUID, Effective, Termination, InsuranceType, Entity, Facility, InsuranceCo, EmployerCode, DateType, InsuranceGroupID, FinClass,UBClaimType, DisplayWebInd, timestamp)
select PUID, ContractID, PyrContractUID, Effective, Termination, InsuranceType, Entity, Facility, InsuranceCo, EmployerCode, DateType, InsuranceGroupID, FinClass, UBClaimType, DisplayWebInd, NULL
from #PayerContracts
go
always get a backup before you try that.
May 29, 2008 at 5:10 am
Give this link a try
October 21, 2010 at 1:44 pm
Here is what worked for me. Remote SQL on a linked server.
declare @value1 int, @value2 int
DECLARE @v1 datetime
SET @v1 = CONVERT(datetime,getdate() )
EXEC('INSERT INTO SCHEMANAME.TABLENAME(RegularCol1, RegularCol2, TIMESTAMPColumnInOracle )
VALUES (?, ?, ?)', @value1, @value2,@v1)
AT OracleLinkedServer
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply