November 10, 2014 at 6:46 am
Hi all,
I have a system where people can apply for registration and also a registration fee is involved. The screen where the application is captured has a couple of inserts into different tables. Each of these tables have triggers on them to transfer data to a linked server and they work fine. Today I just added another trigger on a different table but also part of this same save process. Now I am getting the following error:
Microsoft OLE DB Provider for SQL Server error '80004005'
The statement has been terminated.
/orisys.int.za/applicants/ApplicantInsert.asp, line 111
I do not know this error and think it might be time-out. Can someone please tell me more about this?:w00t::w00t::w00t::w00t::w00t:
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
November 10, 2014 at 6:58 am
manie (11/10/2014)
Hi all,I have a system where people can apply for registration and also a registration fee is involved. The screen where the application is captured has a couple of inserts into different tables. Each of these tables have triggers on them to transfer data to a linked server and they work fine. Today I just added another trigger on a different table but also part of this same save process. Now I am getting the following error:
Microsoft OLE DB Provider for SQL Server error '80004005'
The statement has been terminated.
/orisys.int.za/applicants/ApplicantInsert.asp, line 111
I do not know this error and think it might be time-out. Can someone please tell me more about this?:w00t::w00t::w00t::w00t::w00t:
Did you even try to research this issue? It is NOT a timeout. Try searching for the exact string you posted in the first line of your post. It is a login error.
_______________________________________________________________
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/
November 12, 2014 at 4:04 am
Yes I did research and yes, I did find the login problem but that was not exactly the same as my problem. I did however check out the login problem. Actually that was one of the first things I checked and no, that was not my problem. Maybe I should have said that from the start. I could not find something in connection with triggers and linked server. The reason for thinking it might have anything to do with time out is that I did get a timeout at first.
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
November 12, 2014 at 6:20 am
Well the error message you are getting is a login failure. Since this is across a linked server the failure is almost certainly on the other end.
_______________________________________________________________
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/
November 12, 2014 at 6:26 am
Sean Lange (11/12/2014)
Well the error message you are getting is a login failure. Since this is across a linked server the failure is almost certainly on the other end.
Then why is it that I could do the data transfer to the linked server until I added this trigger? My recommendation to my clients was that this last trigger should not be added and that the owners of the linked server should handle this part on their own. The research I have done shows the same error number as a login error but a different message.
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
November 12, 2014 at 6:32 am
manie (11/12/2014)
Sean Lange (11/12/2014)
Well the error message you are getting is a login failure. Since this is across a linked server the failure is almost certainly on the other end.Then why is it that I could do the data transfer to the linked server until I added this trigger? My recommendation to my clients was that this last trigger should not be added and that the owners of the linked server should handle this part on their own. The research I have done shows the same error number as a login error but a different message.
Without more details it is impossible to say. It may be permissions for the new table?
_______________________________________________________________
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/
November 12, 2014 at 10:46 am
Sean Lange (11/12/2014)
Without more details it is impossible to say. It may be permissions for the new table?
I'd have to agree with Sean on this one, that there is something wrong with the permissions. It may be that the user the linked server is using does not have insert permissions on the new table you are trying to insert into.
I'd also say that moving data across linked servers using a trigger is a very bad idea. There are too many things that can go wrong and will cause the initial transaction to roll back and I've never been in a situation where I'd want that to happen. I don't know what the requirements are for moving this data across servers, but this problem cries out for an asynchronous queue-based solution. Ideally you'd use Service Broker, but you could do a roll your own queuing solution.
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
November 12, 2014 at 11:12 am
You may also want to check if there are any EXECUTE AS statements in the trigger definition. This would change the credentials being used (and indirectly which credentials are accessing the linked server).
(EDIT: this was assuming the TRIGGER is what is invoking the linked server).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 13, 2014 at 12:26 am
Thanks all for your replies. As I said in the beginning the trigger sits on a database passing data to the linked server database. This data is not transferred to another server but another instance on the same sever. Yes, Jack I agree fully that it is madness to do triggers like this but that is what the client wants and that is what they are going to get. There is no EXECUTE AS for or in this trigger. I created the table on the linked server database but I will definitely look at the permissions on the tables on both sides. Once again thanks for all the comments.
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
November 13, 2014 at 6:32 am
manie (11/13/2014)
Yes, Jack I agree fully that it is madness to do triggers like this but that is what the client wants and that is what they are going to get. There is no EXECUTE AS for or in this trigger.
If you are in as a consultant you should be trying to eliminate the madness and offer a better solution. You are there as the expert.
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
November 13, 2014 at 11:55 pm
If you are in as a consultant you should be trying to eliminate the madness and offer a better solution. You are there as the expert.
Jack, you are right but this is a very complicated situation with which I don't want to bore you. I made suggestions as well as offered better solutions but the clients is convinced this is the way to go. Thanks anyway for your input.:hehe::hehe::hehe::hehe::hehe:
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply