Excel Macro to call Store procedure in SQL Server

[atuljadhavnetafim]

Dear Expert
i have below code which run Store procedure which saved in SQL server and paste required data in my excel file
now that Store procedure is running well in SQL Managment studio but when i call it from Excel macro (This file store in my local folder) then i am getting error

Run tiem error '2147467259 (80004005)':
[DBNETLIB][ConnectionOpen (Connet()).] SQL Server does not exist or access denied.

and when i press Debug then one line become yellow, which i have pasted below

con.Open "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;Trusted_Connection=Yes;"


Please help in this regards

Sub Button1_Click()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim par As String
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."
' Remove any values in the cells where we want to put our Stored Procedure's results.
Dim rngRange As Range
Set rngRange = Range(Cells(8, 2), Cells(Rows.Count, 1)).EntireRow
rngRange.ClearContents
' Log into our SQL Server, and run the Stored Procedure
con.Open "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;Trusted_Connection=Yes;"
cmd.ActiveConnection = con
Dim prmCustomerID As ADODB.Parameter
' Set up the parameter for our Stored Procedure
' (Parameter types can be adVarChar,adDate,adInteger)
cmd.Parameters.Append cmd.CreateParameter("Account", adVarChar, adParamInput, 10, Range("D2").Text)
Application.StatusBar = "Running stored procedure..."
cmd.CommandText = "SP_GetOrdersForCustomer"
Set rs = cmd.Execute(, , adCmdStoredProc)
' Copy the results to cell B7 on the first Worksheet
Set WSP1 = Worksheets(1)
WSP1.Activate
If rs.EOF = False Then WSP1.Cells(8, 2).CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set cmd = Nothing
con.Close
Set con = Nothing
Application.StatusBar = "Data successfully updated."
End Sub

 

[RoryA]

It's nothing to do with the stored procedure. Either you don't have permission to access the db as the currently logged in user, or your server name is incorrect. Is the server on the same machine on which you are running the code?

 

[atuljadhavnetafim]

When i open
SQL Server Managment Studio
In Server Name show " CNU94015QZ\SQLEXPRESS"
and Yes server installed in same machine
Should it will run from different machine?

 

[RoryA]

Try replacing localhost in your connection string with the server name you just posted.

 

[atuljadhavnetafim]

Dear Sir

It's Working Fine, Many Many Thanks to you

if i send this file to my coleage then also this will run?

 

[RoryA]

If he has access to your machine, it should do.