The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Corrupt user Record
Posted: 31 Oct 07 7:36 AM
|
I have one user record that throws an error when I attempt to open it from the Administrator. I get the error:
Error inializing form: The statement has been terminated. : Length of text, ntext, or image data (76909) to be replicated exceeds configured maximum 65536
I ran the SLXProfiler and concluded it is the USERSECURITY.Security field - Below is the statement the profiler shows when the screen craps out:
---------- Client SQL ---------
UPDATE USERSECURITY SET SECURITY=[DBTYPE_BYTES,length=76909] WHERE USERID=[DBTYPE_STR,"U6UJ9A00003L"]
---------- Executed SQL ----------
UPDATE USERSECURITY SET SECURITY=?,MODIFYDATE='20071031 12:23:23',MODIFYUSER='ADMIN' WHERE USERID=?
Can I recover this record? I don't want to lose the user's history.. Thanks
|
|
|
|
Re: Corrupt user Record
Posted: 01 Nov 07 5:14 AM
|
Try using "Copy Profile".
Use a "good" user and copy the security over to the "bad" user.
ws |
|
|
|
Re: Corrupt user Record
Posted: 01 Nov 07 6:52 AM
|
Good Idea, no dice - same error when trying to run the utility. I also get the same error trying to login as the user. I have tried replacing the usersecurity and useroption records from a backup that doesn't throw the error.. I tried deleting just the Security field (blob) for the user.. Still getting the same error..
I am going to recommend retiring the user to preserve history and recreating. |
|
|
|
Re: Corrupt user Record
Posted: 02 Nov 07 12:20 PM
|
How many entries are in the Calendar tab in the user profile dialog?
I would think there would have to be well over 400 of them to make the BLOB reach that size, but that's not an impossibility. |
|
|
|
Re: Corrupt user Record
Posted: 08 Nov 07 8:07 AM
|
This one gets weirder - now I am seeing the same error during the Sync Cycle and when trying to cut a remote... I don't think it is limited to that one user..
John, I can't tell how many entries as I can't open the user's profile. |
|
|
|
Re: Corrupt user Record
Posted: 09 Nov 07 11:07 AM
|
Steve, one thing I have noticed is that the Profiler won't show you a statement unless it successfully ran, I think. The reason I think it's this way is because when I run the Profiler to track slow queries, the slow query won't show up until after finishes running. In order to see which query and which field is causing you trouble, I would nail down exactly what point you get to with the corrupted user record (noting what queries run right before it), and then open up a good user record with the profiler running, and identify the query that you never get to, the one that comes right after the last query run before the error on the bad user record - I think that is the query that will point you to the correct table and field, and you might find the problem.
I hope that's helpful. |
|
|
|
Re: Corrupt user Record
Posted: 09 Nov 07 11:14 AM
|
Ok, sorry Steve, I noticed a little more on your question, and it does appear that the statement you have there from the Profiler shows you adding something with the length of 76909.
I am confused as to why an update statement is being run when you try to open up a user's record. I ran the profiler to see if I could find the statement, and I found no updates. |
|
|
|
Re: Corrupt user Record
Posted: 09 Nov 07 11:19 AM
|
Jeff, I have an update. The client actually deleted the user (not my recomendation) to see if that would fix the problem in other places. Didn't work. We googled the error and found a Stored Proc that fixed the issue:
http://www.novicksoftware.com/TipsAndTricks/tip-sql-server-image-replication-maximum-size-configured.htm>
Solution: Use sp_configure to increase 'max text repl size'
The default value for the maximum configuration size is only 65536. Once it's increased, the insert can proceed. To increate the size execue sp_configure on 'max text repl size'. This stored procedure does the job:
CREATE PROC usp_CONFIGURE_ReplicationSizeForBlobs
@NewSize int = 100000000
/*
* Sets the 'max text repl size' instance wide configuration setting
* that governs the maximum size of an image, text, or ntext column
* in a replicated table.
*
* Example:
exec usp_CONFIGURE_ReplicationSizeForBlobs default
**********************************************************************/
AS
print 'Old size'
exec sp_configure 'max text repl size'
print ' Setting new size'
exec sp_configure 'max text repl size', @NewSize
print 'Reconfiguring'
RECONFIGURE WITH OVERRIDE
print 'New size'
exec sp_configure 'max text repl size'
|
|
|
|
Re: Corrupt user Record
Posted: 09 Nov 07 11:35 AM
|
Wow - replication - didn't see that one coming.
I'm going to be dealing with replication soon, so that's a very good one to know... |
|
|
|
Re: Corrupt user Record
Posted: 09 Nov 07 11:38 AM
|
| As many other posters would note, I am glad you can benefit from my pain. That is what SLXDeveloper is all about. |
|
|
|