Hi,

I am trying to execute stored procedure in SQL Server 2005 from Excel
Macro. But I am getting Automation error.

Could any one please help me to sort this problem?

Thanks

Code might help.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"chezhiyan" wrote in message
oups.com...
Hi,

I am trying to execute stored procedure in SQL Server 2005 from Excel
Macro. But I am getting Automation error.

Could any one please help me to sort this problem?

Thanks

Private Sub cmdGetReport_Click()
Dim strConn As String
Dim actcol, actrow, iCols As Integer

strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=vdb;Data Source=test"
Set conn = New ADODB.Connection
conn.Open strConn


Set cmdBudget = New ADODB.Command
cmdBudget.ActiveConnection = conn
cmdBudget.CommandText = "hesri_test"

cmdBudget.CommandType = adCmdStoredProc


Set rstBudget = cmdBudget.Execute()


' Get the active colum and row position (This will be parameter
value 1)
actcol = ActiveCell.Column
actrow = ActiveCell.Row



'Return results on to the current worksheet at the row below the
' active cell location and include a header colum in bold text
For iCols = 0 To rstBudget.Fields.Count - 1
ActiveSheet.Cells(actrow + 1, actcol + iCols).Value =
rstBudget.Fields(iCols).Name
Next iCols
ActiveSheet.Range(ActiveSheet.Cells(actrow + 1, actcol),
ActiveSheet.Cells(actrow + 1, actcol + rstBudget.Fields.Count -
1)).Font.Bold = True
ActiveSheet.Cells(actrow + 2, actcol).CopyFromRecordset
rstBudget 'datastarts at row+2

MsgBox ("data loaded")


End Sub


note: this sp takes atleast 20 mins to execute

Private Sub cmdTEst_Click()
Dim strConn As String
Dim actcol, actrow, iCols As Integer

strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=testdb;Data Source=test"
Set conn = New ADODB.Connection
conn.Open strConn


Set cmdtest = New ADODB.Command
cmdtest.ActiveConnection = conn
cmdtest.CommandText = "hesri_test"

cmdtest.CommandType = adCmdStoredProc


Set rsttest= cmdTest.Execute()


' Get the active colum and row position (This will be parameter
value 1)
actcol = ActiveCell.Column
actrow = ActiveCell.Row



'Return results on to the current worksheet at the row below the
' active cell location and include a header colum in bold text
For iCols = 0 To rstBudget.Fields.Count - 1
ActiveSheet.Cells(actrow + 1, actcol + iCols).Value =
rsttest.Fields(iCols).Name
Next iCols
ActiveSheet.Range(ActiveSheet.Cells(actrow + 1, actcol),
ActiveSheet.Cells(actrow + 1, actcol + rsttest.Fields.Count -
1)).Font.Bold = True
ActiveSheet.Cells(actrow + 2, actcol).CopyFromRecordset rsttest
'datastarts at row+2

MsgBox ("data loaded")


End Sub

Try setting the connection/command timeouts before executing the command.

--
Tim Williams
Palo Alto, CA


"chezhiyan" wrote in message oups.com...
Private Sub cmdTEst_Click()
Dim strConn As String
Dim actcol, actrow, iCols As Integer

strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=testdb;Data Source=test"
Set conn = New ADODB.Connection
conn.Open strConn


Set cmdtest = New ADODB.Command
cmdtest.ActiveConnection = conn
cmdtest.CommandText = "hesri_test"

cmdtest.CommandType = adCmdStoredProc


Set rsttest= cmdTest.Execute()


' Get the active colum and row position (This will be parameter
value 1)
actcol = ActiveCell.Column
actrow = ActiveCell.Row



'Return results on to the current worksheet at the row below the
' active cell location and include a header colum in bold text
For iCols = 0 To rstBudget.Fields.Count - 1
ActiveSheet.Cells(actrow + 1, actcol + iCols).Value =
rsttest.Fields(iCols).Name
Next iCols
ActiveSheet.Range(ActiveSheet.Cells(actrow + 1, actcol),
ActiveSheet.Cells(actrow + 1, actcol + rsttest.Fields.Count -
1)).Font.Bold = True
ActiveSheet.Cells(actrow + 2, actcol).CopyFromRecordset rsttest
'datastarts at row+2

MsgBox ("data loaded")


End Sub

i tried setting timeout for connectiona and command object but still
it is not working

i tried with some other sp which doesn't have temp table it works fine

but when i try executing the sp which has some temp table it gives me
automation error

anbu.