September 9, 2015 at 2:30 pm
I have two different SQL 2008 servers, I don't have permission to create a linked server in any of them. i created a trigger on server1.table1 to insert the same record to the remote server server2.table1 using OPENROWSET
i created a stored procedure to insert this record, and i have no issue when i execute the stored procedure. it insert the recored into the remote server.
the problem is when i call the stored procedure from trigger, i get an error message.
can anyone help me please to solve this issue
Stored Procedure:
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[InsertIntoRemoteTable]
@Description nvarchar(50)
AS
insert into
OPENROWSET(
'SQLNCLI', 'Server=Server2;UID=MySRV2user;PWD=MySRV2Password',
'SELECT Description FROM [RemoteDB].[dbo].[Table_1]')
SELECT @Description
Trigger:
ALTER TRIGGER [dbo].[InsertTable1]
ON [DB1].[dbo].[Table_1]
for insert
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Desc nvarchar(50)
Select @Desc = i.Descr from INSERTED i;
EXEC InsertIntoRemoteTable @Desc
END
When i try to insert a new description value in the table i got the following error message:
No row was updated
the data in row 1 was not committed
Error source .Net SqlClient Data provider.
Error Message: the operation could not be performed because OLE DB
provider "SQLNCLI10" for linked server "(null)" returned message "The partner transaction manager has disabled its support for remote/network transaction.".
correct the errors entry or press ESC to cancel the change(s).
Thanks and appreciate your help
September 9, 2015 at 2:53 pm
Boy this sure looks familiar. http://stackoverflow.com/questions/32481405/trigger-fire-on-each-inserted-row-to-insert-into-remote-table
I will post the same issues.
Your trigger is not set based and will NOT work when there is more than 1 row being inserted.
Most importantly is if you use any search engine for your EXACT error message you will be rewarded with thousands of articles where people solved the error you are running into.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2015 at 3:48 pm
It's a REALLY bad idea to make INSERTs dependent on the availability of a remote server. Your trigger should write to a "local" table and you should have a separate job to pipe the rows to the remote server in this "poor mans" version of replication. And, no... that's not a slam on what you're trying to do because I do it quite often. I'm just telling you from experience that you're making a mistake in your method. Avoid making INSERTs dependent on anything other than local tables.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2015 at 3:18 pm
Thank you Sean Lange
Your trigger is not set based and will NOT work when there is more than 1 row being inserted.
I am inserting the new records one by one
so only one record will be saved on the same time.
and what i understood, the trigger will fire when we insert new single record. am i right?
how can i add the where condition to select the current new single record that going to be saved. can you show me a sample please
thank you for your help
September 10, 2015 at 3:23 pm
Thank you Jeff Moden
the problem is i don't have enough permission to create task or linked server. this why i am using this complicated way to save the record to the remote table
thank you again
September 10, 2015 at 8:56 pm
mohelsheikh (9/10/2015)
Thank you Jeff Modenthe problem is i don't have enough permission to create task or linked server. this why i am using this complicated way to save the record to the remote table
thank you again
Too funny. They'll give you a login and password to the remote system, which you have use in clear text (REALLY bad idea) in a trigger but they won't let you create a linked server or job? Will they allow their DBA to make such things?
You really need to tell whomever it is you're working for that they're violating even the simplest of security protocols and they're making your money maker machine dependent on an external machine. Seriously... the life you safe may be theirs.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 11, 2015 at 7:06 am
mohelsheikh (9/10/2015)
Thank you Sean LangeYour trigger is not set based and will NOT work when there is more than 1 row being inserted.
I am inserting the new records one by one
so only one record will be saved on the same time.
and what i understood, the trigger will fire when we insert new single record. am i right?
how can i add the where condition to select the current new single record that going to be saved. can you show me a sample please
thank you for your help
Yes it will fire for a single row insert but DO NOT make the mistake of assuming that is how it will always work. You will end up at some point being forced to go back and fix it because there will be some task at some point that requires multiple rows to be inserted and the trigger will only capture one row. One of the regulars around here was a consultant at a company that literally had to close the doors because they had triggers everywhere and they were developed with the notion that the application only inserts one at a time so they didn't need to worry about it. The cost of fixing all their triggers was more than they could afford so they closed up shop as a result. Don't let that happen to your company, especially since you have been warned.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2015 at 7:19 am
mohelsheikh (9/9/2015)
I have two different SQL 2008 servers, I don't have permission to create a linked server in any of them. i created a trigger on server1.table1 to insert the same record to the remote server server2.table1 using OPENROWSETi created a stored procedure to insert this record, and i have no issue when i execute the stored procedure. it insert the recored into the remote server.
the problem is when i call the stored procedure from trigger, i get an error message.
can anyone help me please to solve this issue
Stored Procedure:
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[InsertIntoRemoteTable]
@Description nvarchar(50)
AS
insert into
OPENROWSET(
'SQLNCLI', 'Server=Server2;UID=MySRV2user;PWD=MySRV2Password',
'SELECT Description FROM [RemoteDB].[dbo].[Table_1]')
SELECT @Description
Trigger:
ALTER TRIGGER [dbo].[InsertTable1]
ON [DB1].[dbo].[Table_1]
for insert
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Desc nvarchar(50)
Select @Desc = i.Descr from INSERTED i;
EXEC InsertIntoRemoteTable @Desc
END
When i try to insert a new description value in the table i got the following error message:
No row was updated
the data in row 1 was not committed
Error source .Net SqlClient Data provider.
Error Message: the operation could not be performed because OLE DB
provider "SQLNCLI10" for linked server "(null)" returned message "The partner transaction manager has disabled its support for remote/network transaction.".
correct the errors entry or press ESC to cancel the change(s).
Thanks and appreciate your help
OPENROWSET creates and then drops a linked server - see your error message.
Think carefully about how you proceed with this. You have a ton of good advice from Jeff and Sean, steering you away from the two data disasters-waiting-to-happen which they've exposed and which you would have introduced into your own system if DTC hadn't balked.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 11, 2015 at 8:05 am
mohelsheikh (9/10/2015)
Your trigger is not set based and will NOT work when there is more than 1 row being inserted.
I am inserting the new records one by one
so only one record will be saved on the same time.
and what i understood, the trigger will fire when we insert new single record. am i right?
No, not entirely.
A trigger on a table fires ONCE for each INSERT / UPDATE / DELETE statement.
Maybe your code, now, today, is only inserting one row at a time, but in the future you/someone may add/change code to do a multi-row INSERT / UPDATE / DELETE ... and then your one-row-trigger will fail.
You need to write your trigger so that it assumes that INSERT / DELETED tables contain multiple rows.
OR you could raise an error
IF (SELECT COUNT(*) FROM INSERTED) > 1
but, personally, I would never write a trigger that could only handle one row at a time.
September 11, 2015 at 8:33 am
Thank you ChrisM@Work
when i execute the procedure i have no issue (this mean the procedure syntax is correct and using OPENROWSET is not wrong), but when i call it from the trigger i got this error message.
i tried what Jeff said, but I don't have enough permission to create linked server or create replication between these two tables
Sean said the syntax is not correct, and i appreciate if he can send the the syntax that he believe it will work.
This all what they advice. and i appreciate if our expert can find a solution for that issue.
September 11, 2015 at 8:48 am
mohelsheikh (9/11/2015)
when i execute the procedure i have no issue (this mean the procedure syntax is correct and using OPENROWSET is not wrong), but when i call it from the trigger i got this error message.
A difference, which may be relevant, is that from inside a trigger there is an implicit transaction. I suspect that is then requiring a two-phase-commit including both local and remote servers, and the remote server doesn't want to play that game (as least, not in THIS way).
September 11, 2015 at 8:50 am
mohelsheikh (9/11/2015)
Thank you ChrisM@Workwhen i execute the procedure i have no issue (this mean the procedure syntax is correct and using OPENROWSET is not wrong), but when i call it from the trigger i got this error message.
i tried what Jeff said, but I don't have enough permission to create linked server or create replication between these two tables
Sean said the syntax is not correct, and i appreciate if he can send the the syntax that he believe it will work.
This all what they advice. and i appreciate if our expert can find a solution for that issue.
I never said the syntax is wrong, I said you have some major logical errors in your code. The logical errors are two fold. One in the trigger which is assuming there is only one row in inserted. The second logical error is in the procedure that you call because your select statement does NOT have a where clause to only insert the newly inserted rows into the remote table.
I have also told you repeatedly in this AND the thread on SO that your friend google has thousands of articles that explain how to fix the error message you encountered. I even provided a link to a google search for you. I am trying to help but you seem to be unable or unwilling to put any effort into this. After each few posts you ask the same questions again and again. There are a number of things in this process that are just not right.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2015 at 11:38 am
This isn't piling on, but you really need to re-think the architecture here. As has been mentioned, the trigger is not written to handle a set-based insert which means if you insert more than 1 row at a time, you will only transfer a random row to the other server. As Jeff mentioned, you do not want an insert to be dependent on another server/database being available. The remote database could be corrupt, there could be a network issue, or a reboot for maintenance purposes and your initial update is going to fail. There are many other, more reliable ways to do this. Jeff has shared using a local queue table as one method. Another option is Service Broker, which is designed exactly for this purpose. Put a message in a local queue that asynchronously sends the message to a remote queue which can then reads the message and inserts the row. Yes it will take some code, but it will be more reliable and can be coded to handle set-based operations.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply