Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have user who is trying to access some tables from our Microsoft SQL Server 2008 R2 system (which very recently got moved to a brand new hardware/software stack) using Access 2007 via an ODBC connection, a method they've been using for years. Now they're getting 'Reserved Error (-7711); there is no message for this error.'

In my googling process I haven't been able to identify any relevant information that isn't related to 2003 versions of Sybase. I'm guessing there's some security of configuration setting I've forgotten to set on the new box but to me the error seems pretty obscure and I'm not sure where to go from here.

Any help you can provide would be greatly appreciated.

share|improve this question

migrated from stackoverflow.com Jan 17 '12 at 17:31

This question came from our site for professional and enthusiast programmers.

    
Did the advent of the error coincide with the movement of the SQL Server to the new stack? –  Robert Harvey Jan 17 '12 at 17:30
    
Yes it did; however, other users are able to connect correctly using Access 2003, in addition, existing connects seem to work find, it's on certain machines creating new connections. –  nickrobison Jan 17 '12 at 17:34

2 Answers 2

up vote 1 down vote accepted

Turns out the issue was with an assembly created with 'Unrestricted' permissions, when trying the ODBC connection under Excel I received a more verbose error related to a .NET error and this assembly, changing the 'Permission set' to 'Safe' seemed to solve the problem.

share|improve this answer

I have also found that I receive this error when SQL Server (and presumably any other ODBC provider) returns an overly-long error message. In my own testing I found that the error is generated when an ODBC error message is more than 511 characters long.

I have not found any documentation listing 511 characters as the max length for these messages but it does correspond to a round binary number (29 = 512), so it makes sense as a limit in that regard.

Stock error messages are unlikely to be that long, but if you are returning a custom error message it is entirely possible. The workaround I went with was to catch the error and trim it before rethrowing it.

There is some default text that is always returned ([Microsoft][ODBC SQL Server Driver][SQL Server]) at the start of the message. That string may be slightly different depending on how the table has been linked, so you may need to adjust the trim length to suit.

511 - Len("[Microsoft][ODBC SQL Server Driver][SQL Server]") = 464

So we need to trim the original error message to 464 characters. I do this as follows:

BEGIN TRY
  BEGIN TRANSACTION
       -- The T-SQL to execute goes here
  COMMIT TRANSACTION
END TRY
BEGIN Catch
  IF @@TRANCOUNT > 0
      Rollback
  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = LEFT(ERROR_MESSAGE(),464),  -- adjust 464 based on above formula
         @ErrSeverity = ERROR_SEVERITY()
  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.