BCP unility and SQL Server Management Studio Express

I ahve a utility which uses the bulk copy untility (or program) bcp.exe
which doesn't seem to be part of SQL Server Management Studio Express. I
don't need a database server on that machine but I do want the bcp.exe
program. How can I get this?
Many thanks for any help in advance!
Peter (peteATkapiti.co.nz) writes:
> I ahve a utility which uses the bulk copy untility (or program) bcp.exe
> which doesn't seem to be part of SQL Server Management Studio Express. I
> don't need a database server on that machine but I do want the bcp.exe
> program. How can I get this?
BCP comes with SQL Express. I don't remember off-hand if SQL Express
offers a client-only install, but you could try running the install and
see if you get that offer. Just make sure press all Advanced buttons
you see.
I guess you also could cull it from a an existing installation. You
would have to install SQL Native Client (which is available as a separate
install), but if you are lucky that maybe all. (Or have I seen a
BCP.rll somewhere?)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I had to install the SQL Express Toolkit to get bcp.exe.
But I now have a really strange situation I wonder if anyone can answer...
Just installed on Vista Ultimate.
At first I couldn't find BCP in what I thought should be an expected
directory (A bin directory in the SQL Express folder tree).
Instead, after doing a Vista "Search" on the entire SQL Server tree, it's
supposed to be in
...\Program Files\Microsoft SQL Server\90\Tools
But, when I browsed to that location, there are no files, just folders.
Re-launched Explorer to run "As Administrator" (which itself is an irritant)
in case files were hidden, but not so.
Started manually opening up folders and I found it in the same folder as
bcp.rll
...\Program Files\Microsoft SQL Server\90\Tools\Binn
Why the weirdness? This is the first time I've run a Microsoft OS Search for
over 15 years on anything which returned bad results like this.
Tony Su
www.su-networking.com
ISA
SBS
Enterprise Mobile Solutions Architect
"Erland Sommarskog" wrote:

> Peter (peteATkapiti.co.nz) writes:
> BCP comes with SQL Express. I don't remember off-hand if SQL Express
> offers a client-only install, but you could try running the install and
> see if you get that offer. Just make sure press all Advanced buttons
> you see.
> I guess you also could cull it from a an existing installation. You
> would have to install SQL Native Client (which is available as a separate
> install), but if you are lucky that maybe all. (Or have I seen a
> BCP.rll somewhere?)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Tony Su (TonySu@.discussions.microsoft.com) writes:
> I had to install the SQL Express Toolkit to get bcp.exe.
> But I now have a really strange situation I wonder if anyone can answer...
> Just installed on Vista Ultimate.
> At first I couldn't find BCP in what I thought should be an expected
> directory (A bin directory in the SQL Express folder tree).
> Instead, after doing a Vista "Search" on the entire SQL Server tree, it's
> supposed to be in
> ..\Program Files\Microsoft SQL Server\90\Tools
> But, when I browsed to that location, there are no files, just folders.
> Re-launched Explorer to run "As Administrator" (which itself is an
> irritant) in case files were hidden, but not so.
> Started manually opening up folders and I found it in the same folder as
> bcp.rll
> ..\Program Files\Microsoft SQL Server\90\Tools\Binn
> Why the weirdness? This is the first time I've run a Microsoft OS Search
> for over 15 years on anything which returned bad results like this.
It appears that your question is more about the search in Vista, and
I'm afraid I can't help with that. The search in Vista is very mysterious,
and nothing use unless I'm in desperation.
Tools\Binn is indeed the place..
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

BCP Syntax

Please I need some help with bcp syntax. I have the default installation of SQL Express on my system. Also, I'm using windows authentication. Here are some syntax tried:

bcp database.dbo.table out "c:\table.dat" -U? -P? -c

I'm not sure what the user name and password should be but I've tried my windows user id and password without any success.

bcp database.dbo.table out "c:\table.dat" -T -c

I'm not sure if this is a trusted connection,

Do I have to be in App_data directory where my database reside.

