rem parse date to use in filename
for /F "tokens=2-4 delims=/ " %%a in ("%date%") do @set dt=%%c%%a%%b

rem extract table name from params table
for /F "tokens=1 delims=" %%z in ('sqlcmd -S. -E -W -h-1 -s"" -Q"set nocount on;select top 1 tablename from db..params"') do @set table=%%z

rem construct bcp filename
set bcpfile="\\computer\path\%table%_%dt%.csv"

rem run bcp
bcp "db..%table%" out %bcpfile% -S. -T -c -t,

c:\exporttable.bat

quote:

---

Originally posted by robvolk

You can create a job that has an Operating System step, then run it using sp_start_job. The problem is that a job can't accept parameters. You can however load the parameters into a regular table and have the step read them using bcp or osql to a file.

Create a table in your database to hold parameters:

create table params(tablename varchar(128) not null primary key)

Create a stored procedure that can accept a table name:

create procedure ExportTable @table varchar(128) as
set nocount on
truncate table params
insert params values(@table)
exec msdb..sp_start_job @job_name='Export Table'

In Notepad or similar program, copy/paste the following text (change the db reference to match your database name):

rem parse date to use in filename
for /F "tokens=2-4 delims=/ " %%a in ("%date%") do @set dt=%%c%%a%%b

rem extract table name from params table
for /F "tokens=1 delims=" %%z in ('sqlcmd -S. -E -W -h-1 -s"" -Q"set nocount on;select top 1 tablename from db..params"') do @set table=%%z

rem construct bcp filename
set bcpfile="\\computer\path\%table%_%dt%.csv"

rem run bcp
bcp "db..%table%" out %bcpfile% -S. -T -c -t,

Save the file with a .BAT extension, make sure .TXT is not appended automatically. (e.g. c:\exporttable.bat)

Create a job named "Export Table" and add an Operating system step consisting of:

c:\exporttable.bat

You can save the batch file anywhere, just make sure the SQL Agent service account has permissions to its directory. Then execute your stored procedure like so:

exec ExportTable 'myTable'

---

quote:

---

Originally posted by khtan

if this is just a one-off kind of thing and you don't need to do this inside the SQL Server, just issue the BCP command at your local machine or at Server machine command prompt.

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by robvolk

You have to create the job manually. In SSMS, right click SQL Agent, expand the Jobs folder, right-click and choose "New Job". Just make sure the name matches the name you use in sp_start_job.

---