title: bcp command does not write the xml to a folder in the server, but gives no errors
body: <p>I have a stored procedure in SQL Server 2008 which constructs an xml. I need to write this xml to a folder in the same server. The userid I use has execute permissions. However when I run this stored procedure, there are no errors thrown, but it does not write the file in the folder. </p>
  
  <p>Any ideas where I am going wrong?</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[kc_comp_data_sp] 
  AS 
    DECLARE 
      @company_xmlpartO nvarchar(MAX),
      @company_xmlpart nvarchar(MAX),
      @company_xml nvarchar(MAX),
      @xml_beg nvarchar(200),
      @xmlEnd nvarchar(100),
      @xml XML,
      @temp NVARCHAR(255),
      @CompName VARCHAR(255),
      @GivenName  VARCHAR(255),
      @Gender CHAR(1),
      @City VARCHAR(255),
      @State CHAR(2),
      @count int =0,
      @strbcpcmd VARCHAR(500),
      @filename varchar(70)
  
      SET @xml_beg = '&lt;?xml version="1.0" encoding="UTF-16"?&gt;
       &lt;Company xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Company.xsd"&gt;'
      SET @xmlEnd = '&lt;/Company&gt;'
      SET @comp_xmlpartO = '&lt;CompanyData&gt;
          &lt;ID&gt;&lt;/ID&gt;
          &lt;CompanyName&gt;&lt;![CDATA[~fname~]]&gt;&lt;/CompanyName&gt;     
          &lt;GivenName&gt;&lt;![CDATA[~lname~]]&gt;&lt;/GivenName&gt;     
          &lt;Addresses&gt;
              &lt;BillingAddress&gt;
                  &lt;Address1&gt;&lt;/Address1&gt;
                  &lt;Address2&gt;&lt;/Address2&gt;
                  &lt;Address3&gt;&lt;/Address3&gt;
                  &lt;City&gt;&lt;/City&gt;
                  &lt;State&gt;&lt;/State&gt;
                  &lt;ZipCode&gt;&lt;/ZipCode&gt;
                  &lt;Fax1&gt;&lt;/Fax1&gt;
              &lt;/BillingAddress&gt;
            &lt;/Addresses&gt;
        &lt;CompanyData&gt;'
  
  
  set @company_xmlpart = @company_xmlpartO
  
  DECLARE xml_cursor CURSOR FOR
  SELECT CompanyName , GivenName 
  OPEN xml_cursor 
  
  FETCH NEXT FROM xml_cursor into @CompName, @GivennNme 
  
  WHILE @@fetch_status = 0
  BEGIN
  
      SET  @company_xmlpart = REPLACE(@company_xmlpart,N'~fname~',@CompName )
      SET  @customer_xmlpart = REPLACE(@company_xmlpart,N'~lname~',@GivenName  )
      if(@count &gt; 0)
      BEGIN
         SET @company_xml = @company_xml + @company_xmlpart
      END
      else
      BEGIN
          SET @company_xm = @company_xmlpart
      END
  
      SET @count = @count +1
      SET  @company_xmlpart =  @company_xmlpartO
  
     FETCH NEXT FROM xml_cursor into @CompName, @GivenName 
  
  
  
  END
  SET  @company_xml=  @xml_beg +  @company_xml + @xmlEnd
   SET @xml = CAST(@company_xml AS XML)
  
  
   set @fileName = 'D:\Samples\compsample1.xml'
  
   CLOSE xml_cursor 
   DEALLOCATE xml_cursor
  
    set @strbcpcmd = 'bcp  "exec ' + cast(@xml as varchar(max)) + ' " queryout ' + @fileName + ' -S servernmae -U userid -P password -T -w -r -t';
    exec xp_cmdshell @strbcpcmd
  </code></pre>