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

Question

Post reply

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

sqlrd22

SSCrazy

Points: 2255
More actions
October 10, 2012 at 7:52 am

#265518

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.

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

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

durai nagarajan SSCoach Points: 15775 More actions · Answer 1

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

sqlrd22 SSCrazy Points: 2255 More actions · Answer 2

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)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

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;-)

mahesh.dasoni SSCommitted Points: 1653 More actions · Answer 4

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'

sqlrd22 SSCrazy Points: 2255 More actions · Answer 5

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.

sqlrd22 SSCrazy Points: 2255 More actions · Answer 6

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

sqlrd22 SSCrazy Points: 2255 More actions · Answer 7

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"

mahesh.dasoni SSCommitted Points: 1653 More actions · Answer 8

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'

sqlrd22 SSCrazy Points: 2255 More actions · Answer 9

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.

durai nagarajan SSCoach Points: 15775 More actions · Answer 10

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

mahesh.dasoni SSCommitted Points: 1653 More actions · Answer 11

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

sqlrd22 SSCrazy Points: 2255 More actions · Answer 12

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.

sqlrd22 SSCrazy Points: 2255 More actions · Answer 13

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!

durai nagarajan SSCoach Points: 15775 More actions · Answer 14

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