September 14, 2010 at 4:05 am
Folks,
I am struggling to find out any solution of one of production SQL Server 2000 job that is failing with error
message Executed as user: ABC\SQLadmin. xp_readmail: failed with operating system error 80 [SQLSTATE 42000] (Error 18024). The step failed.
Previously it was running successfully then suddenly it stopped complaining above message. If anybody has idea how to deal with it, it would be appreciated.
Server detail is
SQL Server 2000 (8.00.760) , SP3, Standard edition
Kind Regards,
Muhammad
September 14, 2010 at 5:41 am
Operating System Error 80 = The file already exists
How are you using xp_cmdshell? Can you please post the code snippet?
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 14, 2010 at 5:53 am
Thanks Pradeep,
No, there is a job that call a SP. SP code is
declare @status int
declare @msg_id varchar(94)
declare @originator varchar(255)
declare @cc_list varchar(255)
declare @msgsubject varchar(255)
declare @query varchar(8000)
declare @messages int
declare @mapifailure int
declare @resultmsg varchar(80)
declare @filename varchar(12)
declare @current_msg varchar(94)
declare @subject varchar(255)
declare @bitCallout bit
declare @strServerName varchar(20)
declare @strServerName1 varchar(20)
declare @strSql varchar(8000)
declare @strcompany char(2)
declare @strServerRunning varchar(30)
set nocount on
SELECT @bitCallout=0
select @messages=0
select @mapifailure=0
/* get first message id */
exec @status = master.dbo.xp_findnextmsg
@msg_id=@msg_id output,
@unread_only='true'
if @status <> 0
select @mapifailure=1
while (@mapifailure=0)
begin
if @msg_id is null break
if @msg_id = '' break
exec @status = master.dbo.xp_readmail
@msg_id=@msg_id,
@originator=@originator output,
@cc_list=@cc_list output,
@subject=@msgsubject output,
@message=@query output,
@peek='TRUE',
@suppress_attach='true'
if @status <> 0
begin
select @mapifailure=1
break
end
/*********************************************************
Check if the subject contains the word RUNNING
**********************************************************/
if charindex('RUNNING:',@msgsubject) > 0
begin
SELECT 'Server Substring ---- ', ltrim(rtrim(substring(@msgSubject,9,charindex(':',@msgSubject,9)-9))), @msgSubject
SELECT
@bitCallout= Callout, @strServerName1= ServerName
FROM
tblServersToCheck
WHERE rtrim(ServerName) = ltrim(rtrim(substring(ltrim(@msgSubject),9,charindex(':',ltrim(@msgSubject),9)-9)))
SELECT 'Server Substring', ltrim(rtrim(substring(ltrim(@msgSubject),9,charindex(':',ltrim(@msgSubject),9)-9)))
SELECT 'bit flag', @bitCallout, 'ServerName',@strServerName1
/***************************************
RUNNING Server - call IPS Routine
****************************************/
--IF @bitRUNNING = 1
--- JMT 30 August 2007 - >=0 instead of >0
IF @bitCallout >=0
BEGIN
/****************************************************
RUNNING server mark message as read
*****************************************************/
exec @status = master.dbo.xp_readmail
@msg_id=@msg_id,
@originator=@originator output,
@cc_list=@cc_list output,
@subject=@msgsubject output,
@message=@query output,
@peek='FALSE',
@suppress_attach='true'
SELECT @strServerRunning = ltrim(rtrim(substring(ltrim(@msgSubject),9,charindex(':',ltrim(@msgSubject),9)-9)))
SELECT '@StrServerRunning 222',@msgSubject
SELECT 'HERE '
SELECT @strcompany=company
FROM
tblServersToCheck
WHERE ServerName = rtrim(substring(@msgSubject,9,charindex(':',@msgSubject,9)-9))
AND Callout = 1
SET @bitCallout = 0
SELECT @strServerRunning = ltrim(rtrim(substring(ltrim(@msgSubject),9,charindex(':',ltrim(@msgSubject),9)-9)))
SELECT '@StrServerRunning',@strServerRunning
Exec spConfirmServersRunning @strServerRunning
SELECT 'HERE 2'
END
end
/* get new message id before processing & deleting current */
select @current_msg=@msg_id
exec @status = master.dbo.xp_findnextmsg
@msg_id=@msg_id output,
@unread_only='true'
if @status <> 0
begin select @mapifailure=1
end
/********************************************************/
if charindex('RUNNING',@msgsubject) > 0
begin
exec master.dbo.xp_deletemail @current_msg
if @status <> 0
begin
select @mapifailure=1
break
end
select @messages=@messages+1
end /* end of checking block */
end /* end of xp_findnextmsg loop */
/* finished examining the contents of inbox; now send results */
if @mapifailure=1
begin
raiserror(15079,-1,-1,@messages)
select 1 ----return(1)
end
else
select 0 -----return(0)
GO
September 14, 2010 at 10:39 am
Does the C drive has enough space? Also cleaning up the temp directory of the SQL Server service account should help.
It is usually located at
C:\Documents and Settings\[ServiceAccount]\Local Settings\Temp
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 15, 2010 at 3:12 am
Thanks Pradeep.
solved 🙂
Kind Regards,
Muhammad
September 15, 2010 at 10:50 am
Muhammad, glad to know that it worked 🙂
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply