Calling a SQL server stored procedure from VBA

[Awaiz]

Hi I am trying to call a stored procedure from VBA, my code is as below:



' SQL Connection

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command

Set conn = New ADODB.Connection

conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=REVGOV;Data Source=pdlfslab10"


''''''''''''''''''Some of my parameters from excel goes here which I use as variables in the command text''''''''''''''


conn.Open


Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "exec MyProc '& FY &', '& Month &', '& VerticalSrNo &','& Vertical &','& LocationSrNo &','& Location &', '& UserName &'"


cmd.Execute
conn.Close

Set conn = Nothing
Set cmd = Nothing



End Sub




But I am getting an error "Syntax Error or Access Violation"


Help Needed!!!!!

 

[Asala42]

Your command text is evaluating to this:
exec MyProc '& FY &', '& Month &', '& VerticalSrNo &','& Vertical &','& LocationSrNo &','& Location &', '& UserName &'

Did you intend to leave the & symbols within the single quotes?


I have some similar macros, where VBA calls on a SQL stored procedure. If your having trouble feel free to modify the code below (substitute the server name, db name, sheet references, variables - the example only has 3 variables getting passed, etc). If you havn't already done so you'll need to enable a MS ActiveX Object library under VBA Tools->preferences.

Sub exportHostData()
       
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Set cn = New ADODB.Connection
    cn.CommandTimeout = 120

'clear old data
    Worksheets("Data").Range("A2:DM50000").ClearContents
    
    
    cn.Open "Provider=SQLOLEDB;Data Source=servername;" & _
     "Initial Catalog=databasename;Integrated Security = SSPI"
     
     ' Create a recordset object.
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset

     Dim Company As Variant
     Dim Start_Date As Date
     Dim End_Date As Date
          
     Company = Worksheets("Company Scorecard").Range("B2").Value
     Start_Date = Worksheets("Company Scorecard").Range("Q2").Value
     End_Date = Worksheets("Company Scorecard").Range("Q4").Value + 1
              
     With rsPubs
     .ActiveConnection = cn
      .Open "exec spg_CompanyScorecard_HostUptimes @Company ='" & Company & "', @Start_Date ='" & Start_Date & "', @End_Date ='" & End_Date & "'"
      Sheet2.Range("C2").CopyFromRecordset rsPubs
      .Close
     End With

    Set rsPubs = Nothing
    Set cnPubs = Nothing
    
 
End Sub