converting default check constraint messages to friendly ones

I'm trying to set up a table that will convert default SQL error messages
for check constraints into friendly front end messages. What I'm having
trouble with is how to pick apart the default message so I can do this. Is
the error stored anywhere that I can look at it's parts? Any suggestions on
a good strategy for this sort of thing would be appreciated.
Thanks,
KeithFriendly error msg is always welcome by users. Though, this is the arena of
the presentation level. So, you just intercept the returned error code and
use your custom msg instead of the err.message.
-oj
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:e80rgIRTFHA.1404@.TK2MSFTNGP09.phx.gbl...
> I'm trying to set up a table that will convert default SQL error messages
> for check constraints into friendly front end messages. What I'm having
> trouble with is how to pick apart the default message so I can do this. Is
> the error stored anywhere that I can look at it's parts? Any suggestions
> on
> a good strategy for this sort of thing would be appreciated.
> Thanks,
> Keith
>|||Thanks, OJ. But what code? I know how to do that in stored procedures but in
Check Constraints, where is there a code? I created a constriant that
prevents zero length strings on a column in a table as follows:
Len(LTrim(RTrim(CustName))) > 0
And the message that is returned is:
Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
statement conflicted with COLUMN CHECK constraint
'CK_tblCustomers_CustName'. The conflict occurred in database 'WidgetsInc',
table 'tblCustomers', column 'CustName'.
[Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
terminated.'
If I can always be guaranteed that the message will start with "Database
error: '[Microsoft][ODBC SQL Server Driver][SQL Server]" and end with "
[Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
terminated.'" then I suuppose I can parse it out. But I was hoping there
would be an easier way to do it.
Keith
"oj" <nospam_ojngo@.home.com> wrote in message
news:OKznpORTFHA.2548@.TK2MSFTNGP14.phx.gbl...
Friendly error msg is always welcome by users. Though, this is the arena of
the presentation level. So, you just intercept the returned error code and
use your custom msg instead of the err.message.
-oj|||Keith,
I think you misunderstood me.
What I mean is that you would want to handle the display of the message at
the client side (i.e. VB, Web, etc.). You would just watch for the error
number returned (for this particular one, it's 547) then intercept the error
message before showing it to the user.
No, you do not have the capability to set the error message for constraint
inside sqlserver. Well, you could if you want to override the system ones
(not recommended!). All of the sql error messages you get are stored inside
master.dbo.sysmessages. They're nothing more than just generic strings where
sqlserver inserts the actual object names for the incident before returning
it.
-oj
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%235ONvURTFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Thanks, OJ. But what code? I know how to do that in stored procedures but
> in
> Check Constraints, where is there a code? I created a constriant that
> prevents zero length strings on a column in a table as follows:
> Len(LTrim(RTrim(CustName))) > 0
> And the message that is returned is:
> Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
> statement conflicted with COLUMN CHECK constraint
> 'CK_tblCustomers_CustName'. The conflict occurred in database
> 'WidgetsInc',
> table 'tblCustomers', column 'CustName'.
> [Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
> terminated.'
> If I can always be guaranteed that the message will start with "Database
> error: '[Microsoft][ODBC SQL Server Driver][SQL Server]" and end with "
> [Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
> terminated.'" then I suuppose I can parse it out. But I was hoping there
> would be an easier way to do it.
> Keith
>|||Thanks, OJ. No, I did understand. Just that there's no error number
returned to the client side (using Delphi). Just the text I showed you
below. I'll investigate this further in the Borland NGs. Thanks
again. -keith
"oj" <nospam_ojngo@.home.com> wrote in message
news:egat8fRTFHA.2908@.TK2MSFTNGP10.phx.gbl...
Keith,
I think you misunderstood me.
What I mean is that you would want to handle the display of the message at
the client side (i.e. VB, Web, etc.). You would just watch for the error
number returned (for this particular one, it's 547) then intercept the error
message before showing it to the user.
No, you do not have the capability to set the error message for constraint
inside sqlserver. Well, you could if you want to override the system ones
(not recommended!). All of the sql error messages you get are stored inside
master.dbo.sysmessages. They're nothing more than just generic strings where
sqlserver inserts the actual object names for the incident before returning
it.
-oj

converting decimal to time

how do you convert a numeric to time format if it shows hours but a decimal figure for Minutes. For example if I have hours in decimal format like this

28.5000

but I want to show it in this format:

28:30:00

where 28 = hours, 30 = minutes, 00 = seconds

Thanks.This is a presentation issue. It should be handled at the client, not the server. Dealing with formatting inside the database is just a recipe for problems later.

-PatP|||What Pat said

DECLARE @.hours decimal(15,4)
SELECT @.hours = 28.5
SELECT RIGHT('00' + CONVERT(varchar(2),FLOOR(@.hours)),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60))),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60)-FLOOR(((@.hours-FLOOR(@.hours))*60)))*60),2)|||Displaying it as 28:30:00 is a presentation issue, but converting it to a valid datetime format falls within the scope of the database server:declare @.Hours decimal (6, 4)
set @.Hours = 28.5
select dateadd(minute, @.Hours * 60, 0)|||I was gonna give them that, but I realized it wasn't what they asked for...

