Solved

Insert Trigger with a Linked Server

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

792 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