How can I run a stored procedure in ms sql server 2008 from excel 2003 vba

Dov_B
Dov_B used Ask the Experts™
on
Please some one give me a simple example of connecting through vba to sql server 2008 from excel 2003 vba
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Sub main()
Dim vConn As String
vConn = "Provider=SQLOLEDB;Data Source=Server;Initial Catalog=S;User ID=X;Password=Y"
Set objconn = New ADODB.Connection
objconn.Open vConn
Dim vstoreproc As String
vstoreproc = "exec mystoredprocname"
objconn.Execute vstoreproc

Author

Commented:
for datasource I put in the Ip address (which works)
for initial catalog I did not know what to put in so I removed it (perhaps you can tell me what to put in)
my stored procedure is called dbo.usp_adduser2
excel says it cant find it
The initial catalog will be the database name that the stored procedure is located.

Author

Commented:
tHANK YOU
The login failed
I have the correct ip address and have connected successfuly through excel s import external data wizard in fact I recorded a macro and this is the result

With ActiveSheet.QueryTab.Add(Connection:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=*****;Persist Security Info=True;User ID=sa;Data Source=111.111.11.111;Use Procedure for Prepar" _
        , _
        "e=1;Auto Translate=True;Packet Size=4096;Workstation ID=B***Y-PC;Use Encryption for Data=False;Tag with column collation when po" _
        , "ssible=False;Initial Catalog=master"), Destination:=Range("A1"))
        .CommandType = xlCmdTable
        .CommandText = Array("""master"".""dbo"".""myTable""")
        .Name = "+New SQL Server Connection"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Users\benny\Documents\My Data Sources\111.111.11.11master myTable.odc"
        .Refresh BackgroundQuery:=False
    End With

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Change this line

vConn = "Provider=SQLOLEDB;Data Source=Server;Initial Catalog=S;User ID=X;Password=Y"

to

vConn = "Provider=SQLOLEDB.1;Password=*****;Persist Security Info=True;User ID=sa;Data Source=111.111.11.111;Initial Catalog=master"

Here is connects to the database "master".  Change that to the name of your database that contains the SP dbo.usp_adduser2

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial