title: Running SQL Server stored procedure from Excel
body: <p>I've been using this <a href="http://mikesknowledgebase.com/pages/SQLServer/RunStoredProcedureFromExcel.htm" rel="nofollow">http://mikesknowledgebase.com/pages/SQLServer/RunStoredProcedureFromExcel.htm</a> as a framework to try and run a stored procedure from VBA in order to pull data straight into an Excel sheet. So far, my problem is with line that's supposed to connect to SQL Server. </p>
  
  <p>I currently have this:</p>
  
  <pre><code>con.Open "Provider=SQLOLEDB;Data Source=domain\serverName;Initial Catalog=databaseName;Integrated Security=SSPI;Trusted_Connection=Yes;"
  </code></pre>
  
  <p>but that gives me an error that says it doesn't exist, or access is denied. I've been trying to find a better example that actually shows how I would connect to SQL Server, but haven't been able to find anything. Am I missing something obvious? Is there another way to connect to a SQL Server to run a stored procedure?</p>
  
  <p>Beyond that, from looking at the code I'm not sure how the parameters for the stored procedure ever get read by the stored procedure. For example, I have two parameters, <code>@endDate</code> and <code>@beginDate</code>. </p>
  
  <p>I have this:</p>
  
  <pre><code>cmd.Parameters.Append cmd.CreateParameter("@beginDate", adDate, adParamInput, 10, Range("J3").Text)
  cmd.Parameters.Append cmd.CreateParameter("@endDate", adDate, adParamInput, 10, Range("J4").Text)
  </code></pre>
  
  <p>but they're not used anywhere else. Is that all that's needed to connect the input from excel and the parameters in the stored procedure? I'm just trying to understand how that works.</p>
  
  <p>Edit: my syntax was wrong for the database, needed more information in there. My new problem is that when creating the parameters, I get an error </p>
  
  <blockquote>
    <p>Application uses a value of the wrong type for the current operation.</p>
  </blockquote>
  
  <p>I'm thinking that's because I'm trying to turn text into a date, where it might be better just to store it as a <code>varchar</code>? They both need to match up with a datetime column in SQL Server.</p>
  
  <p>Edit2: Apparently I was wrong, because the parameters work now. Not sure why it wasn't, but it does now. </p>