October 21, 2009 at 10:20 am
Hi All,
I have a SQL 2000 job that calls a stored proc. The job step returns the message "Timeout expired". But the procedure is running in query analyzer successfully.
Can anyone help me out from this?
October 22, 2009 at 6:27 am
Have you checked to see what else is running at the time the job is running the procedure? It could be being blocked by another process. You can build a trace and see what is happening.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 24, 2009 at 8:49 am
There is no job running while i am trying to run the said procedure.
It is taking around one minut for running in query analyzer.
This is the command i am using in sql job step.
osql -E -S EEx -Q "Exec Testdb.dbo.SP_GETEXCHANGERATEFRMAMEX_DIV_TEMP"
Please help me out from this.
October 24, 2009 at 9:18 am
Hi
can you just tell me how can i change my coonnection time property in my procedure.
please che ck my procedure.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE SP_GETEXCHANGERATEFRMAMEX_DIV_TEMP AS
set xact_abort on
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
--BEGIN DISTRIBUTED TRANSACTION
DECLARE @DEFAULTCUR NUMERIC
SELECT @DEFAULTCUR=DEF_CUR_ID FROM OR_DEFAULTS
DECLARE @EX_CUR_ID NUMERIC
DECLARE @EX_TT_ID NUMERIC
DECLARE @EX_RATE NUMERIC(18,8)
DECLARE @EX_UpdatedBy NVARCHAR(50)
DECLARE @EX_UpdatedOn DATETIME
DECLARE EXCURSOR CURSOR FOR
SELECT TBL_CCY_EXC_MST.V_EXC_FRM_CCY AS EX_CUR_ID,
OR_TRANSACTIONTYPE.TT_ID AS EX_TT_ID,
TBL_CCY_EXC_MST.N_EXC_SLS_DFL AS EX_RATE,
'AMEX' AS EX_UpdatedBy,
TBL_CCY_EXC_MST.D_UPD_DAT AS EX_UpdatedOn
FROM
(SELECT V_EXC_FRM_CCY_TYP,
V_EXC_FRM_CCY,
(1/N_EXC_SLS_DFL) N_EXC_SLS_DFL,
D_UPD_DAT
FROM AMEXORA..AMEXHO.TBL_CCY_EXC_MST
WHERE V_EXC_TO_CCY = 11
AND V_EXC_TO_CCY_TYP = 0
AND V_EXC_FRM_CCY_TYP <> 0
AND V_EXC_RT_OPR = 'D'
AND V_EXC_FRM_CCY NOT IN (11, 92))
TBL_CCY_EXC_MST INNER JOIN
OR_TRANSACTIONTYPE ON
TBL_CCY_EXC_MST.V_EXC_FRM_CCY_TYP = OR_TRANSACTIONTYPE.TT_AMEX_REF
ORDER BY OR_TRANSACTIONTYPE.TT_ID,
TBL_CCY_EXC_MST.V_EXC_FRM_CCY
FOR READ ONLY
OPEN EXCURSOR
FETCH NEXT FROM EXCURSOR INTO @EX_CUR_ID, @EX_TT_ID, @EX_RATE,
@EX_UpdatedBy, @EX_UpdatedOn
WHILE @@FETCH_STATUS=0
BEGIN
IF EXISTS(SELECT * FROM OR_EXCHRATE WHERE EX_CUR_ID=@EX_CUR_ID AND
EX_TT_ID=@EX_TT_ID)
UPDATE OR_EXCHRATE SET EX_RATE=@EX_RATE,
EX_UpdatedBy=@EX_UpdatedBy, EX_UpdatedOn=@EX_UpdatedOn WHERE
EX_CUR_ID=@EX_CUR_ID AND EX_TT_ID=@EX_TT_ID
ELSE
INSERT INTO OR_EXCHRATE (EX_CUR_ID, EX_TT_ID, EX_RATE,
EX_UpdatedBy, EX_UpdatedOn) VALUES (@EX_CUR_ID, @EX_TT_ID, @EX_RATE,
@EX_UpdatedBy, @EX_UpdatedOn)
--if @@rowcount>0 Requested by Samson to comment 4 lines starting here 25/05/2006
--print 'Updated new Exchange Rate for currency ' +
--convert(nvarchar(10),@EX_CUR_ID) + ' - ' +
--convert(nvarchar(50),dateadd(Hour,4,getutcdate()))
FETCH NEXT FROM EXCURSOR INTO @EX_CUR_ID, @EX_TT_ID, @EX_RATE,
@EX_UpdatedBy, @EX_UpdatedOn
END
CLOSE EXCURSOR
DEALLOCATE EXCURSOR
--COMMIT TRANSACTION
SET XACT_ABORT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
October 24, 2009 at 9:21 am
You are sure there is nothing else running in the database at the time you are executing the osql command? What is happening in the procedure?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 24, 2009 at 9:34 am
Thanks for your reply.
I am sure that nothing is running at the time of executing this procedure..
The procedure is taking some data from linked server and saving to local database.
I am new for this SQL server and we need to remove one intermediate server.
So i should run this procedure to the local dB.
Please helpme from this mess?
Whether i need to change anything in connection timeout property.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply