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 2012 Forums
 Transact-SQL (2012)
 export .csv
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wided
Posting Yak Master

201 Posts

Posted - 02/18/2014 :  05:19:03  Show Profile  Reply with Quote
what is the SQL instrction that allows the export of a table. csv
by code and not directly from the explorer

example:
table1 (col1 int, col2 varchar (10), col3 decimal (15,3)

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/18/2014 :  11:15:49  Show Profile  Reply with Quote
To interact with the file system you need something other than straight t-sql.
possible options include:

the Management Studio - one of the import/export wizards or results to file with specific text result options set
SSIS package
CLR routine
third party csv writer (possibly MS Excel)
something ugly like from a dos cmd prompt use SQLCMD specifying a query, delimiter, and output file.

Be One with the Optimizer
TG
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/18/2014 :  13:08:41  Show Profile  Reply with Quote
bcp,OPENROWSET etc

see
http://visakhm.blogspot.in/2013/10/different-ways-to-export-sql-server.html

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

wided
Posting Yak Master

201 Posts

Posted - 02/20/2014 :  04:32:25  Show Profile  Reply with Quote
I tried this:

EXEC xp_cmdshell 'bcp "SELECT * BSIAZUR.DBO.ZANNEXE_EMP FROM " queryout "D:\manufacturer.txt" -S "AZUR-SQL8-R2\MUTUELLE" -T -c'

but I have this message

Msg 15281, Niveau 16, État 1, Procédure xp_cmdshell, Ligne 1
SQL Server a bloqué l'accès à procédure 'sys.xp_cmdshell' du composant 'xp_cmdshell', car ce composant est désactivé dans le cadre de la configuration de la sécurité du serveur. Un administrateur système peut activer l'utilisation de 'xp_cmdshell' via sp_configure. Pour plus d'informations sur l'activation de 'xp_cmdshell', voir "Configuration de la surface d'exposition" dans la documentation en ligne de SQL Server.

What should I do?

Dtatabsename = BSIAZUR
TableName = ZANNEXE_EMP
Go to Top of Page

wided
Posting Yak Master

201 Posts

Posted - 02/20/2014 :  04:34:56  Show Profile  Reply with Quote
ServerName = "AZUR-SQL8-R2\MUTUELLE"
Go to Top of Page

wided
Posting Yak Master

201 Posts

Posted - 02/20/2014 :  08:47:16  Show Profile  Reply with Quote

I Locate this:

RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

but i have this message:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist or is an advanced option.
Go to Top of Page

wided
Posting Yak Master

201 Posts

Posted - 02/20/2014 :  08:48:26  Show Profile  Reply with Quote
where I can find sp_configure (master, msdb..?)
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 02/20/2014 :  08:57:15  Show Profile  Reply with Quote
I think not in specific database. That is server-level configuration.. So you can run the script as usual...


Refer
http://blog.sqlauthority.com/2007/04/26/sql-server-enable-xp_cmdshell-using-sp_configure/


--
Chandu
Go to Top of Page

wided
Posting Yak Master

201 Posts

Posted - 02/20/2014 :  11:47:17  Show Profile  Reply with Quote
thanks

Go to Top of Page

wided
Posting Yak Master

201 Posts

Posted - 02/21/2014 :  06:05:42  Show Profile  Reply with Quote
it is possible that the output file should be. csv with a column separator (;)?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/21/2014 :  10:22:09  Show Profile  Reply with Quote
well you can name the file with whatever extension you want and you can format the file with any separator you want so yes, it's possible.
But in my opinion by convention a file with .csv extension should actually be a csv file.

Be One with the Optimizer
TG
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/23/2014 :  10:24:34  Show Profile  Reply with Quote
quote:
Originally posted by wided

it is possible that the output file should be. csv with a column separator (;)?


then it wont be comma seperated so cant be csv
You can configure it using registry setting in OPENROWSET

see
http://visakhm.blogspot.in/2013/10/different-ways-to-export-sql-server.html


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.09 seconds. Powered By: Snitz Forums 2000