Exception Handling

Hi All

I m new to the sqlserver.In Oracle we can handle exceptions like this

declare
name varchar(20);
begin
select ename into name from emp where eno=&eno;
dbms_output.put_line(name);
exception
when no_data_found then
dbms_output.put_line('No Entry');
end;
/

We will get the message No Entry When corrsponding employee number dosent exists.

In Sqlserver how to handle these things.

Specifically my requirement is with select statement(Like above program)
Waiting for valuable suggestions

Baba

You can do in sqlserver using @.@.Rowcount

select ename into name from emp where eno=&eno

if((select @.@.Rowcount)<= 0)

print 'No data'

The following explains the errorhandling in simple terms

http://www.sqlteam.com/article/handling-errors-in-stored-procedures

|||

In SQL Server this situation never treated as exception, There is a workaround available - here it is,

Code Snippet

Declare @.Sno as int;

Declare @.name as varchar(100)

Set @.Sno=4

Select @.name=name from sysobjects Where id=@.Sno

If @.@.RowCount = 0

Print 'No Entry'

Else

Print @.Name

|||Hi manivannan

I tried like this before only . I got the output

Thank u for u r valuable reply.

Baba

Exception from HRESULT: 0x80131904

Hi,

I write a custom component (destination component) that handle the error of my dataflow.

The custom component works fine on design time and runtime by using BIDS.

When I'm using the same package that use my custom component with DTEXEC,

I got the following error :

System.Exception: AcquireConnections : Exception from HRESULT: 0x80131904

at SSISGenerator.SSISErrorHandler.ErrorHandlerDestination.AcquireConnections(
Object transaction)

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnectio
ns(IDTSManagedComponentWrapper90 wrapper, Object transaction)

The error message point out that the problem is in acquireconnections method.

This is the code i'm using in my custom component for the acquireconnections method.

public override void AcquireConnections(object transaction)

{

try

{

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;

if (cmado == null)

throw new Exception(String.Format(MSG_ACQUIRECONNECTIONS_ADONET,cm.Name));

this.sqlConnection = cmado.AcquireConnection(null) as SqlConnection;

if (this.sqlConnection == null)

throw new Exception(String.Format(MSG_ACQUIRECONNECTIONS_ADONET, cm.Name));

if (sqlConnection.State != ConnectionState.Open)

this.sqlConnection.Open();

}

}

catch (Exception e)

{

throw new Exception(MSG_ACQUIRECONNECTIONS + e.Message);

}

}

Does someone got an idea ?

Mathieu

Could you try to debug your method while called from DTExec?

Thanks.

|||

Hi,

I did some tests and I will explain you the result.

Debug the method with DTExec

I debugged with DTEXEC a solution that contain two project

01 - class library project for the custom component

02 - integration service project for a test package

I did the following tasks for the debug proces

01 - On debug tab, I put the information needed for debugging (start an external program to debug (DTEXEC) and the file for the parameter)

02 - I put the debug dll in GAC

03 - I make sure that I have a breakpoint set on PreExecute

04 - I hit F5 and the error didn't show up.

05 - I took the custom component (dll) and I copy it on the dev server.

06 - I put the debug dll in GAC.

07 - I executed the test package on the dev server with DTEXEC and the error didn't show up.

08 - I remove the debug information and I've compiled the custom component in release mode.

09 - I put the release dll in GAC

10 - I executed the test package on my machine with DTEXEC and the error didn't show up.

11 - I took the custom component (dll) and I copy it on the dev server.

12 - I put the release dll in GAC

13 - I executed the test package on the dev server with DTEXEC and the error didn't show up.

After all these tests, I was suprise since the error message disappears. So, I will do other tests and post the result.

For now, I have one idea for this error message. When I did some tests, I was using the same command line over and over. I'm using a environment variable that indicate the location of my dtsconfig file for the connection string of my connection. I'm not sure if I create the environment variable before openning the commandline. I think it can be one of the reason.

We will see.

Mathieu

Excel To DataBase (Duplicate Recoards)

for example i exported student table with sno as (PK)

how to handle the upload twise (som of the records already in the data base )

1) How to ignore the existing records in the database

2) How to modify the records which is already exist in the database

1) EXCEL DATA SOURCE(STUDENT.XLS)

2) DATA CONVERSION

3) DESTINATION DATABASE (SQL SERVER 2005 STUDENT TABLE)

this is i have please tell me how to ignore/update the exisitng records

koti

It may be a good idea to load the excel data into a staging table first. This would give you a lot more flexibility than trying to run sql statements on the excel file.

To ignore duplicate records, you could then just do a look up from the staging table to the destination table and only load the rows that fail the lookup.

To update the records is a bit more tricky, as you have to use the OLE DB Command and write the SQL statement yourself.

Hope this helps,

Chris

|||

from the 1st page of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1