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