| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2009-04-13 : 10:35:26
|
wondering if anyone can help me get this filecopy to work, or give me a good alternative.declare @sql varchar(100)declare @xordernum varchar(100)=3736declare @xusername varchar(100)='albert@natimark.com'set @sql = 'cscript C:\orders\FTP_Copy.vbs'+' "order'+@xordernum+'.zip" "'+@xusername+'"'EXEC master..xp_cmdshell @sql output:Microsoft (R) Windows Script Host Version 5.6Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.NULLC:\orders\FTP_Copy.vbs(10, 5) Microsoft VBScript runtime error: Permission deniedNULLNULL vbs code:' VBScript source code strFilename = WScript.Arguments.Item(0) strUsername = WScript.Arguments.Item(1) Dim FSO Set FSO = CreateObject("Scripting.FileSystemObject")'Copies file to FTP Folder sourcefile = "c:\TempDIR\"+strFilename destfile = "\\Server1\FTP\"+strUsername+"\"+strFilename fso.copyfile sourcefile,destfile |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-04-13 : 10:59:07
|
| do this and show what @sql containtPRINT @sql from within SSMS<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2009-04-13 : 11:12:07
|
| i get:cscript C:\TempDIR\FTP_Copy.vbs "order3736.zip" "albert@natimark.com"funny thing is, if i run it from command prompt on the sql server, it works perfectly... |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-04-13 : 12:01:19
|
| aha running it on the sql server works. that seems like some security issue. under what security is it running when you have the problem. Can you check it out using SQL profiler or some other tool?<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2009-04-13 : 12:04:39
|
| well, i've tried sa login, as well as my windows auth. i have domain admin credentials.. :(i am not that great @ the profiler, so if you can help me set up a trace that would be helpful, one of my co-workers is going to setup auditing on the folder we're trying to copy to, to see if/why it's not giving us access... |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-04-13 : 12:56:59
|
| could it be that sa does not have rights to that folder. You might need another proxy user that has rights to that folder I am thinking. Ok get the tsql that is problematic ready to run first. Once you run profiler you will live by it, it is an awesome tool. Under tools in your SSMS Select SQL Server Profiler. Once it comes up, Choose File | New Trace or click New Trace Icon, first one on top right. Select the server on which you want to be doing trace and selet connect. Give it a trace name, whatever name, then I would select the Save to table, then select preferably a non production server and click connect. Give the Destination table a meaningful name. Then in the Event Select tab, leave everything as default or select any of the events your little tool might fire up. Then once you click run, go back to your ready to run your filecopy and run it. Run it a few times from front end and from SSMS. Then keep your eye on the trace as you run it manually or via front end a few times over and over. THen go back to trace stop it. and do a Select * From TraceTableName and see what you can detect. It is very detailed and niformative. Might even find the problem in there.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2009-04-13 : 23:30:51
|
| i figured it out, it was killing me because every where i checked, i couldnt find in the logs where/why access was being denied... i finally suspected it was the server that sql runs under. i changed it to run off my domain creds (i'm the enterprise admin) and that fixed it.Thanks alot, i appreciate the help, and hopefully this'll help someone. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-04-14 : 10:02:16
|
| well glad it worked but how will it run once you deploy it to users or is this thing being run only by you. kind of scary to deploy something running under enterprise admin rights.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2009-04-14 : 10:27:45
|
| lol it's only on a test server now, i plan on creating an MSSQL account in AD for it to run under in production. question though, what kind of group or rights should i give mssql in order for it to run properly? (on the production side. or i guess a more proper question would be what's the best practice for running sql under a user account credential?) |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-04-14 : 11:56:30
|
| :O) ok. best practice is to do it "right" even on test server, that way you do not run into odd behaviours as you did. and deployment to production will be seamless.there are different ways to skin that cat: a group, a fixed AD user or a local SQL user acting as a proxy. Depending on your company size structure and how much red tape it takes to have domain admins create you a user (unless you da man) proxy is much nicer. But it is more complicated than that, you will have to read up some more.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2009-04-14 : 12:04:12
|
| JOY! thanks, atleast i know what caused the problem, now i'll have to read up on fixing it right, instead of with duct tape...Thanks! |
|
|
|
|
help running cscript from t-sql