Hours of what BTW...sounds like derived data gotta be careful with that

Converting Decimal to String W/O Decimal Point

I'd like to convert a Decimal value into a string so that the entire
original value and length remains intact but there is no decimal point.

For example, the decimal value 6.250 is selected as 06250.

Can this be done?select replace(cast (6.250 as varchar),'.','')

JD wrote:
> I'd like to convert a Decimal value into a string so that the entire
> original value and length remains intact but there is no decimal
point.
> For example, the decimal value 6.250 is selected as 06250.
> Can this be done?|||select replace(cast (6.250 as varchar),'.','')|||but length doesn't remain intact|||but length doesn't remain intact|||select replace(space(1)+replace(cast (6.250 as varchar),'.',''),' ','0')|||Thanks!!!

Converting decimal numbers to Words

Hi there all the Gurus,
I am trying to mave a convert a decimal number ie. 1230.30 to words in my report and i am getting result with the 30/100 at the back of the words.
Is there any way i can get a result of :
'ONE THOUSAND TWO HUNDRED THIRTY AND THIRTY'Well, I guess you could split the number into two parts (at the decimal point) and convert each separately.|||UpperCase(TOWORDS(integer({INVOICE.AMOUNT}))) + Uppercase(TOWORDS(fraction({INVOICE.AMOUNT}))) + 'CENTS ONLY'

i have tried this formula but it keeps prompting error "Missing '('

Please advice|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');|||Cool! Many thanks for the solution.|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');

Hi, if I want to convert a Sum of Amount into English Words? How to do this?|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');

Hi there,

If i am not wrong, you must create a formula or using the SUM maths function to get the total amount then put it replacing the (12345.67)

or we shall hear what's the Guru says :)|||Hi there,

If i am not wrong, you must create a formula or using the SUM maths function to get the total amount then put it replacing the (12345.67)

or we shall hear what's the Guru says :)

Yap, I have already tried in this way, which replace the 12345.67 into sum(@.amount), yet it doesn't work.

local stringvar sNumber := cstr(sum({@.Amount}));
local numbervar whole := truncate(sum({@.Amount}));
local numbervar pos := instrrev(sNumber, '.');

local numbervar decimal := 0;
if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Ringgit and ' + ToWords(decimal, 0) + ' Sen Sahaja');|||What error do you get? Presumably 'This field cannot be summarised' when you run the report as you can't sum a formula {@.amount}.
Try summing something that can be summed like the underlying database value, or maybe {@.amount} is already your summed value?sqlsql

Converting DBF to SQL Server

