1

I am running following query -

SELECT 'SELECT * 
FROM LIFE'

I want the expected output as -

SELECT *
FROM LIFE

However, when I copy the result set in new window, it is

SELECT * FROM LIFE

As per the below answer, when I use following 

SELECT 'SELECT * ' + char(13) + char(10) + 'FROM LIFE'

The output is still returned in one line with a wide space as follows -

SELECT *   FROM LIFE

Can I not get newline in the result-set? Sorry if I am missing something but I am not having strong SQL skills.

Suhaib Janjua
  • 3,538
  • 16
  • 59
  • 73
Reeya Oberoi
  • 813
  • 5
  • 19
  • 42

2 Answers2

4

You can add a carriage return (ASCII 13) and line feed (ASCII 10) like:

SELECT 'SELECT * ' + char(13) + char(10) + 'FROM LIFE'
shA.t
  • 16,580
  • 5
  • 54
  • 111
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Please see my edit in the question when I ran your query. It still returned result-set in one line. – Reeya Oberoi Apr 15 '15 at 13:33
  • 1
    @ReeyaOberoi: Did you try that in text mode? (menu `query -> results to -> results to text`, or keyboard `ctrl-T`) – Andomar Apr 15 '15 at 13:35
1

I'd suggest this is working, but SSMS is replacing the carriage return when you copy it from the resultset.

I just ran the below excel-vba code against SQL Server 2008 R2, and the text printed into an excel sheet as it should. To test, open excel and paste the test() routine into a sheet module in the VBA editor (alt + f11), and then insert a new module (name it dbMngr) and then paste the dbMngr code below into that module.

EDIT: You'll also need to include a connection string constant at the top of the dbMngr module.

Integrated Security:

Private Const S_CON As String = "Provider=SQLOLEDB;Data Source=MyServerName;Initial Catalog=MyDB;Integrated Security=SSPI"

SQL Server Auth:

Private Const S_CON As String = "Provider=SQLOLEDB;Data Source=MyServerName;Initial Catalog=MyDB;UID=MyUserName;PWD=MyPassword"

Test code:

Sub test()
Dim sSQL As String
sSQL = "SELECT 'LINE A' + Char(10) + Char(13) + 'LINE B'"

Me.Range("A1").Value = sSQL


dbMngr.PasteToRange sSQL, Me.Range("A4")

End Sub

dbMngr code:

Public Function GetRS(sSQL As String) As ADODB.Recordset
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

On Error GoTo errCatch:

With cn
    .ConnectionString = S_CON
    .CursorLocation = adUseClient
    .Mode = adModeRead
    .Open
End With

With rs
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .ActiveConnection = cn
    .Open sSQL
End With

Set rs.ActiveConnection = Nothing
Set GetRS = rs

errCatch:
If Err.Number <> 0 Then
    MsgBox "Error in GetRS function"
    Debug.Print Err.Number & " " & Err.Description
    Stop
    Err.Clear
End If

If cn.State = adStateOpen Then
    cn.Close
End If

Set rs = Nothing
Set cn = Nothing
End Function

Public Sub PasteToRange(sSQL As String, rngDest As Range)
Dim rs As ADODB.Recordset, x As Integer, orng As Range

rngDest.CurrentRegion.Value = ""

Set rs = GetRS(sSQL)

For x = 0 To rs.Fields.Count - 1
    rngDest.Offset(0, x).Value = rs.Fields(x).Name
Next x

rngDest.Offset(1, 0).CopyFromRecordset rs
rngDest.CurrentRegion.Interior.Color = RGB(255, 255, 255)
rngDest.CurrentRegion.Font.Size = 8
rngDest.WrapText = False

If rs.State = adStateOpen Then
    rs.Close
End If

Set rs = Nothing
End Sub
TehJake
  • 121
  • 9