/* create a function that inserts to a table via xp_cmdshell */

CREATE FUNCTION dbo.fn_write_to_table()

RETURNS int

AS

BEGIN

DECLARE @sql varchar(MAX), @cmd varchar(4000)

SELECT @sql = 'insert into mytesttable select getdate()'

SELECT @cmd = 'sqlcmd -S ' + @@servername + ' -d ' + db_name() + ' -Q "' + @sql + '"'

EXEC master..xp_cmdshell @cmd, 'no_output'

RETURN 1

END

----------------------------------------------------------------------------------------------------------

/* create the log table */

CREATE TABLE dbo.mytesttable(

   mydate datetime NOT NULL

)

----------------------------------------------------------------------------------------------------------

/* create a view that guarantees the function will be called only once (rather than for every row) */

CREATE view dbo.vwWriteToTable

as

select * from sys.all_objects cross join

(select 1 as x where dbo.fn_write_to_table() = 1) a

----------------------------------------------------------------------------------------------------------

No Comments

You must log on to comment.