How can we schedule sql server agent job to fetch all records from the table to the CSV file end of every week in ssms, give me steps to complete task .

Question

How can we schedule sql server agent job to fetch all records from the table to the CSV file end of every week in ssms, give me steps to complete task .

NAVINN T 20

LiHongMSFT-4306 31,471 Reputation points

2023-08-07T02:19:51.5766667+00:00

Hi @NAVINN T

Do you have further question on this issue, could we offer more support?

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

3 answers

Your answer

LiHongMSFT-4306 31,471 Reputation points

2023-08-07T02:19:51.5766667+00:00

Hi @NAVINN T

Do you have further question on this issue, could we offer more support?

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Answer 1

Gérald Döserich 765

See here on how to create a package for the export:https://community.spiceworks.com/topic/843412-export-sql-view-to-csv-using-sql-server-agent#entry-4408368

And here on how to schedule it:

https://community.spiceworks.com/topic/843412-export-sql-view-to-csv-using-sql-server-agent#entry-4409034

Answer 2

While creating the job, in the step provide the SQL command to export the data.

Select "Transact-SQL script (T-SQL)".

Select the database containing the table you want to export.

You can use a command with BCP like this:

   DECLARE @cmd varchar(1000)
     SET @cmd = 'bcp "SELECT * FROM YourDatabase.dbo.YourTable" queryout "C:\path\to\your\file.csv" -c -t, -T -S ' + @@servername
     EXEC xp_cmdshell @cmd

Then try to create a new schedule for this job with the following

Frequency: Select "Weekly".
Recurs every: 1 week.
On: Choose the day of the week you want the job to run.
Start time: Set the time you want the job to run.

Make sure that the SQL Server Agent service is running, as it is required to execute scheduled jobs.

The account running the SQL Server Agent job must have sufficient permissions to execute the BCP command and write to the file system.

The xp_cmdshell command might be disabled on your server for security reasons. If it's necessary to enable it, you can do so by running:

 EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'xp_cmdshell', 1;
  RECONFIGURE;

Erland Sommarskog 120.2K Reputation points MVP

2023-08-06T13:11:57.9433333+00:00

It is true that BCP can be an option to create export files, particularly if you don't want to involve SSIS in your mix. However, as given above, there will be problems if there can be commas in the data. In that case, you typically want the string fields to be enclosed in double quotes. This can be arranged with BCP with help of a format file, but it makes the solution more complex.

But overlooking that, there is absolutely no reason why this would be a T-SQL job step. If the task is to run BCP, that should be a CmdExec-job step which runs BCP directly. This is a lot more straightforward than going through hoops with xp_cmdshell. Would it be the case that the file is to be written to a file share, the service account for SQL Server may not have access to that file share. For a CmdExec job step you can deal with this by setting up a proxy. This is not possible for a T-SQL job step.

Answer 3

The first step is to determine the exact requirements. What table? Where should the file land? What is the exact format of the file? Can you go with just plain commas or semicolons as the delimiter, or you need to consider that the delimiters may be in the data? What is the purpose of the file? What other system will ingest it? What tool do you want to use to generate the file? Many sites use SSIS, SQL Server Integration Services, but far from all.

Once you have the code to produce the file, scheduling the job in Agent should be the easiest part.

If you have further questions, please let us know.

Share via

How can we schedule sql server agent job to fetch all records from the table to the CSV file end of every week in ssms, give me steps to complete task .

3 answers

Your answer