Input string was not in a correct format.Couldn't store

• Tuesday, December 18, 2012 7:28 PM

   

   
  

  0

  I am getting the error listed above for all  my dummy columns . It's expected int32 for some reason. I have a view set up in sql server 2008 . I am using the code listed in this link to export to csv file. http://prosqlserver.com/blog/2011/07/02/sqlclr-procedure-to-export-query-sp-results-into-csv/

  I created some dummy colums in the view by specifing NULL  as columnName. it works fine when I select records from within sql studio but when running the stored procedure listed in the link, it does not like the dummy columns. Here is code below in case link doesn't work. also, when I get the error and I try to run the stored procedure again, it says file is in use. that's the fileSpec file I pass in. How do I avoid that, and get sql server to release the file if an exception occurs?

  //------------------------------------------------------------------------------
  // <copyright file="CSSqlStoredProcedure.cs" company="Microsoft">
  //     Copyright (c) Microsoft Corporation.  All rights reserved.
  // </copyright>
  //------------------------------------------------------------------------------
  using System;
  using System.Data;
  using System.Data.SqlClient;
  using System.Data.SqlTypes;
  using Microsoft.SqlServer.Server;
  using System.IO;
  using System.Text;
  
  public partial class StoredProcedures
  {
      [Microsoft.SqlServer.Server.SqlProcedure]
      public static void Export_To_CSV_File(SqlString Query, SqlString FileSpec, SqlBoolean OverwriteIfExists, SqlBoolean HeaderRow)
      {
          /***************** PREMILINARY CHECKS ***************************/
          //check for empty parameters
          if (Query.Value == string.Empty)
              throw new Exception("Query is missing.");
  
          // parse filespec
          FileInfo fi = new FileInfo(FileSpec.Value);
          string filePath = fi.DirectoryName;
          string fileName = fi.Name;
  
          if (filePath == string.Empty)
              throw new Exception("Missing file path location.");
  
          if (fileName == string.Empty)
              throw new Exception("Missing name of file.");
  
          // does excel spreadsheet already exist?
          if (fi.Exists == true && OverwriteIfExists.IsFalse)
              throw new Exception("File already exists, and OverwriteIfExists was specified as false.");
  
          /***************** Query the DB and exporting the data to file ***************************/
          DataSet exportData = new DataSet();
  
          using (SqlConnection conn = new SqlConnection("server=xxxx;database=xxx;User Id=xx;Password=xxxxx"))
          {
  
              SqlDataAdapter da = new SqlDataAdapter(Query.ToString(), conn);
  
              DataSet ds = new DataSet();
  
              da.Fill(ds, "xxx");
  
              DataTable table = ds.Tables["xxx"];
  
              StringBuilder sb = new StringBuilder();
  
              if (fi.Exists == true && OverwriteIfExists == true)
              {
                  fi.Delete();
                  SqlContext.Pipe.Send("file deleted");
                  
              }
  
              StreamWriter sw = new StreamWriter(FileSpec.ToString(), false);
  
              if (HeaderRow)
              {
                  //write the headers.
                  for (int colCount = 0;
                       colCount < table.Columns.Count; colCount++)
                  {
                      sb.Append(table.Columns[colCount].ColumnName);
                      if (colCount != table.Columns.Count - 1)
                      {
                          sb.Append(",");
                      }
                      else
                      {
                          sb.AppendLine();
                      }
                  }
              }
              sw.Write(sb.ToString());
              //sw.Write(sw.NewLine);
  
              // Write all the rows.
              for (int rowCount = 0;
                   rowCount < table.Rows.Count; rowCount++)
              {
                  StringBuilder sb_row = new StringBuilder();
  
                  for (int colCount = 0;
                       colCount < table.Columns.Count; colCount++)
                  {
                      
                      //remove extra white spaces
                      table.Rows[rowCount][colCount] = table.Rows[rowCount][colCount].ToString().Trim();
  
                      sb_row.AppendFormat("\"{0}\"",table.Rows[rowCount][colCount]);
                      if (colCount != table.Columns.Count - 1)
                      {
                          
                          sb_row.Append(",");
                      }
                  }
                  if (rowCount != table.Rows.Count - 1)
                  {
                      sb_row.AppendLine();
                  }
  
                  sw.Write(sb_row.ToString());
                  //sw.Write(sw.NewLine);
              }
  
              sw.Close();
  
          }
  
      }
  };
  

  
  

  • Reply
  • Quote