Remove Step details from SQL Job

Question

Remove Step details from SQL Job

Tomys

Mr or Mrs. 500

Points: 567
More actions
June 11, 2020 at 3:20 pm

#3762319

Dear,
I have created a SQL Server agent job on SQL 2017 server.
I have the output of the query to txt , now I would like to remove the first line in the output , which shows :
Job 'test' : Step 1, 'Step 1' : Began Executing 2020-06-11 12:13:27
Any suggestions ? thanks

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

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

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 1

What is the purpose of outputting the results of the job to text?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Tomys Mr or Mrs. 500 Points: 567 More actions · Answer 2

Hi Jeffrey, the user would then collect this data from the shared location and prepare report

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 3

This is not the appropriate way to create files from SQL Server. You can use BCP to output the data to a file - or SSIS - or export an SSRS report. You could even create a Powershell script that is run from the agent to create a file...

All of these options (and several others) would be much better options and provide much more control over the process.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Tomys Mr or Mrs. 500 Points: 567 More actions · Answer 4

Tomys

Mr or Mrs. 500

Points: 567

June 11, 2020 at 8:59 pm

#3762462

Thanks for the response I will check them

Tomys Mr or Mrs. 500 Points: 567 More actions · Answer 5

Hi, I used the export wizard for SSMS. This helps but some how I see a single quote in front of the columns - like 'ts

How can I remove this single quote in front of the column and results ?

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 6

How are you exporting the data - what were the options you selected in the export wizard? It should not preface a field with a single-quote like that...

It looks like you may have selected the Excel as the destination - and forced the column type to text. If so, then Excel requires that leading single-quote to identify numeric data as text data. This could possibly be the issue - but not sure since I haven't seen that before.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Tomys Mr or Mrs. 500 Points: 567 More actions · Answer 7

I am exporting data to excel , attaching the screen shot for reference. The defaults are selected while exporting..and the source is a query. The column type that comes automatically as Long text - as seen.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 8

Try to output to a flat file - comma-separate values and see if that works better.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Tomys Mr or Mrs. 500 Points: 567 More actions · Answer 9

Thanks I will try that. This issue seems with only using excel 2007. If I select excel 2016 (while exporting) on another machine I don’t see this happening.

So appears I have to install the oledb for excel 16 on the machine.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 10

That is entirely possible - I don't export files to Excel. I always export to a CSV file and let the users open it in Excel - they can then format it any way they want.

If they require an Excel file - I create a report in SSRS and export from there.

Just note - using the import/export wizard is actually utilizing SSIS under the covers. You can save the package - and then edit it using SSDT or Visual Studio (with the appropriate tools installed). To automate this you will need to install integration services though - and with that you will also want to install the integration services catalog.

If you are not able to install those components - then you will need a different approach. This is where Powershell/BCP and other utilities come in handy - as they can be scheduled in the agent and create files as needed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Tomys Mr or Mrs. 500 Points: 567 More actions · Answer 11

That's nice points you said. I was trying to automate this process using SQL Server agent job and selecting SSIS, once I have my export package saved. I tried using the CSV format but get the rows and columns in an un-proper format. The column values get mixed like one row goes below another and then an empty row this manner.

One question will there be any kind of security or any other risk, concern in case I install -AccessDatabaseEngine_X64 (this is what I found that needs to be installed to support Excel 2016) on a production sql server? Do I have to take any consideration or precautions? thanks again

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 12

I would have to see an example of the un-proper format you are getting - it seems to me that would be due to the data including CR or LF and how the file connection is configured.

There is no risk or issue with installing the driver on a production server. If the production server supports the production application then I would have concerns installing SSIS and any related processes. I utilize a secondary 'reporting' system for those processes - or utilize a data warehouse system.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

frederico_fonseca SSCoach Points: 16017 More actions · Answer 13

Tomys wrote:

That's nice points you said. I was trying to automate this process using SQL Server agent job and selecting SSIS, once I have my export package saved. I tried using the CSV format but get the rows and columns in an un-proper format. The column values get mixed like one row goes below another and then an empty row this manner.
One question will there be any kind of security or any other risk, concern in case I install -AccessDatabaseEngine_X64 (this is what I found that needs to be installed to support Excel 2016) on a production sql server? Do I have to take any consideration or precautions? thanks again

if you extracted to CSV as long as it is a TRUE csv e.g. all columns are double quoted if the contents of the column contains either the column delimiter or any CR and/or LF AND you also escape any double quote.

If you do this then any program capable of dealing with true CSV files will manage to read those "extra lines" you saw - note that Notepad and similar editors ARE not capable of reading CSV files "correctly" but likes of Excel are.

Tomys Mr or Mrs. 500 Points: 567 More actions · Answer 14

This is how I get data in CSV format : As you can see the rows are not aligned to proper columns above

Below shows the options I select while exporting to CSV: