December 7, 2007 at 4:50 pm
try debug the location u r trying to access with xp_cmdshell dir command....
master..xp-xmdshell 'dir '
maybe u need to map a network location before accessing it...if u r not using windows authentication....
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 9, 2007 at 9:40 am
Thank you for your suggestions. I have tried using Windows Authentication (remoted into the server) and I've tried mixed authentication (connecting from my dev machine). Neither works. I've tried sharing the directory and using UNC path (with share and permission rights set to everyone). I've tried checking the permissions on the file itself. I'm truely stumped. I have not tried checking the dir with xpcmdshell... I'll try that in the morning. Keep in mind that this all worked prior to installing sql server sp2 and the 2 critical updates on windows server 2003.
December 10, 2007 at 8:13 am
Windows Authentication
master..xp_cmdshell 'dir C:\MES\Interface\SPECS\Archive\'
Volume in drive C has no label.
Volume Serial Number is 6C7B-3152
NULL
Directory of C:\MES\Interface\SPECS\Archive
NULL
12/10/2007 08:13 AM .
12/10/2007 08:13 AM ..
08/24/2007 12:37 PM 18,944 P09_CF_SPECS.xls
12/07/2007 08:58 AM 28,672 SPEC-CMF-P09-CF-20071204-160123.XLS
2 File(s) 47,616 bytes
2 Dir(s) 15,982,476,800 bytes free
NULL
master..xp_cmdshell 'dir \\s02devmes\MES\Interface\SPECS\Archive\'
Volume in drive \\s02devmes\MES has no label.
Volume Serial Number is 6C7B-3152
NULL
Directory of \\s02devmes\MES\Interface\SPECS\Archive
NULL
12/10/2007 08:13 AM .
12/10/2007 08:13 AM ..
08/24/2007 12:37 PM 18,944 P09_CF_SPECS.xls
12/07/2007 08:58 AM 28,672 SPEC-CMF-P09-CF-20071204-160123.XLS
2 File(s) 47,616 bytes
2 Dir(s) 15,982,427,648 bytes free
NULL
Mixed Authentication
master..xp_cmdshell 'dir C:\MES\Interface\SPECS\Archive\'
Volume in drive C has no label.
Volume Serial Number is 6C7B-3152
NULL
Directory of C:\MES\Interface\SPECS\Archive
NULL
12/10/2007 08:13 AM .
12/10/2007 08:13 AM ..
08/24/2007 12:37 PM 18,944 P09_CF_SPECS.xls
12/07/2007 08:58 AM 28,672 SPEC-CMF-P09-CF-20071204-160123.XLS
2 File(s) 47,616 bytes
2 Dir(s) 15,978,470,400 bytes free
NULL
master..xp_cmdshell 'dir \\s02devmes\MES\Interface\SPECS\Archive\'
Volume in drive \\s02devmes\MES has no label.
Volume Serial Number is 6C7B-3152
NULL
Directory of \\s02devmes\MES\Interface\SPECS\Archive
NULL
12/10/2007 08:13 AM .
12/10/2007 08:13 AM ..
08/24/2007 12:37 PM 18,944 P09_CF_SPECS.xls
12/07/2007 08:58 AM 28,672 SPEC-CMF-P09-CF-20071204-160123.XLS
2 File(s) 47,616 bytes
2 Dir(s) 15,978,470,400 bytes free
NULL
I'm beginning to think that Microsoft has hosed something since I've posted this on this forum as well as on experts-exchange and no one has a solution/suggestion/anything...
I know I can do this with a dtx package. Hardcoded path/filename works.
December 10, 2007 at 11:22 am
it seems to be more of a basic issue rather than microsoft bug....
one idea is basic file is not available/accesible on the target or its a authentication issue...
all i can guess is its a path issue...try to see if u delete file from query analyzer and test with some sample code with sample excel file available on internet to create linked server....
right click on linked server and in properties check what is the authentication method selected...try changing it to other options and see
there was something like NTLM authentication setting is there when u do gpedit.msc which does clears network access as NTLM security or something else... have a look at this link...http://support.microsoft.com/kb/823659
give it a try and see....
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply