Extract data from SQL 6.5

I need to get some data from an enormous, creaky old SQL 6.5 database.
I know nothing about either the data schema (though I believe some sort
of documentation exists), nor 6.5 for that matter, having come to SQL
Server at 7.0.

My clients need the data in comma delimited format.

Please, can anyone suggest any possibilities? One thing that occurred
to me might be to create an Access application, use an ODBC link to the
SQL DB, and then leverage Access' not inconsiderable functionality to
get the data out.

Does anyone foresee any problems with this, or any better ways?

Forever in your debt.

Edward
--
The reading group's reading group:
http://www.bookgroup.org.ukI also came to SQL Server well after version 6.5 but I believe that 6.5
had a version of Enterprise Manager (EM) and that EM supported access
to the 6.5 equilivent of DTS. If so you should be able to use the tool
to do what you need without being 'knowledgable'.

Between the GUI and a manual I have managed to stumble through the
first time I have had to perform certain tasks.

HTH -- Mark D Powell --|||(teddysnips@.hotmail.com) writes:
> I need to get some data from an enormous, creaky old SQL 6.5 database.
> I know nothing about either the data schema (though I believe some sort
> of documentation exists), nor 6.5 for that matter, having come to SQL
> Server at 7.0.
> My clients need the data in comma delimited format.
> Please, can anyone suggest any possibilities? One thing that occurred
> to me might be to create an Access application, use an ODBC link to the
> SQL DB, and then leverage Access' not inconsiderable functionality to
> get the data out.
> Does anyone foresee any problems with this, or any better ways?

If you know Access and is comfortable with that, I guess it will work
fine.

You cold also use BCP, and you could use the BCP that comes with SQL
Server 2000. (I do seem recall that there were some problems when accessing
SQL 6.5 if the database+table names were too long.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Extract data from multiple oracle database

I have to extract data from 5 different oracle databases with same schema.This will be scheduled job.Can someone guide me.

Use a For Each loop to iterate through the database connections. Use data flows inside the For Each loop to copy the data.

You might want to start with the SSIS tutorials in Books Online, if this is your first time using SSIS.

sql

Exporting sp result to flat file using XML schema

Hi there,

To put in context, we are using BizTalk to get the result from a stored proc and export it to a text file using a XML schema (XSD). The XSD includes formatting info such as the justification, padding, etc. We are moving this process to SSIS and we want to reuse the BizTalk schemas.

Is it possible?

Thanks
Frantz

Just curious, why the move from BizTalk to SSIS? They each have their strengths, and I think for what you are doing, BizTalk is the way to go.|||

Phil, the decision to move from BizTalk to SSIS has been taken at an architectural level.
I think it's about performance issues. BizTalk generates XML during the process taking huge amounts of resources. I'm not sure...

Whether it should be done in SSIS or BizTalk... I think SSIS is way more faster at exporting data to a text file. The sp generates the data, SSIS creates the file. It's pretty straightforward. The only thing is we don't want to re-enter the whole formatting info since it's already in XSD files and wondering if it can be reused.

Thanks

|||I can't speak for BizTalk because I don't support that product, but I thought that is what it is designed for -- to move files around, among other things.

SSIS is great for working on the data as it passes through. Have you participated in the BizTalk forums at all? I'm not saying that one way or another is better, it's just that I think you'll be doing plenty of rework in moving exclusively to SSIS.|||

I have no experience with BizTalk at all...

I have already re-wrote a module initially created in BizTalk. The guys said it would take dozens of minutes for the process to complete and with DTS it generates the same files in a matter of minutes. The resulting flat files are up to 100MB so in XML format it was something like twice the size.

I don't know if it was bad design but they are experiencing really bad performance with BizTalk and their decision is already made...
Thanks anyways.

|||Why XML? Is the next system expecting an XML file? That seems to be an issue, to me, but again, it's the cards you've been dealt.|||

Phil Brammer wrote:

Why XML? Is the next system expecting an XML file? That seems to be an issue, to me, but again, it's the cards you've been dealt.

I have no idea! They're telling me it has to go through XML because it's the only way BizTalk can communicate between systems. I have never used BizTalk, I don't know if they have it completly wrong... I couldn't tell.

|||

fleo wrote:

I have no idea! They're telling me it has to go through XML because it's the only way BizTalk can communicate between systems. I have never used BizTalk, I don't know if they have it completly wrong... I couldn't tell.

They are partially right. The output of a BizTalk message is XML. If the destination system can't handle that XML file, BizTalk will have to convert it. No biggie. But they should be creating an output file appropriate for your use, I would believe. Again though, the performance issues are probably a question for the BizTalk forum.|||

Hi Phil,
The only appropriate output file is a flat file. There's no need to go through XML.

Suppose I create the best BizTalk orchestration possible for exporting a set of data to a text file, would it be as fast as a SSIS package?

My guess is that SSIS outperforms BizTalk on the benchmark.

|||

fleo wrote:

Hi Phil,
The only appropriate output file is a flat file. There's no need to go through XML.

Suppose I create the best BizTalk orchestration possible for exporting a set of data to a text file, would it be as fast as a SSIS package?

My guess is that SSIS outperforms BizTalk on the benchmark.

I would hope BizTalk is comparable. It is designed to move data from system to system. SSIS is designed to process data, not necessarily to move it.