Export to csv file - SQL Server Forums

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)

Export to csv file

Author

Topic

boonboon
Starting Member

5 Posts

Posted - 2013-10-10 : 01:48:02

i able to run this successfully in my sql job

declare @sql varchar(8000)
select @sql = 'bcp "SELECT* FROM [NTUCHC-LIVE].[dbo].[Shelflabel_Promotion_ESL]" queryout F:\PDT\ESL-Promotion.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

but I hit error when I try to run this.. is there anything went wrong? pls advise.. thanks!

declare @sql varchar(8000)
select @sql = 'bcp "SELECT
[No_]
,[PLU]
,[Item Family Description]
,[Description]
,[Description 2]
,[Remarks]
,[Offer Price]
,[Usual Price]
,[Quantity]
,[Starting Date]
,[Ending Date]
,[HSA Code]
,[Type]
FROM [NTUCHC-LIVE].[dbo].[Shelflabel_Promotion_ESL] Where
[Price Group] = 'TPO' OR [Price Group] = 'ALL'
" queryout F:\PDT\ESL-Promotion.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 03:58:56

yes. you've escape those single quotes coming inside.

like


declare @sql varchar(8000)
select @sql = 'bcp "SELECT
[No_]
,[PLU]
,[Item Family Description]
,[Description]
,[Description 2]
,[Remarks]
,[Offer Price]
,[Usual Price]
,[Quantity]
,[Starting Date]
,[Ending Date]
,[HSA Code]
,[Type]
FROM [NTUCHC-LIVE].[dbo].[Shelflabel_Promotion_ESL] Where 
[Price Group] = ''TPO'' OR [Price Group] = ''ALL''
" queryout F:\PDT\ESL-Promotion.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 04:00:16

also see

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

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

boonboon
Starting Member

5 Posts

Posted - 2013-10-17 : 07:52:08

quote:
Originally posted by visakh16

yes. you've escape those single quotes coming inside.

like

declare @sql varchar(8000)
select @sql = 'bcp "SELECT
[No_]
,[PLU]
,[Item Family Description]
,[Description]
,[Description 2]
,[Remarks]
,[Offer Price]
,[Usual Price]
,[Quantity]
,[Starting Date]
,[Ending Date]
,[HSA Code]
,[Type]
FROM [NTUCHC-LIVE].[dbo].[Shelflabel_Promotion_ESL] Where
[Price Group] = ''TPO'' OR [Price Group] = ''ALL''
" queryout F:\PDT\ESL-Promotion.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

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

it did not prompt any error but the file did not export out. any ideas why?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-17 : 08:14:00

do you've access to the path specified? Is it local machine path?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-17 : 08:15:57

try running this from command line and see if you get any error message


bcp "SELECT
[No_]
,[PLU]
,[Item Family Description]
,[Description]
,[Description 2]
,[Remarks]
,[Offer Price]
,[Usual Price]
,[Quantity]
,[Starting Date]
,[Ending Date]
,[HSA Code]
,[Type]
FROM [NTUCHC-LIVE].[dbo].[Shelflabel_Promotion_ESL] Where 
[Price Group] = 'TPO' OR [Price Group] = 'ALL'
" queryout "F:\PDT\ESL-Promotion.csv" -c -t, -T -S <YourServerName>

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources