Create CSV file using xp_cmdshell

• Manie Verster

  SSCertifiable

  Points: 7152

  More actions

  February 10, 2017 at 4:51 am

  #340467

  Good day all,

  I am trying to run a script that will, using xp_cmdshell, create a CSV file. When I did it first I created the file on the same server and it worked famously. Now, however, I was told to place the file on a different server. A folder was created and permissions was given to various uses as well as the SQL Service Account (full control). Now below is my script.
  
declare @sql varchar(8000), @filename varchar(50)
set @filename = replace(replace(replace(convert(varchar(25),GETDATE(),120),'-',''),' ',''),':','')
select @sql = 'bcp "select * from sometable" queryout \\servername\foldername\'+@filename+'.csv -c -t, -T -S ' + @@servername
exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'xp_cmdshell',1;
reconfigure;
exec master..xp_cmdshell @sql
exec sp_configure 'xp_cmdshell',0;
reconfigure;
exec sp_configure 'show advanced options', 0;
reconfigure;
 
  The result:
  
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file


  I then went and mapped the network drive "S:/" and changed my script.
  
select @sql = 'bcp "select * from sometable" queryout S:\foldername\'+@filename+'.csv -c -t, -T -S ' + @@servername


  Same result. I was then told to run the following script.
  
   exec sp_configure 'show advanced options', 1;
    reconfigure;
    exec sp_configure 'xp_cmdshell',1;
    reconfigure;
    exec master..xp_cmdshell 'MKDIR "\\servername\foldername\"'
    exec sp_configure 'xp_cmdshell',0;
    reconfigure;
    exec sp_configure 'show advanced options', 0;
    reconfigure;

  or
  exec master..xp_cmdshell 'MKDIR "S:\foldername\"'
   The result Access denied. I checked and double checked the folder permissions but could not find anything wrong. Can someone please help me? I need to have this running by next week Wednesday. :unsure::unsure::unsure:

  :-PManie Verster
  Developer
  Johannesburg
  South Africa

  I can do all things through Christ who strengthens me. - Holy Bible
  I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  February 10, 2017 at 5:02 am

  #1927720

  Using a mapped drive won't work: the drive is mapped in your user context, but the xp_cmdshell statements run in the context of the SQL Server service account.  Is the folder on the file server actually shared?  What is the path to the folder and  the name of the share (please obfuscate if necessary)?  Did you grant the permissions you mentioned at NTFS and share level?

  John

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  February 10, 2017 at 2:51 pm

  #1927895

  I know this is a completely different approach but check this PS script page: http://www.sqlteam.com/article/fast-csv-import-in-powershell-to-sql-server

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  February 10, 2017 at 3:06 pm

  #1927899

  Step one is to materialize the BCP command contained in the variable and try it from the command prompt to make sure that things are working correctly.  Also be advised that if there are special characters or (especially) spaces in the path or server names, they must be encapsulated in double quotes.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Manie

  SSCarpal Tunnel

  Points: 4568

  More actions

  February 14, 2017 at 12:33 am

  #1928270

  John Mitchell-245523 - Friday, February 10, 2017 5:02 AM

  Using a mapped drive won't work: the drive is mapped in your user context, but the xp_cmdshell statements run in the context of the SQL Server service account.  Is the folder on the file server actually shared?  What is the path to the folder and  the name of the share (please obfuscate if necessary)?  Did you grant the permissions you mentioned at NTFS and share level?

  John

  The path as I gave in my code example in my post is \\servername\foldername\

  Manie Verster
  Developer
  Johannesburg
  South Africa

  I am happy because I choose to be happy.
  I just love my job!!!

• Stuart Davies

  SSCoach

  Points: 19505

  More actions

  February 14, 2017 at 12:56 am

  #1928273

  One thing to test, if you haven't already, is the permissions on the destination folder. Do a "dir  \\servername\foldername\*.* > c:\files.txt" and make sure that you can see files in the output file - I've been caught like this before when you think things are right but  . . . . .

  -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
  There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
  I would never join a club that would allow me as a member - Groucho Marx

