October 10, 2012 at 7:52 am
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.
October 10, 2012 at 8:13 am
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
October 10, 2012 at 8:22 am
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)
October 10, 2012 at 11:42 pm
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;-)
October 11, 2012 at 1:51 am
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'
October 11, 2012 at 2:17 am
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.
October 11, 2012 at 2:18 am
mahesh.dasoni (10/11/2012)
use this in the job step while executingEXEC 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
October 11, 2012 at 4:40 am
mahesh.dasoni (10/11/2012)
use this in the job step while executingEXEC 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"
October 11, 2012 at 6:11 am
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'
October 11, 2012 at 7:33 am
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.
October 11, 2012 at 8:02 am
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
October 11, 2012 at 8:09 am
U can use SSIS PACKAGE for fetching data in csv format and save it to a location which u want
October 11, 2012 at 8:10 am
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.
October 12, 2012 at 4:51 am
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!
October 12, 2012 at 5:02 am
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