@@Error not catching error.

Hi all,

I want to catch error in stored procedure and return error message.
I want to catch error 'Syntax error converting the varchar value 'a'
to a column of data type int.' Means error occuring if i enter wrong
value.

Say suppose i have statment like

select * from emp where rowid = 'a'
PRINT @.@.ERROR
print 'reach'

here rowid is integer value so i am getting above mention error.

So what i am expecting is it should print error and then print 'reach'
which is not happening.
can anyone tell me reason behind this and how to overcome this
problem.

thanks in advance.(trialproduct2004@.yahoo.com) writes:

Quote:

Originally Posted by

I want to catch error in stored procedure and return error message.
I want to catch error 'Syntax error converting the varchar value 'a'
to a column of data type int.' Means error occuring if i enter wrong
value.
>
Say suppose i have statment like
>
select * from emp where rowid = 'a'
PRINT @.@.ERROR
print 'reach'
>
here rowid is integer value so i am getting above mention error.
>
So what i am expecting is it should print error and then print 'reach'
which is not happening.
can anyone tell me reason behind this and how to overcome this
problem.

If you are on SQL 2005, you need to use TRY-CATCH. If you are using SQL
2000, you first need to upgrade to SQL 2005. In SQL 2000 you cannot detect
this error, because the entire batch is aborted because of the error.
If you want to know more about error handling in SQL 2000, I have an
article on my web site: http://www.sommarskog.se/error-handling-I.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||(trialproduct2004@.yahoo.com) writes:

Quote:

Originally Posted by

I want to catch error in stored procedure and return error message.
I want to catch error 'Syntax error converting the varchar value 'a'
to a column of data type int.' Means error occuring if i enter wrong
value.
>
Say suppose i have statment like
>
select * from emp where rowid = 'a'
PRINT @.@.ERROR
print 'reach'
>
here rowid is integer value so i am getting above mention error.
>
So what i am expecting is it should print error and then print 'reach'
which is not happening.
can anyone tell me reason behind this and how to overcome this
problem.

If you are on SQL 2005, you need to use TRY-CATCH. If you are using SQL
2000, you first need to upgrade to SQL 2005. In SQL 2000 you cannot detect
this error, because the entire batch is aborted because of the error.
If you want to know more about error handling in SQL 2000, I have an
article on my web site: http://www.sommarskog.se/error-handling-I.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

.Read() not able to get data with special caracter

Hi all,

My SQL database has varchar that store names with special caracter like "René". The problem is when I try to get the data back from the database, the .Read() command from theSqlDataReader object is not working. In extra, it does not Throw any error????

Please help me,

JR

Use an nVarchar to store it|||

albertpascual:

Use an nVarchar to store it

That's not necessarily needed. With this example. "René" can be stored in and retrieved from a varchar column just fine.

JRG:

My SQL database has varchar that store names with special caracter like "René". The problem is when I try to get the data back from the database, the .Read() command from theSqlDataReader object is not working. In extra, it does not Throw any error????

First, and it's a silly question, so please bear with me. Are you *sure* it is stored in the database with the accented character? If it is, then I am guessing there *might* be an issue with the collation. Here's some introductory information regarding collation which may or may not help:SQL Server and Collation.

|||

Thanks all...

I found the problem minutes after posting... the problem was coming from the Gridview... I desable HTML encoded and it worked, but this was really strange because I tested where the code was crashing by putting code like Label1.Text = "here1";... and it was crashing when excuting .Read()?

Thanks,

JR

.net 1.1 SqlDbType accepts varchar(MAX)

My app uses .net 1.1 and MS SQL 2005 as backend. How to add a SqlParamter that is varchar(MAX)?

SqlParameter myParameter = new SqlParameter("@.Description",SqlDbType.VarChar,whatToPutHere?,ParameterDirection.Input,
true,0,0,"Whatever",DataRowVersion.Current,"Whatever");

That's the size position, right? Put the max value you expect to have. Max allowable is 8000

|||

Try:

SqlParameter myParameter = new SqlParameter("@.Description",SqlDbType.VarChar);

|||

Motley:

SqlDbType.VarChar

I'm not positive about this, but make sure that if you leave the VarChar declaration w/o a length parm that it won't default to VarChar(1) (ie, max 1 char). I think it may do this but I could be wrong. I am pretty sure that if you declare a variable as

declare @.v varchar

that it sets the max size to 1, for example (but I'mnot anywhere where I can double check that statment, though<g>)

|||

Motley:

SqlDbType.VarChar

I'm not positive about this, but make sure that if you leave the VarChar declaration w/o a length parm that it won't default to VarChar(1) (ie, max 1 char). I think it may do this but I could be wrong. I am pretty sure that if you declare a variable as

declare @.v varchar

that it sets the max size to 1, for example (but I'mnot anywhere where I can double check that statment, though<g>)

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly