Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>When you use EXEC() each use is in its own context. So, when you do EXEC('USE MyDB') it switches to MyDB for that context then the command ends and you're back where you started. There are a couple of possible solutions...</p> <p>You can call sp_executesql with a database name (for example, MyDB..sp_executesql) and it will run in that database. The trick is to let you do that dynamically, so you basically wrap it twice like so:</p> <pre><code>DECLARE @cmd NVARCHAR(2000), @my_db VARCHAR(255) SET @my_db = 'MyDatabaseName' SET @cmd = 'DECLARE @my_cmd NVARCHAR(2000); SET @my_cmd = ''CREATE TRIGGER DBA_Audit ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @data XML DECLARE @cmd NVARCHAR(1000) DECLARE @posttime NVARCHAR(24) DECLARE @spid NVARCHAR(6) DECLARE @loginname NVARCHAR(100) DECLARE @hostname NVARCHAR(100) SET @data = EVENTDATA() SET @cmd = @data.value(''''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'''', ''''NVARCHAR(1000)'''') SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'''''''',''''''''))) SET @posttime = @data.value(''''(/EVENT_INSTANCE/PostTime)[1]'''', ''''DATETIME'''') SET @spid = @data.value(''''(/EVENT_INSTANCE/SPID)[1]'''', ''''nvarchar(6)'''') SET @loginname = @data.value(''''(/EVENT_INSTANCE/LoginName)[1]'''', ''''NVARCHAR(100)'''') SET @hostname = HOST_NAME() INSERT INTO [DBA_AUDIT].dbo.AuditLog(Command, PostTime,HostName,LoginName) VALUES(@cmd, @posttime, @hostname, @loginname);''; EXEC ' + @my_db + '..sp_executesql @my_cmd' EXEC (@cmd) </code></pre> <p>The other option is to do this as a two-step process where the first step generates and prints out the actual code with USE statements and all, then you run that generated code.</p>
    singulars
    1. This table or related slice is empty.
    1. POCreate a DDL trigger in every database on a 2005 instance
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USTom H
    1. USTom H
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCreate a DDL trigger in every database on a 2005 instance
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COGO is interpreted by the SSMS, so I don't think that will work. But maybe I'm doing it incorrectly: 'USE ' + @dataname + ' GO CREATE Trigger....'. I've added semi-colons to no avail. Without the go, I'm back to 'Create Trigger must be first statement...'
      singulars
      1. PO
      1. USSam
    2. COYou're absolutely right... I've found a solution though, so I'm going to redo this answer...
      singulars
      1. PO
      1. USTom H
    3. COCould you get this to parse with an actual CREATE TRIGGER statement in there? I'm still getting the 'trigger must be first' error - among others. It's not often that I have to double escape all my quotes
      singulars
      1. PO
      1. USSam
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload