Solved

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

Posted on 2004-09-10
8
337 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
8 Comments
 
LVL 6

Assisted Solution

by:OlegP
OlegP earned 125 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 250 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 9

Assisted Solution

by:solution46
solution46 earned 125 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

856 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