?
Solved

How to suppress some initial words in sql server 2000 error messages.

Posted on 2004-09-10
8
Medium Priority
?
343 Views
Last Modified: 2006-11-17
When a request passed to sql server is invalid the error message is usually like this.

[Microsoft][ODBC Sql Server Driver][Sqlerror]violation of unique key constraint 'ix_tablex'. Cannot insert duplicate key
in object 'tablex'.
[Microsoft][ODBC Sql Server Driver][Sql Server]The statement has been terminated.

Is it possible to remove or suppress the odbc prefix line. I mean, I want to see the abobe error message like this:

"Violation of unique key constraint 'ix_tablex'. Cannot insert duplicate key in object 'tablex'."

Please note that I want to suppres the odbc prefix whether request is passed to sql server from enterprise manager or
query analyzer or from any front end.

0
Comment
Question by:Mateen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 6

Assisted Solution

by:OlegP
OlegP earned 500 total points
ID: 12024240
As i know it is possible to process at client application only.
for example for VB
ON ERROR GOTO MYERROR
...
...
...
MYERROR:
'err.Description - description of error like [Microsoft][ODBC Sql Server Driver][Sqlerror]violation of unique key
'constraint 'ix_tablex'. Cannot insert duplicate key
'Here you may cut [Microsoft][ODBC Sql Server Driver][Sqlerror]
'and output error by msgbox
0
 

Author Comment

by:Mateen
ID: 12024262
Ok Olegp. In fact I need this in client application mainly.

Unfortunately my front end is powerbuilder and I don't know how to suppress there.

 
0
 
LVL 10

Accepted Solution

by:
imrancs earned 1000 total points
ID: 12024334
In powerbuilder their might be REPLACE() fucntion. Simply get the error and use REPLACE() function like

myErrorString = REPLACE(strErr, '[Microsoft][ODBC Sql Server Driver][Sqlerror]', '')

its not the exact syntax for PowerBuilder, it just an idea.


Imran
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 9

Assisted Solution

by:solution46
solution46 earned 500 total points
ID: 12024506
Mateen,

seems to me like you need to spend a little more time working in Powerbuilder - you really shouldn't be reporting this sort of error message to your users.

Anyone who is not familiar with RDBMS is not going to know what a 'unique key constraint' or similar is, and most are unlikely to be familiar with your table structure, so an error along the lines of ...

'violation of unique key constraint 'ix_tablex'. Cannot insert duplicate key in object 'tablex'.'

is not going to tell them very much. You should really be testing for these errors in the front end and generating a message along the lines of...

'No customer exisits with ID xxxxx. Please check your customer ID and try again.'

Regards,

s46.
0
 

Author Comment

by:Mateen
ID: 12024995
Hi everybody

I guess I have got what I should do. Imrancs, u have given me the clue.
I guess, the right event in pb7 is the dberror event where sqlerrtext and sqldbcode is built in.

So far I have experimented

string ls_msg
window lw_parent

lw_parent = parent

// POWERBUILDER SPECIFIC ERROR HANDLING

choose case sqldbcode

      CASE -193
                ls_msg = "Duplicate Recored/Row  "+ string(row)
      CASE -195
      ls_msg = "Please provide values for all required fields"
      case 1031
      ls_msg = "You are not authorized to perform the requested action"

              case else
               ls_msg = &
      "Error Details~n" &
      + "Error Occurred: " + string(today(),"dd/mm/yyyy hh:mm am/pm") + "~n" &
      + "Error Number:" + string(error.number) + "~n" &
      + "Error Message: " + error.text + "~n" &
      + "Details: ~n" &
      + "   Window Menu: " + error.WindowMenu + "~n" &
      + "   Object: " + error.Object + "~n" &
      + "   Event: " + error.ObjectEvent + "~n" &
      + "   Line: " + String(error.line) &
      + "~n~n" + "Do you wish to stop the DATABASE?"
            ls_msg = "Error in row " + string(row) + "~n~r"
            ls_msg = ls_msg + "Database vendor error code - " + string(sqldbcode) + "~n~r"
            ls_msg = ls_msg + sqlerrtext
            
end choose

Messagebox(lw_parent.title, ls_msg, exclamation!)

// Scroll to the row in error
this.setrow(row)
this.scrolltorow(row)
this.selectrow(0,false)

// Set the return value to 1 which supressess PowerBuilder's build in message
return 1
etc.

Imrancs, as u suggested I am considering using pos and mid.
position_of_unwanted_words=pos(sqlerrtext,"[SQL SERVER]")
SQLERRTEXT=MID(SQLERRTEXT,POSITION_OF_WANTED_WORDS,10000)



0
 

Author Comment

by:Mateen
ID: 12025011
Hi solution46:
I agree with u.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12025398
In many cases you can change the body of the text eg:

change

[Microsoft][ODBC Sql Server Driver][Sqlerror]violation of unique key constraint 'ix_tablex'. Cannot insert duplicate key
in object 'tablex'

to :
[Microsoft][ODBC Sql Server Driver][Sqlerror]Duplicates arent alowed in tablex


but  the
[Microsoft][ODBC Sql Server Driver][Sqlerror]  but is un-suppressable in SQL so as all have suggested you would need to do this in the frontent.
0
 
LVL 9

Expert Comment

by:solution46
ID: 12028871
Glad to hear it, Mateen :)

Unfortunately I know precisely nothing about Powerbuilder - I would try asking how to go about doing this in the Powerbuilder forum (it's under Programming \ Languages).

Regards, and good luck,

s46.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question