Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Insert Trigger with a Linked Server

Posted on 2009-05-19
2
Medium Priority
?
889 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

636 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