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
MarchJ
Saud
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).
JohnDXMurphy
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
SYED HANIF SH
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