February 28, 2006 at 9:08 am
Can any please inform me how I can trap an error from a call to a stored procedure on a remote server.
I am building a string such as @cmd =
[ServerName\InstanceName].master.dbo.sp_password 'CM22MN5AG','FG5XYD2GKJC','Login'
Then calling exec @result = SP_EXECUTESQL @cmd
This returns the expected result of
Server: Msg 15211, Level 16, State 1, Line 1
Old (current) password incorrect for user. The password was not changed.
But @result = 0, I assume because the actual exec to the remote server has been successful and therefore it is not picking up the result from the failure on the remote server.
I have also attempted to call exec @result = @cmdline and this returns
Could not find stored procedure 'master.dbo.sp_password 'FG5XYD2GKJC','Q5Q5J5MZCSK','dbatest''.
So I beleive my syntax to be correct in the first call but cannot for the life of me figure out how I can trap the error.
The aim of this call is to change the 'sa' password on every occurrence of an Instance from one controlling Instance as part of SOX compliance!
March 1, 2006 at 1:05 pm
What version of MS SQL Server are running?
On MS SQL Server 2000 sp3a This will raise the same error and return a value of 1
March 1, 2006 at 1:12 pm
Hold on the return val of 0 is coming from the sp_executesql.
March 1, 2006 at 1:36 pm
This will give you a 1 for errors and a 0 for success. Now I am assuming you are trying to log all this activity without using isql -o file.out files and so on.
A few options:
Note There is no way to capture the raiserror that is being thrown at run time "Old (current) password incorrect for user. The password was not changed.(42000,15211) Procedure(test)", which is useful.
#1 Use an external language c# in which you can capture the returned message
#2 Modify sp_password on every MS SQL Server to return @@error so that you can resolve to master.dbo.sysmessages for the text version (This will not give you %d %s or any other variable provided to raiserror at runtime)
Create
Procedure test
As
Begin
Declare @retVal Integer
Declare @old SysName
Declare @new SysName
Declare @login SysName
Set @old = 'oldpasswd'
Set @new = 'newpasswd'
Set @login = 'kbecker'
Exec @retVal = [server].master.dbo.sp_password @old, @new, @login
Select @retVal
End
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply