Exporting Data to Excel

[bombdropVB]

Hi all i wish to transfer some data from SQLServer7 to an excel spreadsheet, when i run the script in Analyzer it works as expected but when i try to use it as an export script in Enterprise manager it doews not work.

DECLARE Ace_Handlers CURSOR FOR 

SELECT DISTINCT 
    handler 
FROM 
    claim

OPEN Ace_Handlers

DECLARE @HandlerName varchar(100)

FETCH NEXT FROM 
    Ace_Handlers 
INTO
     @HandlerName
WHILE (@@FETCH_STATUS=0 )
    BEGIN
        SELECT 
            RIGHT(CAST(Claim.reportdate AS varchar(10)), 2)  + RIGHT('00000' + CAST(Claim.gaid AS varchar(5)), 5) 'Our Ref', 
            Client.Clientshort,
            Claim.handler, 
            Claim.Description,
            cd.Reserve
        FROM 
            Claim
            INNER JOIN  claimdetail cd ON 
	            claim.claimref = cd.claimref 
            INNER JOIN client ON 
	            claim.clientref = client.clientref
        WHERE 
            CLAIM.HANDLER=@HandlerName
        AND    
           	CONVERT(datetime,cd.detaildate,103) 
    
    		IN 
    
	    	(
		    	SELECT  MAX(CONVERT(datetime, claimdetail.detaildate,103))
            
                FROM 
	                claim 
            
                INNER JOIN  claimdetail ON 
	                claim.claimref = claimdetail.claimref 
            
                INNER JOIN client ON 
	                claim.clientref = client.clientref
            
                GROUP by
	                claimdetail.claimref
                HAVING 
                    claimdetail.claimref=cd.claimref

    		)

        AND 
            cd.reserveflag !='s'

        FETCH NEXT FROM 
            Ace_Handlers 
        INTO 
            @HandlerName
    END
        
CLOSE Ace_Handlers
DEALLOCATE Ace_Handlers

This will not nopt transfrer the data can anyone help??

 

[rwb1912]

What error message are you getting ? I presume that you are trying to schedule this task in SQL Agent to run automatically ? Or are you placing this in a DTS package ?

An alternative is to use the bcp command and output the query directly to a flat file with semi colon or tab delimitation. Excel will happily read this in.

BCP will easily allow you to run a query to output the data and it is heaps faster than using any other mechanisms.

Cheers

 

[bombdropVB]

Hi thanks for that still does not work.
here was my out put in a text file

"handler"
" "
" "
" "
" "
" "
" "

in the excel one cell in one sheet has "handler" within it does exporting not like currsors???

 

[AtomicWedgie]

Hi,

why do you use a cursor to select the data you want anyway? I don't see any advantage over a normal selectstatement in this case.


Regards,

Atomic Wedgie

 

[bombdropVB]

The main reason I'm using a cursor is due to the fact the person who whants this data wants each handler on there own sheet, I'm for to lazy to go throught one result set fishing out 'Handlers' one by one hence the cursor returning 1 recordset per handler.

 

[AtomicWedgie]

Hi,

but where in your cursor does it say that it has to write each handler seperately to it's own sheet (or output file)?

Regards,

Atomic Wedgie

 

[bombdropVB]

Soloved the problem using vb to run through each recordset returned by the cursor.

Private Sub Command1_Click()

    Dim conExport           As ADODB.Connection
    Dim recExport           As ADODB.Recordset
    Dim objFiled            As ADODB.Field
    Dim objApp              As Excel.Application
    Dim objBook             As Excel.Workbook
    Dim objSheet            As Excel.Worksheet

    Dim intSheetCounter     As Integer
    Dim intFieldCounter     As Integer
    Dim strHandler          As String
    Dim astrCatalog(3)      As String

    On Error GoTo Command1_Click_Error

    '/*
    'Populate the array with the name of each of the catalogs
    'held on the server
    '*/

    astrCatalog(0) = "ace"
    astrCatalog(1) = "ire"
    astrCatalog(2) = "nonscheme"
    astrCatalog(3) = "pi5"

    Set conExport = New ADODB.Connection
    Set recExport = New ADODB.Recordset


    'Open an Excel application an set it to a work book workbook.

    Set objApp = New Excel.Application
    Set objBook = objApp.Workbooks.Add


    'Open the connection.
    conExport.Open _
        "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=ace;Data Source=LVP-GANDAPI"

    'Open the recordset will return multiple
    recExport.Open "sb_testc", conExport
    intSheetCounter = 0


    While Not recExport Is Nothing

        'If redcordset has records then populate spreadsheet
        If Not recExport.EOF Then

            intSheetCounter = intSheetCounter + 1

            'After the third sheet add extra sheets
            If intSheetCounter > 3 Then
                objBook.Worksheets.Add , objSheet
            End If 'ntSheetCounter > 3

            'Get handlers name
            strHandler = recExport!handler & ""
            
            If Len(strHandler) = 0 Then
                strHandler = "NoName"
            End If 'Len(strHandler) = 0
            strHandler = Replace(strHandler, "/", "")

            'set a sheetobject to the current worksheet so it can be renamed
            Set objSheet = objBook.Worksheets(intSheetCounter)
            objSheet.Name = strHandler

            'reset for each new sheet
            intFieldCounter = 1

            'Set up column headers
            For Each objFiled In recExport.Fields

                With objSheet.Cells(1, intFieldCounter)
                    .Value = objFiled.Name
                    .Font.Bold = True
                    .Font.Size = 11
                    .Interior.Color = &H808080
                End With 'objSheet.Cells(1, intFieldCounter)

                intFieldCounter = intFieldCounter + 1
            Next 'objFiled


            'Export the recordset to the worksheet
            objSheet.Range("a2").CopyFromRecordset recExport
            objSheet.Columns.AutoFit

        End If 'Not recExport.EOF

        'Move to the next worksheet
        Set recExport = recExport.NextRecordset

    Wend 'Not recExport Is Nothing
    objBook.SaveAs "c:\ACEOpen by handler.xls"

    'Close connection to database
    conExport.Close


    'Save the Work book


    MsgBox "finished"

    objBook.Close False

    objApp.Quit



    GoTo CleanExit:

Command1_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ")" & vbCr & _
        "Found In Form: Form1 " & vbCr & "Found In Procedure: Command1_Click" & _
        vbCr & IIf(Erl > 0, "Found In Line:" & Erl, ""), vbCritical, _
        "Error Occurred"

    'Call LogError ("Form1:Command1_Click",Err.Description, err.Number, erl)

CleanExit:
    On Error GoTo 0
    'Destroy all objects used

    If Not objSheet Is Nothing Then
        Set objSheet = Nothing
    End If

    If Not objBook Is Nothing Then
        Set objBook = Nothing
    End If

    If Not objApp Is Nothing Then
        Set objApp = Nothing
    End If

    If Not recExport Is Nothing Then
        Set recExport = Nothing
    End If 'Not recExport Is Nothing Then

    If Not conExport Is Nothing Then
        Set conExport = Nothing
    End If 'Not conExport  Is Nothing Then

End Sub 'Command1_Click (Sub)