Problem Using A Linked Server In An Insert Trigger
Aug 24, 2004
I'm writing an insert trigger in one SQL Server database that is supposed to insert another record into a linked SQL Server database. I have the linked server set up and have been using it for a few weeks in queries and stored procedure with no problem. Now that I'm trying to use it within a trigger and it just bombs.
I'm getting the following message in one of my logs and I don't know what it means... "Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE". I've googled around, but can't really find anything. Any help would be appreciated.
Thanks,
Bryan
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
Hei,We have 2 MS SQL SERVER 2000 installed on 2 different servers (2 separatedmachines).I am triing to connect them så that when one row is added to the table inthe database in main server - then the same row is added to the same tablein the second server database.I made the insert trigger on the table in the first server ( the secondserver is added as a linked server):-----------------------------------------------------------------------------------------create trigger ti_myTabe1 on myTable1 for insert asbegindeclare ........BEGINinsert into server2.myDatabase2.owner.myTable2(column1, column2, column3)SELECT column1, column2, column3FROM inserted insEND......end-----------------------------------------------------------------------------------------When I run the statement in "SQL Query Analyzer"on the first server:insert into Table1 values(va1,val2,val3)then error is coming:Server: Msg 7391, Level 16, State 1, Procedure ti_myTabe1 , Line 19The operation could not be performed because the OLE DB provider 'SQLOLEDB'was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in thespecified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a].The straing thing is: if I run the statement in "SQL Query Analyzer"on thefirst server:insert into server2.myDatabase2.owner.myTable2 values(va1,val2,val3)then it works!But not inside the trigger!!! - What I am doing wrong ?Any idea is greatly appeciated.
View 2 Replies
View Related
Oct 29, 1999
CREATE TRIGGERReportRequestINSERT_trg
ONReportRequest_tbl
FOR INSERTAS
Can an insert trigger like this successfully insert into a linked server's table?
Replication is NOT an option. And I only need to do it in very limited circumstances anyway.
INSERT INTO
[172.22.202.180].MultiVendor.dbo.ReportRequest_tbl
(
ControlCustomerNumber,
UserID,
ReportName,
RequestedDate
)
SELECT
ins.ControlCustomerNumber,
ins.UserID,
ins.ReportName,
ins.RequestedDate
FROM
Inserted ins
View 1 Replies
View Related
Sep 13, 2007
I have 2 database servers ( 2 hardware-servers : A and B ) and I ve written a trigger for update in server A to execute insert statement in server B through linked servers, is there other way to achieve this without linked servers?? All using T-SQL.
Best Regards
Joseph
View 4 Replies
View Related
Aug 2, 2006
i have set up
a linked server. i can query the linked server in query analyzer and
also do update/delete. but when i try to run the same query for linked
server through insert trigger, i get following error: [OLE/DB provider returned message. [Microsoft][ODBC Sql Server Driver]Distributed transaction error].btw, i am using Sql server 2000, SP4. main server is windows 2003 server and linked server is windows xp pro.any suggestions will be appreciated.
View 1 Replies
View Related
Oct 8, 2015
Used linked-server during logon trigger? is it possible? i know we can access tables, SPs, etc with in that server, if i want to check something in other server during logon trigger, can i do it?
View 0 Replies
View Related
Jul 23, 2005
Hi -We have two SQL 2000 Servers. We have the linked server setup and wecan perform updates and inserts between the databases. But when we adda trigger and insert something into a table, the database hangs. Thereare NO processes blocking or being block in either database. This ONLYoccurs when we have one OS as Windows 2000 Server and the other OS asWindows 2003 Server. This problem does not occur when both servers areWindows 2000. Has anyone ran into anything similar to this???*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Jul 20, 2005
Hi there,I'm pretty new to SQL and am having some porblems with a linked server.I have a table on a SQL server which stores employee information.I also have a view on a linked server which stores the same information.What I would like to happen is, whenever the view changes on the linkedserver I want the information to be changed in the table on my server.I've been trying to write a trigger to do this, but have had noluck so far.Can anyone help me?ThanksSimon--Posted via http://dbforums.com
View 1 Replies
View Related
Jul 3, 2006
What is the syntax for creating a update trigger and passing the values which were updated to a stored procedure on a linked server?? Specifically need syntax for updated value, as well as the syntax for executing the stored proc on the linked server.
Thank you
View 1 Replies
View Related
Jul 22, 2007
i did "Linked server" between To Servers , and it's Working.
---------------------------
For Example :
Server 1 =S1.
Server = S2.
i create table in S1 : name = TblS1
i create same table in S2 : name TblS2
and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2
/****************** trigger Code ***************
CREATE TRIGGER dbo.tr_cpD
ON dbo.TblS1
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from insertedEND
**************************************************
result is :
Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14
The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
how i can execute this trigger
View 5 Replies
View Related
Mar 24, 2002
hi everybody.I have linked db2 server .Select from this server goes fine but when i do insert
case A
insert into pricing..UCIT.BOOM(A,B) VALUES(3,5)
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error
case B
SELECT * from
OPENQUERY(pricing, 'insert into UCIT.BOOM(A,B) VALUES(3,5)')
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'insert into UCIT.BOOM(A,B) VALUES(3,5)'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.
how to do insert into linked db2 server?
I am running SQL 2000 sp2 on WIN20000
and DB2 UDB 7.2 service pack 4
View 2 Replies
View Related
Nov 8, 2000
Hi,
I am trying to insert data from a SQL (7.0) table into an Access table but I get an error message saying the MS DTC is nor running. Apart from E.M, how can you check this. Using Query Analyzer I can select form tyhe destination table but not insert - perhaps I have missed something - acn you please help?
View 2 Replies
View Related
Feb 26, 2008
Hey there..
I need to insert some data into a linked server where I need to insert the Identity field. When I try to turn IDENTITY INSERT on, I get this error
The object name 'Server-SQL.MyDatabase.dbo.MyTable' contains more than the maximum number of prefixes. The maximum is 2.
The line I try to execute is this...
SET IDENTITY_INSERT [Server-SQL].MyDatabase.dbo.MyTable ON
From my searching around about this error, the workaround seems to be aliasing the table name, but I can't really see how to use an alias in this situation.
Thanks a bunch!
Greg
View 4 Replies
View Related
Jul 23, 2005
I have two sql servers, I have defined each one as a linked server tothe other. I can mostly access the servers from one another, but I getthe following error on a sql insert.Insert statement...INSERT INTO [U1STSV02].[Custom Log Shipping].dbo.ls_secondary_files(database_name, tl_file_name, tl_applied, lsplanid, lssecid,compression_type) VALUES ('javaweb', 'c:', 'N', 1, 1, 0)i get an error messageServer: Msg 913, Level 16, State 8, Line 1Could not find database ID 10. Database may not be activated yet or maybe in transition.I can query the table with select using the followingselect * from [u1stsv02].[custom log shipping].dbo.ls_secondary_filesand I can delete rows from the table usingdelete from [u1stsv02].[custom log shipping].dbo.ls_secondary_filesI have searched Microsoft's site and googled for a while and cannot seemto find a solution.Both servers are running SQL Server 2000 with service pack 4Thanks in advance for any replies.Steve KuekesPhysicians Pharmacy Alliancejust remove the "1", "2", "3" from my email to reach me.
View 2 Replies
View Related
Mar 6, 2007
hi,on localServer i execute this queryINSERT INTO table (A, B, C)SELECT A, B, C FROM LinkedServer.myDB.dbo.tableeverything is fine. But if i execute this oneINSERT INTO LinkedServer.myDB.dbo.table (A, B, C)SELECT A, B, C FROM tableit is very slow. Is there any solution to make it any faster?
View 2 Replies
View Related
Jul 20, 2005
Both servers running SQL 2000I have set up on our local SQL server (using Enterprise Manager) a linkedserver running on our ISP. Just did new linked server and added remotepassword and login.The following three queries work:insert into LinkedServer.dbname.dbo.Table2select *from LinkedServer.dbname.dbo.Table1select *into LocalTablefrom LinkedServer.dbname.dbo.Table1insert into LocalTableselect *from LinkedServer.dbname.dbo.Table1This query, which is what we really want to do, does not work:insert into LinkedServer.dbname.dbo.Table1select *from LocalTableand returns the error: 'The cursor does not include the table being modifiedor the table is not updatable through the cursor.'I am new to all this and would welcome some help.Adrian
View 1 Replies
View Related
Oct 23, 2006
Insert statement to remote server is running very slowly. I have run Profiler and find there is a 'sp_cursor' call for each row. The source system is SQL2005 and destination is SQL2000(sp4). The linked server is using 'SQL server' type connection. Source query is against a single table with a where clause. source and destination table are identical with Primary keys. Purpose is just to move the rows. Connection is a slow network connection - should be ok. I have already overcome same problem for related update and delete queries by use of 'EXECUTE (query) AT LinkedServer' that works great - but insert can not take advantage of this...
INSERT [LinkedServSQL2000sp4].dbname.schema.tablename
({column list})
Select
{column list}
from tablename
WHERE col1 = '7/20/2006'
AND col2 in (2,5,7,12,32,54,45,33)
Any thoughts?
View 1 Replies
View Related
Jul 14, 2006
I have configured a non-SQL linked server (via an OLE DB provider) and I wish to insert data into it via Service Broker but I am getting the following error in the SQL Server log:
The activated proc [dbo].[sp_ mytableServiceProgram] running on queue TestDB.dbo.mytableQueue output the following: 'Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.'
As you see below, my strored proc. is not issuing any 'save trans' statements, so why is it not allowing me to wrap my code in a transaction? How else can I use a transaction (in order to not lose anything from the queue) and yet still be able to insert to the linked server?
CREATE PROC sp_mytableServiceProgram
AS
SET NOCOUNT ON;
DECLARE
@XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName nvarchar(256),
@Dialog UNIQUEIDENTIFIER;
-- This procedure continues to process messages in the queue until the
-- queue is empty.
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION;
--BEGIN DISTRIBUTED TRANSACTION; --Tried this but didn't help.
-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@Dialog = conversation_handle
FROM mytableQueue
), TIMEOUT 2000 ;
-- If RECEIVE did not return a message, roll back the transaction
-- and break out of the while loop, exiting the procedure.
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END ;
SET @XML = CAST(@MessageBody AS XML);
INSERT INTO LINKEDSERVER.dbname.user.mytable
SELECT tbl.rows.value('@doc_no', 'INT') AS doc_no,
tbl.rows.value('@queryid', 'NVARCHAR(50)') AS queryid,
tbl.rows.value('@ar_num', 'NVARCHAR(50)') AS ar_num,
tbl.rows.value('@status', 'NVARCHAR(20)') AS status,
tbl.rows.value('@creationtime', 'DATETIME') AS creationtime,
tbl.rows.value('@note', 'NVARCHAR(250)') AS note,
tbl.rows.value('@posted', 'NCHAR(1)') AS posted,
tbl.rows.value('@kms', 'INT') AS kms,
tbl.rows.value('@schresid', 'NVARCHAR(50)') AS schresid,
tbl.rows.value('@resolution_code', 'NCHAR(8)') AS resolution_code,
tbl.rows.value('@page_count', 'INT') AS page_count,
tbl.rows.value('@new_serial_number', 'NVARCHAR(20)') AS new_serial_number,
tbl.rows.value('@taskresolution', 'NVARCHAR(250)') AS taskresolution
FROM @XML.nodes('/inserted') tbl(rows);
-- If the INSERT did not insert any rows, rollback.
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END
COMMIT TRANSACTION;
END
GO
View 8 Replies
View Related
Dec 29, 2006
Hi -
I am using linked server to insert data to a table. When I do select, it does show me results but when I do insert, it does not work. My source/destination has exact same data types defined. Any idea?
insert into dbo.tb_PERSONNEL
select * from openquery(CADC1, 'SELECT PERSONNEL_1_NF.ID, PERSONNEL_1_NF.NAME, PERSONNEL_1_NF.PNUM, PERSONNEL_1_NF.PN
FROM PERSONNEL_1_NF')
I get:
Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.
View 1 Replies
View Related
Nov 6, 2005
I'm trying to insert a record into a table and I'm finding that I can only insert the primary key. When I try to insert only another field only it does not happen which makes me think the primary key is not being auto-created and I think that I have to set up a trigger to do thisExample:<CODE> oConn.ExecuteMySQL("INSERT INTO tblUsers(strUserID,strName)VALUES(45,'x')", True)</CODE>.The code above works well, but I want to auto-generate a trigger that will insert a unique strUserID (primary Key) each time a user does an insert so that I dont have to specifically indicate strUserID.<CODE> oConn.ExecuteMySQL("INSERT INTO tblUsers(strName)VALUES('x')", True)</CODE>The code above does not work well because it does not create a strUserID. How do I create a trigger that will auto-insert a primary key into strUserID?
Thanks.Joe
View 3 Replies
View Related
Nov 26, 2014
create or replace
trigger trgRNR_Budget before insert on tblRNR_Budgets
for each row
when (new.BUDGETID is null)
begin
select RNR_BID.nextval into :new.BUDGETID from dual;
end;
/
How to write above before insert trigger for each row in sql server...
View 1 Replies
View Related
Aug 28, 2014
I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies
View Related
Nov 19, 2013
I wonder if it possible to move data from tables on a linked server to a "normal database"?
Name linked server: Covas
Name table on linked server: tblCountries
Name field: cntCountryName
Name "normal" database: CovasCopy
Name "normal" table: Countries (or dbo.Countries)
Name "normal" field: Country
This is just a test setup. I figure that if I get this working the rest will be easier.
My current query:
select * from openquery(COVAS,'
INSERT INTO CovasCopy.dbo.Countries(Country)
SELECT cntCountryName FROM db_covas.tblCountries;')
View 8 Replies
View Related
Oct 12, 2000
Good afternoon one and all,
I have the folowing problem that I could use some help with :
I have an SQL server database acting as a back end to an access dbase. The SQL srv table contains over 32 million records and I am trying to use an append query (in access) to import a further 2 million records to the SQLSRV table. The append query fails with the message 'Insert on table bcdsales failed' followed by an ODBC timeout error message. I can append one record fine but a mass import fails.
Unfortunately i can't use SQL srv to do the import (internal policy says we must stick with access front end for now).
Any and all ideas welcomed.
TIA for your time and attention
Gurmi
View 2 Replies
View Related
Aug 13, 2012
I have a historical table on a dedicated SQL Server (let's call it the reporting db) that is populated every morning with production data that does not already exist. The data in the prod table is purged after 7 days and nothing is ever deleted from the historical table. I have set up the linked server between the two 2008 SQL Servers, but when I try to run this simple query from the reporting DB, it takes more than 5 minutes and still "executing". I eventually have to cancel it:
-- INSERT INTO Temp_Import_historical
SELECT TOP 1 *
FROM [192.168.1.100].ProdDB.dbo.Temp_Import_historical a
WHERE NOT EXISTS (select [Temp_Import_ID] from Temp_Import_historical where a.[Temp_Import_ID] = Temp_Import_historical.[Temp_Import_ID])
I have omitted the INSERT statement on purpose, since I can't even get to output 1 row. Why this is such a resource intensive query?
View 9 Replies
View Related
Nov 30, 2007
I am getting error when I try Inserting data in sybase 12.5 using linked server from SQL2K5
I am able to select
Following is the code i am using.error is same for both stmts
insert into l_syb_ibt.ibtqa.dbo.rajtest (id)values (1)
insert openquery(l_syb_ibt, 'select id from rajtest where 1=0') values (1000)
please help.thanks in advance
following is the error
OLE DB provider "MSDASQL" for linked server "l_syb_ibt" returned message "Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "l_syb_ibt" could not INSERT INTO table "[l_syb_ibt].[ibtqa].[dbo].[rajtest]".
View 5 Replies
View Related
Jul 12, 2007
I have a 2000 machine which calls a stored procedure on another 2000 machine via a linked server. The results come back and insert into a temporary table.
When I use the same code executing the from the 2000 machine over to 2005 machine via a linked server I cannot insert into the table. But I am able to see the data if I remove the insert statement.
I have tried to place the data into a permanent table without success. I have also checked to be sure the linked server properties are the same.
Any help on this would be appreciated. Below is the code. It is very simple and returns only one value but the bigger procedure that is ran returns several records and mutliple columns. This seems to easy but doesn't work.
DECLARE @retval AS INT
DECLARE @value AS INT
SET @value = 4
CREATE TABLE #TempTable (Value DECIMAL(19, 10) NULL)
INSERT INTO #TempTable
EXEC @RetVal = Server.Database.dbo.testproc @value
SELECT * FROM #TempTable
DROP TABLE #TempTable
View 6 Replies
View Related
Jun 5, 2008
Good morning all!
Let us suppose I have a table (tblInfo) with 4 fields. PK, SSN, Date_Created, and Status. Let us assume there are currently 4 records in the field, 3 with the same SSN. Of those 3, the status of 2 are 'inactive' and the other is 'active'.
When I insert a new record into tblInfo, with the same SSN as the other 3, I need the status of those 3 to change to 'inactive' (or stay as inactive if already set) leaving the 4th record with a different SSN alone.
I know I need an insert trigger, but not sure on the actual code of it.
View 2 Replies
View Related
Mar 22, 2006
Hello,I am learning SQL Server 2005.I need to create a trigger which increments number of book'spublications:CREATE TRIGGER InsertPublicationON PublicationsAFTER INSERTASBEGINSET NOCOUNT ON;DECLARE @Num smallintSET @Num = SELECT NumPublications FROM Books WHERE ISBN IN(SELECT ISBN FROM inserted);UPDATE BooksSET NumPublications = @Num + 1WHERE ISBN IN(SELECT ISBN FROM inserted);ENDUnfortunately I receive a message:Incorrect syntax near the keyword 'SELECT'.Could you explain me please how to correct the code?I am new to SQL Server.Thank you very much./RAM/
View 2 Replies
View Related
Sep 25, 2007
i have 2 server named A and B
in A server have database server and B have database server
in A have database named A1 with table TA1 and in B have database named B1 with table TB1
i want if i insert data into database A1 table TA1 in server A, database B1 table TB1 in server B will insert the same data to
how can do that with trigger or other ways
thx u
View 2 Replies
View Related
Oct 30, 2007
Table 1
First_Name
Middle_Name
Surname
John
Ian
Lennon
Mike
Buffalo
Tyson
Tom
Finney
Jones
Table 2
ID
F
M
S
DOB
1
Athony
Harold
Wilson
24/4/67
2
Margaret
Betty
Thathcer
1/1/1808
3
John
Ian
Lennon
2/2/1979
4
Mike
Buffalo
Tyson
3/4/04
5
Tom
Finney
Jones
1/1/2000
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
View 17 Replies
View Related
Feb 5, 2008
A
ID
Name
1
Joe
2
Fred
3
Ian
4
Bill
B
ID
1
4
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?
I believe the trigger should look something like this:
create trigger test_trigger
on a
for insert
as
insert into b(ID)
select i.id
from inserted i
where
--specific USER
View 9 Replies
View Related
Mar 11, 2015
I have created an insert trigger to update an address column with a default when it is null, for different clients using a case statement.
When I import data using the import wizard, the trigger doesn't fire.
Doesn't SQL see an import as an insert?
How can I get this trigger to fire for an import?
View 0 Replies
View Related