There are 2 authentication modes that bcp supports:

a) SQL Server authentication in which case you have to specify your SQL Server login and password, say your SQL Server login ID is foo and password is bar then you should specify -Ufoo -Pbar.

b) If you can login to SQL Server using Windows authentication, then just specify -T and it will used Trusted connection.

Thanks

BCP in SQL SERVER 2005

Hello folks!
I have installed SQL Server 2005 Express and SQL Server Management Studio Express. Everything looks ok and works fine, but I can't find the Data Import/Export Wizard. Can anyone tell me how to Import/Export data?

Thank you!!!

SQL Server 2005 Express Manager does not offer Import/Export functionality as it is dependent on SQL Server Integration Services (SSIS) and SQL Server Agent. You will need to optain the full version of SQL Server Management Studio.

Peter

|||You can you other means to move data in and out of SQL Express that don't rely on SSIS. BCP is one such tool, you can find more information about BCP at http://msdn2.microsoft.com/en-us/library/ms162802.aspx.

If you are working with data in Access, you can use the functionality of Access to directly export the data into SQL Server. Most other programs can generate data in a format that BCP can accept.

Mike|||

I have to say, I have been developing for many years. This time I am truely disappointed in MS. I avoid say this kind of thing, and come to MS's defense almost always. Never bit into the hand that feeds you (karma-ish).

How am I supposed to learn this great technology between jobs, or when I work for a cheap start up company.

We (your users) can not easily import data into this new version of SQL Server. If you are going to put it out there as education; at least make it useful.

I just don't get it. I appologize for sounding disenchanted; but I have become accustom to being able to depend on MS products atleast being usable. For completeness sake maybe I am missing something. And do not say bcp, command lines went out years ago, that iss what Microsoft is founded on.

Enlighten me please; I fear I have just taken the first step to becoming one of them, the na sayers. Prove me wrong. After all that expression of opinion, I hope you find it heartfelt and consructive.

I look forward to your response. Please tell me there is a quick windows orienent approach, not some prehistoric utility.

MKruz

|||

Michael,

Agree with you - what a big disappointment. I'm sure that there are many of us who have data that we would like to bring into a SQL Server Express table and work with - it's natural form being a text or Excel base that comes from a bank or some other source. All the functionality in the world is of little use if the basic data import is not there.

Bill Sullivan

|||I totally agree, SQL 2005 Express is useless if you can't import/export data from a GUI. I'll move back to SQL2000 and DTS until it is fixed.|||

I really want to find out what the missing feature is, there was no GUI for importing excel files in MSDE and there is not one in Express, which is the equivalent SKU.

You can use the eval version for free to eval features, or if it is just for development then the dev edition is < 50 USD.

|||

Yeah, after all the hoopla surrounding the Visual Studio release man I'm bummed out about this. Guess there always has to be a catch. I was excited, I had a program idea mulling in my head for a few years and with Visual Studio I saw a chance to bring it to life.

But for my idea to come to life I need access to Access databases! (Isn't that why it's called "Access"?..) Oh well, as this n00by knows, if there is a will there is a way! Hope I don't bump into my much of this 'fine print' on my travels...

Luke

|||I disagree that command lines went out years ago. They are still the most expedient way to accomplish a whole host of tasks, and I use them daily.

That said, my 30-second review of the bcp link does suggest that importing data into a table for SQL Express may be more complicated than just getting it into CSV format. I'll reserve judgment until I've actually given it a try.
|||There is a company that makes a whole bunch of db converters. I just used one of them to convert from Access to MS SQL Express:
http://www.convert-in.com/acc2mss.htm

They have a demo that will convert the first five records from each of your tables if you'd like to try it out before you buy.
|||

If you have MS Access, you can simply export directly to your SQL Server data base by creating an ODBC link. You will have to export the tables from MS access one at a time but once the ODBC link has been created it takes only seconds to export individual tables.

Before you start exporting make sure you have a data base in SQL where you want to export to.

Open your data base in access

