SQL SERVER – xp_cmdshell and Net Use ERROR: The Local Device Name is Already in Use

People generally ask me; how do I manage to find a blog topic for every day blogging? And my answer is – “Connect yourself to the ground, try to help people with the basics, have open eyes & ears and promise yourself to learn one new thing every day”. If one can achieve these, every day blogging is easier. As a part of my consulting, I was helping a client to run some scripts and while testing, I learned something new. In this blog, we would learn about cause and solution of the error, the local device name is already in use.

So, while running some script for testing, I encountered an interesting error which I have not seen earlier.

Here is the script using which I am mapping a share on the local machine as Y drive.

exec xp_cmdshell 'net use Y: \\127.0.0.1\Shared aVeRyStR0nGP@s$w0rd@123 /USER:Administrator /PERSISTENT:yes'
go

output
—————————————–
System error 85 has occurred.
NULL
The local device name is already in use.
NULL
NULL

When I checked My computer, I didn’t see Y: drive mapped there.

WORKAROUND

While searching on the internet, I learned that drive mapped with in SQL (via net use command) would not be visible in the OS as its running as a service and it doesn’t have it desktop. I have not tried this, but I think if I login with service account to the operating system, we should be able to see the drive in OS also. Again, I have not tested this so if you find answer to this question then please share via comments.

To confirm this, we can use below command.

exec xp_cmdshell 'net use'

and as mentioned in error message, Y is already in use.

To delete Y drive, we need to use below command.

exec xp_cmdshell 'net use Y: /delete'

Once the command is completed, we were able to map the drive again.

Hope this blog would help someone to fix the error involving net use and xp_cmdshell.

Reference : Pinal Dave (https://blog.sqlauthority.com)