eseeweb (4/30/2014)
hiI just have an update that we don't need to delete the record from the table as long as every time a record inserted can be write to a text file for the other side to consume. So, i did a quick test like this and have access denied issue.
exec master.xp_cmdshell 'echo hello > c:\file.txt' which try to write to the c drive on the sql server.
but if i quality it as
exec master.xp_cmdshell 'echo hello > \\myPC\c$\file.txt'
The issue is when i log on to sql server, i can add/write/mod files from c drive. Any reason? How to i use "execute as" statement?
thx, Ted.
Does your command shell proxy account have adequate privileges set on the destination folder and does it have network privileges (the built in local service account will have local admin rights but no network privileges - other accounts including the network service account will need permissions set)?
Have you considered creating an SSIS package to poll the table write to the table and tidy up for this rather than a trigger? It would be easier to handle error conditions.