am trying to replicate the execution of a stored procedure and hence am using isolation level of serializable as recommended by BOL for purge operations.
This following code works fine when executed on the server ( logging in via terminal services) bu gives me an error when executed thru Query Analyzer.
Really puzzled.
__________________________________________________ ______
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
begin transaction
exec dbo.DeleteJobRunResults @.SourceSystem = 'GSP Risk Engine',
@.SourceSystemCode = '13397'
commit transaction
go
__________________________________________________ _______
************************************************** *
Server: Msg 7390, Level 16, State 1, Line 1
The requested operation could not be performed because the OLE DB provider 'ADSDSOObject' does not support the required transaction interface.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IUnknown::QueryInterface returned 0x80004002].
************************************************** ***
Any help will be appreciated
thanks
-srini
the error message is coming from the ADSI (Active Directory) provider.
is active directory in the mix somehow?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"srini" <srini@.discussions.microsoft.com> wrote in message
news:32ED50DF-5820-42FF-88E8-E7A7475DAE3D@.microsoft.com...
> am trying to replicate the execution of a stored procedure and hence am
using isolation level of serializable as recommended by BOL for purge
operations.
> This following code works fine when executed on the server ( logging in
via terminal services) bu gives me an error when executed thru Query
Analyzer.
> Really puzzled.
> __________________________________________________ ______
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> begin transaction
> exec dbo.DeleteJobRunResults @.SourceSystem = 'GSP Risk Engine',
> @.SourceSystemCode = '13397'
> commit transaction
> go
> __________________________________________________ _______
> ************************************************** *
> Server: Msg 7390, Level 16, State 1, Line 1
> The requested operation could not be performed because the OLE DB provider
'ADSDSOObject' does not support the required transaction interface.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
IUnknown::QueryInterface returned 0x80004002].
> ************************************************** ***
> Any help will be appreciated
> thanks
> -srini
|||Hilary:
Is there a way to figure that out ? Just joined a new job and this
workstation was being used by another developer. Let me look at the proc in
more detail
-srini
"Hilary Cotter" wrote:
> the error message is coming from the ADSI (Active Directory) provider.
> is active directory in the mix somehow?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "srini" <srini@.discussions.microsoft.com> wrote in message
> news:32ED50DF-5820-42FF-88E8-E7A7475DAE3D@.microsoft.com...
> using isolation level of serializable as recommended by BOL for purge
> operations.
> via terminal services) bu gives me an error when executed thru Query
> Analyzer.
> 'ADSDSOObject' does not support the required transaction interface.
> IUnknown::QueryInterface returned 0x80004002].
>
>
|||Here is the code. inherited it ;-)
*******************************************
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DeleteJobRunResults
-- procedure to delete job run results given the source system's job id
-- INPUT Parameters
@.SourceSystemVARCHAR(80),
@.SourceSystemCodeVARCHAR(255)
-- OUTPUT Parameters
AS
BEGIN
DECLARE @.ErrNumINT
DECLARE @.CountINT
DECLARE @.ResultsType CHAR(2)-- 'MD' = market data
-- 'TV' = trade valuations
DECLARE @.JobRunUID INT
SELECT
@.JobRunUID = JobRunUID
FROM
JobRun jr
INNER JOIN
CodeLegend cl
ON jr.SourceSystemUID = cl.CodeLegendUID
WHERE
jr.SourceSystemCode = @.SourceSystemCode AND
cl.FullName = @.SourceSystem
-- determine if the job results are market data or trade valuations
IF EXISTS (SELECT 1 FROM JobMarketData WHERE JobRunUID = @.JobRunUID)
SELECT @.ResultsType = 'MD'
ELSE
SELECT @.ResultsType = 'TV'
IF @.ResultsType = 'TV'
BEGIN
CREATE TABLE #TradeValueUIDs (
TradeValueUID INT,
PRIMARY KEY CLUSTERED (TradeValueUID))
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
INSERT #TradeValueUIDs
SELECT TradeValueUID
FROM
JobTradeValue jtv1
WHERE
JobRunUID = @.JobRunUID AND
(SELECT COUNT(*) FROM JobTradeValue jtv2 WHERE jtv1.TradeValueUID =
jtv2.TradeValueUID) = 1
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
-- This holds shared locks on TradeValue until the transaction commits.
The count is not used.
SELECT @.Count = COUNT(*) FROM TradeValue tv WITH (HOLDLOCK) INNER JOIN
#TradeValueUIDs tvu ON tv.TradeValueUID = tvu.TradeValueUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE PnlComponentFROM PnlComponent pcINNER JOIN #TradeValueUIDs tvu ON
pc.TradeValueUID = tvu.TradeValueUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE PnLMark FROM PnLMark pm INNER JOIN #TradeValueUIDs tvu ON
pm.TradeValueUID = tvu.TradeValueUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE CDSens FROM CDSens cdsINNER JOIN #TradeValueUIDs tvu ON
cds.TradeValueUID = tvu.TradeValueUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE CSSens FROM CSSens css INNER JOIN #TradeValueUIDs tvu ON
css.TradeValueUID = tvu.TradeValueUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE IRSens FROM IRSens irs INNER JOIN #TradeValueUIDs tvu ON
irs.TradeValueUID = tvu.TradeValueUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE IRVega FROM IRVega irv INNER JOIN #TradeValueUIDs tvu ON
irv.TradeValueUID = tvu.TradeValueUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE SimpleValue FROM SimpleValue sv INNER JOIN #TradeValueUIDs tvu ON
sv.TradeValueUID = tvu.TradeValueUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE JobTradeValueWHERE JobRunUID = @.JobRunUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE TradeValueFROM TradeValue tvINNER JOIN #TradeValueUIDs tvu ON
tv.TradeValueUID = tvu.TradeValueUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE StatusMessageWHERE JobRunUID = @.JobRunUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE JobRunWHERE JobRunUID = @.JobRunUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
END
ELSE
BEGIN
CREATE TABLE #MarketDataUIDs (
MarketDataUID INT,
PRIMARY KEY CLUSTERED (MarketDataUID))
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
INSERT #MarketDataUIDs
SELECT MarketDataUID
FROM
JobMarketData jmd1
WHERE
JobRunUID = @.JobRunUID AND
(SELECT COUNT(*) FROM JobMarketData jmd2 WHERE jmd1.MarketDataUID =
jmd2.MarketDataUID) = 1
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
-- This holds shared locks on TradeValue until the transaction commits.
The count is not used.
SELECT @.Count = COUNT(*) FROM MarketData md WITH (HOLDLOCK) INNER JOIN
#MarketDataUIDs mdu ON md.MarketDataUID = mdu.MarketDataUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE FXRateFROM FXRate fxINNER JOIN #MarketDataUIDs mdu ON
fx.MarketDataUID = mdu.MarketDataUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE IRRate FROM IRRate ir INNER JOIN #MarketDataUIDs mdu ON
ir.MarketDataUID = mdu.MarketDataUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE CreditCurveSpreads FROM CreditCurveSpreads ccsINNER JOIN
#MarketDataUIDs mdu ON ccs.MarketDataUID = mdu.MarketDataUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE IRDiscountRate FROM IRDiscountRate idrINNER JOIN
#MarketDataUIDs mdu ON idr.MarketDataUID = mdu.MarketDataUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE JobMarketDataWHERE JobRunUID = @.JobRunUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE MarketDataFROM MarketData mdINNER JOIN #MarketDataUIDs mdu ON
md.MarketDataUID = mdu.MarketDataUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE StatusMessageWHERE JobRunUID = @.JobRunUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
DELETE JobRunWHERE JobRunUID = @.JobRunUID
SELECT @.ErrNum = @.@.ERROR
IF @.ErrNum <> 0 GOTO Exit_no_log
END
RETURN 0
Exit_no_log:
RETURN 999
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"srini" wrote:
[vbcol=seagreen]
> Hilary:
> Is there a way to figure that out ? Just joined a new job and this
> workstation was being used by another developer. Let me look at the proc in
> more detail
> -srini
> "Hilary Cotter" wrote:
|||there is no ADSI there. Let me look into this further.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"srini" <srini@.discussions.microsoft.com> wrote in message
news:6543B87F-DA49-44AC-A2D1-FD4340A5969F@.microsoft.com...
> Here is the code. inherited it ;-)
> *******************************************
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE DeleteJobRunResults
> -- procedure to delete job run results given the source system's job id
> -- INPUT Parameters
> @.SourceSystem VARCHAR(80),
> @.SourceSystemCode VARCHAR(255)
> -- OUTPUT Parameters
> AS
> BEGIN
> DECLARE @.ErrNum INT
> DECLARE @.Count INT
>
> DECLARE @.ResultsType CHAR(2) -- 'MD' = market data
> -- 'TV' = trade valuations
> DECLARE @.JobRunUID INT
>
> SELECT
> @.JobRunUID = JobRunUID
> FROM
> JobRun jr
> INNER JOIN
> CodeLegend cl
> ON jr.SourceSystemUID = cl.CodeLegendUID
> WHERE
> jr.SourceSystemCode = @.SourceSystemCode AND
> cl.FullName = @.SourceSystem
>
> -- determine if the job results are market data or trade valuations
> IF EXISTS (SELECT 1 FROM JobMarketData WHERE JobRunUID = @.JobRunUID)
> SELECT @.ResultsType = 'MD'
> ELSE
> SELECT @.ResultsType = 'TV'
>
> IF @.ResultsType = 'TV'
> BEGIN
> CREATE TABLE #TradeValueUIDs (
> TradeValueUID INT,
> PRIMARY KEY CLUSTERED (TradeValueUID))
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
>
> INSERT #TradeValueUIDs
> SELECT TradeValueUID
> FROM
> JobTradeValue jtv1
> WHERE
> JobRunUID = @.JobRunUID AND
> (SELECT COUNT(*) FROM JobTradeValue jtv2 WHERE jtv1.TradeValueUID =
> jtv2.TradeValueUID) = 1
>
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
>
> -- This holds shared locks on TradeValue until the transaction commits.
> The count is not used.
> SELECT @.Count = COUNT(*) FROM TradeValue tv WITH (HOLDLOCK) INNER JOIN
> #TradeValueUIDs tvu ON tv.TradeValueUID = tvu.TradeValueUID
>
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE PnlComponent FROM PnlComponent pc INNER JOIN #TradeValueUIDs tvu ON
> pc.TradeValueUID = tvu.TradeValueUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE PnLMark FROM PnLMark pm INNER JOIN #TradeValueUIDs tvu ON
> pm.TradeValueUID = tvu.TradeValueUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE CDSens FROM CDSens cds INNER JOIN #TradeValueUIDs tvu ON
> cds.TradeValueUID = tvu.TradeValueUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE CSSens FROM CSSens css INNER JOIN #TradeValueUIDs tvu ON
> css.TradeValueUID = tvu.TradeValueUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE IRSens FROM IRSens irs INNER JOIN #TradeValueUIDs tvu ON
> irs.TradeValueUID = tvu.TradeValueUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE IRVega FROM IRVega irv INNER JOIN #TradeValueUIDs tvu ON
> irv.TradeValueUID = tvu.TradeValueUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE SimpleValue FROM SimpleValue sv INNER JOIN #TradeValueUIDs tvu ON
> sv.TradeValueUID = tvu.TradeValueUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE JobTradeValue WHERE JobRunUID = @.JobRunUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE TradeValue FROM TradeValue tv INNER JOIN #TradeValueUIDs tvu ON
> tv.TradeValueUID = tvu.TradeValueUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE StatusMessage WHERE JobRunUID = @.JobRunUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE JobRun WHERE JobRunUID = @.JobRunUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> END
> ELSE
> BEGIN
> CREATE TABLE #MarketDataUIDs (
> MarketDataUID INT,
> PRIMARY KEY CLUSTERED (MarketDataUID))
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> INSERT #MarketDataUIDs
> SELECT MarketDataUID
> FROM
> JobMarketData jmd1
> WHERE
> JobRunUID = @.JobRunUID AND
> (SELECT COUNT(*) FROM JobMarketData jmd2 WHERE jmd1.MarketDataUID =
> jmd2.MarketDataUID) = 1
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> -- This holds shared locks on TradeValue until the transaction commits.
> The count is not used.
> SELECT @.Count = COUNT(*) FROM MarketData md WITH (HOLDLOCK) INNER JOIN
> #MarketDataUIDs mdu ON md.MarketDataUID = mdu.MarketDataUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
>
> DELETE FXRate FROM FXRate fx INNER JOIN #MarketDataUIDs mdu ON
> fx.MarketDataUID = mdu.MarketDataUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE IRRate FROM IRRate ir INNER JOIN #MarketDataUIDs mdu ON
> ir.MarketDataUID = mdu.MarketDataUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE CreditCurveSpreads FROM CreditCurveSpreads ccs INNER JOIN
> #MarketDataUIDs mdu ON ccs.MarketDataUID = mdu.MarketDataUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE IRDiscountRate FROM IRDiscountRate idr INNER JOIN
> #MarketDataUIDs mdu ON idr.MarketDataUID = mdu.MarketDataUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE JobMarketData WHERE JobRunUID = @.JobRunUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE MarketData FROM MarketData md INNER JOIN #MarketDataUIDs mdu ON
> md.MarketDataUID = mdu.MarketDataUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE StatusMessage WHERE JobRunUID = @.JobRunUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> DELETE JobRun WHERE JobRunUID = @.JobRunUID
> SELECT @.ErrNum = @.@.ERROR
> IF @.ErrNum <> 0 GOTO Exit_no_log
> END
> RETURN 0
> ----
--[vbcol=seagreen]
> Exit_no_log:
> RETURN 999
> END
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> "srini" wrote:
in[vbcol=seagreen]
am[vbcol=seagreen]
in[vbcol=seagreen]
provider[vbcol=seagreen]
