bcp utility to export tables?

Hi,

You can use bcp to create a csv file. The param -t specifies fieldterminator.

bcp dbname.schemaname.tablename out filename -Sservername -Uusername -Ppassword -c -t,

/peter

hi Pivar,

Thanks for your reply

So do I run this in a SPROC? Do I need to refer to anything in the Servername / username / password if the .mdf isn't on a server i.e. is a local database

Thanks

ALTER PROCEEDURE dbo.exportCSV
 
AS
 
BCP internal.mdf.locations OUT C:\\locations.csv -Sservername -Uusername -Ppassword -c -t,
 
RETURN

Select allOpen in new window

No, bcp is an application. If you want to run it in a sproc you have to use

EXEC master.dbo.xp_cmdshell 'bcp dbname.schemaname.tablename out filename -Sservername -Uusername -Ppassword -c -t,'

But there are some securityimplication with xp_cmdshell, it may be disabled by admin.

By the way, there are more formatting possibilites with bcp see http://msdn.microsoft.com/en-us/library/ms162802.aspx

ok, cool,

whats the schema name?

I found this on a related link:
'A schema is a container that holds tables, views, procedures, and so on'

I'm not sure if I'm using one. Where do I find it?

ALTER PROCEEDURE dbo.exportCSV
 
AS
 
EXEC master.dbo.xp_cmdshell BCP internal.?? .locations OUT C:\\locations.csv -Sservername -Uusername -Ppassword -c -t,
 
RETURN

Select allOpen in new window

Standard schemaname would be dbo, but you can omit it. The syntax should look like this:

ALTER PROCEEDURE dbo.exportCSV
AS
  EXEC master.dbo.xp_cmdshell 'bcp dbname.dbo.tablename out filename -Sservername -Uusername -Ppassword -c -t,'
RETURN

Or
  EXEC master.dbo.xp_cmdshell 'bcp dbname..tablename out filename -Sservername -Uusername -Ppassword -c -t,'

A typo

Should be

ALTER PROCEDURE dbo.exportCSV

Hi Pivar, thanks loads for your help.

I'm executing the SPROC but not seeing a file. Where am I going wrong?


ALTER PROCEEDURE dbo.exportCSV
AS
  EXEC master.dbo.xp_cmdshell 'bcp internal.dbo.locations out locations.csv -Sservername -Uusername -Ppassword -c -t,'
RETURN

You probably have to specify which path you want to save the file to. I suppose you changed the server/user/pw info as well?

EXEC master.dbo.xp_cmdshell 'bcp internal.dbo.locations out c:\temp\locations.csv -Sservername -Uusername -Ppassword -c -t,'

You can also redirect error and output

EXEC master.dbo.xp_cmdshell 'bcp internal.dbo.locations out c:\temp\locations.csv -Sservername -Uusername -Ppassword -c -t, -ec:\temp\errors.txt
-oc:\temp\output.txt'

Hi Pivar,

Sorry, I'm still new to Visual Studio and SQL server, so appologies for having to walk me through this.

The database is local i.e. not on a server.

What would you put there in this instance. Some of the examples on the link you posted don't make reference to -Sservername -Uusername -Ppassword.

Thanks again, your times really appreciated.

Removing them doesn't work:

EXEC master.dbo.xp_cmdshell 'bcp internal.dbo.locations out c:\temp\locations.csv -c -t, -ec:\temp\errors.txt
-oc:\temp\output.txt'

Hi Pivar thanks for your help. I think its working now but the admin issues you mentioned have come back to haunt me.