is it possible to add line from Database to text file in the server ?

Question

hi

i have sql-server 2008 database that in the main server.

i have 10 terminals that connect to this server and work against the database.

i need that every new inserting to database through any terminal - in the server this

line will inserting to text file (every line)

can i do it ?

thank's in advance

Answer 1

You can write in to file using xp_cmdshell as shown below :

exec master..xp_cmdshell 'echo hello > c:\file.txt'

Answer 2

I would recommend using an after insert trigger to do this, there are some good examples at:

http://msdn.microsoft.com/en-us/library/ms189799.aspx

If you create your trigger on the tables where the data is being inserted, it will be triggered every time an insert from any terminal happens. Inside the trigger you can write out the details of the insert to the log file, perhaps using the pranay_stacker's advice.

As an example of something I just whipped up quickly:

create TRIGGER dbo.productTrigger
    on dbo.Product
    after insert
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @data nvarchar(255);

    DECLARE dataCursor CURSOR FAST_FORWARD FOR 
        select ProductCode from inserted;

    OPEN dataCursor
    FETCH NEXT FROM dataCursor
    INTO @data

    WHILE @@FETCH_STATUS = 0 BEGIN
        declare @cmd sysname;
        set @cmd = 'echo ' + @data + ' >> c:\test\var_out2.txt';
        exec master..xp_cmdshell @cmd, NO_OUTPUT;

        FETCH NEXT FROM dataCursor
        INTO @data
    END

    CLOSE dataCursor;
    DEALLOCATE dataCursor;
END

You would of course have to modify the database and table names, etc.

The other thing to keep in mind is that you need to enable xp_cmdshell as by default SQL server has it switched of for security reasons, this is done via:

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE
go

And finally the last thing I noticed is that when I had the echo to c:\var_out2.txt it was giving me an "Access Denied" however when I changed it to c:\test\var_out2.txt it worked fine. So keep that in mind too.