Hi,
I am working with a 3rd party application that uses DBF files. We are in the
process of discontinuing our contract with them and we need to convert the
data to SQL Server. I setup a Linked Server to access the data, but when I
try to select from it I get an error. Details below.
Any assistance you can provide is greatly appreciated.
Thank you,
Michael
SELECT * From
OpenQuery(DBF, 'select * from Person')
Error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select * from Person'. The OLE DB provider
'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0',
Query=select * from Person'].
--
Message posted via http://www.sqlmonster.comHi Michael,
First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
FoxPro OLE DB data provider, downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
follows:
-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC file
is present in the directory) use only the path to the directory and put
double quotes around it if it has spaces. Something like '"C:\My
Directory\"'
-- If a DBC files is present the @.DataSrc value needs to point to the DBC
file as below.
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_NORTHWIND', -- Your linked server name here
@.srvproduct=N'Visual FoxPro 9', -- can be anything
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
Then, you can query your tables like:
Select * From YourLinkedServer...YourTable
-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
if your data source points to a DBC file just use the table name you want to
select from.
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelg via SQLMonster.com" <u13012@.uwe> wrote in message
news:63d4247ce59e5@.uwe...
> ... DBF files. ...I setup a Linked Server to access the data, but when I
> try to select from it I get an error. Details below.
> SELECT * From
> OpenQuery(DBF, 'select * from Person')
> Error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'select * from Person'. The OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' ....|||Hi Cindy,
Thanks for the feedback. The setup we have is a DBF and a DBT file in the
folder. The options below do not work. When I run Select * from LinkedServer..|||Hi Michael,
The contents of FoxPro Memo fields are stored in FPT files with names
matching the name of the DBF they go with. Some other DBF file formats,
perhaps Clipper, use DBT files for their Memo fields.
If this is a one-time thing try changing the extension of the DBT file to
FPT. However, if the DBF's table header format is incompatible with Jet (or
FoxPro OLE DB if you use that) then I can't help you further. You'll have to
find out exactly what application created the files and probably use drivers
related to that table format.
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelgangeri via SQLMonster.com" <u13012@.uwe> wrote in message
news:63df901b8d1c5@.uwe...
> Thanks for the feedback. The setup we have is a DBF and a DBT ...
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Cannot locate the requested Xbase memo
> file.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBSchemaRowset:
> :GetRowset returned 0x80004005: ].

Converting DBF to SQL Server

Hi,
I am working with a 3rd party application that uses DBF files. We are in the
process of discontinuing our contract with them and we need to convert the
data to SQL Server. I setup a Linked Server to access the data, but when I
try to select from it I get an error. Details below.
Any assistance you can provide is greatly appreciated.
Thank you,
Michael
SELECT * From
OpenQuery(DBF, 'select * from Person')
Error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select * from Person'. The OLE DB provider
'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to proc
ess
object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0
',
Query=select * from Person'].
Message posted via http://www.droptable.comHi Michael,
First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
FoxPro OLE DB data provider, downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
follows:
-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC file
is present in the directory) use only the path to the directory and put
double quotes around it if it has spaces. Something like '"C:\My
Directory\"'
-- If a DBC files is present the @.DataSrc value needs to point to the DBC
file as below.
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_NORTHWIND', -- Your linked server name here
@.srvproduct=N'Visual FoxPro 9', -- can be anything
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
Then, you can query your tables like:
Select * From YourLinkedServer...YourTable
-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
if your data source points to a DBC file just use the table name you want to
select from.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelg via droptable.com" <u13012@.uwe> wrote in message
news:63d4247ce59e5@.uwe...
> ... DBF files. ...I setup a Linked Server to access the data, but when I
> try to select from it I get an error. Details below.

