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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 question related to BCP when exporting to CSV
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raaj
Posting Yak Master

126 Posts

Posted - 07/12/2013 :  01:03:16  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/12/2013 :  01:44:07  Show Profile  Reply with Quote
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
Go to Top of Page

raaj
Posting Yak Master

126 Posts

Posted - 07/12/2013 :  01:49:14  Show Profile  Reply with Quote
Thanks for u r reply visakh.
But I am getting the follwing error :
Unknown argument '+' on command line.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/12/2013 :  01:57:47  Show Profile  Reply with Quote
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
Go to Top of Page

raaj
Posting Yak Master

126 Posts

Posted - 07/12/2013 :  02:04:38  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/12/2013 :  02:14:46  Show Profile  Reply with Quote
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
Go to Top of Page

raaj
Posting Yak Master

126 Posts

Posted - 07/12/2013 :  02:18:37  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/12/2013 :  02:22:00  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/12/2013 :  02:27:05  Show Profile  Reply with Quote
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
Go to Top of Page

raaj
Posting Yak Master

126 Posts

Posted - 07/12/2013 :  02:37:06  Show Profile  Reply with Quote
Thanks a lot visakh.
That worked perfectly.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/12/2013 :  02:46:18  Show Profile  Reply with Quote
you're welcome

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

raaj
Posting Yak Master

126 Posts

Posted - 09/05/2013 :  02:32:09  Show Profile  Reply with Quote
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
Go to Top of Page

raaj
Posting Yak Master

126 Posts

Posted - 09/05/2013 :  02:32:45  Show Profile  Reply with Quote
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
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.08 seconds. Powered By: Snitz Forums 2000