Generate LOG file

[vikasprajapati]

Hi,

I have written one procedure to update two table. There is 2 update query. Now i want to capture the result into text file i.e. once query completes one file will generate automatically at specified location and it contains status like Current Date, Total no. of records updated etc.

Please help me

Vikas

[almaz]

Creating files from within SQL Server generally is not a best practice.
Instead, you may create and populate a log table, something like this:

create table tbl_log (log_date datetime default (getdate()), table_name sysname, record_count int)
 
create table working_table (id int identity (1,1), val int default (0))
go
create trigger trg_working_table_update 
    on working_table 
after update
as
    insert tbl_log (table_name, record_count) values ('working_table', @@rowcount)
go
 
insert working_table default values
insert working_table default values
insert working_table default values
insert working_table default values
 
update working_table 
set val = id 
where id<4
 
select * from tbl_log