Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

trigger to call a program to write a text file onto a folder in the server Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 12:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:49 AM
Points: 7, Visits: 34
hi
I 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.


Post #1566537
Posted Wednesday, April 30, 2014 12:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:58 PM
Points: 36,794, Visits: 31,253
eseeweb (4/30/2014)
hi
I 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.




SQL Server needs to have the privs to write to whatever UNC you're using. I STRONGLY recommend that you NEVER have SQL Server writing to UNCs that are desktop servers. I can see it writing to company shares on a dedicated share box but not to desktop PCs.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1566561
Posted Wednesday, April 30, 2014 1:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:13 PM
Points: 33,100, Visits: 15,210
Jeff Moden (4/30/2014)


SQL Server needs to have the privs to write to whatever UNC you're using. I STRONGLY recommend that you NEVER have SQL Server writing to UNCs that are desktop servers. I can see it writing to company shares on a dedicated share box but not to desktop PCs.


+1







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1566586
Posted Thursday, May 8, 2014 7:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:53 AM
Points: 207, Visits: 823
eseeweb (4/30/2014)
hi
I 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.
Post #1568893
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse