June 14, 2010 at 5:05 am
HI
I use the query below for insert data from local table into remote server table (using Linked Server), but i get this error :
OLE DB provider "SQLNCLI10" for linked server "it-43\sql2008" returned message "Unspecified error".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "SQLNCLI10" for linked server "it-43\sql2008" could not INSERT INTO table "[SQLNCLI10]".
Insert into
OPENQUERY([it-43\sql2008],
'Select [GuId],[UserId],127.0.0.1,
[LastDate],[LoginDate],
[ComputerName],cast([FileListXML] as nvarchar(max)) as [FileListXML]
From Mehregan2_Report.dbo.tblActiveUsers')
Select top 1 [GuId],[UserId],127.0.0.1,[LastDate],[LoginDate],[ComputerName],cast ([FileListXML] as nvarchar(max)) as FileListXML From tblActiveUsers
what's the problem? i'm so depressed 🙁 is the query syntax right? PLZ help mee
June 14, 2010 at 6:30 am
Post whole and clean query you run and errors you get.
June 14, 2010 at 6:47 am
actually i want to create trigger on table which has a XML column , and i got error that cant use table with xml column in distributed queries.then i changed my query i use this query to create insert trigger:
Create Trigger Ins_tblActiveUsers
On
tblActiveUsers
After Insert
AS
Insert
OPENQUERY([it-43\sql2008],
'Select [GuId],[UserId],127.0.0.1,
[LastDate],[LoginDate],
[ComputerName]
From Mehregan2_Report.dbo.tblActiveUsers')
Select [GuId],[UserId],127.0.0.1,[LastDate],[LoginDate],[ComputerName] From Inserted
GO
i omitted xml column from trigger and the trigger was created .but now when i want insert somthing in my table i get this new error:
OLE DB provider "SQLNCLI10" for linked server "it-43\sql2008" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure Ins_tblActiveUsers, Line 6
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "it-43\sql2008" was unable to begin a distributed transaction.
i really dont know what happend :sick:
June 14, 2010 at 7:37 am
1. Check DTC service is running on both servers.
2. Check security configuration of service. It should allow Network DTC
Access and Inbound/Outbound. It can be done in Administrative Tools/Component Services/Computers/My Computer. Right click, Properties, MSDTC tab, Security Configuration.... Serbvice will be restarted, but it may require server re-boot.
3. read this: http://support.microsoft.com/kb/306212
June 14, 2010 at 10:47 pm
Thank U soooooooo much:-) .i didnt check MSDTC not at all .and it was the mistake.now i want to create update Trigger for this table.how can I do that ?
June 15, 2010 at 4:09 am
Why not just use
INSERT INTO Mehregan2_Report.dbo.tblActiveUsers ....
Or use OPENROWSET instead of OPENQUERY
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply