November 17, 2011 at 11:28 am
Hi Everyone,
This one has be puzzzled. I have a sp that runs fine on its own like so:
exec dbo.dsbn_get_LoginNames_from_DSBNAC @school_code='SWC',@panel='SEC'
but when I try to insert it's output into a table ( be it a temporary table variable or real table) like so:
declare @t table
(
login_name varchar(50),
company_name varchar(100),
student_no varchar(20)
)
insert @t
exec dbo.dsbn_get_LoginNames_from_DSBNAC @school_code='SWC',@panel='SEC'
select * from @t
I get this:
Msg 3930, Level 16, State 1, Line 10
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
(0 row(s) affected)
If I change the @school_code parameter to some other value and try the whole statment it MAY or MAY NOT work, bit it ALWAYS works just running the exec statement, no matter what parameter is in @school_code....
Any ideas why I am getting this error ONLY when trying to insert
Thanks,
Max
November 17, 2011 at 11:46 am
Are you using Distributed Transaction / Linked Server?
November 17, 2011 at 12:36 pm
Hi,
Yes it is doing an OPENQUERY to an AD domain controller through a linked server connection. I was able to fix this issue after doing some more digging and removing a try..catch block in the code that does the OPENQUERY.
By default AD domain controllers only return 1000 rows for any given AD query. If the row count was over 1000, then the error would occur ( which explains why changing the @school_code parameter sometimes made a difference ). By increasing the MaxPageSize LDAP policy of the AD DC to something larger totally eliminated the error. This KB tells you all you need to know:
http://support.microsoft.com/kb/315071
Lesson... Watch where you place your try...catch blocks especially if you are using them just to suppress errors !
November 18, 2011 at 6:35 am
Try SET XACT_ABORT ON; in the beginning of your code and turn it OFF after that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply