?
Solved

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

Posted on 2004-09-10
8
Medium Priority
?
342 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

743 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