3

I am trying to execute below script to Network a Drive

use master
go
exec sp_configure 'show advanced options', 1;
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1;
go
reconfigure
go

EXEC XP_CMDSHELL 'net use Z: \\RemoteServerName\SharedName'
EXEC XP_CMDSHELL 'Dir Z:'

Is there any script that I can use to connect the network drive in long term

  • What are you trying to achieve? You can use UNC paths without resorting to xp_cmdshell and net use. – vonPryz Sep 4 '15 at 5:41
  • 1
    Why are you doing this in SQL Server? – Aaron Bertrand Sep 4 '15 at 12:00
1

I agree that you could use \\\server\share\Whatever without needing to map a drive at all (so UNC path only) but the security context running the T-SQL will need appropriate permissions to the SHARE and the NTFS of the full path to ensure it can do whatever commands you are running.

You could use PUSHD and POPD as:

PUSHD 
\\server\share 
Command \subfolder1\*.txt 
POPD

You could also use:

IF NOT EXIST Z: NET USE Z: \\server\share
command z:\folder\files.txt

Or perhaps something like the below with plugging in your variables:

DECLARE @folder AS VARCHAR(1000) = '\\server\share\folder1\folder2\finalfolder\'

DECLARE @command VARCHAR(500) = 'DIR /B "' + @folder + '*.xml"'
DECLARE @file VARCHAR(100)
DECLARE @filesinafolder TABLE (filenameswithfolder VARCHAR(500))
DECLARE @sql NVARCHAR(4000)

-- create global temp table
IF OBJECT_ID('tempdb..##globalname') IS NOT NULL
DROP TABLE ##globalname

CREATE TABLE ##globalname (
StepResultID VARCHAR(50)
,StartTime VARCHAR(50)
,StopTime VARCHAR(50)
,ReturnCode INT
)

INSERT INTO @filesinafolder
EXEC master..xp_cmdshell @command

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.