Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Exporting / Importing from/to SQL Express ! Urgent!!

Posted on 2006-05-05
18
Medium Priority
?
271 Views
Last Modified: 2009-12-16
Hi,
I need to know the functionality of SQL Express in relation to working with exports and imports of custom file formats (delimited).
I found a lot of mentions to DTS but this is not part of SQL express...
Is there a programmatic way to execute these functions...?
I think the import might work with  a bulk insert but what is the equivalent for the export?

Regards,
Plowmister.
0
Comment
Question by:Plowmister
  • 6
  • 5
  • 5
16 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 16615835
Hi Plowmister,

DTS is part of SQL Express its in SQL Server Business Intelligence Studio that replaces the previous version DTS wizard

its part of Microsoft SQL Server 2005 Express Edition Toolkit
source: http://msdn.microsoft.com/vstudio/express/sql/download/

hope this helps a bit
bruintje
0
 

Author Comment

by:Plowmister
ID: 16616303
I will check it out...Thanks I didn't realise this was available....

Plowmister...
0
 

Author Comment

by:Plowmister
ID: 16618773
Hi,
I installed both the Toolkit one and the Adv Features one but still don't know how to use/access the DTS???

Any idea?

Plowmister.
0
Technology Partners: 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 75

Expert Comment

by:Anthony Perkins
ID: 16619739
Unfortunately, neither DTS (which belongs to SQL Server 2000) or its new incarnation SSIS (SQL Server Integration Services ) is avaliable to SQL Sever Express you will have to upgrade to the full version.
0
 

Author Comment

by:Plowmister
ID: 16621498
So, in respect to my problem....can someone help with using a filesystem object (e.g. data123456.txt) and appending query data in to it?

I'm not really in a position to fork out a few thou for the added bonus of exporting data in the easier DTS way...there must be a programmatic way surely?

Plowmister.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16622248
>>can someone help with using a filesystem object <<
What is your front-end language, VB, .NET, Java, Delphi?
0
 

Author Comment

by:Plowmister
ID: 16625028
I use VB, ASP...
but is there a T-SQL procedure for accessing/creating (I/O) files?

Plowmister...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16625729
>>but is there a T-SQL procedure for accessing/creating (I/O) files?<<
No there is no T-SQL syntax for handling files.  However, since you are using SQL Server 2005 you should be able to access the .NET CLR to open files.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16625757
>>>is avaliable to SQL Sever Express you will have to upgrade to the full version

that is not entirely true, i downloaded the sql  server business intelligence studio as per above
and yes i ported my sql 2000 dts packages to the sql 2005 since they wouldn't run after updating my 2K db

i run these express tools because i have no access to the full version :)
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16625767
----------
Microsoft SQL Server 2005 Express Edition Toolkit (more...)

SQL Server Express Toolkit provides tools and resources to manage Microsoft SQL Server 2005 Express Edition, and to create reports by using SQL Server 2005 Reporting Services. The kit includes Connectivity Components, Business Intelligence Development Studio, Management Studio Express, and a Software Development Kit. To create reports by using Reporting Services, you must use Business Intelligence Development Studio.
----------

not sure why you can't access what i can, but i did a complete install with all the whistles and bells so also the sample db's for reporting services and other stuff
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16626790
bruintje,

>>downloaded the sql  server business intelligence studio as per above<<
When you download and install the tools to SQL Server Express you should have got the following message at the end:

<quote>
Business Intelligence Development Tools for Integration Services and Analysis Services
The version of Business Intelligence Development Tools that is included in SQL Server 2005 Express Edition Toolkit does not include projects for SQL Server 2005 Integration Services or SQL Server 2005 Analysis Services. These projects are available only with editions of SQL Server 2005 that include Integration Services and Analysis Services. SQL Server 2005 Express Edition does not include Integration Services or Analysis Services.

SQL Server 2005 Express Edition and Express Edition with Advanced Services
To download SQL Server 2005 Express Edition or SQL Server 2005 Express Edition with Advanced Services, see the SQL Server Development Center on MSDN (<http://go.microsoft.com/fwlink/?linkid=51793>).

Documentation and Samples
To install SQL Server 2005 Books Online, download them from the SQL Server Developer Center on MSDN (http://go.microsoft.com/fwlink/?linkid=51793).
</quote>

If you are saying that despite that you managed to install Integration Services on SQL Server Express, all I can say is more power to you and congratulations.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16631504
Thanks for you clarification, but i thought this question was related to DTS and DTS comes with "The version of Business Intelligence Development Tools that is included in SQL Server 2005 Express Edition Toolkit "

at least i was able to port my existing SQL 2000 DTS package to the SQL Express version

if i mistakenly thought this question was related to DTS in SQL Express then i've made a  mistake

for other functionality haven't looked, because i don't need it :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16644936
>> DTS comes with "The version of Business Intelligence Development Tools that is included in SQL Server 2005 Express Edition Toolkit "<<
You learn something new everyday at EE.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16646094
@acperkins, you're good ;-)

went back to my machine trying to determine what version it really was, the only real proof was the db size [17.5 GB]

forgot i already decided to install the msdn 2005 full version instead of SQL Express which was really my intention but because its limited to 4GB i chose for the full version

sorry for my mistake so i guess DTS is not in the express version and also not in the downloads on the Microsoft site

i'm an idiot :)
0
 

Author Comment

by:Plowmister
ID: 16653863
The Quest Continues...

...I did try to to use BCP to some extent it did part of what I was looking for (data export) but no great flexibility with formatting...so it's the BCP wordwrap method or nothing at the mo...


0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 16654573
bruintje,

No problem and thank you for clarifying.

Plowmister,

That is correct.  BCP is somewhat limited as to the formatting.  However, consider using a VIEW instead of a table and you should have better luck.
0

Featured Post

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!

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

581 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