Solved

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

Posted on 2004-09-10
8
335 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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

912 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now