Skip to main content
Database Administrators

Return to Question

edited tags
Link
Solomon Rutzky
Solomon Rutzky
  • 69.5k
  • 8
  • 155
  • 300
Tweeted twitter.com/StackDBAs/status/1083423411450322944
clarification
Source Link
Marcello Miorelli
Marcello Miorelli
  • 16.6k
  • 53
  • 170
  • 311

what am I trying to accomplish? something like on the example below at the end I print out the name of the server and the name of the database that I am on: enter image description here

but on the example below, although I was technically inside database cola, the statement was apcore.upl.applicationDocument, apcore is another database.

is there a way to print out apcore, instead of cola?

SET NOCOUNT OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRAN T1

PRINT ''
PRINT 'AFTER BEGIN TRAN'
PRINT ''
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT '@@SERVERNAME: ' + CAST(@@SERVERNAME AS VARCHAR) + CHAR(10) + 'Databasename: ' + DB_NAME()
PRINT ''


DECLARE @row INT

USE COLA
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

            update t
            set documentstateid = 5
             from apcore.upl.applicationDocument t
            where applicationid in (
                                    319761
                                    ,320455
                                    ,333433
                                    ,351642
                                    ,371539
                                    ,372508
                                    )

             and documentStateId not in (1,5)

SELECT @row = @@ROWCOUNT

PRINT ''
PRINT 'AFTER RUNNING THE UPDATE(s)'
PRINT ''
PRINT 'The number of rows updated:' + SPACE(1) + CAST(@row as varchar)
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT 'Server Name is:' + space(1) +  @@SERVERNAME + CHAR(10) + 'Databasename: ' + DB_NAME()


COMMIT TRAN T1

PRINT ''
PRINT 'AFTER COMMIT'
PRINT ''
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT 'Server Name is:' + space(1) +  @@SERVERNAME + CHAR(10) + 'Databasename: ' + DB_NAME()

what am I trying to accomplish? something like on the example below at the end I print out the name of the server and the name of the database that I am on: enter image description here

but on the example below, although I was technically inside database cola, the statement was apcore.upl.applicationDocument, apcore is another database.

is there a way to print out apcore, instead of cola?

SET NOCOUNT OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRAN T1

PRINT ''
PRINT 'AFTER BEGIN TRAN'
PRINT ''
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT '@@SERVERNAME: ' + CAST(@@SERVERNAME AS VARCHAR) + CHAR(10) + 'Databasename: ' + DB_NAME()
PRINT ''


DECLARE @row INT

USE COLA
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

            update t
            set documentstateid = 5
             from apcore.upl.applicationDocument t
            where applicationid in (
                                    319761
                                    ,320455
                                    ,333433
                                    ,351642
                                    ,371539
                                    ,372508
                                    )

             and documentStateId not in (1,5)

SELECT @row = @@ROWCOUNT

PRINT ''
PRINT 'AFTER RUNNING THE UPDATE(s)'
PRINT ''
PRINT 'The number of rows updated:' + SPACE(1) + CAST(@row as varchar)
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT 'Server Name is:' + space(1) +  @@SERVERNAME + CHAR(10) + 'Databasename: ' + DB_NAME()


COMMIT TRAN T1

PRINT ''
PRINT 'AFTER COMMIT'
PRINT ''
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT 'Server Name is:' + space(1) +  @@SERVERNAME + CHAR(10) + 'Databasename: ' + DB_NAME()
Source Link
Marcello Miorelli
Marcello Miorelli
  • 16.6k
  • 53
  • 170
  • 311

how to find the database context of the last sql statement?

I would like to find out from the code below, what was the name of the database where the last command was run.

In this case I am looking for my_other_database.

his there any way of finding this? Is there any DMV that I should be looking at for this info?

Also, what if the update was wrapped in a dynamic sql, would it be possible to track it then?

use my_database
go

begin tran t1

UPDATE my_other_database.REF.applicationReference
SET uploadPaperReferences = 0
WHERE REFERENCEID IN (
69361,
69690,
69354,
69358,
69362,
69732,
69863,
70187
)

commit tran t1