SQL Server 2005 running stored procedure from Excel (VBA)

Question

I am trying to run a stored procedure on my SQL server. I think the connection type is 'ODBC', but I am unsure, I am trying the below:

'Declare some variables
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String
Dim gway As String

'Create a new Connection object
Set cnn = New ADODB.Connection

'Set the connection string
cnn.ConnectionString = connectionString 'See http://connectionstrings.com if you need help on building this string for your database!

'Create a new Command object
Set cmd = New ADODB.Command
'Associate the command with the connection
'Open the Connection to the database
cnn.Open
cmd.ActiveConnection = cnn


VARIABLE= "param"
strSQL = "EXECUTE dbo.NAMEWASHERE '" & VARIABLE & "';"



cmd.Execute strSQL

'Close the connection again
cnn.Close

When I try to run this I recieve the error: Run-time error '3709';

Requested operation requires an OLE DB Session object, which is not supported by the current provider.

I am sure I am just getting my connection type wrong somewhere, but could anybody help me out?

To confirm, I'm trying to connect via ODBC to an SQLServer2005, via Excel VBA (2003)

Many thanks.

UPDATE WITH FIX: You must open the connection before using ".ActiveConnection = """. I have editted the original code to show this change.

The code looks ok, the reason for the error depends on the specific connection string your using, what is it? — Alex K.
– Alex K., Commented Sep 1, 2011 at 10:11
ODBC;DSN=serverAddr;UID=LoginName;PWD=MyPassword;LANGUAGE=us_english; DATABASE=DatabaseName. Obviously this is sensitive, so if the error is here you may not be able to help. I hope you can help. EDIT: I am using SQL Express 2005 if this matters. — Waller
– Waller, Commented Sep 1, 2011 at 10:23
I would use the SQL Server Native Client 10.0 OLE DB Provider connection string from connectionstrings.com/sql-server-2005#p2 — Alex K.
– Alex K., Commented Sep 1, 2011 at 10:29
Same error again, this is really frustrating. Perhaps it is something wrong interally that you are unable to help further with? Could you explain your reasoning behind choosing that connectionString? Then I could try to feed on from there. EDIT : Problem is fixed. I will try to edit my main post with the resolution — Waller
– Waller, Commented Sep 1, 2011 at 10:35
@Waller Please post your fix as an answer. You will be able to accept your own answer after a little time. — Fionnuala
– Fionnuala, Commented Sep 2, 2011 at 7:16

Waller · Accepted Answer · 2011-09-06 08:54:20Z

2

UPDATE WITH FIX: You must open the connection before using ".ActiveConnection = """. I have editted the original code to show this change.

answered Sep 6, 2011 at 8:54

Waller

1,8834 gold badges20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server 2005 running stored procedure from Excel (VBA)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related