Re: DDL Server Trigger - SQL Agent

dickster (grd@xxxxxxxxx) writes:
I created this trigger.

ALTER TRIGGER ddl_trig_database ON ALL SERVER
AFTER CREATE_DATABASE
AS

DECLARE @DBNAME AS nvarchar(100);
SET @DBNAME =
EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)
[1]','nvarchar(100)')

DECLARE @cmd NVARCHAR(512)
SET @cmd = 'sqlcmd -S BDA-RMSDB -d '+ @DBNAME +' -E -i \\share
\script.sql

EXEC MASTER..XP_CMDSHELL @cmd
GO

script.sql creates a table say

Then I call
CREATE DATABASE myDb WITH TRUSTWORTHY ON;

but i get the following error message:

Msg 4060, Level 11, State 1, Server INTERLAGOS, Line 1
Cannot open database "myDb" requested by the login. The login failed.
Msg 18456, Level 14, State 1, Server INTERLAGOS, Line 1
Login failed for user 'ACME\_srvrmssql'.

ACME\_srvrmssql - runs the SQL agent

Any ideas???

The problem is that the database is not accessible for other sessions
until the CREATE DATABASE statement has completed, and the trigger is
part of that statement. I tried a variation of your trigger where I
had:

SET @cmd = 'sqlcmd -S ' + @@servername +
' -E -Q "USE ' + @DBNAME + ' CREATE TABLE affe(u int)"'

This just hang, a nice find deadlock.

I think the best solution for what you want to achieve is write a CLR
stored procedure that reads the script file, and then runs it using
the context connection. Then you are running the script from the same
process that create the database. I have not tested whether that it
actually works, though.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Quantcast