Crashing Trigger on View - Linked Server
-
For a variety of reasons, it would be very useful for me to be able to work with data in views on FoxPro 2.6 tables. Not an ideal situation for sure, but it is what I appear to be stuck with at the moment.
I can create the views on the FoxPro tables - either with or without a linked server, with or without a DSN.
I can use T-SQL from Query Analyzer or pass-through SQL from MS Access to insert, update and delete records from the Foxpro tables - with or without the linked servers. I can also use T-SQL or pass-through to run stored procedures to do the same.
I can insert/delete etc in the SQL Server view via T-SQL (QA) or SQL pass through (Access).
I can not edit the views directly, either in Ent. Manager or from Access as a linked table. Error reported re MSDASQL/ODBC driver couldn't begin a transaction.
I figured that the views aren't updateable directly. Fine - create Instead Of triggers for the views that run the insert/update/delete commands on the FoxPro table directly b/c those do work - WRONG.
The triggers fail with the same ODBC error message as the direct alteration attempt on the views. Further more, when the triggers exist, the T-SQL command to insert/delete etc on the view also fail, even though they worked before the trigger was created. Suggestions? It looks like I will have to recode all of the various apps that use these FoxPro tables to pass SQL through to a linked server, which will be a huge job and isn't in the current dev. budget.
Here is the provider string for the linked server to a FoxPro table on a test machine:
*************************
Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=\\[Test machine name]\[Test data path]\;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes
Note: settings for the OLE DB provider for ODBC drivers are:
Collation Compatible = No
Data Access = Yes
RPC = No
RPC Out = No
Use Remote Collation = Yes
Collation Name = blank
Connection Timeout = 0
Query Timeout = 0
**************************
Here are SQL commands that successfully inserts a dummy row into the Foxpro table from QA or Access (SQL pass through):
**************************
(via linked server)
INSERT INTO OPENQUERY(TestDev_FoxPro_No_DSN,'Select * From VesGear1')
([vrn], [vescode], [gearcode], [active], [netclass], [codend], [vertspread], [wingspread], [doorspread], [roller], [actvdate], [inactvdate])
VALUES('333333', 'CCC', '99', 0, 1, 1.11, 10, 10, 100, 2.22, Convert(smalldatetime,'8/19/2004'), Convert(smalldatetime,'1/1/1900'))
****************************
Here is the command that created a view on this test table:
****************************
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON
GO
CREATE VIEW dbo.v_Dev_TrawlGearSpecifications
AS
SELECT [vrn], [vescode], [gearcode], [active], [netclass], [codend], [vertspread], [wingspread], [doorspread], [roller], [actvdate], [inactvdate]
FROM
OPENQUERY(TestDev_FoxPro_No_DSN,'SELECT * FROM VesGear1')
GO
SET QUOTED_IDENTIFIER OFF
SET XACT_ABORT OFF
GO
*************************
Here is the code that created an insert trigger on the view:
*************************
CREATE TRIGGER IO_Trig_INS_v_Dev_TrawlGearSpecifications
ON v_Dev_TrawlGearSpecifications
INSTEAD OF INSERT
AS
BEGIN
-- Execute the insert command to update the base FoxPro table
INSERT INTO OPENQUERY(TestDev_FoxPro_No_DSN,'Select * From VesGear1')
([vrn], [vescode], [gearcode], [active], [netclass], [codend], [vertspread], [wingspread], [doorspread], [roller], [actvdate], [inactvdate])
SELECT vrn, vescode, gearcode, active, netclass, codend, vertspread, wingspread, doorspread, roller, actvdate, inactvdate
FROM Inserted
END
**************************
Here is the insert command on the view and the error message I get on attempt - with the insert trigger:
**************************
-- Insert command on test view
INSERT INTO [Trawl].[dbo].[v_Dev_TrawlGearSpecifications]([vrn], [vescode], [gearcode], [active], [netclass], [codend], [vertspread], [wingspread], [doorspread], [roller], [actvdate], [inactvdate])
VALUES('666666', 'AAA', '88', 0, 8, 8.88, 8, 80, 180, 8.88, Convert(smalldatetime,'8/19/2004'), Convert(smalldatetime,'1/1/1900'))
-- result
(1 row(s) affected)
Server: Msg 7391, Level 16, State 1, Procedure IO_Trig_INS_v_Dev_TrawlGearSpecifications, Line 8
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Driver not capable]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].
(Note: it says one row affected when show execution plan and trace are enabled, but the new row is not present.)
If I remove the trigger, the above T-SQL succeeds. If I try to edit the view directly in ent. manager, I get nearly the same error report:
"[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message:
[Microsoft][ODBC Visual FoxPro Driver]Driver not capable](#7312) [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a]. (#7300) [Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.(#7391)
-
This was removed by the editor as SPAM
-
Further to my initial post.
I gave up trying to get the trigger to work on the view. I created a copy of the FoxPro table on the SQL server and set up a trigger on it to copy new records into the FoxPro table.
The same error occurs as trying to update the view on the FoxPro table.
I can run Insert/Update/Delete commands on the FoxPro table using T-SQL from Query Analyzer or from stored procedures.
If I try those same commands from a trigger, or try running one of the sp's from the trigger, they all fail with that same "can't begin a distributed transaction" ODBC error.
Any ideas? Anyone?
-
Well, I got the copy-to-FoxPro trigger on the local SQL table to work! It was just a matter of hitting on the right search engine question and wading through all the hits to get on the right track. 
I still can't get a trigger to work directly on a view on a FoxPro linked server table. Time to move on.
Thanks to this thread for giving me the right phrase for searching the Microsoft KB:
http://www.winnetmag.com/SQLServer/Forums/messageview.cfm?catid=1683&threadid=97891
This is the relevant KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274348&Product=sql2k
Here is the create statement for the successful copy-to-FoxPro trigger on the local SQL server table:
**********************************
CREATE TRIGGER IO_Trig_INS_TrawlGearSpecifications
ON dbo.TrawlGearSpecifications
AFTER INSERT
AS
PRINT 'TrawlGearSpecifications - Insert Trigger activated.'
BEGIN
-- Execute the insert command for the base FoxPro table
PRINT 'Executing Insert stored procedure for Trawl Shared FoxPro VesGear.dbf'
-- Force local transactions to commit to remove automatic DTC wrapper from this event.
COMMIT TRAN
EXEC [Trawl].[dbo].[spInsert_Dev_TrawlGearSpecifications]
PRINT 'TrawlGearSpecifications - insert trigger completed.'
END
**************************
Here is the stored proc that is successfully copying the SQL record to the FoxPro table (via a linked server, but it also works without a linked server using OpenRowSet):
**************************
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
SET XACT_ABORT ON
GO
CREATE PROCEDURE dbo.spInsert_Dev_TrawlGearSpecifications
AS
PRINT 'Setting server configuration for distributed transactions...'
SET XACT_ABORT ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
-- Execute the insert command for the base FoxPro table
PRINT 'Executing Insert statement against Trawl Shared FoxPro VesGear.dbf'
-- Copy new TrawlGearSpecifications records into the
-- equivalent FoxPro Table via Linked Server - for legacy application support
INSERT INTO OPENQUERY(TestDev_Foxpro_No_DSN,'SELECT * FROM VesGear1')
([vrn], [vescode], [gearcode], [active], [netclass], [codend], [vertspread], [wingspread], [doorspread], [roller], [actvdate], [inactvdate])
SELECT [vrn], [vescode], [gearcode], [active], [netclass], [codend], [vertspread], [wingspread], [doorspread], [roller], [actvdate], [inactvdate]
FROM dbo.TrawlGearSpecifications
WHERE [FoxProStatus]='INSERT'
PRINT 'Foxpro insert completed.'
PRINT 'Resetting server configuration...'
SET XACT_ABORT OFF
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT OFF
PRINT 'Updating FoxProStatus flag in SQL table TrawlGearSpecifications...'
-- Update the FoxProStatus flag of the local table to COMMITTED
UPDATE [Trawl].[dbo].[TrawlGearSpecifications]
SET [FoxProStatus]='COMMITTED'
WHERE TrawlGearID IN
(SELECT TGS.TrawlGearID FROM [Trawl].[dbo].[TrawlGearSpecifications] AS TGS
INNER JOIN OPENQUERY(TestDev_Foxpro_No_DSN,'SELECT * FROM VesGear1') AS fp
ON TGS.VRN = fp.VRN AND TGS.GearCode = fp.GearCode
WHERE TGS.FoxProStatus='INSERT')
PRINT 'FoxProStatus flag update complete.'
-- Test transaction success - are there any "INSERT" records left in the local table?
IF (SELECT COUNT(*) FROM [Trawl].[dbo].[TrawlGearSpecifications] WHERE [FoxProStatus]='INSERT') > 0
BEGIN
-- One or more FoxPro insert commands failed - alert dba via CDONTS mail stored proc
DECLARE @from varchar(100)
DECLARE @to varchar(100)
DECLARE @subject varchar(300)
DECLARE @body varchar(8000)
-- Set parameter values
SET @from = [set some flag account]
SET @to=[set dba's account]
SET @subject=[set some flag subject]
SET @body = [set some suitable message]
EXEC [Trawl].[dbo].[spTrawlMail] @from, @to, @subject, @body
END
PRINT 'spInsert_Dev_TrawlGearSpecifications completed.'
GO
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT OFF
SET XACT_ABORT OFF
GO
**************************
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply