logging the DDL stmts

Hi :

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.

This is a broader SQL question than just SQL Express so I'm moving it to the Database Engine forum; I think you'll find a better answer there.

In looking around a bit, I found information about DDL Triggers which would seem to do what you suggest. The folks in the other forum can validate my guess.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

From the sounds of it you just want output in a text file to know whether or not your create table statement succeeded or not. DDL triggers can do this on the server, however if you have malformed sql (as in your post) or just want to log out to a text file directly from sqlcmd have a look at the -r parameter

sqlcmd -icreate_employee_table.sql -S. -E -o create_employee_table.log -r1

The file "create_employee_table.log" will contain output like this:

Msg 102, Level 15, State 1, Server name, Line 7
Incorrect syntax near ']'.
Msg 319, Level 15, State 1, Server name, Line 11
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

To print out success messages you'll need to actually check the value of the @.@.error in the script and print out an appropriate message.

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] UNIQUEIDENTIFIER DEFAULT (newid()),
[ModifiedDate] datetime NOT NULL CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

if @.@.error = 0
print 'employee table created sucessfully'