Error 8525 after SQL2000 SP3 install

Last Post 11 Mar 2003 12:34 PM by mimadon. 12 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
mimadon
New Member
New Member

--
25 Feb 2003 09:22 AM
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 ON
BEGIN TRANSACTION

<some data modification statements (local or linked)>

DELETE REMOTESERVER.DBNAME.OWNER.TABLE
WHERE ......

<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?


Daveko
New Member
New Member

--
25 Feb 2003 01: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?
mimadon
New Member
New Member

--
25 Feb 2003 01: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.

satya
New Member
New Member

--
26 Feb 2003 12:53 AM
This problem comes when data length is more then the feild length, check if any.

HTH
mimadon
New Member
New Member

--
26 Feb 2003 06: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:

-------------------------------------------------
CREATE TABLE #temp_authors
( au_lname varchar(40) not null)

SET XACT_ABORT ON
BEGIN TRAN

DELETE a
FROM remoteserver.pubs.dbo.authors a
JOIN #temp_authors b
ON b.au_lname = a.au_lname

COMMIT TRAN

DROP TABLE #temp_authors
--------------------------------------------------

If the 'local' server is SQL2000 SP2 no error occurs.

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!
mimadon
New Member
New Member

--
27 Feb 2003 01: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....
mimadon
New Member
New Member

--
11 Mar 2003 12:34 PM
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.
satya
New Member
New Member

--
12 Mar 2003 12:54 AM
Taker, what was the problem/error you're getting?
satya
New Member
New Member

--
19 Mar 2003 01: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
satya
New Member
New Member

--
29 Apr 2003 05: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.
Lee
New Member
New Member

--
21 Feb 2005 11:46 AM
I just got the error also, sql 2000 boxes. Did you resolve?
BalintN
New Member
New Member

--
08 Feb 2006 04: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.

Bye,
BalintN
SQLUSA
New Member
New Member

--
19 Feb 2006 03:41 AM
What is the reason using SQL7 when SQL9 is on the market?


Kalman Toth, SQL Database Architect
http://www.sqlusa.com/orderdoubleheader/

You are not authorized to post a reply.

Acceptable Use Policy