Sql-server – Connection is Using Properly or not in Net Use via SQL Procedure

sql serverstored-procedureswindowsxp-cmdshell

am doing an operation of creating a file and writing in a SAN SAMBA where it will be done using the cmd_shell via SQL Procedure. It's creating and writing it successfully but while am calling that procedure 2 two times at the same time am getting the issue as

The local device name is already in use.

Is there any options while using this net use command I have 15 different procedures each will be having this type of behavior.

Note: Every Procedure is given a Drive name differently.

EXEC xp_cmdshell 'net use r: \10.44.11.44\dpsshare Password
/user:Username /persistent:no'

Am also deleting the Network Connection at the end of the Procedure.

Best Answer

Your NET USE command tells Windows - not just SQL Server - to map a network drive to a letter. When it runs, it makes that drive letter available to the SQL Server process, where it can be used for all connections.

A lot of things in SQL Server are per-connection - like creating a temp table - but what you're doing, mapping a network drive to a letter, is for the entire SQL Server process.

Your first thought might be, "Oh, so I need to check to see if the folder is mapped first - like use xp_dirtree to get the directory contents. If the folder is already mapped, then I can skip that NET USE step."

Unfortunately, that won't work either.

Because you mentioned at the end of your procedures, you're un-mapping those drives. That means you can run into this situation:

  1. Instance A of the stored proc starts, checks to see if the drive is mapped. It isn't, so it maps the drive, and starts working.
  2. Instance B of the stored proc starts, checks to see if the drive is mapped. It IS already mapped, so it just starts working without creating the drive mapping.
  3. Instance A finishes up, and unmaps the drive.
  4. Instance B was still running - but it now can no longer write to the drive. It fails.

So how do you work around it? Don't map drive letters. Access folders by their UNC path instead, like \\10.44.11.44\dpsshare. Grant the necessary permissions to the SQL Server's account rather than hard-coding usernames and passwords inside your application code. (Security is a much bigger discussion too - I get nervous when I see you exposing passwords in your code, and I also get nervous about SQL Server being able to write to a remote file share. You're in an area where application servers are a much better fit.)

Related Question