Right click on the table you want to export

Select export

A dialog box opens...

at the bottom of this dialog box there is a "Save as Type" drop down box

select it and scroll to the bottom you will find "ODBC Databases () "

select it

Give your table a new name, or leave as is, if it that suits you.... Click OK

A Select Data Source dialog box opens..... Click the "New" button

A create new data source dialog opens

Scroll to the bottom of that box and select SQL Server

click NEXT

give your data source a name...... what ever you like.

click NEXT.... click FINISH

a 'Create New Data Source to SQL Sever' dialog box opens

Give your connection a description

select or type the name of your server in the Server drop down box

Click NEXT...... Click NEXT......

check the 'change the default data base to' check box

select the data base where you want the export your table to.

Click NEXT.... click Finish.

That's all. Now you can select each table from you access data base then select

Export.......

ODBC databases ()

now you can select the data source you created then click OK

return to SQL Management Studio Express and if it was already open be sure to refresh or you won't see the newly imported table.

Hope this is helpful

|||

Hello Everyone,

Just after reading this form, I tried out some import/ export operation like below

SQL 2000 - SQL 2005 Express (Import/Export)

Excel - SQL 2005 Express (Import/Export)

It worked out very fine. I just used the DTS in SQL 2000 to do this. Any comments on this.

Thanks

Swami

|||Hello,
Try:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe
Worked for me!
|||

Wolfey305 wrote:

If you have MS Access, you can simply export directly to your SQL Server data base by creating an ODBC link. You will have to export the tables from MS access one at a time but once the ODBC link has been created it takes only seconds to export individual tables.

Before you start exporting make sure you have a data base in SQL where you want to export to.

Open your data base in access

Right click on the table you want to export

Select export

A dialog box opens...

at the bottom of this dialog box there is a "Save as Type" drop down box

select it and scroll to the bottom you will find "ODBC Databases () "

select it

Give your table a new name, or leave as is, if it that suits you.... Click OK

A Select Data Source dialog box opens..... Click the "New" button

A create new data source dialog opens

Scroll to the bottom of that box and select SQL Server

click NEXT

give your data source a name...... what ever you like.

click NEXT.... click FINISH

a 'Create New Data Source to SQL Sever' dialog box opens

Give your connection a description

select or type the name of your server in the Server drop down box

Click NEXT...... Click NEXT......

check the 'change the default data base to' check box

select the data base where you want the export your table to.

Click NEXT.... click Finish.

That's all. Now you can select each table from you access data base then select

Export.......

ODBC databases ()

now you can select the data source you created then click OK

return to SQL Management Studio Express and if it was already open be sure to refresh or you won't see the newly imported table.

Hope this is helpful

If COnnection Failed..what I want to do?

|||Hi swami,

Could you pls give me more info as to how to run DTS.

I have installed Sql server 2005 Ex Ed.
Is it possible to install only the DTS from SQL 2000.

Tnx

BCM with accounting 2007

I have bcm with outlook 2007 and accounting express. when i create an opportunity in bcm, everything is fine. when i click the convert to quote button, it dumps all of the item information (quantity, name, description, price, tax....) into the description field in accounting. Then I have to copy each piece out of the description field into it's appropriate field in accounting 2007 express. Where is the mapping for these fields, so that I can map each one correctly? Is there something else i should be doing?This does not appear related to Setup & Upgrade

Batch execute of SQL script from ADO.Net

