Hi :
The database i am using is SQL Express.
I am using .cmd files to execute .sql files.
sqlcmd is used in .cmd files to execute the .sql stmts.
In .cmd file the sqlcmd line of code is as follows:
sqlcmd -i .\..\..\sql\tables\create_employee_table.sql
In .sql file the ddl stmt is as follows:
CREATE TABLE [Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[NationalIDNumber] [nvarchar](15) COLLATE Latin1_General_CS_AI NOT NULL,
[ContactID] [int] NOT NULL,
[LoginID] [nvarchar](256) COLLATE Latin1_General_CS_AI NOT NULL,
[ManagerID] [int] NULL,
DF_Employee_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
DDL used are create and drop of tables and indexes.
How do i log all the ddl stmts executed into some .log file (xyz.log)
The log file should read something like,
employee table created sucessfully
employee table dropped sucessfully.
...................
sqlcmd -o c:\log\xyz.log , gives me only the output for dml stmts (like select * from emp).. anything other than this will be very helpful.
Any solutions will be of great help.
logging DDL Stmts
Andrew Kidd
Michaelxyz3
Hi:
I don't want to write DDL stmts to any file.
I just want to write "employee table created sucessfuly" to xyz.log file
i am ready to use DDL triggers.
for example:
CREATE TRIGGER safety1
ON myDATABASE
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE
AS
PRINT 'table created successfully' ;
"table created successfully" should go into xyz.log file.
Any solutions will be of great help (code example will be excellent).
Thanking you,
Best regards,
Kumar
Cleber Dantas
If I am reading you right, you just need to put the following after your create table statement:
if object_id('dbo.employee') is not null
print 'employee table created successfully'
else
print 'employee table failed'
Then this will be printed and then added to the output in sqlcmd. I wouldn't use a DDL trigger for this, as it would happen all of the time (and possibly at times when you don't want it to).
hazzoom
Hello
You should take a closer look at the DDL Trigger section of BOL.. With these you can log this information into a table or logfile or whatever.
Since its a lot of typing and I am lazy you should read this:
http://msdn2.microsoft.com/en-us/library/ms190989(sql.90).aspx