Customized Error Message

Hi,
I have a table like this:
CREATE TABLE Table1 (
[ID] int NOT NULL Primary Key)
After inserting some records, obviously when I try to update all records to
a particular value, SQL server raises an error (number 2627) that indicates
the "Violation of PRIMARY KEY constraint" has happened.
What I need to do is to return an error message instead of SQL server's.
Suppose that I have this SP:
CREATE PROCEDURE UpdateTable1 AS
BEGIN TRAN
UPDATE table1 set id=1
IF @.@.Error = 2627
begin
print 'Duplicate Value'
raiserror('Duplicate Value',16,1)
rollback tran
end
else
begin
print 'Update was ok'
commit tran
end
GO
SQL server returns two error descriptions when I execute this SP: One from
its original messages and the other one from my raiserror statement.
I want to display my own error description to the client without writing
extra code for error handling in my client app(and also for centralizing my
own error descriptions those are returned instead of SQL server's error
messages).
Any help would be greatly appreciated.
Amin
See my reply in .programming. Please don't multipost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Amin Sobati" <amins@.morva.net> wrote in message news:uy1qx39FEHA.1368@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a table like this:
>
> CREATE TABLE Table1 (
> [ID] int NOT NULL Primary Key)
>
> After inserting some records, obviously when I try to update all records to
> a particular value, SQL server raises an error (number 2627) that indicates
> the "Violation of PRIMARY KEY constraint" has happened.
> What I need to do is to return an error message instead of SQL server's.
> Suppose that I have this SP:
>
> CREATE PROCEDURE UpdateTable1 AS
> BEGIN TRAN
> UPDATE table1 set id=1
> IF @.@.Error = 2627
> begin
> print 'Duplicate Value'
> raiserror('Duplicate Value',16,1)
> rollback tran
> end
> else
> begin
> print 'Update was ok'
> commit tran
> end
> GO
> SQL server returns two error descriptions when I execute this SP: One from
> its original messages and the other one from my raiserror statement.
> I want to display my own error description to the client without writing
> extra code for error handling in my client app(and also for centralizing my
> own error descriptions those are returned instead of SQL server's error
> messages).
> Any help would be greatly appreciated.
> Amin
>
>
>

Custom Ordering question

Hi group, i'm having a problem with custom ordering in a SP. My code is
the following:

CREATE PROCEDURE [dbo].[PersonasSelectAll_P]
@.fromRow int,
@.toRow int,
@.expresionOrdenamiento int = null
AS
SET NOCOUNT ON

DECLARE @.TotalFilas int

Select @.TotalFilas = count(*) from [Personas]

SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @.expresionOrdenamiento =
1 THEN [Id]
--WHEN @.expresionOrdenamiento = 2 THEN [Nombre_RazonSocial]
WHEN @.expresionOrdenamiento = 3 THEN [CUIT_CUIL]
ELSE null
END
) AS Row
, * , @.TotalFilas as TotalRows
FROM [Personas]
AS NumberedPersons
WHERE
(Row >= isnull(@.fromRow, 0)) AND (Row <= isnull(@.toRow, row))

The line WHEN @.expresionOrdenamiento = 2 THEN [Nombre_RazonSocial] is
erroring out (the sp compiles, it's a runtime error), i'm getting an
'Cannot convert varchar to int'. Maybe it's because Nombre_RazonSocial
is varchar? If i comment that line then everything works fine. Any
Help??

Thanks in advanceGonza (gonzalow@.gmail.com) writes:

Quote:

Originally Posted by

SELECT ROW_NUMBER() OVER (ORDER BY CASE
WHEN @.expresionOrdenamiento = 1 THEN [Id]
WHEN @.expresionOrdenamiento = 2 THEN [Nombre_RazonSocial]
WHEN @.expresionOrdenamiento = 3 THEN [CUIT_CUIL]
ELSE null
END
>...
The line WHEN @.expresionOrdenamiento = 2 THEN [Nombre_RazonSocial] is
erroring out (the sp compiles, it's a runtime error), i'm getting an
'Cannot convert varchar to int'. Maybe it's because Nombre_RazonSocial
is varchar? If i comment that line then everything works fine. Any
Help??

The data type from a CASE expression is always the same and is determine
from the strict data-type precedence that SQL Server employs. (See in Books
Online under Datatypes for details). In this case, varchar has lower
precedence than int, so it's converted to int, which then fails.

The remedy is to write:

ORDER BY CASE @.x WHEN 1 THEN id WHEN 2 THEN CUIT_CUIL END,
CASE @.x WHEN 2 THEN Nombre_RazonSocial END

--
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

custom Discretization patterns

I have a problem with setting a reasonable Discretization pattern on some dimensions.

One of my dimensions has int numbers scattered from 400 to 900. another dimension has float numbers from 0 to 100, however Automatic discretization does not create even ranges for example 0-100,100-200,etc nor 0-10, 10-20, etc. I tried setting DiscretizationBucketCounts to 10,20,100 etc, however, AS does not create rounded bounds for the buckets.

Since the analisys applications are mostly for use by humans, it would make sense to add support for human-readable discretization, like is done in Excel Charts, etc.

Is there a way to setup my own discretization pattern (other than changing the data view to Query with a bunch of Case statements?) ?

From the ASSL documentation in SQL Server 2005 BOL, it looks like an "EqualRanges" discretization method was envisaged, which might have met your needs. Unfortunately, it doesn't seem to have been implemented (as of SP1), at least for DimensionAttribute:

http://msdn2.microsoft.com/en-us/library/ms127037.aspx

>>

SQL Server 2005 Books Online

DiscretizationMethod Element (ASSL)

Defines the method to be used for discretization.

...

Relationship	Element
Parent elements	DimensionAttribute, ScalarMiningStructureColumn

...

EqualRanges

Equivalent to the EQUAL_RANGES discretization method for mining structure columns.

>>

|||

I tried other discretization methods, - Automatic, Clusters, Equal Ranges.

they all seem to group members on odd intervals, like 12.3-13.5, 13.5-14.6, etc.

Custom Charts...

Does reporting services currently allow me to write my own particular chart
that will appear inline int he report as the stock reports do?
reports and reports, but I would like to have some specific views that arent
in the chart selection.
Thanks in advance
Weston WeemsThere are several options:
* RS 2000:
You could draw your chart in a custom assembly and retrieve the value via
the Image.Value expression. Note: the custom assembly would be responsible
to retrieve all the data needed to draw the chart.
If you search the archives of this newsgroup you should find some related
postings from me and other people about how to do this.
* RS 2005:
In addition to what you can do on RS 2000, you could take advantage of the
new CustomReportItem feature and write your own custom processing control.
In this case, you could leverage the capabilities of the RS processing
engine to retrieve, group, sort, filter the data for you and the custom
processing control can read the already processed data through an object
model and then draw the chart image and return it as Image "rendering item".
More information and samples about this will be available when SQL Server
2005 is going to be released.
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Weston Weems" <wweems@.NOSPAMno-span-gmail.com> wrote in message
news:%23lzKjnWXFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Does reporting services currently allow me to write my own particular
> chart that will appear inline int he report as the stock reports do?
> reports and reports, but I would like to have some specific views that
> arent in the chart selection.
> Thanks in advance
> Weston Weems
>