Solved

Insert Trigger with a Linked Server

Posted on 2009-05-19
2
852 Views
Last Modified: 2012-05-07
I am trying to perform on insert into a linked server through a trigger. Code is as follows:
--********************************
USE [TEST2]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_WARNINGS ON
Go

ALTER TRIGGER [dbo].[SV00200_DAILY_TOTALS]
   ON  [dbo].[SV00200] FOR INSERT
 
AS
BEGIN

SET NOCOUNT ON;
-- LInked server is websql.servicewebportal
Insert into websql.servicewebportal.dbo.customerlocations ( [ADRSCODE], [LOCATNNM],[Custnmbr], [CITY])
      select a.adrscode, a.locatnnm, a.Custnmbr, a.city from inserted a
            where a.Custnmbr in (select Custnmbr from websql.servicewebportal.dbo.customermain)

END
--************************************
 
I continually get the error message 'Microsoft][SQL Native Client][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.' from my client app.

Things I have tried so far are:
-ANSI NULLs and Warnings are on in my ODBC client
-I have placed them inside the begin statement on my trigger
-I have tried synonyms
-The DTS Service is on and I turned on the setting for 'require DTS Transfer between servers' on the server with the trigger
-I can insert and update via stored proc into this linked server
-My linked server provider (SQLNCLI) has 'Allow inprocess' and 'Dosallow adhoc access' checked
-My linked server has Data Access and Remote Collation to true and the rest to false
thanks
0
Comment
Question by:gogetsome
2 Comments
 
LVL 39

Expert Comment

by:appari
ID: 24427917
are you getting this error only from your client application?
what happens if you insert from tsql/sqlserver management studio?
if it is only from client application can you post the connection string you are using from the client application and related code?
0
 

Accepted Solution

by:
gogetsome earned 0 total points
ID: 24442717
From SQL Management Studio I get the following error

"OLE DB provider "SQLNCLI" for linked server "websql" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure SV00200_DAILY_TOTALS, Line 23
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "websql" was unable to begin a distributed transaction."

I did some digging on the Msg 7391 and found it was related to DTC errors. I ran the DTCTest on both boxes with errors 1722 (The RPC Server is unavailable) , I ran dtctester from the primary box with the error 'SQLState=24000, Native error =0

SQL Server a (the one where the client and triggers are located) is on my domain, SQL Server b is outside the firewall in a DMZ (not on my domain),  Port 1433 is open from the LAN to the DMZ.  We opened port 135 from RPC and had the same results.



0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Word Template Mail merge with vb.net 4 40
separate column 24 20
sql server query? 6 28
Sql query 34 22
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now