Tuesday, March 27, 2012
Error 8650
error. This code is being executed as a stored procedure in another code tha
t
executes about 5 different sprocs. My question where does one place a query
hint to rectify this error message?
Intra-query parallelism caused your server command (process ID #69) to
deadlock. Rerun the query without intra-query parallelism by using the query
hint option (maxdop 1).You can use the MAXDOP value of 1 as a query hint- see bol for proper use.
"learningSQL" wrote:
> In a normal select into from code, I encountered the intra-query paralleli
sm
> error. This code is being executed as a stored procedure in another code t
hat
> executes about 5 different sprocs. My question where does one place a quer
y
> hint to rectify this error message?
> Intra-query parallelism caused your server command (process ID #69) to
> deadlock. Rerun the query without intra-query parallelism by using the que
ry
> hint option (maxdop 1).
Error 8525: Distributed transaction completed. Either enlist this session ... PROBLEM!
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there'
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefId varchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/defaul...b;en-us;834849.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in m
essage news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/defaul...b;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Error 8525: Distributed transaction completed. Either enlist this session ... PROBLEM!
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there?
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefIdvarchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT
> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/default...;en-us;834849.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/default...;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Error 8525: Distributed transaction completed. Either enlist this session ... PROBLEM!
Fun problem for a Monday morning (NOT!). Running a stored procedure on
one server (SQL 2000, with latest patches), which is similar to the
example below, in connecting to a linked server to archive data out
from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
a simplified part of the actual code to illustrate the functionality).
The stored procedure is run via a job on 'SERVER1'
This was OK, but recently we upgraded to a new clustered server and
are getting these problems now and again. The DBA informs me all looks
OK for the MSDTC and the Component Services is looking OK. Are there
any tips out there'
Thanks for any ideas.
Rob
--Running on SERVER1
CREATE TABLE #RecordsToDelete
(RefId varchar(100) NOT NULL)
INSERT INTO #RecordsToDelete
SELECT RefID FROM SERVER2.MyDB.dbo.MyTable
WHERE [name] = 'Jo Bloggs'
BEGIN DISTRIBUTED TRAN
INSERT INTO HISTORY.dbo.MyTable mt
SELECT * FROM SERVER2.MyDB.dbo.MyTable mt
JOIN #RecordsToDelete rtd ON rtd.RefID = mt.RefID
DELETE FROM SERVER2.MyDB.dbo.MyTable
WHERE RefID IN (SELECT RefId FROM #RecordsToDelete)
COMMIT> Fun problem for a Monday morning (NOT!). Running a stored procedure on
> one server (SQL 2000, with latest patches), which is similar to the
> example below, in connecting to a linked server to archive data out
> from 'SERVER2' to a database on 'SERVER1' (the code below is obviously
> a simplified part of the actual code to illustrate the functionality).
> The stored procedure is run via a job on 'SERVER1'
If the other server is 7.0, then this article might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834849.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<e90GmTCsEHA.1164@.TK2MSFTNGP10.phx.gbl>...
> If the other server is 7.0, then this article might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834849.
No, they are both SQL Server 2000, which makes it confusing as the
only explanation/docmentation on this problem is to do with one server
being SQL Server 7.0!
Regards
Rob
Wednesday, March 21, 2012
Error 7405 with a linked server and stored procedure
Everytime I try to save the procedure I get "error 7405: Heterogenous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection."
I've checked everywhere and set these options on, yet it still doesn't work.
Any ideas?
It's a simple procedure and when I type the sql into query analyzer it works.
HELP?
Here is the procedure.
===============================
CREATE PROCEDURE [dbo].[stpDKLink] AS
select * from openquery(dk, 'select * from pub.invoice')
GO
===============================Yes, SQLServer error message is a bit misleading in this case. Try this out:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[stpDKLink] AS
select * from openquery(dk, 'select * from pub.invoice')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql
error 7405
I try to save the procedure I get the following error
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable these
options and then reissue your query.
I have put SET ANSI_WARNINGS ON as the first line of the procedure with no
help.
Are there any suggestions?
Jim Capehart
The connection creating the stored procedure needs to have
the settings on. So you'd want to create the stored
procedure using something along the lines of:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Fri, 17 Dec 2004 08:11:02 -0800, Jim Capehart
<JimCapehart@.discussions.microsoft.com> wrote:
>I have created a stored procedure wich queries a linked ORACLE database. When
>I try to save the procedure I get the following error
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
>set for the connection. This ensures consistent query semantics. Enable these
>options and then reissue your query.
> I have put SET ANSI_WARNINGS ON as the first line of the procedure with no
>help.
>Are there any suggestions?
>Jim Capehart
|||Guys,
This doesn't necessarily work. If your not happ y with adding these
settings to the the server as a whole then try using the ISQL window to
create the procedure.
Be sure to make a copy of the oprginal (just in case) but this will
allow the SET command to intigated at the top of he procedure.
Hope this helps.
JohnT-A
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message654588.html
error 7391 in distributed transaction
insert into a table after getting a return value from a select done on
another server say B . Server B is set up as linked server on server A. th
error i get shows as
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Has anyone seen this before? Is there any solution to this or any setting
that needs to be changed to get this working? any help will be greatly
appreciated.
TIA
Message posted via http://www.webservertalk.comFor distributed transaction you need to have a Primary key on updated table.
So pLease add primary key to resolve your issue. Please let me know if
doesn't work.
Regards,
Jagan Mohan
MCDBA
"ishaan99 via webservertalk.com" wrote:
> I have a procedure running locally on my server A. This procedure does an
> insert into a table after getting a return value from a select done on
> another server say B . Server B is set up as linked server on server A. th
> error i get shows as
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Has anyone seen this before? Is there any solution to this or any setting
> that needs to be changed to get this working? any help will be greatly
> appreciated.
> TIA
> --
> Message posted via http://www.webservertalk.com
>|||I've had this same error, mine only happened during times when transactions
were high on the system, I think there is a setting to increase the number o
f
concurrent distributed transactions, don't know what else you could do, I
know it has to do with MSDTC.
"ishaan99 via webservertalk.com" wrote:
> I have a procedure running locally on my server A. This procedure does an
> insert into a table after getting a return value from a select done on
> another server say B . Server B is set up as linked server on server A. th
> error i get shows as
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Has anyone seen this before? Is there any solution to this or any setting
> that needs to be changed to get this working? any help will be greatly
> appreciated.
> TIA
> --
> Message posted via http://www.webservertalk.com
>sql
Monday, March 19, 2012
error 7390 when isolation level is set to serializable...
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]
error 7390 when isolation level is set to serializable...
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
-sriniHi srinivas,
Check the following registry key and make sure it is set to 1:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Providers\ADSDSOObject
Thanks,
Chandan
"srinivas" wrote:
> I 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
error 7390 when isolation level is set to serializable...
ing isolation level of serializable as recommended by BOL for purge operatio
ns.
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::QueryInterf
ace returned 0x80004002].
****************************************
*************
Any help will be appreciated
thanks
-sriniHi srinivas,
Check the following registry key and make sure it is set to 1:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\ Providers\ADSDSOObject
Thanks,
Chandan
"srinivas" wrote:
> I am trying to replicate the execution of a stored procedure and hence am
using isolation level of serializable as recommended by BOL for purge operat
ions.
> This following code works fine when executed on the server ( logging in vi
a 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::QueryInte
rface returned 0x80004002].
> ****************************************
*************
> Any help will be appreciated
> thanks
> -srini
error 7390 when isolation level is set to serializable...
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
Is DeleteJobRunResults performing a linked server query using the ADSI
OLEDB provider? This is what the ADSDSOObject is referring to. What is the
MDAC version on the SQL Server and on the machine where Query Analyzer is
running on? If the MDAC on the client is at a lower version, then upgrade
it so that it is at the same level as the SQL Server. You can use Component
Checker to get the MDAC versions. It is available at
http://www.microsoft.com/downloads/d...df6-4a21-4b43-
bf53-14332ef092c9&displaylang=en.
Regards,
Farooq Mahmud [MS SQL Support]
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
error 7390 when isolation level is set to serializable...
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
Hi srinivas,
Check the following registry key and make sure it is set to 1:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Providers\ADSDSOObject
Thanks,
Chandan
"srinivas" wrote:
> I 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
Friday, March 9, 2012
Error 409: The assignment operator operation could not take a text data type as an argumen
I am getting the following error
Error 409: The assignment operator operation could not take a text data type as an argument
===========my sp=================================
CREATE PROCEDURE [dbo].[sp_SelectABC]
(@.a varchar(50) output,
@.b text output
)
AS
set nocount on
select @.a=name, @.b= description from ABC
GONo, you can't do that.
You're going to have to replace it with
select name, description from ABC
Wednesday, March 7, 2012
Error 3930, The current transaction cannot be committed and cannot support
I am trying to exectue this store procedure and I am getting this error
Msg 3930, Level 16, State 1, Line 190
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
I am using a linked server "SODIM" and I am trying to execute a distributed transaction between my SQL Server 2005 Express Edition and this linked server SQL Server 2005 standar Edition.
I need to set the isolation level to serializable because I need to block tbl_GuiasDistribuidores table.
I`m executing this SP under sa user and I dont know if a need certanly permissions in the database or in the MS DTC.
I dont know what I`m missing, Could give me some help?
This is the sp code:
ALTERPROCEDURE [dbo].[PCN_ASIGNARGUIAS]
@.Planta varchar(15),
@.DB varchar(50)
AS
BEGIN
DECLARE @.lvaSentencia VARCHAR(8000)
set @.lvaSentencia ='
SET XACT_ABORT on
begin try
begin transaction
declare @.DisCodigo as varchar(15)
declare @.idProceso as int
declare @.guia as numeric
declare @.idDato as int
declare @.Estado as bit
declare @.GuiaFinal as numeric
declare @.AsignarGuia as varchar(2)
delete tbl_ErrorRangosDistribuidores
IF OBJECT_ID('+'''ErrorGuias'''+') IS NOT NULL
BEGIN
drop table ErrorGuias
END
insert into sodim.'+ @.DB +'.dbo.Tbl_ArchivosSubidos
(Ars_Fecha,
Doc_Codigo,
Ars_NombreArchivo,
Ars_NombreArchivoSubido,
Ars_OrdenProduccion,
Ars_Requerimiento,
Ars_OrdenServicio,
Ars_Ciclo,
Ars_UsuarioUltimoCambioEfectuado,
Ars_FechaUltimoCambioEfectuado)
select Ars_Fecha,
Doc_Codigo,
Ars_NombreArchivo,
Ars_NombreArchivoSubido,
Ars_OrdenProduccion,
Ars_Requerimiento,
Ars_OrdenServicio,
Ars_Ciclo,
Ars_UsuarioUltimoCambioEfectuado,
Ars_FechaUltimoCambioEfectuado
from tbl_archivossubidos
SELECT @.idProceso=a.Ars_Codigo FROM sodim.'+ @.DB +'.dbo.Tbl_ArchivosSubidos a, Tbl_ArchivosSubidos b
where a.Ars_FechaUltimoCambioEfectuado = b.Ars_FechaUltimoCambioEfectuado
and a.Ars_Fecha = b.Ars_Fecha
and a.Ars_NombreArchivo = b.Ars_NombreArchivo
and a.Ars_OrdenProduccion = b.Ars_OrdenProduccion
and a.Doc_Codigo = b.Doc_Codigo
declare CursorCodDistribuidor cursor for
select Dis_Codigo,IndiceOLlaveunica from VisOrdenamientoDatosFinalCiudades
order by Dis_Codigo
open CursorCodDistribuidor
fetch next from CursorCodDistribuidor
into @.DisCodigo,@.idDato
set @.Estado = 1
while @.@.fetch_status = 0
begin
SET TRANSACTION ISOLATION LEVEL Read COMMITTED
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
select @.Estado = Gdis_Estado from sodim.'+ @.DB +'.dbo.tbl_GuiasDistribuidores with(TABLOCKX SERIALIZABLE)
where
Dis_codigo = @.DisCodigo and plan_Codigo = '+ @.Planta +'
while @.Estado = 1
begin
select @.Estado = Gdis_Estado from sodim.'+ @.DB +'.dbo.tbl_GuiasDistribuidores with(TABLOCKX SERIALIZABLE)
where
Dis_codigo = @.DisCodigo and plan_Codigo = '+ @.Planta +'
end
update sodim.'+ @.DB +'.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)
set GDis_Estado = 1
where
Dis_codigo = @.DisCodigo
and plan_Codigo = '+ @.Planta +'
select @.AsignarGuia = Dis_ImprimirGuia from sodim.'+ @.DB +'.dbo.tbl_Distribuidores --with(TABLOCKX SERIALIZABLE)
where Dis_Codigo = @.DisCodigo
if @.AsignarGuia = '+'''SI'''+'
begin
set @.guia = -1
--consulta si hay guias disponibles en la tabla Tbl_GuiasActivas
select @.guia = GAct_Guia from sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas
where dis_Codigo = @.DisCodigo
and plan_Codigo = '+ @.Planta +'
order by GAct_Guia desc
--si no hay guias disponibles en la tabla Tbl_GuiasActivas, la variable @.guia = -1
if @.guia = -1
begin
-- consulta el valor de GDis_RangoFinal y lo almacena en la variable @.GuiaFinal
select @.GuiaFinal = GDis_RangoFinal from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where Dis_codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
-- consulta el valor de la siguiente guia disponible y lo almacena en la variable @.guia
select @.guia = convert(numeric,GDis_ultimaguia) + 1 from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
-- si @.guia > @.GuiaFinal no hay guias disponibles
if @.guia > @.GuiaFinal
BEGIN
--PRINT '+'''@.guia > @.GuiaFinal'''+'
--PRINT @.guia
--PRINT @.GuiaFinal
--consulta el valor de GDis_RangoFinalReserva y lo almacena en la variable @.GuiaFinal
select @.GuiaFinal = GDis_RangoFinalReserva from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where Dis_codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
--si @.GuiaFinal = 0 no existen guias en el rango de reserva (no hay guias para asignar)
if @.GuiaFinal = 0
begin
--PRINT '+'''entro error guias'''+'
insert into Tbl_ErrorRangosDistribuidores
(Dis_Codigo,error)
values
(@.DisCodigo,'+'''Se agot el rango principal y de reserva para el Distribuidor'''+')
--PRINT '+'''despues del insert'''+'
RAISERROR (@.DisCodigo, 16, 1)
end
--no hay guias para asignar pero si hay guias en reserva
else
BEGIN
insert into sodim.'+ @.DB +'.dbo.tbl_HistoricoRangoGuias
(Dis_Codigo,HR_RangoInicial,HR_RangoFinal)
select Dis_Codigo,GDis_RangoInicial,GDis_RangoFinal from
sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
--asigna las guias de reserva al rango principal
update sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
set
GDis_RangoInicial = GDis_RangoInicialReserva
,GDis_RangoFinal = GDis_RangoFinalReserva
,GDis_UltimaGuia = GDis_RangoInicialReserva
,GDis_RangoInicialReserva = 0
,GDis_RangoFinalReserva = 0
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
--selecciona la ultima guia disponible y la almacena en la variable @.guia
select @.guia = GDis_UltimaGuia from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
where dis_codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
update sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
set
GDis_ultimaguia = @.guia
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
insert into Tbl_ErrorRangosDistribuidores
(Dis_Codigo,error)
values
(@.DisCodigo,'+'''se agoto el rango principal para el Distribuidor'''+')
end
end
--no hay guias disponibles en la tabla tbl_guiasActivas y si hay guias disponibles del distribuidor
else
BEGIN
--PRINT '+'''graba ultima guia'''+'
--PRINT @.guia
update sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores
set
GDis_ultimaguia = @.guia
where dis_Codigo = @.DisCodigo
and Plan_Codigo = '+ @.Planta +'
end
end
else
BEGIN
--PRINT '+'''elimina Guia activa'''+'
--PRINT @.guia
delete sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas
where GAct_Guia = @.guia
and dis_Codigo = @.DisCodigo
END
--PRINT '+'''guarda la Guia en Guia asignada'''+'
insert into sodim.'+ @.DB +'.dbo.Tbl_GuiasAsignadas
(Ars_Codigo,Dis_Codigo,GA_Guia,Plan_Codigo)
values
(@.idProceso,@.DisCodigo,@.guia,'+ @.Planta +')
--PRINT '+'''guarda la Guia en datos subidos'''+'
--PRINT @.guia
update dbo.Tbl_datosSubidos
set Guia = @.guia
where IndiceOLlaveunica = @.idDato
END
update sodim.'+ @.DB +'.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)
set GDis_Estado = 0
where
Dis_codigo = @.DisCodigo and plan_Codigo = '+ @.Planta +'
--PRINT '+'''lee otro registro del cursor'''+'
fetch next from CursorCodDistribuidor
into @.DisCodigo,@.idDato
--PRINT '+'''despues del commit'''+'
end
commit transaction
end try
--manejo de errores
begin catch
insert into sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas
(Dis_Codigo,GAct_Guia,Plan_Codigo)
select Dis_Codigo,GA_Guia,plan_Codigo from sodim.'+ @.DB +'.dbo.Tbl_GuiasAsignadas
where Ars_Codigo = @.idProceso
update Tbl_DatosSubidos
set
guia = '+'''SIN DATOS'''+'
update sodim.'+ @.DB +'.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)
set GDis_Estado = 0
--PRINT '+'''mostrar error'''+'
select
ERROR_NUMBER() AS Numero,
ERROR_LINE() AS Linea,
ERROR_MESSAGE() AS Mensaje
into ErrorGuias
rollback transaction
end catch
close CursorCodDistribuidor
deallocate CursorCodDistribuidor
delete sodim.'+ @.DB +'.dbo.Tbl_GuiasAsignadas
where Ars_Codigo = @.idProceso
delete sodim.'+ @.DB +'.dbo.Tbl_ArchivosSubidos
where Ars_Codigo = @.idProceso
SET TRANSACTION ISOLATION LEVEL Read COMMITTED'
--PRINT @.lvaSentencia
EXEC(@.lvaSentencia)
end
try rolling back the failed transaction before you update data in your catch() block.
e.g.
begin catch
if xact_state()=-1 rollback
insert into sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas
Friday, February 24, 2012
Error 2812: Could not find stored procedure and
2000 Developer Edition SP3a it returns an Error 2812: "Could not find
stored procedure"
What's weird is that we configured "Publishing, Subscribers and
Distribution" last night and after some tries we got thru the wixard
and we were able to set up a push subscription to a test database.
Now when we try to set up a new publication SQL server returns a "
server is not enabled as a Publisher on distibutor server" To enable
run "Publishing, Subscribers and Distribution and clich the Publishers
tab". When we do that we can only create a Distributor. Continuing on
that we get a new error: "21045 Cannot uninstall the Distributor
because there are databases enabled for publishing or merge
publishing". There is no way as far as we can see to disconnect any
enabled databases.
We feel like we're walking in circles. Help is greatly appreciated.
Martin,
try running these commands on each database then hopefully you'll be able to
uninstall the distributor and restart.
HTH,
Paul Ibison
exec sp_dboption 'yourdb','published',false
exec sp_dboption 'yourdb','merge publish',false
|||Thanks Paul, I was just about to write that I solved the problem by
uninstalling SQL and reinstalling. It looks like THAT did the trick.
No problems so far.
On Tue, 24 Aug 2004 19:39:33 +0100, "Paul Ibison"
<Paul.Ibison@.Pygmalion.Com> wrote:
>Martin,
>try running these commands on each database then hopefully you'll be able to
>uninstall the distributor and restart.
>HTH,
>Paul Ibison
>exec sp_dboption 'yourdb','published',false
>exec sp_dboption 'yourdb','merge publish',false
>
Error 2812 Could not Find Stored Procedure msdb.dbo.sp_add_jobstep_internal
SQL Server 2000 SP4
reinstalled SP4 and that doesn't help
Error 2812 Could not Find Stored Procedure
msdb.dbo.sp_add_jobstep_internalProbably the easiest solution is to reinstall sp4 (on distributor, publisher
then subscriber). It might be possible to selectively install the
replication scripts but we don't know what else didn't install correctly
(possibly some info in the sp install log file for this) so I'd simply
reinstall sp4 as the simplest way out.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Error 2812 Could not Find Stored Procedure msdb.dbo.sp_add_jobstep_internal
SQL Server 2000 SP4
reinstalled SP4 and that doesn't help
Error 2812 Could not Find Stored Procedure
msdb.dbo.sp_add_jobstep_internal
Probably the easiest solution is to reinstall sp4 (on distributor, publisher
then subscriber). It might be possible to selectively install the
replication scripts but we don't know what else didn't install correctly
(possibly some info in the sp install log file for this) so I'd simply
reinstall sp4 as the simplest way out.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Error 2812 Could not Find Stored Procedure msdb.dbo.sp_add_jobstep_internal
SQL Server 2000 SP4
reinstalled SP4 and that doesn't help
Error 2812 Could not Find Stored Procedure
msdb.dbo.sp_add_jobstep_internalProbably the easiest solution is to reinstall sp4 (on distributor, publisher
then subscriber). It might be possible to selectively install the
replication scripts but we don't know what else didn't install correctly
(possibly some info in the sp install log file for this) so I'd simply
reinstall sp4 as the simplest way out.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Error 2812 Could not Find Store Procedure dbo.MSmerge_expand_sp_A2F.......
Sudently when i am trying to insert a new record on a table i am
getting this Error.
Error 2812 Could not Find Store Procedure
dbo.MSmerge_expand_sp_A2F......
Can anyone help please
Thanks
Savvas Christodoulou
Sounds like this proc is missing. Can you locate it on other subscribers? If
so copy it here.
Normally when you get errors like this your replication metadata is
inconsistent and it is best solved by regenerating and redistributing the
snapshot.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1163094573.683827.298500@.m73g2000cwd.googlegr oups.com...
> Hi i am running SQL 2005 and i have a merge replication.
> Sudently when i am trying to insert a new record on a table i am
> getting this Error.
> Error 2812 Could not Find Store Procedure
> dbo.MSmerge_expand_sp_A2F......
> Can anyone help please
> Thanks
> Savvas Christodoulou
>
Sunday, February 19, 2012
error 241: Syntax error converting datetime from character string
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:
CREATE proc CP_Cashbox @.mID varchar,@.startdate datetime,@.enddate datetime
as
set dateformat dmy
go
declare @.startdate as varchar
declare @.enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@.mID' and njdate between '@.startdate' and '@.enddate'
GO
Thanx in front
CyaOriginally posted by Wimmo
Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:
CREATE proc CP_Cashbox @.mID varchar,@.startdate datetime,@.enddate datetime
as
set dateformat dmy
go
declare @.startdate as varchar
declare @.enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@.mID' and njdate between '@.startdate' and '@.enddate'
GO
Thanx in front
Cya
Simple ... just run this command
sp_helptext cp_cashbox
and you will understand.|||Thanx, i tried what you said but it returns the sp code,
which i edited to
CREATE proc CP_Cashbox @.mID varchar,@.startdate datetime,@.enddate datetime
as
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@.mID' and njdate between '@.startdate' and '@.enddate'
but when i exec it now it returns with the same error as before
the exec command i use is:
exec CP_cashbox @.mID = '10001', @.startdate = '01-01-2000', @.enddate = '01-01-2004'
go
can you tell me what i do wrong?
Thanx
Regards Wim|||Originally posted by Wimmo
Thanx, i tried what you said but it returns the sp code,
which i edited to
CREATE proc CP_Cashbox @.mID varchar,@.startdate datetime,@.enddate datetime
as
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@.mID' and njdate between '@.startdate' and '@.enddate'
but when i exec it now it returns with the same error as before
the exec command i use is:
exec CP_cashbox @.mID = '10001', @.startdate = '01-01-2000', @.enddate = '01-01-2004'
go
can you tell me what i do wrong?
Thanx
Regards Wim
Sorry ... should have seen it first time around
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = @.mID and njdate between @.startdate and @.enddate|||thanx that solves the error,
but the strangest thing happened,
when i exec the sp wit the next code:
exec CP_cashbox @.mID = '10001',@.startdate ='01-01-2000',@.enddate = '01-01-2004'
it returns with a Null value
now when i make a query with the same values it returns the correct value. the query i use is :
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '10001' and njdate between '01-01-2000' and '01-01-2004'
What am i doing wrong
Thanx for your precious time
Greet Wim|||try this :
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = @.mID and convert(varchar,njdate,121) between convert(varchar,@.startdate,121) and convert(varchar,@.enddate,121)|||Originally posted by Enigma
try this :
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = @.mID and convert(varchar,njdate,121) between convert(varchar,@.startdate,121) and convert(varchar,@.enddate,121)
it returns the annoying NULL value again|||Can you paste the DDL of the table ...
CREATE proc CP_Cashbox @.mID varchar(x),@.startdate datetime,@.enddate datetime
where x is the width of the machine_id column in your table|||Originally posted by Enigma
Can you paste the DDL of the table ...
CREATE proc CP_Cashbox @.mID varchar(x),@.startdate datetime,@.enddate datetime
where x is the width of the machine_id column in your table
What is DDL?
the with of the machin_id column is 10|||So .. this should work for you
CREATE proc CP_Cashbox
@.mID varchar(10),
@.startdate datetime,
@.enddate datetime
as
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where
machineID = @.mID
and convert(varchar,njdate,121)
between convert(varchar,@.startdate,121)
and convert(varchar,@.enddate,121)|||Thanx alot man i inserted the with of the machine_id column and now it is working. Thanx to your help i really lose some stress today.
Greet Wim:) :) :cool:|||DBA's dont take stress .. they are some of the coolest persons I have seen ... i personally havent seen a dba fuming in public.|||You are completely right, and i am kinda new to this stuf so i am in the proces of becoming on of the coolest!|||Any time you need help you can rely on some of the best dba's i ve known on this site ... especially ... Brett Kaiser, blindman, rnealjr and satya
i myself rely a lot on this site in emergencies|||Originally posted by Enigma
Any time you need help you can rely on some of the best dba's i ve known on this site ... especially ... Brett Kaiser, blindman, rnealjr and satya
i myself rely a lot on this site in emergencies
Thanx for the tip, i think i gonna need some more help in the near future.