Solved

Insert Trigger with a Linked Server

Posted on 2009-05-19
2
876 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 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