After some weeks evaluating tools and platforms for developing an application, I decided to move to SQL Server 2005 Express Edition. Everything was fine till last night, when after creating my tables, I needed to populate them. I tried to find the Import and Export Data Wizard that SQL Server 7.0 and 2000 used to have, but great was my surprise when I found - in this forum - a post that said that it's not available in the Express Edition.
I'll have to move back in time (what I hate) to remember the way BCP worked. Can somebody post some examples to not start from zero ? Does anybody know a third party visual tool that can import/export data from text files to a SQL Server DB via ODBC ?
What's the reason it was not included in the EE ?
THanks !
Hi,
BCP is documented in Books Online.
SQL Express is a free product and not all features are included. In this version they removed the Import/Export wizard for a number of reasons. We're looking at alternative soluitons for a future version of SQL Express.
Mike Wachal
SQL Express team
Hi,
First off, please excuse the lack of specific detail but it's been a while since I installed this feature -
there is an import/export facility in SQL Server Express edition but it's in the "Advanced Features/Services" version.
You have to do a "custom" install and the import feature is buried in one of the tree feature diagrams and is NOT selected by default.
Open all the feature trees and check out the hints for each of the features to find the one you're after - it's definitely there 'cos I've used it to import data from an Excel workbook and from Access.
Really simple to use and very effective!
Best of luck!
|||Hi Steve,
There is no supported Import/Export functionality in any version of SQL Express. Some people have managed to get the old Import/Export wizard onto their computers, but it has not been tested and it is not supported. If you go down this path, you do so at your own risk. BCP on the other hand is tested and supported.
Mike
|||Steve,
thanks a lot for your coment. I'll give it a try if I don't succeed with BCP. My deadline is almost over and I can't make BCP work (I'm getting some memory allocation errors whn running it).
MIke,
do you have any other reference to BCP ? I need some working examples rather than the explanation of every parameter.
Other thing I need to know is how can I check the current version I have installed. Before starting this thread, I had just installed SQL Server 2005 Express Edition complete (with Managment Studio, Advanced Services and Toolkit). A couple of days ago, I noticed there was a SP2 and installed it over the older one and I generated an Hybrid that didn't work, so I removed all the installations (restarted) and then installed just SQL Server 2005 Express Edition and Management Studio (nothing else yet).
THanks !
|||Go into Add/Remove Programs, find Microsoft SQL Server 2005 and click Change. When the wizard opens, there should be a Report button, clicking this will give you a report of everything you have installed and what version it is.
Try these references for using BCP:
http://support.microsoft.com/default.aspx/kb/67409
http://www.devarticles.com/c/a/SQL-Server/An-Introduction-To-The-Bulk-Copy-Utility/
Mike
|||Mike,
thanks for letting me know the way to check version. I'll check the BCP links now.
Before getting this answer, I tried the following (I'm a bit desperate ):
Sent all the txt files to a friend and he imported to a SLQ Server 2k database and then he sent it back to me. After that, I ran the following in my SQL Server 2005 EE :
CREATE DATABASE Bolsa ON
(FILENAME = 'u:\Bolsa de Santiago\CD Bolsa\Bolsa_Data.MDF'),
(FILENAME = 'u:\Bolsa de Santiago\CD Bolsa\Bolsa_Log.ldf')
FOR ATTACH;
GO
and got this error.
Msg 3415, Level 16, State 1, Line 1
Database 'Bolsa' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.
Any hint on how to fix it ?
Thanks !
|||
Not sure what the U: drive is for you, if that is actually the CD drive, you will need to copy the files to a different directory since CDs are read-only. If that is not yoru CD drive, try...
Take a look at the properties of the files (right-click | Properties) to see if they are set to Read-only.
One final note, SQL Express does not support attaching files from network locations or mapped drives, they have to be on the local hard drive. Again, just wondering about that U: drive.
Mike
|||Mike,
before posting the above, I checked the files weren't Read Only and they are not in a CD (also no network here). U: drive for me is a 4GB NTFS partition from my master HDD. Could be a problem if it's not on a FAT32 drive ? I'm running Windows XP Home Edition SP2 (spanish) and SQL Server 2005 Express Edition SP2 (english).
Bye !
|||The KB article at http://support.microsoft.com/kb/931640 suggests that this issue is actually caused by a setting on the files created on your friends SQL 2K server. It seems like you'll need to check the properties of the database server your friend used to create the files.
It might be faster to finish down the path of figuring out your BCP issues rather than start troubleshooting a completely different problem.
Mike
|||Hi folks,
with all deference to your knowledge, Mike, I have DTS installed on my (newish) PC and have only ever installed SQL Express with Advanced Features and the Toolkit..
Admittedly, it took some finding, but there it is at "C:\Program Files\Microsoft SQL Server\90\Binn\DTS\DTSWizard.exe".
From a pragmatic point of view, with deadlines approaching, perhaps rrudolph should try (custom) installing the Toolkit and checking out ALL the component trees?
Regards to all
Steve
|||Hi Steve,
I'm just telling you that using that wizard in Express has not been tested, nor is it supported, so you use it at your own risk.
The amount of work in a project is the sum of the work to do it the first time and then to do it over to fix your mistakes. I find using unsupported technology is usually one of those mistakes that needs to be fixed later.
Mike
|||Mike,
the good news are that finally made the BCP work.
I have uploaded most of the data I needed. Just missing some files that when uploading them to the database, I noticed they contained had corrupted data. Thanks a lot for your helpful tips, links and directions. (hope next time I have to deal with BCP, I have no problems).
Steve,
I'm planning, after ending this project to re-install everything from scratch and then I will try to add the Wizard you mentioned. My doubt is last Thursday I started both installation programs ("SQL Server 2005 Express Edition with Advanced Services SP2" and "Microsoft SQL Server 2005 Express Edition Toolkit SP2") but canceled them before they modify my curent config. In none of them I was able to find the Import and Export Wizard you mentioned. I opened all the branches I found in the custom setup. When you installed yours, did you use the SP2 installation files (it's fresh from February 2007) or the ones from previous release ?
Thanks a lot !
|||Rrudolph,
glad you got BCP running ok.
I'm still running SP1. I think(?) the wizard was in the Toolkit..!
However, if you have cracked the problems with BCP you might want to stick with that - as Mike correctly points out this DTS seems to be an un-supported feature (though why it should be so is a mystery).
I'm in the fortunate position of producing small, bespoke stuff where speed/ease of development is more important to my boss than anything else so can "risk" going with DTS (which, I'll admit, works for me like a dream).
OK, this doesn't seem to be full-blown DTS from SQL2000/2005 but it's sure good enough for my requirements.
Regards
Steve
ps - if you want me to find exactly where DTS was contact me and I'll re-install on my home PC to see where everything is.
|||Steve,
thanks a lot for the offer.
I've just modified my profile to make my email addy visible. Email me to this one.
THanks !
No comments:
Post a Comment