How to extract the data from sql server?

[Sarankbm]

Hi friends,

i have used Please find below mention query.but that code some issue.
i am using SQL server management studio.how to resolve this?

Sub aa()

Dim conn As ADODB.Connection

    Dim rs As ADODB.Recordset
    Dim sConnString As String

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=INSTANCE\SQLEXPRESS;" & _
                  "Initial Catalog=Raja;" & _
                  "Integrated Security=SSPI;"

    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("SELECT * FROM raja.dbo.saran")

    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(1).Range("A1").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Sub

MOD EDIT: CODE TAGS ADDED

 

[shrivallabha]

Hi Sarankbm,

This is general script and works well. On which line does the code fail?

You need to check the parameters which are specific to your case.
Data Source - You SQL instance
Initial Catalog - Your DB Name (The instance should have this DB)
Query - "SELECT * FROM raja.dbo.saran"

 

[Sarankbm]

This was i expected thank you but table header will not come.how to resolve this?

 

[shrivallabha]

Untested, try modifying following portion:

   IfNot rs.EOF Then
       ' Transfer result.       Sheets(1).Range("A1").CopyFromRecordset rs
   ' Close the recordset       rs.Close
   Else
        MsgBox "Error: No records returned.", vbCritical
   EndIf

to

   If Not rs.EOF Then
        ' Transfer result.
       Sheets(1).Range("A2").CopyFromRecordset rs
       Dim Fld
       Dim lCnt As Long: lCnt = 1
       For Each Fld In rs.Fields
            Sheets(1).Cells(1, lCnt) = Fld.Name
            lCnt = lCnt + 1
       Next
    ' Close the recordset
       rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

 

[Sarankbm]

Thank you so much