Error handling
DFS
statement and a User name, tries to execute the SQL, then handles errors by
writing them to a log table:
CREATE PROCEDURE MyApp_execSQL
@SQLStatement nvarchar(4000),
@UserName nvarchar(10)
AS
DECLARE @Err int
EXECUTE sp_ExecuteSQL @SQLStatement
SELECT @Err = @@ERROR
IF (@Err <> 0)
EXECUTE sp_ExecuteSQL
"INSERT INTO ERRORS_LOG (UserName, errorNum)
VALUES(@UserName,@Err);"
The error handling doesn't work right. I keep getting the message:
Server: Msg 137, Level 15, State 2, Line 2
Must declare the variable '@UserName'.
If I declare it, I get "Already declared".
Any ideas?
Thanks
Erland Sommarskog
> CREATE PROCEDURE MyApp_execSQL
> @SQLStatement nvarchar(4000),
> @UserName nvarchar(10)
> AS
> DECLARE @Err int
>
> EXECUTE sp_ExecuteSQL @SQLStatement
>
> SELECT @Err = @@ERROR
> IF (@Err <> 0)
> EXECUTE sp_ExecuteSQL
> "INSERT INTO ERRORS_LOG (UserName, errorNum)
> VALUES(@UserName,@Err);"
>
>
> The error handling doesn't work right. I keep getting the message:
>
> Server: Msg 137, Level 15, State 2, Line 2
> Must declare the variable '@UserName'.
@UserName is not declare in the dynamic SQL statement. It is declared
in the calling procedure, but that is a different scope. Also, you should
use N'' to quote the SQL statement you pass to sp_executesql, since the
parameter is of type ntext.
I have a longer article on dynamic SQL, you will find more useful
information there. It's http://www.algonet.se/~sommar/dynamic_sql.html.
(Actually I've just uploaded a new version of it.)
--
Erland Sommarskog, SQL Server MVP, som...@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
BP Margolin
The following Microsoft Knowledge Base article might provide you some pointers on using the sp_ExecuteSQL system stored procedure:
INF: Using Output Parameters with sp_executesql
http://support.microsoft.com/default.aspx?scid=kb;EN-US;262499
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"DFS" <nos...@nospam.com> wrote in message news:vbfbbrc...@corp.supernews.com...
DFS
final [working] procedure:
CREATE PROCEDURE MyApp_execSQL
@SQLStatement nvarchar(4000),
@AppUser nvarchar(10)
AS
DECLARE @Err int
EXEC(@SQLStatement)
SELECT @Err = @@ERROR
IF (@Err <> 0 AND @Err IS NOT NULL)
BEGIN
EXEC(N'INSERT INTO ERROR_LOG (AppUserID, errorNum) VALUES(''' + @AppUser +
''',' + @Err + ');')
END
Short and very simple, with as few declarations as possible. The use of "N"
is required in the error logging, because the EXEC function expects an
nvarchar string.
Overall it's still a kludge, but it helps return better error messages than
the ubiquitous "3146 ODBC call failed" to my Access client app.
In addition to the error number, I'd also like to store the exact error
message returned by SQL Server (like 'Invalid object name 'TBL_NAME2'.') but
that's a topic for tomorrow.
"BP Margolin" <bpm...@attglobal.net> wrote in message
news:3eb83...@news1.prserv.net...
DFS
procedure:
CREATE PROCEDURE MyApp_execSQL
@SQLStatement nvarchar(4000),
@AppUser nvarchar(10)
AS
DECLARE @Err int
EXEC(@SQLStatement)
SELECT @Err = @@ERROR
IF (@Err <> 0 AND @Err IS NOT NULL)
BEGIN
EXEC(N'INSERT INTO ERROR_LOG (AppUserID, errorNum) VALUES(''' + @AppUser +
''',' + @Err + ');')
END
Short and very simple, with as few declarations as possible. The use of "N"
in the error logging SQL was necessary - as you suggested.
Overall it's still a kludge, but it helps return better error messages than
the ubiquitous "3146 ODBC call failed" to my Access client app.
In addition to the error number, I'd also like to log the exact error
message returned by SQL Server (like 'Invalid object name 'TBL_NAME2'.') but
that's a topic for tomorrow.
"Erland Sommarskog" <som...@algonet.se> wrote in message
news:Xns9373F400C...@127.0.0.1...
Erland Sommarskog
> Thanks very much Erland. I eventually created this final [working]
> procedure:
>
> CREATE PROCEDURE MyApp_execSQL
> @SQLStatement nvarchar(4000),
> @AppUser nvarchar(10)
> AS
> DECLARE @Err int
>
> EXEC(@SQLStatement)
>
> SELECT @Err = @@ERROR
> IF (@Err <> 0 AND @Err IS NOT NULL)
> BEGIN
> EXEC(N'INSERT INTO ERROR_LOG (AppUserID, errorNum) VALUES(''' + @AppUser
+
> ''',' + @Err + ');')
> END
>
> Short and very simple, with as few declarations as possible. The use of
> "N" in the error logging SQL was necessary - as you suggested.
>
> Overall it's still a kludge,
Certainly. But why use dynamic SQL at all to insert into the table.
And if you use dynamic SQL, why use EXEC() which is far more compliacted
to use than sp_executesql? Did you look at the article I suggested.
Erland Sommarskog
> In addition to the error number, I'd also like to store the exact error
> message returned by SQL Server (like 'Invalid object name 'TBL_NAME2'.')
> but that's a topic for tomorrow.
No, that is a topic for next year. Or the next decade. There is no way
you can do this.
DFS
> DFS (nos...@nospam.com) writes:
> > In addition to the error number, I'd also like to store the exact error
> > message returned by SQL Server (like 'Invalid object name 'TBL_NAME2'.')
> > but that's a topic for tomorrow.
>
> No, that is a topic for next year. Or the next decade. There is no way
> you can do this.
I think there is, but not from within my stored procedure.
From Books Online: "@@ERROR is the only part of a Microsoft® SQL ServerT
2000 error available within the batch, stored procedure, or trigger that
generated the error. All other parts of the error, such as its severity,
state, and message text containing replacement strings such as object names,
are returned only to the application in which they can be processed using
the API error handling mechanisms."
DFS
> Certainly. But why use dynamic SQL at all to insert into the table.
I don't see another way. I want to store the User's name in the table, and
it's passed into the procedure.
Looks like I spoke too soon, anyway. When I submit a query longer than 128
characters to my procedure, I get an error message:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'blah blah blah' is too long. Maximum length
is 128.
???????
How do I submit a long query (say up to 3500 characters) to a procedure?
Thanks Erland
Erland Sommarskog
> I think there is, but not from within my stored procedure.
>
> From Books Online: "@@ERROR is the only part of a Microsoft® SQL ServerT
> 2000 error available within the batch, stored procedure, or trigger that
> generated the error. All other parts of the error, such as its severity,
> state, and message text containing replacement strings such as object
> names, are returned only to the application in which they can be
> processed using the API error handling mechanisms."
Yes, that is correct. I was assuming that you were looking into do this
from a stored procedure. If you want to do it from your client, you have
better possibilities.
--
Erland Sommarskog
> "Erland Sommarskog" <som...@algonet.se> wrote in message
>
>> Certainly. But why use dynamic SQL at all to insert into the table.
>
> I don't see another way. I want to store the User's name in the table,
> and it's passed into the procedure.
INSERT INTO ERRORS_LOG (UserName, errorNum)
VALUES(@UserName,@Err)
is all it takes. Without any EXEC or sp_executesql at all.
> Looks like I spoke too soon, anyway. When I submit a query longer than
> 128 characters to my procedure, I get an error message:
>
> Server: Msg 103, Level 15, State 7, Line 1
> The identifier that starts with 'blah blah blah' is too long. Maximum
> length is 128.
It appears that you pass a string enclosed in double quotes.
The preferred string delimiter in T-SQL is single quote. Double quote can
be a string delimiter, depending on the setting SET QUOTED_IDENTIFIER. When
this setting is ON, "" encloses identiers, permitting you to have table
names like "Order Details". (The normal quote delimiter in T-SQL is [],
but "" is there, because this is what ANSI uses.) The default setting
for QUOTED_IDENTIFIER depends on context, but it is ON in most contexts.
Now, your code was
EXEC(@SQL_statement)
so it seems that this variable you pass, start and ends with ", which
no matter the setting of QUOTED_IDENTIFIER appears flat wrong anyway.
There should not be any quotes at all. (Then again, you SQL statement
may include a very long string literal, in which it is OK, if you change
" to '.)
DFS
> DFS (nos...@nospam.com) writes:
> > "Erland Sommarskog" <som...@algonet.se> wrote in message
> >
> >> Certainly. But why use dynamic SQL at all to insert into the table.
> >
> > I don't see another way. I want to store the User's name in the table,
> > and it's passed into the procedure.
>
> INSERT INTO ERRORS_LOG (UserName, errorNum)
> VALUES(@UserName,@Err)
>
> is all it takes. Without any EXEC or sp_executesql at all.
Gotcha. I removed EXEC and the variable delimiters and it works fine.
Thanks.
> > Looks like I spoke too soon, anyway. When I submit a query longer than
> > 128 characters to my procedure, I get an error message:
> >
> > Server: Msg 103, Level 15, State 7, Line 1
> > The identifier that starts with 'blah blah blah' is too long. Maximum
> > length is 128.
>
> It appears that you pass a string enclosed in double quotes.
Yes.
> The preferred string delimiter in T-SQL is single quote. Double quote can
> be a string delimiter, depending on the setting SET QUOTED_IDENTIFIER.
When
> this setting is ON, "" encloses identiers, permitting you to have table
> names like "Order Details". (The normal quote delimiter in T-SQL is [],
> but "" is there, because this is what ANSI uses.) The default setting
> for QUOTED_IDENTIFIER depends on context, but it is ON in most contexts.
>
> Now, your code was
>
> EXEC(@SQL_statement)
>
> so it seems that this variable you pass, start and ends with ", which
> no matter the setting of QUOTED_IDENTIFIER appears flat wrong anyway.
> There should not be any quotes at all. (Then again, you SQL statement
> may include a very long string literal, in which it is OK, if you change
> " to '.)
No matter what, it won't accept a SQL statement longer than 128. With
QUOTED_IDENTIFIER ON or OFF, I get the same errors:
Say I try to do 6 INSERTS:
EXEC my_stored_proc (note: string length is 6 * 63 = 378 including spaces
and punctuation)
"INSERT INTO ERR_LOG (AppUserID,errorNum) VALUES('DFS',050701);
INSERT INTO ERR_LOG (AppUserID,errorNum) VALUES('DFS',050702);
INSERT INTO ERR_LOG (AppUserID,errorNum) VALUES('DFS',050703);
INSERT INTO ERR_LOG (AppUserID,errorNum) VALUES('DFS',050704);
INSERT INTO ERR_LOG (AppUserID,errorNum) VALUES('DFS',050705);
INSERT INTO ERR_LOG (AppUserID,errorNum) VALUES('DFS',050706);"
,"JONES"
Dbl-quotes "" or brackets [] around the INSERTS gives me .....
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'blah blah blah' is too long. Maximum length
is 128.
A single-quote or no delimiter around the INSERTS gives me....
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near ','.
If the SQL is shorter than 128 the proc works fine using double-quotes or
brackets (single-quote raises error).
Thanks for your help, Erland. This is important to me, so I'd like to figure
it out.
Erland Sommarskog
> No matter what, it won't accept a SQL statement longer than 128. With
> QUOTED_IDENTIFIER ON or OFF, I get the same errors:
>
> Say I try to do 6 INSERTS:
>
> EXEC my_stored_proc (note: string length is 6 * 63 = 378 including spaces
> and punctuation)
> "INSERT INTO ERR_LOG (AppUserID,errorNum) VALUES('DFS',050701);
> INSERT INTO ERR_LOG (AppUserID,errorNum) VALUES('DFS',050702);
> INSERT INTO ERR_LOG (AppUserID,errorNum) VALUES('DFS',050703);
> INSERT INTO ERR_LOG (AppUserID,errorNum) VALUES('DFS',050704);
> INSERT INTO ERR_LOG (AppUserID,errorNum) VALUES('DFS',050705);
> INSERT INTO ERR_LOG (AppUserID,errorNum) VALUES('DFS',050706);"
> ,"JONES"
>
>
> Dbl-quotes "" or brackets [] around the INSERTS gives me .....
>
> Server: Msg 103, Level 15, State 7, Line 1
> The identifier that starts with 'blah blah blah' is too long. Maximum
> length is 128.
>
>
> A single-quote or no delimiter around the INSERTS gives me....
>
> Server: Msg 170, Level 15, State 1, Line 9
> Line 9: Incorrect syntax near ','.
you invoke EXEC() in the stored procedure.
The above could fly with SET QUOTED_IDENTIFIER OFF. It also flies with
QUOTED_IDENTIFIER ON as long as the argument is less than 128 characters,
because it is a legal identifier.
If you change to single quotes, you have to keep in mind that you have
single quotes nested in the SQL statement, so you need to say:
EXEC 'INSERT ....... VALUES (''DFS'', 050701); ...
that is double the nested string delimiters.
But if you are submitting this call from Access, you would make it
a lot easier for you if you used RPC rather than composed an EXEC
statement.
--
DFS
I devised a solution. No SQL Server stored procedure. Instead, my
pass-thru queries from Access are altered dynamically in VBA code, and
wrapped inside some error trapping statements, like so:
set qItem = db.QueryDefs("PassThruQuery")
qItem.SQL = "DECLARE @ERR int SET @ERR = 0 "
qItem.SQL = qItem.SQL & SQL statement (update, insert, delete)
qItem.SQL = qItem.SQL & " SELECT @ERR = @@ERROR "
qItem.SQL = qItem.SQL & " IF @ERR <> 0 BEGIN INSERT INTO WORK_ERRORS
(AppUserID, errorNum) VALUES ('" & getLoggedUser() & "', @ERR); END "
qItem.Execute
It's rather kludgey I think, but it works perfectly. Just today one of my
users got "SQL Server error 2601: Cannot insert duplicate key row in table
with unique index" (my slight rewording of the actual sysmessage).
Thanks for your advice.
"Erland Sommarskog" <som...@algonet.se> wrote in message
news:Xns9376CEA3...@127.0.0.1...
Erland Sommarskog
> I devised a solution. No SQL Server stored procedure. Instead, my
> pass-thru queries from Access are altered dynamically in VBA code, and
> wrapped inside some error trapping statements, like so:
> It's rather kludgey I think, but it works perfectly. Just today one of my
> users got "SQL Server error 2601: Cannot insert duplicate key row in table
> with unique index" (my slight rewording of the actual sysmessage).
Glad to hear that you got something working!