Excel and SQL SP

[esengul]

Hi
I am trying to execute a sql sp in ms excel. it seems like i am doing something wrong that i couldnt figure out.
here is the code
========
Sub PutRecordsetInRange()
Dim cn As ADODB.Connection
Dim cd As ADODB.Command
Dim rsData As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.CursorLocation = adUseClient
.ConnectionString = "DRIVER=SQL Server;SERVER=ServerName; UID=;PWD=;DATABASE=Northwind"
.Mode = adModeRead
.ConnectionString = "ODBC;DRIVER=SQL Server;SERVER=END4258-5;UID=;APP=Microsoft Office 2003;WSID=END4258-5;DATABASE=Northwind;Trusted_Connection=Yes"
.Open
End With
Set cd = New ADODB.Command
With cd
Set .ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "SalesByCategory"
.CommandTimeout = 180
Set rsData = .Execute
End With
With rsData
If .State = adStateOpen Then
If Not (.BOF And .EOF) Then
Range("A1").CopyFromRecordset rsData
End If
.Close
Else
'/ error msg will be there
End If
End With
Set rsData = Nothing
Set cd = Nothing
cn.Close
Set cn = Nothing
End Sub

when i run this code, i get an error message which is not helpfull at all. Here is the error msg
===
Microsoft Visual Basic
Runtime error '-2147467259(80004005)'
Automation Error
Unspecified Error
=====
Thanks in advance

 

[RoyVidar]

You are setting the connectionstring twice. What happens if you just use the first? Or take a look here Connection strings - i e, try OleDB in stead of ODBC.

Else, which line is it that bombs?

You can loop the ADO errors collection, for a more informative error message, I think. There should be samples in the help files.

Roy-Vidar

 

[esengul]

Hi Roy-Vidar
i changed my connection string to oledb which it looks like
cn.ConnectionString = "Provider=sqloledb;" & _
"Data Source=END4258-5;" & _
"Initial Catalog=Northwind;" & _
"Integrated Security=SSPI"

now i am having problem when it is trying to execute sql sp
the line is : Set rsData = .Execute
Thanks for quick respond and trying to help me

 

[RoyVidar]

Error message? Doubt it has anything to do with it, but might perhaps want to use owner prefix on the SP.

[tt] .CommandText = "dbo.SalesByCategory"[/tt]

More important, does there exist an SP with that name in that database?

Roy-Vidar

 

[esengul]

Yes i've tried it like that too. i am using the northwind db that comes with sql server. i also checked if it has any parameter. it doesnt so i dont know what i am doing right now.
Here is the error ms dialog box
Microsoft Visual Basic
Runtime error '-2147217900(80040e14)'
Automation Error

Thanks again

 

[SkipVought]

Else, which line is it that bombs?

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[esengul]

hey SkipVought
i am not sure i understood you correctly. can you explain what you mean?
Thanks

 

[SkipVought]

Else: In the event that change the connect string does not solve your problem.

which: refers to line, e pluribus unum.

line: the line on which the error occured.

is: depends???

it: refers to line

that: this is getting tiresome.

bombs: error.


Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[fredericofonseca]

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[RoyVidar]

... lot of 80040e14 points to syntax errors in sql, but it is hard to know, we don't know any more than "Automation Error" and we don't see the sql ... check out for instance faq222-3704 to see how you can work with the error collection of ADO (though declare explict (Dim Errs1 As ADODB.Errors, errLoop As ADODB.Error))

Roy-Vidar