[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

bcp utility to export tables?

Posted on 2009-02-23
11
Medium Priority
?
957 Views
Last Modified: 2013-11-26
Hi,

data Connection:  internal.mdf
table: locations

I've got a .mdf in an application built in VB.Net attached as a Data Connection in Visual Studio 2008

I'm trying to export a table or all the tables as a CSV files or a file that can be easily read by Access or excel.

can I use the bcp utility if so how? I'm stuck. Can I store it as a SPROC and then call it from a button click? Or can I can I just use it in VB in a button click?

Any help much appreciated,
0
Comment
Question by:davecocks
  • 6
  • 5
11 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 23709689
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
0
 
LVL 1

Author Comment

by:davecocks
ID: 23709795
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

Open in new window

0
 
LVL 22

Expert Comment

by:pivar
ID: 23709866
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:davecocks
ID: 23710050
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

Open in new window

0
 
LVL 22

Expert Comment

by:pivar
ID: 23710082
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,'

0
 
LVL 22

Expert Comment

by:pivar
ID: 23710086
A typo

Should be

ALTER PROCEDURE dbo.exportCSV
0
 
LVL 1

Author Comment

by:davecocks
ID: 23710194
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
0
 
LVL 22

Expert Comment

by:pivar
ID: 23710246
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'
0
 
LVL 1

Author Comment

by:davecocks
ID: 23710335
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'
0
 
LVL 22

Accepted Solution

by:
pivar earned 2000 total points
ID: 23710607
No, you must assign values to them, not remove them. But if it's a local server you could use a dot as servername and if you use windows authentication you could use "-T" instead of  "-Uusername -Ppassword".

EXEC master.dbo.xp_cmdshell 'bcp internal.dbo.locations out c:\temp\locations.csv -S. -T -c -t,'

0
 
LVL 1

Author Closing Comment

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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question