I have a VB.Net application that needs to create about 5 stored
procedures and a couple of functions on SQL Server 2005 Express.
Currently I can execute all of them in one window of Sql Server
Management Studio, just separate each of them with a "GO" statement. Is
there a way I can accomplish this "one shot" approach via ADO.Net in my
application? If so, I can just put all my ddl SQL code in a text file
as an embedded resource, and then execute it in a couple of lines of
code. However, I suspect that I need to execute each ddl statement
separately and thus will need to parse the text file to break it up, or
break the sql code into multiple files, one for each stored proc.
Thanks for your thoughts,
Marcus[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
Marcus (holysmokes99@.hotmail.com) writes:
> I have a VB.Net application that needs to create about 5 stored
> procedures and a couple of functions on SQL Server 2005 Express.
> Currently I can execute all of them in one window of Sql Server
> Management Studio, just separate each of them with a "GO" statement. Is
> there a way I can accomplish this "one shot" approach via ADO.Net in my
> application? If so, I can just put all my ddl SQL code in a text file
> as an embedded resource, and then execute it in a couple of lines of
> code. However, I suspect that I need to execute each ddl statement
> separately and thus will need to parse the text file to break it up, or
> break the sql code into multiple files, one for each stored proc.
Yes, if you read this file from your own application, you will need to
parse the file for "go" and send down batch by batch with ExcecuteNonQuery.
Parsing the file for "go" is a trivial matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Basic, perhaps silly SQLServer 2005 question

I've previously used version 7 and 2000.

I've recently started to learn ASP.net. I've installed
Visual Web Developer 2005 Express and this includes
SQLServer 2005 in the installation. The task manager
and and the list of services definitely show I've got
SQLServer running on my computer.

Here's the problem: how do I actually use it? I can't
find any tools to create and access databases.

I've tried installing the Enterprise Manager from 2000
but this gives me an error message saying I must use the 2005
tools to connect.

So, is there an Enterprise Manager (or similar) for 2005?
Where do I find it?Download Microsoft SQL Server Management Studio Express from
http://www.microsoft.com/downloads/...&DisplayLang=en

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145465194.532705.252780@.u72g2000cwu.googlegr oups.com...
> Download Microsoft SQL Server Management Studio Express from
> http://www.microsoft.com/downloads/...&DisplayLang=en

Thanks, thats great

Basic SQL Server Express Questions

I'm a long time user of SQL Server 2000 but no knowledge of 2005 Express.

Among other things I support some small websites that are hosted at very low cost. The host currently provides version 1.1 of the dot net framework and MySQL v4 is standard. I need to add database capability to one of the sites. I'm looking at MySQL but curious if maybe 2005 Express would not work.

Can I simply deploy a 2005 Express (similar to msde) database with my web app (asp.net 2003) on a win2k server? Or will 2005 Express only run on win2003 with framework 2.0? Also, what are the practical user limits for a express 2005 database? I may need to support as many as 5 concurrent users. Will Express 2005 handle that?

ThanksThe express edition of sql server 2005 is far superior to the old MSDE. The performance governor is gone! Express edition will run on Windows XP SP2, Windows 2000 SP4, and Windows 2003. The .NET Framework is required for all editions of SQL Server 2005. However, you do not have to convert your website to the 2.0 framework. The 1.1 framework should connect to the SQL Express just fine. Also, 5 concurrent database connections should not be a problem. I strongly recommend using database connection pooling with ASP.net. Good Luck!|||Thanks Jon that was very helpful. I'd prefer to spend the time learning more about SQL server 2005 versus MySQL.

One more question... or two. I currently use Visual Studio 2003.net. If I need version 2.0 of the framework for db development, do I also need to develop the web app with Visual Studio 2005?

My biggest concern is the host and the framework limitation. From your response and what I've read, sounds like I need to install version 2 of the framework to install and develop with SQL Server 2005 express. To avoid the need for framework 2.0 on the web server/host then don't I need to develop the web app with Visual Studio .Net 2003? Assuming I'm on the right track here, what about deployment? I read that 2005 will package 2005 Express along with the app. Will Visual Studio 2003 do the same?

Thanks again

Mardy|||Here is a forum thread that discusses different versions of .NET Framework and Visual Studio: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=123637&SiteID=1.

If you build with .NET 2.0 and SQL Express 2005, your host will need to support those. Fortunately, there's a good list of hosters already doing this - you can see them at http://www.asp.net/hosters/ then click on the "ASP.NET 2.0" and "SQL 2005" links on the right side under Quick Hoster Search.