Job fails - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Job fails

Author

Topic

vaidyanathanpc
Starting Member

24 Posts

Posted - 2002-05-24 : 07:03:19

Hi,
I have scheduled a job to run everyday night. The job is failing sometimes and not always with this error.

OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312). The step failed.

Sometimes I get a timeout message too..

If I run the job again without changing anything, it runs perfectly.
What could be the problem?
Thanks
P.C. Vaidyanathan

izaltsman
A custom title

1139 Posts

Posted - 2002-05-24 : 08:51:20

How about telling us what that job actually does? From the looks of this error message, I believe it might have been raised by a maintenance plan... In that case you should probably check maintenance plan history to figure out why it bombed.

vaidyanathanpc
Starting Member

24 Posts

Posted - 2002-05-25 : 01:42:12

This jobs checks for user ids on my database and inserts the same into another database on the linked server.
I checked the job history. It is giving the same message... The linked server is using windows authentication whereas I'm using sql server authentication. Can this cause problems?

JamesH
Posting Yak Master

149 Posts

Posted - 2002-05-29 : 08:18:37

It might be related to the user you actually have specified as the owner. If you're using DTS and scheduling it then you should be okay. If you have a sp that is being executed by a non-sa account in SQL Server Agent, then you are probably getting slammed by the setuser statement. This is by design.

Is this a DTS job that has been scheduled or just a regular SQL Agent job that you created??

Is this your error message: "Server: Msg 7410, Level 16, State 1, Line 1 Remote access not allowed for Windows NT user activated by SETUSER.
"??

JamesH.

vaidyanathanpc
Starting Member

24 Posts

Posted - 2002-05-30 : 07:18:01

No my error is not the one you have specified. the error I get sometimes is

The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support distributed transactions. [SQLSTATE 42000] (Error 7391) [SQLSTATE 01000] (Error 7312). The step failed.

JamesH
Posting Yak Master

149 Posts

Posted - 2002-05-30 : 08:23:53

Looks like you're having problems with DTC, make sure that it is running and then try to run the job again.

I'm confused though, are these two SQL Servers or is one an Access Database? The error 7399 which you reported first is related to the Microsoft OLE DB provider for Jet...

If it's an Access Database then let me know and we can work from there.

JamesH.

vaidyanathanpc
Starting Member

24 Posts

Posted - 2002-05-31 : 02:56:14

Hi,
I am explaining the job that runs on my server.

I have written a procedure as below. I'm connected to a remote server server1 with valid login id and permissions. When I run the procedure, I get the following error.

Server: Msg 7391, Level 16, State 1, Procedure procAddMissingAssociates, Line 09
The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support distributed transactions.
[OLE/DB provider returned message: Distributed transaction error]

Sometimes I get the following error.

OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312). The step failed.

MSDTC is enabled on both the server, the local as well as the remote ones.
In the example below ASSOCIATE_ID in table2 is a primary key.

Both the servers are SQl Server 7.0 versions.
The job I have mentioned about runs this procedure as a scheduled task every night.

Thanks
P.C. Vaidyanathan

CREATE PROCEDURE procAddMissingAssociates
AS
DECLARE @USERID INT

BEGIN
SET TRANSACTION ISOLATION LEVEL repeatable read

BEGIN DISTRIBUTED TRANSACTION
SET XACT_ABORT ON

DECLARE MISSING_CURSOR CURSOR FOR
SELECT USR_ID
FROM SERVER1.DB1.DBO.TABLE1
WHERE USR_ID NOT IN (SELECT COLUMN2
FROM TABLE2)

OPEN MISSING_CURSOR
FETCH FROM MISSING_CURSOR
INTO @USERID

WHILE (@@FETCH_STATUS=0)
BEGIN
INSERT INTO TABLE2 (ASSOCIATE_ID,
REDEEM_POINTS,
UPDATED_DATE)
VALUES (@USERID,
0,
GETDATE())
FETCH NEXT FROM MISSING_CURSOR
INTO @USERID
END
CLOSE MISSING_CURSOR
DEALLOCATE MISSING_CURSOR
COMMIT TRANSACTION
END
GO

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-31 : 14:54:01

Why use a cursor in your proc? (it's fridy afternoon, which means cursor-fightn' time...) If you do the task as a single dml statement it'll be wrapped in an implicit transaction and integrity will be assured...


CREATE PROCEDURE procAddMissingAssociates 
AS 
insert table2 (
    associate_id,
    redeem_points,
    updated_date )
select
    usr_id,
    0,
    getdate()
from
    server1.db1.dbo.table1 t1
where
    not exists (
        select 47
        from
            table2
        where
            column2 = t.usr_id)
GO

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources