March 30, 2009 at 1:00 pm
Hello to all the gurus
I have a reports project that gets data from another server in text file format and inputs the data into a database, daily. I have the application working on Sql Server 2005 x86 (Windows 2003 x86) and it has been working for about 5 months. I am now in the process of transfering this project to a new server, where I have installed WIndows 2003 x64 and Sql Server 2008 x64. Every function/procedure appears to work well except for this procedure that does the FTP. It fails with the error
> Rename Temp File calloc:I/O Error
I tried the ftp directly from the dos prompt and the file is successfully transfered, but I need it to work in the procedure so that this process can be automated.
I appreciate any assistance you can offer.
March 30, 2009 at 1:11 pm
I can't see the procedure from here. Could you please show it to us?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2009 at 12:23 pm
Actually, I had a similar problem, but just not on the exact environment,
Our stored proc code first builds a ftpcommand text file with statments like:
open blah.blah.com
MyLoginUser
loginpassword
CD /blah/
Get abc.123
bye
Then the stored proc builds a .bat file like:
CD D:\script\dir
ftp -i -s:D:\script\dir\FTPCmds.txt
Then the stored proc executes the bat file to ftp files to the local directory by using xm_cmdshell like:
EXEC master..xp_cmdshell 'getfiles.bat'
This has always worked with no issues until recently, when we upgraded to Windows server 2003 SP2. Now we have problems with the FTP and we get this error in the log:
> GetTempFileName calloc:Unknown error number
Running the .bat file manually from a dos prompt gives no issues and it does indeed ftp the files without issues.
any ideas ?
August 12, 2009 at 12:51 pm
Try executing this command yourself manually from within SQL Server and see what you get:
EXEC master..xp_cmdshell 'getfiles.bat'
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2009 at 1:06 pm
It works from my account, my account is sysadmin. But the stored proc is run through SQL Agent job and is owned by a user with no sysadmin........... would that be the issue ? non sysadmin user through SQL Agent ?
August 12, 2009 at 1:38 pm
clarence.cheng (8/12/2009)
It works from my account, my account is sysadmin. But the stored proc is run through SQL Agent job and is owned by a user with no sysadmin........... would that be the issue ? non sysadmin user through SQL Agent ?
Yes, that could be. You may need to change the Agent Job to use a CmdExec type step instead (you can specify the username) and you will almost certainly need a sysadmin or SA to own the job.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2009 at 1:45 pm
Strange though, this setup has been working for like 2 yrs at least, the only change I can indentify is that we did a upgrade to windows server 2003 SP2 recently. It's hard to pin point cause this job is used only monthly. And all was fine the previous month.
August 12, 2009 at 2:14 pm
clarence.cheng (8/12/2009)
Strange though, this setup has been working for like 2 yrs at least, the only change I can indentify is that we did a upgrade to windows server 2003 SP2 recently. It's hard to pin point cause this job is used only monthly. And all was fine the previous month.
I see stuff like this break on my customers systems every time they apply a new SP. Microsoft has been making a concerted effort for years to to tighten up the holes that it has previously left open in the network security space. And that naturally causes problems like this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2009 at 1:42 pm
I have a similar issue. I'm curious if you found a workaround without elevating privileges. This is against our segregation of duties configuration. I too have a system with Windows Server 2003 Sp2.
September 1, 2009 at 11:20 am
Okay. Here was the answer to my scenario. I run my agent with an admin account. The proxy account I set had to have access to the personal TEMP folder under the c:\Documents and Settings\\Local Settings\Temp
. The reason is that FTP creates a temporary file but that file is created under the pseraon lTEMP folder of the account under which the agent runs.
Woot.
I did see that there are similarly cryptic errors with Win 2003 SP1. I was running SP2 and finally figured it out after reading http://support.microsoft.com/?kbid=269074.
I hope this helps somebody else.
Thanks
Note
September 1, 2009 at 11:31 am
Nope I wasn't able to find a solution without raising permissions. I ended up making the proxy account used to run cmdshell to be a server sysadmin. But I guess your stated solution makes sense !! If ftp needs to create a temp file in that folder, it would fail. I couldn't figure out why the ftp works when I run it as a sysadmin, but not work otherwise !! Thanks for unboggling my mind !!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply