Whenever we restart sql server the following error appears:
2005-12-20 12:34:22.28 server Attempting to initialize Distributed
Transaction Coordinator.
2005-12-20 12:34:22.31 server Failed to obtain
TransactionDispenserInterface: Result Code = 0x8004d01b
When I check the MSDTC service in the SQL service manager to see if is
running - the service says it is running. Do I still have anything to worry
about?
We are running SL server 2000 sp3a.
thanks
MeenalHi
Checking other posts it seems that this is a warning telling you that MSDTC
has not started at the time and most people seem to ignore it. You could make
the SQL Service dependent on MSDTC although this is not a usual condition and
will slow down the bootup process.
John
"Meenal Dhody" wrote:
> Whenever we restart sql server the following error appears:
> 2005-12-20 12:34:22.28 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2005-12-20 12:34:22.31 server Failed to obtain
> TransactionDispenserInterface: Result Code = 0x8004d01b
> When I check the MSDTC service in the SQL service manager to see if is
> running - the service says it is running. Do I still have anything to worry
> about?
> We are running SL server 2000 sp3a.
> thanks
> Meenal
>
>|||Many thanks
Meenal
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ECF7845A-79A0-4D8E-91EC-0D367454B336@.microsoft.com...
> Hi
> Checking other posts it seems that this is a warning telling you that
MSDTC
> has not started at the time and most people seem to ignore it. You could
make
> the SQL Service dependent on MSDTC although this is not a usual condition
and
> will slow down the bootup process.
> John
> "Meenal Dhody" wrote:
> > Whenever we restart sql server the following error appears:
> >
> > 2005-12-20 12:34:22.28 server Attempting to initialize Distributed
> > Transaction Coordinator.
> > 2005-12-20 12:34:22.31 server Failed to obtain
> > TransactionDispenserInterface: Result Code = 0x8004d01b
> >
> > When I check the MSDTC service in the SQL service manager to see if is
> > running - the service says it is running. Do I still have anything to
worry
> > about?
> > We are running SL server 2000 sp3a.
> >
> > thanks
> > Meenal
> >
> >
> >|||Meenal Dhody Wrote:
> Many thanks
> Meenal
> "John Bell" <jbellnewsposts (AT) hotmail (DOT) com> wrote
THis won't cause any issue because SQL server starts earlier than DTC
This happens due to chain process, that DTC depends on RPC and it take
long time.
If you able to access SQL server once it is released for use then ok
Else you can add depend on in the registry of regedt32 for sql server
Cheers,
muth
--
Muth
----
Muthu's Profile: http://www.dbtalk.net/m37
View this thread: http://www.dbtalk.net/t30290
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Wednesday, March 21, 2012
Wednesday, March 7, 2012
error in in nested try catch-
HI,
getting error like this while using nested try catch.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
also it doesnt roll back because of error, instead of other procedures,insert ,update are executing ending with wrong creations......works partially.
if the try catch is removed form subprocedure1 it works perfectly.
below is the example exactly what i use with more exec procedures in main procedure.
main procedure
begin
begin try
begin transaction
exec subprocedure 1
insert.....
update
COMMIT TRANSACTION
END TRY
BEGIN CATCH
insert into spErrorLog(spName, params, errorMsg)
values('dbo.project_inspectionproject_save', @.newprojectnumber, @.@.error)
if @.@.error <> 0
begin
if @.@.trancount > 0 ROLLBACK TRANSACTION
end
END CATCH
end
sub procedure 1
Begin Try
insert into yy(a,c,c)values(a,b,c)
select @.@.identity
End Try
Begin Catch
IF (XACT_STATE())=-1 ROLLBACK TRANSACTION
insert into spErrorLog(spName, params, errorMsg)
values(@.spName, '', @.errorMsg)
select -1
RAISERROR(@.errorMsg, @.errSeverity, 1)
End Catch
please help me. struggling with for long time.
venp..
Perhaps your RAISERROR in the called proc is not a severity level high enough to force the error in the calling sproc, thereby when the attempted COMMIT finds no active TRANSACTION, you are getting the Transaction Count error message.
Try checking @.TRANCOUNT before the commit just like you do on the ROLLBACK -OR make sure that your RAISERROR is a high enough severity level (is it over 10?) to case the CATCH failure.
|||HI,
if i remove the try catch from the main procedure sub procedure works fine always. right now i'm using
if @.@.error >o
rollback transaction
--
in my main procedure . i'm using the above st for every transaction st. I dont want to use this old one. Please help me with try catch.()
it doesnt produce any error right now.(just without try catch on main)
my problem is some other person is working on this sub procedure. I 've the main procedure. we both are in situation ro rollback the whole if something goes wrong.
venp
Wednesday, February 15, 2012
error handling using JDBC DRIVER
Hi All,
I ammaking use of the microsoft JDBC driver.
In the SP, in a transaction when a error is encountered --> fatal error (PK violation / incompatible data types) then the control returns to the application abruptly.
the error handling within the SP is not carried out.
Any help on the same is appreciated.
Thanks
Anu
| Thread-Topic: error handling using JDBC DRIVER
| thread-index: AcR1WumpXFxDagmKQe6fJDRWMRkPsQ==
| X-WBNR-Posting-Host: 203.200.17.10
| From: "=?Utf-8?B?QW51cmFkaGE=?=" <Anuradha@.discussions.microsoft.com>
| Subject: error handling using JDBC DRIVER
| Date: Thu, 29 Jul 2004 03:58:00 -0700
| Lines: 15
| Message-ID: <DE5BB305-8AEA-4E26-A84E-F7CDA3A3DA82@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6213
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi All,
|
| I ammaking use of the microsoft JDBC driver.
|
| In the SP, in a transaction when a error is encountered --> fatal error
(PK violation / incompatible data types) then the control returns to the
application abruptly.
|
| the error handling within the SP is not carried out.
|
| Any help on the same is appreciated.
|
| Thanks
| Anu
|
|
|
|
Hello,
Can you provide sample code that demonstrates this problem?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
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.
|||Hi @.ll
i have the same-problem
sample code:
SP USER_REGISTER
@.username char(25),@.usershowname char(25), @.useremail
char(100),@.userpasswort varchar(10)
AS
declare @.message varchar(300), @.errno bigint
set @.message='Registrierung wird durchgeführt'
insert into game_user(user_name,user_showname,user_email,user_ passwort)
values(ltrim(rtrim(@.username)),ltrim(rtrim(@.usersh owname)),ltrim(rtrim(@.useremail)),ltrim(rtrim(@.use rpasswort)))
set @.errno = @.@.error
print @.errno
IF @.errno <> 0
BEGIN
if @.errno = 2627
begin
set @.message='Username oder Email-Adresse bereits registriert'
end
END
ELSE
BEGIN
set @.message ='User wurde registriert'
END
select @.message as RESULT
end SP
The application get the errorcode 2627 ....
"Carb Simien [MSFT]" wrote:
> --
> | Thread-Topic: error handling using JDBC DRIVER
> | thread-index: AcR1WumpXFxDagmKQe6fJDRWMRkPsQ==
> | X-WBNR-Posting-Host: 203.200.17.10
> | From: "=?Utf-8?B?QW51cmFkaGE=?=" <Anuradha@.discussions.microsoft.com>
> | Subject: error handling using JDBC DRIVER
> | Date: Thu, 29 Jul 2004 03:58:00 -0700
> | Lines: 15
> | Message-ID: <DE5BB305-8AEA-4E26-A84E-F7CDA3A3DA82@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6213
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | Hi All,
> |
> | I ammaking use of the microsoft JDBC driver.
> |
> | In the SP, in a transaction when a error is encountered --> fatal error
> (PK violation / incompatible data types) then the control returns to the
> application abruptly.
> |
> | the error handling within the SP is not carried out.
> |
> | Any help on the same is appreciated.
> |
> | Thanks
> | Anu
> |
> |
> |
> |
> Hello,
> Can you provide sample code that demonstrates this problem?
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> 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.
>
|||| Thread-Topic: error handling using JDBC DRIVER
| thread-index: AcSfHIJuOFthVFyySJS0yWEV7H5QYg==
| X-WBNR-Posting-Host: 81.223.39.26
| From: "=?Utf-8?B?T25saW5ldGFsay5uZXQ=?="
<Onlinetalk.net@.discussions.microsoft.com>
| References: <DE5BB305-8AEA-4E26-A84E-F7CDA3A3DA82@.microsoft.com>
<nu7#Z1XeEHA.2036@.cpmsftngxa10.phx.gbl>
| Subject: RE: error handling using JDBC DRIVER
| Date: Mon, 20 Sep 2004 07:17:06 -0700
| Lines: 102
| Message-ID: <8E77A45A-DDAB-45E2-BC12-29697F00471D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:6281
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi @.ll
|
| i have the same-problem
|
| sample code:
|
| SP USER_REGISTER
| @.username char(25),@.usershowname char(25), @.useremail
| char(100),@.userpasswort varchar(10)
| AS
| declare @.message varchar(300), @.errno bigint
|
| set @.message='Registrierung wird durchgeführt'
|
| insert into game_user(user_name,user_showname,user_email,user_ passwort)
values(ltrim(rtrim(@.username)),ltrim(rtrim(@.usersh owname)),ltrim(rtrim(@.user
email)),ltrim(rtrim(@.userpasswort)))
|
| set @.errno = @.@.error
|
| print @.errno
|
| IF @.errno <> 0
| BEGIN
| if @.errno = 2627
| begin
| set @.message='Username oder Email-Adresse bereits registriert'
| end
| END
| ELSE
| BEGIN
|
| set @.message ='User wurde registriert'
| END
|
| select @.message as RESULT
|
| end SP
|
| The application get the errorcode 2627 ....
|
|
| "Carb Simien [MSFT]" wrote:
|
| >
| > --
| > | Thread-Topic: error handling using JDBC DRIVER
| > | thread-index: AcR1WumpXFxDagmKQe6fJDRWMRkPsQ==
| > | X-WBNR-Posting-Host: 203.200.17.10
| > | From: "=?Utf-8?B?QW51cmFkaGE=?=" <Anuradha@.discussions.microsoft.com>
| > | Subject: error handling using JDBC DRIVER
| > | Date: Thu, 29 Jul 2004 03:58:00 -0700
| > | Lines: 15
| > | Message-ID: <DE5BB305-8AEA-4E26-A84E-F7CDA3A3DA82@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.jdbcdriver
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6213
| > | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| > |
| > | Hi All,
| > |
| > | I ammaking use of the microsoft JDBC driver.
| > |
| > | In the SP, in a transaction when a error is encountered --> fatal
error
| > (PK violation / incompatible data types) then the control returns to
the
| > application abruptly.
| > |
| > | the error handling within the SP is not carried out.
| > |
| > | Any help on the same is appreciated.
| > |
| > | Thanks
| > | Anu
| > |
| > |
| > |
| > |
| >
| > Hello,
| >
| > Can you provide sample code that demonstrates this problem?
| >
| > Carb Simien, MCSE MCDBA MCAD
| > Microsoft Developer Support - Web Data
| >
| > Please reply only to the newsgroups.
| > 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.
| >
| >
|
If you are using Query Analyzer to execute the stored procedure, then you
will see the remainder of the stored procedure being processed. However,
this is not the case with both JDBC and ODBCTest (direct ODBC API calls).
As a test, I took the stored procedure from this post and added an INSERT
statement immediately after "set @.message='Username oder Email-Adresse
bereits registriert'". What I found is that even though the subsequent
code in the procedure was not shown in the JDBC and ODBCTest code, the
INSERT did, in fact, fire. I don't think you will get the other results
back to the client because the statement has been terminated by that point.
However, the INSERT results can be seen if you check the table after the
fact.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
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.
I ammaking use of the microsoft JDBC driver.
In the SP, in a transaction when a error is encountered --> fatal error (PK violation / incompatible data types) then the control returns to the application abruptly.
the error handling within the SP is not carried out.
Any help on the same is appreciated.
Thanks
Anu
| Thread-Topic: error handling using JDBC DRIVER
| thread-index: AcR1WumpXFxDagmKQe6fJDRWMRkPsQ==
| X-WBNR-Posting-Host: 203.200.17.10
| From: "=?Utf-8?B?QW51cmFkaGE=?=" <Anuradha@.discussions.microsoft.com>
| Subject: error handling using JDBC DRIVER
| Date: Thu, 29 Jul 2004 03:58:00 -0700
| Lines: 15
| Message-ID: <DE5BB305-8AEA-4E26-A84E-F7CDA3A3DA82@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6213
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi All,
|
| I ammaking use of the microsoft JDBC driver.
|
| In the SP, in a transaction when a error is encountered --> fatal error
(PK violation / incompatible data types) then the control returns to the
application abruptly.
|
| the error handling within the SP is not carried out.
|
| Any help on the same is appreciated.
|
| Thanks
| Anu
|
|
|
|
Hello,
Can you provide sample code that demonstrates this problem?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
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.
|||Hi @.ll
i have the same-problem
sample code:
SP USER_REGISTER
@.username char(25),@.usershowname char(25), @.useremail
char(100),@.userpasswort varchar(10)
AS
declare @.message varchar(300), @.errno bigint
set @.message='Registrierung wird durchgeführt'
insert into game_user(user_name,user_showname,user_email,user_ passwort)
values(ltrim(rtrim(@.username)),ltrim(rtrim(@.usersh owname)),ltrim(rtrim(@.useremail)),ltrim(rtrim(@.use rpasswort)))
set @.errno = @.@.error
print @.errno
IF @.errno <> 0
BEGIN
if @.errno = 2627
begin
set @.message='Username oder Email-Adresse bereits registriert'
end
END
ELSE
BEGIN
set @.message ='User wurde registriert'
END
select @.message as RESULT
end SP
The application get the errorcode 2627 ....
"Carb Simien [MSFT]" wrote:
> --
> | Thread-Topic: error handling using JDBC DRIVER
> | thread-index: AcR1WumpXFxDagmKQe6fJDRWMRkPsQ==
> | X-WBNR-Posting-Host: 203.200.17.10
> | From: "=?Utf-8?B?QW51cmFkaGE=?=" <Anuradha@.discussions.microsoft.com>
> | Subject: error handling using JDBC DRIVER
> | Date: Thu, 29 Jul 2004 03:58:00 -0700
> | Lines: 15
> | Message-ID: <DE5BB305-8AEA-4E26-A84E-F7CDA3A3DA82@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6213
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | Hi All,
> |
> | I ammaking use of the microsoft JDBC driver.
> |
> | In the SP, in a transaction when a error is encountered --> fatal error
> (PK violation / incompatible data types) then the control returns to the
> application abruptly.
> |
> | the error handling within the SP is not carried out.
> |
> | Any help on the same is appreciated.
> |
> | Thanks
> | Anu
> |
> |
> |
> |
> Hello,
> Can you provide sample code that demonstrates this problem?
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> 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.
>
|||| Thread-Topic: error handling using JDBC DRIVER
| thread-index: AcSfHIJuOFthVFyySJS0yWEV7H5QYg==
| X-WBNR-Posting-Host: 81.223.39.26
| From: "=?Utf-8?B?T25saW5ldGFsay5uZXQ=?="
<Onlinetalk.net@.discussions.microsoft.com>
| References: <DE5BB305-8AEA-4E26-A84E-F7CDA3A3DA82@.microsoft.com>
<nu7#Z1XeEHA.2036@.cpmsftngxa10.phx.gbl>
| Subject: RE: error handling using JDBC DRIVER
| Date: Mon, 20 Sep 2004 07:17:06 -0700
| Lines: 102
| Message-ID: <8E77A45A-DDAB-45E2-BC12-29697F00471D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:6281
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi @.ll
|
| i have the same-problem
|
| sample code:
|
| SP USER_REGISTER
| @.username char(25),@.usershowname char(25), @.useremail
| char(100),@.userpasswort varchar(10)
| AS
| declare @.message varchar(300), @.errno bigint
|
| set @.message='Registrierung wird durchgeführt'
|
| insert into game_user(user_name,user_showname,user_email,user_ passwort)
values(ltrim(rtrim(@.username)),ltrim(rtrim(@.usersh owname)),ltrim(rtrim(@.user
email)),ltrim(rtrim(@.userpasswort)))
|
| set @.errno = @.@.error
|
| print @.errno
|
| IF @.errno <> 0
| BEGIN
| if @.errno = 2627
| begin
| set @.message='Username oder Email-Adresse bereits registriert'
| end
| END
| ELSE
| BEGIN
|
| set @.message ='User wurde registriert'
| END
|
| select @.message as RESULT
|
| end SP
|
| The application get the errorcode 2627 ....
|
|
| "Carb Simien [MSFT]" wrote:
|
| >
| > --
| > | Thread-Topic: error handling using JDBC DRIVER
| > | thread-index: AcR1WumpXFxDagmKQe6fJDRWMRkPsQ==
| > | X-WBNR-Posting-Host: 203.200.17.10
| > | From: "=?Utf-8?B?QW51cmFkaGE=?=" <Anuradha@.discussions.microsoft.com>
| > | Subject: error handling using JDBC DRIVER
| > | Date: Thu, 29 Jul 2004 03:58:00 -0700
| > | Lines: 15
| > | Message-ID: <DE5BB305-8AEA-4E26-A84E-F7CDA3A3DA82@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.jdbcdriver
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6213
| > | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| > |
| > | Hi All,
| > |
| > | I ammaking use of the microsoft JDBC driver.
| > |
| > | In the SP, in a transaction when a error is encountered --> fatal
error
| > (PK violation / incompatible data types) then the control returns to
the
| > application abruptly.
| > |
| > | the error handling within the SP is not carried out.
| > |
| > | Any help on the same is appreciated.
| > |
| > | Thanks
| > | Anu
| > |
| > |
| > |
| > |
| >
| > Hello,
| >
| > Can you provide sample code that demonstrates this problem?
| >
| > Carb Simien, MCSE MCDBA MCAD
| > Microsoft Developer Support - Web Data
| >
| > Please reply only to the newsgroups.
| > 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.
| >
| >
|
If you are using Query Analyzer to execute the stored procedure, then you
will see the remainder of the stored procedure being processed. However,
this is not the case with both JDBC and ODBCTest (direct ODBC API calls).
As a test, I took the stored procedure from this post and added an INSERT
statement immediately after "set @.message='Username oder Email-Adresse
bereits registriert'". What I found is that even though the subsequent
code in the procedure was not shown in the JDBC and ODBCTest code, the
INSERT did, in fact, fire. I don't think you will get the other results
back to the client because the statement has been terminated by that point.
However, the INSERT results can be seen if you check the table after the
fact.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
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 handling in OLEDB source in data flow
I am trying to execute a SP like below in OLEDB source in data flow... and this statement include the insert stament ( row by row transaction).. I would like to creat an error hadling logic so that if the trasaction fail to insert the row then ignore that particular row then, move to the next row without stopping the whole process.. how can i do this?
exec usp_Inert_Registration_Episodes_Assessments
@.Unique_ID=?,
@.Gender_Cd=?,
@.Birth_Date=?,
@.Race_Ind=?,
@.Ethnicity_Cd=?,
@.Registration_Dt=? ,
--
--@.Object_Key
Recently i was working on similar thing.
To do this in SSIS the following article will be handy
1) http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx
2) http://www.sqlis.com/55.aspx
3) http://www.sqlis.com/58.aspx
4) http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx?CommentPosted=true#commentmessage
Regarding error handling thats very much possible
Say for instance you getting data through OLE DB source. Then in the editor window, you can find "Error Output". here you can specify what to do with your error set. You can "Fail the component"/ ' Re Direct the row / ' Ignore Failure'
In case you want to redirect the row, then by setting the proper output to the error(Red Line) you can do as you want.
I hope this solves your problem.
|||thanks,.. but
problem is my oledb source is a SP that execute insert statment.. so there are no output columns that i can map it to are available.. how can i solve this problem?
|||Why are you using an OLE DB source to insert data via a stored procedure? An Execute SQL task in the control flow would be a much better idea.|||I agree with Phil
1) If you need to Insert with same datbase, use Execute SQl task
2) If in different database, use data flow task in which you can specify the source and destination.
secondly if you have some queries running bnefore running and this all is happening in a sproc then you can run that Sproc using Execute SQL task- Simple!!!
|||thanks.. but if i use SQLTASK , i don;t have the option to use "Error Output" in data flow which i can specify what to do with the error row. in my case skip that row ( put that row in the error log table) and go to the next row..
how to handle the error row in the SQLTask if i want to redirect that row and go to the next row without stopping the whole process?
|||You can use an OLE DB Command component in the data flow to call the procedure. That will let you redirect error rows. However, you still need a source component to feed rows to the OLE DB Command.Where were you planning on getting the data to feed into the procedure?
|||Thanks jwelch -
"That will let you redirect error rows"
--Do i have to do something inside OLEDB command to be able to do that? or is it going to automatically redirect the row and move to the next row?
|||If you drag and drop the red output arrow from the OLEDB command to another component, it will prompt you to configure it.
|||ok.. in the data flow, i am using OLE DB source ( SQL command variable) and pass them to the OLE DB command to execute the insert statment ( by passing variables ).. I changed the error output to redirect rows to skip the row which didn't get inserted( because of an error) and move to the next row( it works)...
I drag and drop the red output arrow from the OLEDB command to another OLE DB command which will execute the insert statment to insert the failed row to Error_Log table...however, even though the row which has an error got skipped , that row didn;t get inserted to the error table.. i am not sure what i am doing wrong..
i am trying to insert the error row with the error description to a table.. what is the best way to do this?
|||When you run it in the debugger, is a row count displayed on the red arrow?
|||do i have to put a data viewer to be able to see it? i dont see it
but i see the color of insert to error table OLEDB command task turn to green for a sec
|||If you are not seeing a number, it sounds like no rows are being sent to the error output. You can add a data viewer to confirm this. That indicates that the error output is not configured properly, or that the OLE DB Command is not failing on any rows.
|||but only one row out of two got inserted.. how to insert that failed row to a table?|||
safddddddddddddddddddddd wrote:
but only one row out of two got inserted.. how to insert that failed row to a table?
Hook the error output to a second OLE DB Destination.
error handling in OLEDB source in data flow
I am trying to execute a SP like below in OLEDB source in data flow... and this statement include the insert stament ( row by row transaction).. I would like to creat an error hadling logic so that if the trasaction fail to insert the row then ignore that particular row then, move to the next row without stopping the whole process.. how can i do this?
exec usp_Inert_Registration_Episodes_Assessments
@.Unique_ID=?,
@.Gender_Cd=?,
@.Birth_Date=?,
@.Race_Ind=?,
@.Ethnicity_Cd=?,
@.Registration_Dt=? ,
--
--@.Object_Key
Recently i was working on similar thing.
To do this in SSIS the following article will be handy
1) http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx
2) http://www.sqlis.com/55.aspx
3) http://www.sqlis.com/58.aspx
4) http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx?CommentPosted=true#commentmessage
Regarding error handling thats very much possible
Say for instance you getting data through OLE DB source. Then in the editor window, you can find "Error Output". here you can specify what to do with your error set. You can "Fail the component"/ ' Re Direct the row / ' Ignore Failure'
In case you want to redirect the row, then by setting the proper output to the error(Red Line) you can do as you want.
I hope this solves your problem.
|||thanks,.. but
problem is my oledb source is a SP that execute insert statment.. so there are no output columns that i can map it to are available.. how can i solve this problem?
|||Why are you using an OLE DB source to insert data via a stored procedure? An Execute SQL task in the control flow would be a much better idea.|||I agree with Phil
1) If you need to Insert with same datbase, use Execute SQl task
2) If in different database, use data flow task in which you can specify the source and destination.
secondly if you have some queries running bnefore running and this all is happening in a sproc then you can run that Sproc using Execute SQL task- Simple!!!
|||thanks.. but if i use SQLTASK , i don;t have the option to use "Error Output" in data flow which i can specify what to do with the error row. in my case skip that row ( put that row in the error log table) and go to the next row..
how to handle the error row in the SQLTask if i want to redirect that row and go to the next row without stopping the whole process?
|||You can use an OLE DB Command component in the data flow to call the procedure. That will let you redirect error rows. However, you still need a source component to feed rows to the OLE DB Command.Where were you planning on getting the data to feed into the procedure?
|||Thanks jwelch -
"That will let you redirect error rows"
--Do i have to do something inside OLEDB command to be able to do that? or is it going to automatically redirect the row and move to the next row?
|||If you drag and drop the red output arrow from the OLEDB command to another component, it will prompt you to configure it.
|||ok.. in the data flow, i am using OLE DB source ( SQL command variable) and pass them to the OLE DB command to execute the insert statment ( by passing variables ).. I changed the error output to redirect rows to skip the row which didn't get inserted( because of an error) and move to the next row( it works)...
I drag and drop the red output arrow from the OLEDB command to another OLE DB command which will execute the insert statment to insert the failed row to Error_Log table...however, even though the row which has an error got skipped , that row didn;t get inserted to the error table.. i am not sure what i am doing wrong..
i am trying to insert the error row with the error description to a table.. what is the best way to do this?
|||When you run it in the debugger, is a row count displayed on the red arrow?
|||do i have to put a data viewer to be able to see it? i dont see it
but i see the color of insert to error table OLEDB command task turn to green for a sec
|||If you are not seeing a number, it sounds like no rows are being sent to the error output. You can add a data viewer to confirm this. That indicates that the error output is not configured properly, or that the OLE DB Command is not failing on any rows.
|||but only one row out of two got inserted.. how to insert that failed row to a table?|||
but only one row out of two got inserted.. how to insert that failed row to a table?
Hook the error output to a second OLE DB Destination.
error handling in OLEDB source in data flow
I am trying to execute a SP like below in OLEDB source in data flow... and this statement include the insert stament ( row by row transaction).. I would like to creat an error hadling logic so that if the trasaction fail to insert the row then ignore that particular row then, move to the next row without stopping the whole process.. how can i do this?
exec usp_Inert_Registration_Episodes_Assessments
@.Unique_ID=?,
@.Gender_Cd=?,
@.Birth_Date=?,
@.Race_Ind=?,
@.Ethnicity_Cd=?,
@.Registration_Dt=? ,
--
--@.Object_Key
Recently i was working on similar thing.
To do this in SSIS the following article will be handy
1) http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx
2) http://www.sqlis.com/55.aspx
3) http://www.sqlis.com/58.aspx
4) http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx?CommentPosted=true#commentmessage
Regarding error handling thats very much possible
Say for instance you getting data through OLE DB source. Then in the editor window, you can find "Error Output". here you can specify what to do with your error set. You can "Fail the component"/ ' Re Direct the row / ' Ignore Failure'
In case you want to redirect the row, then by setting the proper output to the error(Red Line) you can do as you want.
I hope this solves your problem.
|||thanks,.. but
problem is my oledb source is a SP that execute insert statment.. so there are no output columns that i can map it to are available.. how can i solve this problem?
|||Why are you using an OLE DB source to insert data via a stored procedure? An Execute SQL task in the control flow would be a much better idea.|||I agree with Phil
1) If you need to Insert with same datbase, use Execute SQl task
2) If in different database, use data flow task in which you can specify the source and destination.
secondly if you have some queries running bnefore running and this all is happening in a sproc then you can run that Sproc using Execute SQL task- Simple!!!
|||thanks.. but if i use SQLTASK , i don;t have the option to use "Error Output" in data flow which i can specify what to do with the error row. in my case skip that row ( put that row in the error log table) and go to the next row..
how to handle the error row in the SQLTask if i want to redirect that row and go to the next row without stopping the whole process?
|||You can use an OLE DB Command component in the data flow to call the procedure. That will let you redirect error rows. However, you still need a source component to feed rows to the OLE DB Command.Where were you planning on getting the data to feed into the procedure?
|||Thanks jwelch -
"That will let you redirect error rows"
--Do i have to do something inside OLEDB command to be able to do that? or is it going to automatically redirect the row and move to the next row?
|||If you drag and drop the red output arrow from the OLEDB command to another component, it will prompt you to configure it.
|||ok.. in the data flow, i am using OLE DB source ( SQL command variable) and pass them to the OLE DB command to execute the insert statment ( by passing variables ).. I changed the error output to redirect rows to skip the row which didn't get inserted( because of an error) and move to the next row( it works)...
I drag and drop the red output arrow from the OLEDB command to another OLE DB command which will execute the insert statment to insert the failed row to Error_Log table...however, even though the row which has an error got skipped , that row didn;t get inserted to the error table.. i am not sure what i am doing wrong..
i am trying to insert the error row with the error description to a table.. what is the best way to do this?
|||When you run it in the debugger, is a row count displayed on the red arrow?
|||do i have to put a data viewer to be able to see it? i dont see it
but i see the color of insert to error table OLEDB command task turn to green for a sec
|||If you are not seeing a number, it sounds like no rows are being sent to the error output. You can add a data viewer to confirm this. That indicates that the error output is not configured properly, or that the OLE DB Command is not failing on any rows.
|||but only one row out of two got inserted.. how to insert that failed row to a table?|||
but only one row out of two got inserted.. how to insert that failed row to a table?
Hook the error output to a second OLE DB Destination.
Subscribe to:
Posts (Atom)