All,
We try to extract data from a SQL Server 2008 R2 database using a stored procedure into an Excel 2010 workbook. The parameters (client code and end date) are populated by the client at runtime, and used as parameters for the stored procedure.
The problem is following.
When we send the date parameter value as a date, it does not have the quotes around it, and SQL Server cannot parse it. (e.g. 2012-08-29)
When we send the date parameter value as a date and add quotes ("'") around the date, it is no longer seen as a date but as a string, and VBA gives an error (e.g. '2012-08-09')
When we change type to string, the stored procedure does not accept the value, as it is a string, not a date.
Does anyone have expericence with parsing a date parameter to a SQL server stored procedure?
*** VBA sniplet of the code ***
'Execute the stored procedure once & store the output on the Main Data Tab.</SPAN></SPAN>
Set objRs = New ADODB.Recordset</SPAN></SPAN>
Set objCmd = New ADODB.Command</SPAN></SPAN>
With objCmd</SPAN></SPAN>
.ActiveConnection = objDBConn</SPAN></SPAN>
.CommandType = adCmdStoredProc</SPAN></SPAN>
.CommandText = "XXX" ' Stored procedure</SPAN></SPAN>
'Return value parameter to check SP executed sucessfuly of not</SPAN></SPAN>
.Parameters.Append .CreateParameter("@RETURNVALUE", adInteger, adParamReturnValue)</SPAN></SPAN>
' Actual Parameters</SPAN></SPAN>
.Parameters.Append .CreateParameter("@ClientCode", adVarChar, adParamInput, 2400)</SPAN></SPAN>
.Parameters.Append .CreateParameter("@EndDate", adDate, adParamInput, 12)</SPAN></SPAN>
'Parameter Values</SPAN></SPAN>
.Parameters("@ClientCode").Value = "'" & ClientCode & "'"</SPAN></SPAN>
.Parameters("@EndDate").Value = CDate(strEndDate)</SPAN></SPAN>
End With</SPAN></SPAN>
Set objRs = objCmd.Execute()</SPAN></SPAN>
lRetVal = objCmd.Parameters("@RETURNVALUE").Value</SPAN></SPAN>
If lRetVal = 0 Then</SPAN></SPAN>
MsgBox "Stored procedure executed sucessfully", vbInformation</SPAN></SPAN>
Else</SPAN></SPAN>
MsgBox "Stored procedure execution Fail", vbInformation</SPAN></SPAN>
Exit Sub</SPAN></SPAN>
End If</SPAN></SPAN>
We try to extract data from a SQL Server 2008 R2 database using a stored procedure into an Excel 2010 workbook. The parameters (client code and end date) are populated by the client at runtime, and used as parameters for the stored procedure.
The problem is following.
When we send the date parameter value as a date, it does not have the quotes around it, and SQL Server cannot parse it. (e.g. 2012-08-29)
When we send the date parameter value as a date and add quotes ("'") around the date, it is no longer seen as a date but as a string, and VBA gives an error (e.g. '2012-08-09')
When we change type to string, the stored procedure does not accept the value, as it is a string, not a date.
Does anyone have expericence with parsing a date parameter to a SQL server stored procedure?
*** VBA sniplet of the code ***
'Execute the stored procedure once & store the output on the Main Data Tab.</SPAN></SPAN>
Set objRs = New ADODB.Recordset</SPAN></SPAN>
Set objCmd = New ADODB.Command</SPAN></SPAN>
With objCmd</SPAN></SPAN>
.ActiveConnection = objDBConn</SPAN></SPAN>
.CommandType = adCmdStoredProc</SPAN></SPAN>
.CommandText = "XXX" ' Stored procedure</SPAN></SPAN>
'Return value parameter to check SP executed sucessfuly of not</SPAN></SPAN>
.Parameters.Append .CreateParameter("@RETURNVALUE", adInteger, adParamReturnValue)</SPAN></SPAN>
' Actual Parameters</SPAN></SPAN>
.Parameters.Append .CreateParameter("@ClientCode", adVarChar, adParamInput, 2400)</SPAN></SPAN>
.Parameters.Append .CreateParameter("@EndDate", adDate, adParamInput, 12)</SPAN></SPAN>
'Parameter Values</SPAN></SPAN>
.Parameters("@ClientCode").Value = "'" & ClientCode & "'"</SPAN></SPAN>
.Parameters("@EndDate").Value = CDate(strEndDate)</SPAN></SPAN>
End With</SPAN></SPAN>
Set objRs = objCmd.Execute()</SPAN></SPAN>
lRetVal = objCmd.Parameters("@RETURNVALUE").Value</SPAN></SPAN>
If lRetVal = 0 Then</SPAN></SPAN>
MsgBox "Stored procedure executed sucessfully", vbInformation</SPAN></SPAN>
Else</SPAN></SPAN>
MsgBox "Stored procedure execution Fail", vbInformation</SPAN></SPAN>
Exit Sub</SPAN></SPAN>
End If</SPAN></SPAN>