> SELECT * From
> OpenQuery(DBF, 'select * from Person')
> Error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'select * from Person'. The OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' ....|||Hi Cindy,
Thanks for the feedback. The setup we have is a DBF and a DBT file in the
folder. The options below do not work. When I run Select * from LinkedServer
.
Table, I get the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Cannot locate the requested Xbase mem
o
file.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBSchemaR
owset:
:GetRowset returned 0x80004005: ].
Hopefully this sheds some light on my issue?
Thanks again,
Michael
Cindy Winegarden wrote:[vbcol=seagreen]
>Hi Michael,
>First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
>FoxPro OLE DB data provider, downloadable from
>msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
>follows:
>-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC fil
e
>is present in the directory) use only the path to the directory and put
>double quotes around it if it has spaces. Something like '"C:\My
>Directory\"'
>-- If a DBC files is present the @.DataSrc value needs to point to the DBC
>file as below.
>EXEC master.dbo.sp_addlinkedserver
> @.server = N'VFP_NORTHWIND', -- Your linked server name here
> @.srvproduct=N'Visual FoxPro 9', -- can be anything
> @.provider=N'VFPOLEDB',
> @.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
>9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
>Then, you can query your tables like:
>Select * From YourLinkedServer...YourTable
>-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
>if your data source points to a DBC file just use the table name you want t
o
>select from.
>
>
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||Hi Michael,
The contents of FoxPro Memo fields are stored in FPT files with names
matching the name of the DBF they go with. Some other DBF file formats,
perhaps Clipper, use DBT files for their Memo fields.
If this is a one-time thing try changing the extension of the DBT file to
FPT. However, if the DBF's table header format is incompatible with Jet (or
FoxPro OLE DB if you use that) then I can't help you further. You'll have to
find out exactly what application created the files and probably use drivers
related to that table format.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelgangeri via droptable.com" <u13012@.uwe> wrote in message
news:63df901b8d1c5@.uwe...

> Thanks for the feedback. The setup we have is a DBF and a DBT ...

> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Cannot locate the requested Xbase m
emo
> file.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBSchemaRowset:
> :GetRowset returned 0x80004005: ].

Converting DB2 Timestamp Data to SQL Server 2005 - Problems with Unique Index

I am attempting to move a timestamp data column from DB2 to SQL Server 2005. Normally not a big deal but the column is part of unique index.

The DB2 timestamp has seconds of ss.ssssss but SQL Server only has ss.sss.

Most all the times entered into this column are a from an automated process so they are really close together timewise.

Here is what I have come up with so far:

1. Fast Load OLEDB with a batch of 10,000 records at a time

2. On the fail of the batch redirect rows to a regular table load OLEDB insert task

3. On the fail of the single insert redirect rows to script that ups the seconds one tick.

4. Attempt one last insert of the modified rows

5. If fail, then store the record off to a delimited text file

I am hoping to get the number of records that wind up in the delimited text file to be a very small number and not in the 1,000+.

Any help would be appreciated.

Redirecting errors from the FastLoad to a regular OLEDB destination is a technique that I've used before to capture only the row that caused the error - that should work fine.

I have another question, though. If you can change this timestamp value (as you are planning in step 3), it doesn't seem to be all that significant. Why not drop just add an identity column to the unique index?

|||

Adding an Identity column to the table and then adding into the index sounds like a good idea to get around the problem. Especially since the package is going to be scheduled to run every 15 to 30 minutes to sync the DB2 table to the SQL Server table during the migration time frame.

Thanks for the suggestion. I'll let you know what we have decided to do.

|||

Upon further inspection of this process and doing a small test this method is not an option for me.

The addition of an indentity column to the unqiue index would now in essence make the index non-unique. The identity column is a unique value and would then allow for the business rule of this date time column to be broken.

I am attempting to try and see if I can capture the data column as a string for manipulation, but right now the OleDB connection is pulling the catalog info from DB2 and knows that the column is supposed to be a date time. Any attempt to use the column in the SSIS package as a character I get errors.

Still looking for suggestions before starting down the path of writing an application or off the shelf tool to handle the sync between these tables.

Thanks.

|||

You should be able to use a derived column to convert the value to a string - that is probably your best bet to avoid violating the business rule.

Code Snippet

(DT_WSTR, 10) [ColumnName]