SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 xp_cmdshell		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

279 Posts
Posted - 02/22/2013 :  03:36:28  Show Profile  Reply with Quote
how can i add in the file name together with the date?

TableName_20130221

Below query seems not working:

declare @Date varchar(max),@path varchar(max)
set @Date = replace(convert(varchar, getdate()-1,111),'/','')
EXEC master..xp_cmdshell'bcp "select * from ##A" queryout "\\...\TableName_+''@Date''.txt" -c -t"|" -T -S'

bandi
Flowing Fount of Yak Knowledge

India
1430 Posts
Posted - 02/22/2013 :  04:36:58  Show Profile  Reply with Quote

declare @sql varchar(4000)
declare @Date varchar(max)
set @Date = replace(convert(varchar, getdate()-1,111),'/','')
set @sql = 'bcp "SELECT * FROM DBNAME.dbo.TableNAme" queryout "D:\temp\'+ @date +'.txt" -c -t"|" -T -S'
EXEC master..xp_cmdshell @sql


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/23/2013 :  03:29:59  Show Profile  Reply with Quote
also to run this you need to have xp_cmdshell enabled

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO
EXEC sp_configure 'xp_cmdshell'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

peace
Constraint Violating Yak Guru

279 Posts
Posted - 02/24/2013 :  05:40:49  Show Profile  Reply with Quote
Thanks!
Is working fine now..

How can I add in the column name above?

FirstName|LastName|MiddleName
Mary|Ann|Joseph
Henry|John|Johnson
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1511 Posts
Posted - 02/24/2013 :  08:15:57  Show Profile  Reply with Quote
BCP does not have an option that lets you specify that column headers be included in the output. There are workarounds that people use such as creating a view that includes the header etc., that you will find if you google for it. None of those are really clean solutions though.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/24/2013 :  23:20:59  Show Profile  Reply with Quote
quote:
Originally posted by peace

Thanks!
Is working fine now..

How can I add in the column name above?

FirstName|LastName|MiddleName
Mary|Ann|Joseph
Henry|John|Johnson

see

http://weblogs.sqlteam.com/brettk/archive/2005/04/13/4395.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000