• Manie

  SSCarpal Tunnel

  Points: 4568

  More actions

  February 14, 2017 at 1:47 am

  #1928283

  Stuart Davies - Tuesday, February 14, 2017 12:56 AM

  One thing to test, if you haven't already, is the permissions on the destination folder. Do a "dir  \\servername\foldername\*.* > c:\files.txt" and make sure that you can see files in the output file - I've been caught like this before when you think things are right but  . . . . .

  Well, my test looks a bit different and I was getting the Access denied result and hence my post
  
  exec sp_configure 'show advanced options', 1;
  reconfigure;
  exec sp_configure 'xp_cmdshell',1;
  reconfigure;
  exec master..xp_cmdshell 'MKDIR "\\servername\foldername\"'
  exec sp_configure 'xp_cmdshell',0;
  reconfigure;
  exec sp_configure 'show advanced options', 0;
  reconfigure;

  Manie Verster
  Developer
  Johannesburg
  South Africa

  I am happy because I choose to be happy.
  I just love my job!!!

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  February 14, 2017 at 2:21 am

  #1928287

  manie - Tuesday, February 14, 2017 12:33 AM

  John Mitchell-245523 - Friday, February 10, 2017 5:02 AM

  Using a mapped drive won't work: the drive is mapped in your user context, but the xp_cmdshell statements run in the context of the SQL Server service account.  Is the folder on the file server actually shared?  What is the path to the folder and  the name of the share (please obfuscate if necessary)?  Did you grant the permissions you mentioned at NTFS and share level?

  John

  The path as I gave in my code example in my post is \\servername\foldername\

  And the permissions?  What happens if you run this?

  exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'xp_cmdshell',1;
reconfigure;
exec master..xp_cmdshell 'dir \\servername\foldername';
exec master..xp_cmdshell 'echo Hello > Manie.txt';
exec master..xp_cmdshell 'del Manie.txt';
exec sp_configure 'xp_cmdshell',0;
reconfigure;
exec sp_configure 'show advanced options', 0;
reconfigure;

  John

• Manie

  SSCarpal Tunnel

  Points: 4568

  More actions

  February 14, 2017 at 6:28 am

  #1928336

  Joe Torre - Friday, February 10, 2017 2:51 PM

  I know this is a completely different approach but check this PS script page: http://www.sqlteam.com/article/fast-csv-import-in-powershell-to-sql-server

  Thanks Joe, I decided to try this one and it works. Now I have a question. You see, the query I am using to create the CSV file from will not always have data. At the moment the CSV gets created even if there is no data. I would like to put a check in to my script to check for data and if there is none then skip the CSV. My script is below.
  
$ConnectionString = "Data Source=SQLInstance; Database=db; Trusted_Connection=True;";
$streamWriter = New-Object System.IO.StreamWriter "\\servername\someFolder\Testing.csv"
$sqlConn = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlConn
$sqlCmd.CommandText = "SELECT * FROM db..mytable where updated = 0"
$sqlConn.Open();
$reader = $sqlCmd.ExecuteReader();

  # Initialze the array that hold the values
  $array = @()
  for ( $i = 0 ; $i -lt $reader.FieldCount; $i++ )
      { $array += @($i) }
  # just for test
  $streamWriter.Write($reader.Count)
  # Write Header
  $streamWriter.Write($reader.GetName(0))
  for ( $i = 1; $i -lt $reader.FieldCount; $i ++)
  { $streamWriter.Write($("," + $reader.GetName($i))) }

  $streamWriter.WriteLine("") # Close the header line

  while ($reader.Read())
  {
      # get the values;
      $fieldCount = $reader.GetValues($array);

      # add quotes if the values have a comma
      for ($i = 0; $i -lt $array.Length; $i++)
      {
          if ($array[$i].ToString().Contains(","))
          {
              $array[$i] = '"' + $array[$i].ToString() + '"';
          }
      }

      $newRow = [string]::Join(",", $array);

      $streamWriter.WriteLine($newRow)
  }
  $reader.Close();
  $sqlConn.Close();
  $streamWriter.Close();

  Please can some one help. I believe it must be done on $reader.

  Manie Verster
  Developer
  Johannesburg
  South Africa

  I am happy because I choose to be happy.
  I just love my job!!!