0

I am beginner in SQL Server. When I am trying to run code:

'EXEC xp_cmdshell 'bcp "SELECT * FROM sysfiles" queryout "D:\sysobjects.txt" -T -c -t'

I am getting this error:

[Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file NULL
Peter Wishart
  • 11,600
  • 1
  • 26
  • 45
Prasana Ravikumar
  • 1
  • 3

1 Answers1

0

When you run xp_cmdshell, SQL server will start a new process. The user which this process runs as can vary depending on the user configured to run SQL server and whether the SQL agent is enabled.

To find which user the command runs as (I'm assuming at least Windows 7 on the server), execute:

EXEC xp_cmdshell 'whoami & cd & path'

The first line will be the user who is running xp_cmdshell, the second shows what the proces' working directory is and the rest will list the folders in the path.

To check effective permissions, use Explorer to navigate to:

  • "d:\" Properies
  • Security
  • Advanced
  • Effective permissions
  • 'Select' and enter the name returned from whoami

E.g. on my system, whoami returned nt authority\network service which is NETWORK SERVICE for the effective security dialog.

The path output will include a 'SQL server\xxx\binn' folder, check that the first of these matches the version of SQL your connected server is running - otherwise you may have to specify the path explicitly.

Peter Wishart
  • 11,600
  • 1
  • 26
  • 45