Error 8525 after SQL2000 SP3 install

After installing SP3 for SQL 2000, have been getting the following error when running some statements against a linked SQL 7.0 (SP4) server:

Server: Msg 8525, Level 16, State 1, Procedure <procname>, Line <linenum>Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

The linked server is SQL7.0 SP4. This error seems to only occur when the statement run from SQL2000 SP3 is contained in an explicit transaction like following:

SET XACT_ABORT ONBEGIN TRANSACTION

<some data modification statements (local or linked)>

DELETE REMOTESERVER.DBNAME.OWNER.TABLEWHERE ......

<some more data modification statements (local or linked)>

COMMIT TRANSACTION

The error does not occur if the BEGIN TRAN / COMMIT TRAN statements are removed.

MS Knowledgebase has a couple postings over the last two years describing similar problems, and both were supposedly resolved by "installing latest service pack" or "upgrading to latest MDAC". Since I'm running the 'latest' SP and MDAC appropriate for each of the two platforms involved, it would appear that this error has come back to life with the latest MDAC which is included in SQL2000 SP3.

The error does not occur if linked server is SQL2000 SP3.

I'm in the process of trying to put together a package for MS Support to reliably reproduce the error.

Has anyone else seen this after a SQL2000 SP3 upgrade?

DavekoNew Member

25 Feb 2003 12:16 PM

Not that specific errors but I am also having a new problem after upgrading to SQL2000 SP3. I have been running a sp_makewebtask which executes a stored procedure that makes a call over a Linked server. Since the upgrade I have been getting Msg 7410 Remote access not allowed for Windows NT user activated by SETUSER.

I think our errors may be related.

Anyone else have similar errors or found fix's?

mimadonNew Member

25 Feb 2003 12:35 PM

Is the 'linked' server running SQL7.0?

If not, I doubt our errors are related. As I mentioned in my posting, my problem does not occur if the 'linked' server is running SQL2000.

Although I may not have made it abundantly clear, all other linked server functionality seems to check out fine in our environment after the SP3 upgrade. Only the statement construction in my posting seems to suffer.

satyaNew Member

25 Feb 2003 11:53 PM

This problem comes when data length is more then the feild length, check if any.

HTH

mimadonNew Member

26 Feb 2003 05:23 AM

That is not the case, but thanks for the thought.

As my posting indicates, the problem statements can be as simple as a single-table DELETE statement.

Although it is not a single table statement, following is an example of code (using the PUBS database) that reproduces the error if the 'local' server is SQL2000 SP3 and the 'linked' server (REMOTESERVER) is SQL7.0 SP4:

Also, if the BEGIN TRAN and COMMIT TRAN are commented out, no error occurs regardless of the SP level on the 'local' SQL2000 server.

I encourage you to test this code if you have similar SQL Server versions available.

Thanks!

mimadonNew Member

27 Feb 2003 12:33 PM

Problem solved.

MS Support indicated that I would need to run the SQL2000 SP3 version of instcat.sql on the remote 'linked' sql 7.0 server to correct the problem.

They were 100% correct. That fixed the problem....

mimadonNew Member

11 Mar 2003 11:34 AM

The note at the top of instcat.sql is apparently intended for MS developers. You can ignore it.

We simply ran the script on the SQL 7.0 server. The script automatically figures out what SQL Server version it's being run against and configures the MDAC-related system tables and procedures appropriately.

satyaNew Member

11 Mar 2003 11:54 PM

Taker, what was the problem/error you're getting?

satyaNew Member

19 Mar 2003 12:12 AM

The steps you've mentioned to remove transaction from SP is followed to get rid of error 8525.

In general INSTCAT.SQL can be executed to upgrade the catalog stored procedure to be compatible with higher versions. If you have test bed with similar setup you can get the results. And I never had any trouble in executing that script against my production servers.

MSDN article refers The Instcat.sql script generates many messages. Most of these indicate how rows were affected by Transact-SQL statements issued by the script. These messages can be ignored, although the output should be scanned for messages that indicate an execution error.

The Instcat.sql script fails when there is not enough space available in the master database to store the catalog stored procedures or to log the changes to existing procedures. If the Instcat.sql script fails, contact your system administrator.

HTH

satyaNew Member

29 Apr 2003 04:56 AM

Those are informational messages generated when you run INSTCAT.SQL script, and I recently executed the same on one of the production services without any trouble. Make sure the issue before has been resolved and check thru SQL error log for any information.

LeeNew Member

21 Feb 2005 10:46 AM

I just got the error also, sql 2000 boxes. Did you resolve?

BalintNNew Member

08 Feb 2006 03:34 AM

I just came across the same problem.
When running a DTS package, the mentioned error came up at a specific step. It turned out, that though the error was displayed at this step, the one that actually generated it was the previous one.
After identifing this it was easy to find the code that threw an error.

So it seems, that when you get this error message from DTS, it may not be generated by that step, and the rolled back distributed transaction hides the source of the problem.

Thus the advice: make sure that the error occures where SQL Server sais it does. Do a binary search (remove code step-by-step) to locate the real source of the error. Fix there.