| Author |
Topic |
|
manikandan
Starting Member
35 Posts |
 Posted - 2002-04-25 : 03:12:50
|
| Hi i am using sql 2000 and powerbuilder. I am executing a stored procedure from pb code. (i don't have any display fields to show the results). But unfortunately, i've few print statements in that stored proc. I am using ODBC to connect. The problem is that if i execute that procedure, i am getting the error code as 999. (there is no help for this code in sql books online).If i remove the print statment in that proc and execute it, it works properly. I don't know whether my inference is right or is there any way( set commands ) to suppress those print values.I thought it fails because it does not know where to print those values...Am i right? If anyone could help me out. It will be great. we are migrating from sybase to sql 2000 and in sybase it works. That's the problem. cheersmanikandanCheersManikandan |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-04-25 : 03:18:29
|
| put SET NOCOUNT ON as first statement in your sp .remove all the prints. and replace them with select command. if you want to return more then one value . union all the selects.Print is only for displaying you cant catch printed values. a better option is using a select.i would suggest you to post your sp code too.-------------------------------------------------------------- |
 |
|
|
manikandan
Starting Member
35 Posts |
Posted - 2002-04-25 : 05:47:01
|
| Hi NazimThanks for the reply.But the only problem is i can't replace print statement with select. there are more than 1000 procedures and so many print statements.My procedure is very simple. It just deletes a record, inserts a new record. Depending on the state, it print the messages.It is working with sybase, so i believe it should also work with sql...Has anyone has come against the error number 999. If so, what does it mean..cheersmanikandanCheersManikandan |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-04-25 : 08:16:07
|
| if you are assigning to a result set a stored procedure...then the print statements in the stored procedure will cause errors...because a result set expects everything that comes from the stored procedure to be the recordset...it doesn't anticipate the output from the print statements and can't handle it.PRINT statements are for debugging BEFORE you go live when testing through Query Analyser, etc ....and before you call code from VB (and I presume the same problem happens in ASP)If these procedures are a direct port from Sybase, I would look up the compatability table between these 2 products....I would think that the "what doesn't work, without tweaking" list will include PRINT statements. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-04-25 : 08:17:58
|
| I think you are outta luck here.The ODBC driver for SQL Server obviously doesn't support print statements.I don't know if PB can support OLEDB, but you might try using that as a data connector.I think the only way around it though is to remove your print statements. This is not too hard to do, you can script out all your procedures in Enterprise Manager, then use a search and replace in an editor to comment out all the print statements.I know you are going to tell me you can't do this. But I don't think you have an option....sorryDamian |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-04-25 : 08:18:54
|
Sniped dammit  Yeah, what he said.Damian |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-04-25 : 13:11:39
|
| "Sniped dammit "That's just made my day.....to be able to beat the Great Merkin across 5000miles and 11 hours of a time-difference....maybe Graz could put together a "sniping indicator"...to identify the hot shots....and all the dead ducks!!!! |
|
|
|
manikandan
Starting Member
35 Posts |
Posted - 2002-04-26 : 02:54:07
|
| Hi Guys,Thanks for all the replies. When i am coding for the stored procs, i will definitely avoid using print statements in the final version ( why final ? i will never use it)But actually, even with a print statement, the procedure works fine. I tried with VB as well as PB...but if you check for the error code, then it says that there is an error (because of the print problem). In PB terminologies, sqldbcode will give the db error number. it will be 999. And sqlerrtext should ideally contain the error description. But here sqlerrtext contains the value that is getting printed (the first print statement.)In my PB code, i've executed the procedure. And after that i've checked for the return value. and so the application is getting terminated (sqlcode <> 0). I've added a condition to check for this error number also. And i ignore the error message for this error number. I know it is pretty naive. but i don't have the rights to change the stored procs. and the guys who worked with stored procs are not going to change these (atleast not before my deadline....more than 600 procs are there.)FOR PB guys :- There won't be any problems if you are using datawindow to execute the procs. If you directly execute the proc, then only the problem comes.Thanks for u guys help.cheersmanikandanCheersManikandan |
|
|
|
|
Print statement in stored procedures