1
/*
SELECT @@VERSION    
Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64)   Jan  6 2017 14:24:37   Copyright (c) 
Microsoft Corporation  Developer Edition (64-bit) on Windows 8.1 Enterprise 6.3 <X64> (Build 9600: ) 
*/
USE tempdb
-- PHASE 1 - BUILD THE Test Table, insert test values
IF OBJECT_ID('dbo.tbl_Test') IS NOT NULL
    DROP TABLE dbo.tbl_Test
GO
CREATE TABLE dbo.tbl_Test (
    TestTableKey INT IDENTITY(1,1),
    TestTableName VARCHAR(100) UNIQUE,
    Status INT DEFAULT 1,
    DateStamp DATETIME DEFAULT GETDATE(), 
    Signature VARCHAR(50) DEFAULT 'SYSADMIN' )

INSERT INTO dbo.tbl_Test
    (TestTableName)
VALUES ('FISHPASTE'),('REDRO'),('PEANUTBUTTER')
GO
IF OBJECT_ID('dbo.sp_Update') IS NOT NULL
    DROP PROCEDURE dbo.sp_Update
GO
CREATE PROCEDURE dbo.sp_Update (
    @TestTableKey INT NULL,
    @TestTableName VARCHAR (100) NULL,
    @Status INT NULL,
    @Signature VARCHAR (50) NULL)
AS
BEGIN
    UPDATE dbo.tbl_Test
    SET TestTableName = ISNULL(@TestTableName,TestTableName),
        Status = ISNULL(@Status,Status),
        Signature = ISNULL(@Signature,Signature),
        DateStamp = GETDATE()
    OUTPUT INSERTED.TestTableKey
    WHERE TestTableKey = @TestTableKey
        OR  TestTableName = @TestTableName
END
GO

SELECT * FROM tbl_Test
--This is the base state of the data.

EXEC dbo.sp_Update NULL,'REDRO',0,NULL
SELECT * FROM tbl_Test
--NOTE at this time Row 2 (as output) Status = 0 and DateStamp is updated.

EXEC ('dbo.sp_Update NULL,''REDRO'',10,NULL')
SELECT * FROM tbl_Test
--NOTE at this time Row 2 (as output) Status = 10 and DateStamp is updated.

DECLARE @STATEMENT VARCHAR(MAX) = '''SET FMTONLY OFF; EXEC dbo.sp_Update 1, ''''FISHPASTE'''', ''''2'''',''''TESTING'''';'''
DECLARE @SQLStatement VARCHAR(MAX) = REPLACE(REPLACE('
SELECT * 
FROM OPENROWSET (
            ''SQLNCLI'',
            ''DRIVER=SQL Server;SERVER=#SERVERNAME#;Trusted_Connection=Yes;DATABASE=#DatabaseName#'',
            ' + CONVERT(VARCHAR(MAX) , @Statement) + '
            )','#SERVERNAME#', CONVERT(VARCHAR(200),SERVERPROPERTY('SERVERNAME'))),'#DatabaseName#',CONVERT(VARCHAR(200),DB_NAME()))
PRINT @SQLStatement
EXEC (@SQLStatement)
SELECT * FROM tbl_Test
/*
Note at this time Row 1 (as output) is UNCHANGED! 
How can OUTPUT.INSERTED result the correct row number from NON-UPDATED ROW?
Look at the messages tab in SQL Management Studio for the exact query exeucted, copy it and run it. 
It will update the row; but not in openrowset!
*/

/*Clean up*/
DROP PROCEDURE sp_Update
DROP TABLE tbl_Test

Hoping someone can explain why my openrowset execution of my procedure returns the key for the row updated, but the procedure does not update the row returned.

The scenario is used to update a remote database in a t-sql context... with native tools, in a controlled manner. Currently targeting same server for demonstration of failing.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.