SQL Server Agent job running query only sends half of the results to file

  • I have an SQL Server Agent job running every day at 9am to run a query and using the Advanced tab in the specific step to run the query I send the output of the query to a CSV file. However, when checking the CSV file, only half of the results of the query display.

    When I run the query itself outside of the job and save the results to a CSV file, the whole results show.

    Any help greatly appreciated.

  • can you provide the below details.

    are using excel to open the csv file?

    what is the sql server and office version?

    approximate no of rows for 2 or 3 days

    Regards
    Durai Nagarajan

  • durai nagarajan (10/10/2012)


    can you provide the below details.

    are using excel to open the csv file?

    what is the sql server and office version?

    approximate no of rows for 2 or 3 days

    I save it as a CSV file on the server to a folder on the network then open it using Excel on my PC as the server does not have Excel.

    The version of SQL server is 2008 r2 and I then use Excel 2007 on my PC to open the file.

    It should show roughly around 130 rows but only outputs roughly 50.. then ends with (null)

  • is it started happeinig at sudden ?

    and

    are you using same location for CSV file by both the job and manual process? because there could be space issue :unsure:

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • use this in the job step while executing

    EXEC MSDB.DBO.SP_SEND_DBMAIL

    @PROFILE_NAME = @MPOFILENAME,

    @RECIPIENTS = @MRECIPIENTS,

    @COPY_RECIPIENTS = @MRECIPIENTS_CC,

    @QUERY = @MQUERY,

    @SUBJECT = @MPROFILEDESC,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'myfile.csv'

  • Bhuvnesh (10/10/2012)


    is it started happeinig at sudden ?

    and

    are you using same location for CSV file by both the job and manual process? because there could be space issue :unsure:

    I've only just started trying to do this so I'm not sure.

    There isn't a space issue in the folder I'm saving to as the output data is only small and I don't save anything else there.

  • mahesh.dasoni (10/11/2012)


    use this in the job step while executing

    EXEC MSDB.DBO.SP_SEND_DBMAIL

    @PROFILE_NAME = @MPOFILENAME,

    @RECIPIENTS = @MRECIPIENTS,

    @COPY_RECIPIENTS = @MRECIPIENTS_CC,

    @QUERY = @MQUERY,

    @SUBJECT = @MPROFILEDESC,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'myfile.csv'

    I'll give this a go now, thanks

  • mahesh.dasoni (10/11/2012)


    use this in the job step while executing

    EXEC MSDB.DBO.SP_SEND_DBMAIL

    @PROFILE_NAME = @MPOFILENAME,

    @RECIPIENTS = @MRECIPIENTS,

    @COPY_RECIPIENTS = @MRECIPIENTS_CC,

    @QUERY = @MQUERY,

    @SUBJECT = @MPROFILEDESC,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'myfile.csv'

    I added this, but currently the job is failing with error:

    'Executed as user: NT AUTHORITY\SYSTEM. Must declare the scalar variable @MPROFILENAME"

  • For Database what profile name who have made mention that.

    EXEC MSDB.DBO.SP_SEND_DBMAIL

    @PROFILE_NAME = 'XYZ',

    @RECIPIENTS = 'test@gmail.com',

    @COPY_RECIPIENTS = 'test1@gmail.com',

    @body= 'Job successfull',

    @SUBJECT = 'This is for test',

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'myfile.csv'

  • Sorry, I didn't know what this stored proc was, I've looked more into it now and it's not what I want. I do not want the results emailed to me, I just want them to save to a CSV file. The problem I am having is that only half the results are showing. I'm currently trying BCP to see if that shows all results of the query.

  • are you getting the complete output in the email?

    can you mask the sensitive code and post a sample so that we can check.

    Regards
    Durai Nagarajan

  • U can use SSIS PACKAGE for fetching data in csv format and save it to a location which u want

  • mahesh.dasoni (10/11/2012)


    U can use SSIS PACKAGE for fetching data in csv format and save it to a location which u want

    I don't have SSIS with my version of SQL, sadly.

  • durai nagarajan (10/11/2012)


    are you getting the complete output in the email?

    can you mask the sensitive code and post a sample so that we can check.

    Sorry I've only just managed to set up the email to work - I am getting the complete output in the email yes so I don't know why when I save it via an Agent job its only outputting half the results!

  • sqlrd22 (10/12/2012)


    durai nagarajan (10/11/2012)


    are you getting the complete output in the email?

    can you mask the sensitive code and post a sample so that we can check.

    Sorry I've only just managed to set up the email to work - I am getting the complete output in the email yes so I don't know why when I save it via an Agent job its only outputting half the results!

    can you post the code and mask the sensitive parts eg change table names.

    Regards
    Durai Nagarajan

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply