Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Unanswered: @@error for dynamic SQL

    Does anyone know of a simple, correct way to retrieve the value of @@error from dynamic SQL? I can't use:
    Code:
    SELECT @cCmd = 'EXECUTE @i = [' + @cServer
    +  '].master.dbo.sp_password NULL, ''junque'', ''' 
    +  @cLogin + ''''
    
    EXECUTE (@cCmd)
    SELECT @@error  -- only shows locally reported error
    
    EXECUTE sp_executesql @cCmd, N'@i INT OUTPUT'
    ,  @iError OUTPUT -- Only shows remotely reported error
    I can get the error from a local procedure (one run on this box) using the first example, or on a remote server (via RPC) using the second, but not both. Any suggestions?

    -PatP

  2. #2
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    I'm confused as to what your expected output should be since I'm getting errors all over the place (typical results for me).

    QUERY:
    Set Concat_Null_Yields_Null Off

    Declare @cCmd nvarchar(4000)
    Declare @cServer nvarchar, @cLogin nvarchar
    , @iError int

    SELECT @cCmd = 'EXECUTE @i = [' + @cServer
    + '].master.dbo.sp_password NULL, ''junque'', '''
    + @cLogin + ''''

    Print @cCmd + N' << The Command' + nchar(10)

    EXECUTE (@cCmd)
    SELECT @@error As 'Error' -- only shows locally reported error

    EXECUTE @iError = sp_executesql @cCmd, N'@i INT OUTPUT'
    , @iError OUTPUT -- Only shows remotely reported error

    Select @iError
    RESULTS
    EXECUTE @i = [].master.dbo.sp_password NULL, 'junque', '' << The Command

    Server: Msg 137, Level 15, State 2, Line 1
    Must declare the variable '@i'.
    Server: Msg 1038, Level 15, State 1, Line 1
    Cannot use empty object or column names. Use a single space if necessary.
    Error
    -----------
    1038

    (1 row(s) affected)

    Server: Msg 1038, Level 15, State 3, Line 1
    Cannot use empty object or column names. Use a single space if necessary.
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near ''.

    -----------
    170

    (1 row(s) affected)
    What value do you expect for the error codes?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm attempting to dynamically build a password change request, that will be executed on some arbitrary number of servers determined by values in a table. The code that I posted assumes that your current login has the ability to dynamically execute sp_password for a login on both your local server and all of the remote servers.

    When it executes sp_password on the local server, the first example works well and the error code is available via the SELECT. When it executes sp_password on a remote server (via RPC, Remote Procedure Call), then the second example works well, but I haven't found any reliable way to retrive the @@error result.

    At the moment, I'm thinking that I'll just use the 0/1 return code from sp_password and wait until I have time to create an extended stored procedure (or the problem goes away) for a more elegant fix.

    -PatP

  4. #4
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Thanks - now I get it.

    The only way I could find around this is to put a wrapper around sp_password by creating my_sp_password via hacking sp_password - one of the flaws with this approach is that when sp_password changes, you have to also potentially change the wrapper (my_sp_password).

    If you choose to use this hack, you may want to comment out/remove the RAISERRORs. Note: I did not test this on linked servers.

    WRAPPER
    create procedure my_sp_password
    @old sysname = NULL, -- the old (current) password
    @new sysname, -- the new password
    @loginame sysname = NULL -- user to change password on
    as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @self int, @error int
    select @self = CASE WHEN @loginame is null THEN 1 ELSE 2 END
    , @error = -1

    -- RESOLVE LOGIN NAME
    if @loginame is null
    select @loginame = suser_sname()

    -- CHECK PERMISSIONS (SecurityAdmin per Richard Waymire) --
    IF (not is_srvrolemember('securityadmin') = 1)
    AND not @self = 1
    begin
    dbcc auditevent (107, @self, 0, @loginame, NULL, NULL, NULL)
    raiserror(15210,-1,-1)
    select @error = 15210
    return (@error)
    end
    ELSE
    begin
    dbcc auditevent (107, @self, 1, @loginame, NULL, NULL, NULL)
    end

    -- DISALLOW USER TRANSACTION --
    set implicit_transactions off
    IF (@@trancount > 0)
    begin
    raiserror(15002,-1,-1,'sp_password')
    select @error = 15002
    return (@error)
    end

    -- RESOLVE LOGIN NAME (disallows nt names)
    if not exists (select * from master.dbo.syslogins where
    loginname = @loginame and isntname = 0)
    begin
    raiserror(15007,-1,-1,@loginame)
    select @error = 15007
    return (@error)
    end

    -- IF non-SYSADMIN ATTEMPTING CHANGE TO SYSADMIN, REQUIRE PASSWORD (218078) --
    if (@self <> 1 AND is_srvrolemember('sysadmin') = 0 AND exists
    (SELECT * FROM master.dbo.syslogins WHERE loginname = @loginame and isntname = 0
    AND sysadmin = 1) )
    SELECT @self = 1

    -- CHECK OLD PASSWORD IF NEEDED --
    if (@self = 1 or @old is not null)
    if not exists (select * from master.dbo.sysxlogins
    where srvid IS NULL and
    name = @loginame and
    ( (@old is null and password is null) or
    (pwdcompare(@old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1) ) )
    begin
    raiserror(15211,-1,-1)
    select @error = 15211
    return (@error)
    end

    -- CHANGE THE PASSWORD --
    exec sp_configure 'allow', 1
    Reconfigure
    set @error = @@error

    if @error = 0
    begin
    -- update master.dbo.sysxlogins
    -- set password = convert(varbinary(256), pwdencrypt(@new)), xdate2 = getdate(), xstatus = xstatus & (~2048)
    -- where name = @loginame and srvid IS NULL
    exec @error = sp_password @old, @new, @loginame
    end

    -- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
    if @error = 0
    begin
    exec('use master grant all to null')
    set @error = @@error
    end

    -- FINALIZATION: RETURN SUCCESS/FAILURE --
    if @error <> 0
    return (@error)
    raiserror(15478,-1,-1)
    set @error = 15478
    return (@error) -- sp_password
    EXECUTION
    Declare @error int
    Select @error = -1
    exec @error = my_sp_password 'fred', 'ted', 'sam'
    Select @error

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Or

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE ProcessLog(Spid int, ProcessName varchar(255), Row_Count int, Error int, Return_Code int, Add_Dt datetime DEFAULT GetDate())
    GO
    
    DECLARE @cCmd varchar(8000), @cServer sysname, @cLogin sysname
    SELECT @cServer = @@SERVERNAME, @cLogin = SYSTEM_USER
    
    SELECT @cCmd =    'DECLARE @i int' + CHAR(13)
    		+ 'EXECUTE @i = [' + @cServer
    		+  '].master.dbo.sp_password NULL, ''test'', ''' 
    		+  @cLogin + ''''+ CHAR(13)
    		+ 'INSERT INTO ProcessLog(Spid, ProcessName, Row_Count, Error, Return_Code)' + CHAR(13)
    		+ 'SELECT ' + CONVERT(varchar(15),@@SPID) + ', ''sp_password'''+', @@ROWCOUNT, @@ERROR, @i' + CHAR(13)
    
    SELECT @cCmd
    
    EXECUTE (@cCmd)
    
    SELECT * FROM ProcessLog o 
     WHERE Spid = @@SPID 
       AND Add_Dt IN ( SELECT MAX(Add_Dt)
    		  FROM ProcessLog i 
    		 WHERE i.Spid = o.Spid)
    GO
    
    SET NOCOUNT OFF
    DROP TABLE ProcessLog
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Brett's example works like my example 1 for the local server, and would make Rube Goldberg giddy with glee in the process. I really want something that works equally well for both local and remote without a whole lot of work to make it fly.

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How does your first one work?

    Doesn't it return an error?

    Server: Msg 137, Level 15, State 2, Line 1
    Must declare the variable '@i'.
    ???

    I didn't test it remotely, are you saying it won't work?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What I posted is actually an excerpt from a much larger script, only to show the general idea of what I'm doing... I didn't intend for anyone to execute it "as is", but I should have been more clear about that.

    The first bit of logic works nicely when the code is being executed on the local server (in other words, when @cServer = @@servername like in your example). The second bit of code works when the dynamic SQL generates an RPC, and it can be elaborated on to return the actual @@error value by following the SELECT with a SET command in the dynamic SQL. Because the code is trapped at two different layers within the SQL Engine, and is reported differently depending on who catches what, I can't think of a single, coherant way to trap the actual error code (15087 if I remember correctly) for both cases unless I construct wildly different bits of SQL and test to see which SQL I should use.

    As I said, I think this is a design issue in SQL Server that I can't readily fix. I think that the proper answer is to just break down and code it as an extended stored procedure, where I can "put a leash on the monster" and stop fighting it. I just don't have the luxury of the time (probably around 8 hours to write and test, without the enhancements I'd really like to add) needed for that kind of fix at the moment.

    -PatP

  9. #9
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    DECLARE @j INT
    CREATE TABLE #temp ( id INT IDENTITY (1,1), result VARCHAR(255) )
    INSERT INTO #temp (result)
    EXEC @j = master.dbo.xp_cmdshell 'osql -E -Q "EXIT(EXECUTE master.dbo.sp_password NULL, ''junque'', ''test3''; )"'
    SELECT @j
    IF @j = -100
    BEGIN
    SELECT SUBSTRING(result, CHARINDEX('Msg ', result) + 4, (CHARINDEX(' ',result,CHARINDEX('Msg ', result) + 5) - CHARINDEX('Msg ', result) - 5))
    FROM #temp
    WHERE id = 1
    END

    DROP TABLE #temp


    NOTE: I don't have a remote server to test it with at home.

    Tim S
    Last edited by TimS; 02-05-05 at 01:58.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't think that the query from TimS will get me much further than what I've got right now, but it gives me an idea! I think he's resolved the issue of how to work around the distinctly different way that MS-SQL handles errors on the local SQL Server versus remote SQL servers by using osql.exe to put all servers on a local footing. I'll have to experiment a bit when I get back to a machine with MS-SQL available, but I think it might work to do something like:
    Code:
    SET @cmd = 'osql -E -Q "EXECUTE sp_password NULL, ''abcde'', ''' 
    +  @cLogin + ''' EXIT(SELECT @@error)" -s ' + @cServer'
    
    EXECUTE @iRC = master.dbo.xp_cmdshell @cCmd
    To simulate the remote server, just create a second instance of MS-SQL on your machine. As long as the network isn't specifically a part of your test, a second instance works nicely for whatever I've needed to test.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •