Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-09-05 : 11:16:29
|
| I'm writing a stored procedure and I've had to resort to using dynamic SQL.One of the passed parameters is a query which has been formed to select a set of users.This procedure builds a query which inserts rows in another table based on the passed query.Works fine - for a short string length query like:SELECT TOP 20 Firstname, Lastname FROM UsersWhen I pass a longer query, the procedure returns an error. In ASP:response.write err.descriptionWrites out about half of the SQL string, no description of the error.I'm guessing that since I haven't written any code to capture and return the error from the procedure properly, it error is defaulting to the query string, which is truncated to the maximum length of err.description.If that's correct, how to I code a return of the actual error to the err.description string?The last statement I have in this procedure is:EXEC (@SQL)Is itRETURN(@@ERROR)??SamC |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-05 : 11:38:35
|
| return (@@error) will return the error number - which is a good thing but not what you are looking for.You can get the actual error in the SP by looking at the output buffer seehttp://mysite.freeserve.com/root/spFormatOutputBufferThe offsets are for v7 - they have changed for v2000.That isn't pretty and misses the first few chars of the error message anyway.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-09-05 : 19:55:04
|
| One of the most common errors people run into when using dynamic SQL is that they did not define their variable large enough. This could explain why shorter commands work and longer ones don't. Are you sure that @SQL is long enough to hold the entire SQL command you are building? |
|
|
|
|
|
|
Retrieving an error from a stored procedure