Folks,
I am implementing some SQL processing which will be kicked off from a windows batch file. This file will use osql to call an SP which contains nested SPs. (The batch file also calls some windows applications afterwards). The SPs will result in the creation of acsii output files via bcp.
I have two question in this post a/ on error contexts and b/ on hardcoding server names in SP sqls.
---
a/
I'm using the 'standard' IF @@error = 0 error checking in my top level SP. This seems to be the only way (as RAISERROR will cause the top-level osql command to quit I think). However, if I hit an error in my lower SPs I want to know where it occurred.
So.. is there a way of capturing the error context (as is printed interactively (e.g. Server: Msg 137, Level 15, State 2, Procedure testprocedure, Line 3 Must declare the variable '@foobar'.).
I'm planning to write error messages to an error table which will be dumped to file at the end of processing. (Users can acces the file, but cannot see the DB).
---
b/
Also, I'd rather not hardcode the server/db/user/password info into my osql call or inside the SQLs in my stored proc. I intended to pass them as DOS parameters, but it seems I cannot use variables for these within the stored procedures without using messy (and less maintainable) dynamic sqls. Is this true? And anyone got a neat solution ?
Thanks in advance!
I am implementing some SQL processing which will be kicked off from a windows batch file. This file will use osql to call an SP which contains nested SPs. (The batch file also calls some windows applications afterwards). The SPs will result in the creation of acsii output files via bcp.
I have two question in this post a/ on error contexts and b/ on hardcoding server names in SP sqls.
---
a/
I'm using the 'standard' IF @@error = 0 error checking in my top level SP. This seems to be the only way (as RAISERROR will cause the top-level osql command to quit I think). However, if I hit an error in my lower SPs I want to know where it occurred.
So.. is there a way of capturing the error context (as is printed interactively (e.g. Server: Msg 137, Level 15, State 2, Procedure testprocedure, Line 3 Must declare the variable '@foobar'.).
I'm planning to write error messages to an error table which will be dumped to file at the end of processing. (Users can acces the file, but cannot see the DB).
---
b/
Also, I'd rather not hardcode the server/db/user/password info into my osql call or inside the SQLs in my stored proc. I intended to pass them as DOS parameters, but it seems I cannot use variables for these within the stored procedures without using messy (and less maintainable) dynamic sqls. Is this true? And anyone got a neat solution ?
Thanks in advance!