question related to BCP when exporting to CSV

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

question related to BCP when exporting to CSV

Author

Topic

raaj
Posting Yak Master

129 Posts

Posted - 2013-07-12 : 01:03:16

Hi

I am scheduling a SQL job to run everyday and it has the following SQL statement :
EXEC master..xp_cmdshell 'BCP "Select * from AdventureWorks.dbo.Employees" queryout "\\SERV120\C$\test.csv" -T -c -t, -r \n -R'

As per the above SQL statement whenever the SQL job runs, the data from the query will be exported to server SERV120 (in C drive) with name test.csv

Now what I want is I would like to have that file created everyday with date (like test_dateexported) so that I can see all the csv files created daily.

So how to modify the above SQL statement so that I can include the runtime in the filename extension?

Thanks,
Ravi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 01:44:07

you need to use dynamic sql for that something like


DECLARE @SQL varchar(2000),@Date varchar(11)

SET @Date=CONVERT(varchar(11),GETDATE(),101)

SET @SQL='EXEC master..xp_cmdshell ''BCP "Select * from AdventureWorks.dbo.Employees" queryout "\\SERV120\C$\test" + @Date + ".csv" -T -c -t, -r \n -R'''

EXEC(@SQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

raaj
Posting Yak Master

129 Posts

Posted - 2013-07-12 : 01:49:14

Thanks for u r reply visakh.
But I am getting the follwing error :
Unknown argument '+' on command line.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 01:57:47

ah ...a typo
it should be ' and not "


DECLARE @SQL varchar(2000),@Date varchar(11)

SET @Date=CONVERT(varchar(11),GETDATE(),101)

SET @SQL='EXEC master..xp_cmdshell ''BCP "Select * from AdventureWorks.dbo.Employees" queryout "\\SERV120\C$\test' + @Date + '.csv" -T -c -t, -r \n -R'''

EXEC(@SQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

raaj
Posting Yak Master

129 Posts

Posted - 2013-07-12 : 02:04:38

visakh,
still getting the following error :

output
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 02:14:46

is the path \\SERV120\C$\ accessible from the machine where you're trying to execute this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

raaj
Posting Yak Master

129 Posts

Posted - 2013-07-12 : 02:18:37

yes definitely it is accessible.
Initially, when i ran the SQL query which I posted in the original post, it worked and when i went to that server, i am able to see the test.csv file.
But, after making the change to include @Date, it was giving that error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 02:22:00

ok can you make this small change and post the result obtained?


DECLARE @SQL varchar(2000),@Date varchar(11)

SET @Date=CONVERT(varchar(11),GETDATE(),101)

SET @SQL='EXEC master..xp_cmdshell ''BCP "Select * from AdventureWorks.dbo.Employees" queryout "\\SERV120\C$\test' + @Date + '.csv" -T -c -t, -r \n -R'''

PRINT(@SQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 02:27:05

ok i got the issue.try this instead


DECLARE @SQL varchar(2000),@Date varchar(11)

SET @Date=CONVERT(varchar(11),GETDATE(),112)

SET @SQL='EXEC master..xp_cmdshell ''BCP "Select * from AdventureWorks.dbo.Employees" queryout "\\SERV120\C$\test' + @Date + '.csv" -T -c -t, -r \n -R'''

EXEC(@SQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

raaj
Posting Yak Master

129 Posts

Posted - 2013-07-12 : 02:37:06

Thanks a lot visakh.
That worked perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 02:46:18

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

raaj
Posting Yak Master

129 Posts

Posted - 2013-09-05 : 02:32:09

Hi Visakh,

I just realised there is one more issue I am facing :

In one of my columns (column Name is ServiceName) there is a value called Plan, Environment, Liability
i.e the value is comma seperated.

Now, when the query is run in SQL, it is showing perfectly correct result set.
But in csv, the commas are disappearing and the value is splitting into three different cells.

Is there anyway that I can make the value appear as it is showing in the SQL resultset.

Thanks,
raaj

raaj
Posting Yak Master

129 Posts

Posted - 2013-09-05 : 02:32:45

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources