title: Error on passing variable on the function xp_dirtree
body: <p>I am trying to write a stored procedure that gathers the names of all of the files located in a specific archive and stores it in a temp table. When I write it and hardcode the path as a param in the function xp_dirtree, it yields the results I wanted. </p>
  
  <pre><code>IF ( SELECT Object_id('tempdb..#archf_names')) IS NOT NULL
  BEGIN
    DROP TABLE #archf_names
  END
  
  --table to hold file information
  CREATE TABLE #archf_names(filename VARCHAR(MAX), depth INT, filetype INT NOT NULL)     
  
  INSERT #archf_names (filename, depth, filetype)
  EXEC MASTER.sys.xp_dirtree '\\sqlvm82\ImportsExports\Archive\Archive', 1, 1
  
  SELECT * FROM #archf_names
  WHERE filetype &lt;&gt; 0
  </code></pre>
  
  <p>When I try to pass in a variable of type VARCHAR that has been set to equal the same path that was hardcoded above, I get this error "
  A severe error occurred on the current command.  The results, if any, should be discarded."</p>
  
  <pre><code>DECLARE @path  AS VARCHAR(MAX) --Path to folder containing archive
  
  SET @path = 'C:\\sqlvm82\ImportsExports\Archive\Archive'
  
  IF ( SELECT Object_id('tempdb..#archf_names')) IS NOT NULL
  BEGIN
    DROP TABLE #archf_names
  END
  
  IF ( SELECT Object_id('tempdb..#sh_out')) IS NOT NULL
  BEGIN
    DROP TABLE #sh_out
  END
  
  CREATE TABLE #sh_out(line VARCHAR(MAX)) -- table for xp_cmdshell output
  CREATE TABLE #archf_names(filename VARCHAR(MAX) NOT NULL, depth INT NOT NULL, filetype INT NOT NULL) --table to hold file information
  
  INSERT #archf_names (filename, depth, filetype)
  EXEC MASTER.sys.xp_dirtree @path, 1, 1
  SELECT * FROM #archf_names
  WHERE filetype &lt;&gt; 0
  </code></pre>
  
  <p>There are some other functionalities that are a part of this stored proc but the snippets above are the ones I'm having a problem with. I hope I explained myself clearly enough. </p>