Solved

Insert Trigger with a Linked Server

Posted on 2009-05-19
2
858 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

920 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