Importing from PL/SQL to MSSQL

Hi there,

I've done some searching around on this subject and can't find
anything helpful or non-expensive so far.

I need to export some tables from my companies Oracle server locally
so I can work on a reporting tool which (hopefully) will speed some
things up on our weekly report runs. I know I could just use an ODBC
link and get the data directly from our server but unfortunatly it's
not the newest or fastest of things and these are pretty big reports,
so my idea is to export the tables and run the queries through an
MSSQL database on a faster machine.

However, not being an Oracle or MSSQL developer I'm not too sure how
to export the tables I need. I've looked all over for a script or a
tool to help me do this, but all I have found are tools that cost a
hell of a lot of money, and for something that I don't even know will
work just yet, I don't want to put a PO through for nothing.

I was wondering if anyone has ever used a script that is available or
a GPL'd tool that might help me out here? Or at least point me in the
direction so that I can get started on this.

Thanks in advance for any suggestions

--
StuartIIRC there is a SPOOL command that will redirect the ouput of a SELECT
statement to a file. It should be fairly easy to import that to MSSQL
using DTS or BCP. However, there are probably several ways of doing
this and I'm not an Oracle expert so for better advice you may want to
repost in an Oracle group.

--
David Portas
SQL Server MVP
--|||SQL Server DTS is your best bet for this.

Importing data using WebService

Hello,

i'm searching a way to update data using a web service.

For example, i want to store the temprature of cities in a database. I have a table with all the cities and an other table storing the temperature.

I have access to à web service that give temperature for the cities...

i hope you understand what i want to do... ;-)

thanks a lotThe web service task can access the web service and store the SOAP envelope as an XML file locally. You can then access the XML file in a data flow task using the XML Source Adapter and use the data in your data-flow as you would any other data.

-Jamie|||Ok !

Where can i find a tutorial for using webservice task ?|||BOL should always be your first port of call. Try ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/5c7206f1-7d6a-4923-8dff-3c4912da4157.htm and if there isn't enough there to help use the "Send Feedback" link.

-Jamie|||It's not clear from the question whether the web service gives you address for a specific city or for all cities. Likely it's the former in which case you'd likely want to call the web service through the script component that wraps the SOAP call and does a little of it's own caching to avoid redundant web service calls.

If the shoe fits, feel free to wear it.|||Hi all,

I am using the web service task with XML source to insert data from a web service into an SQL Server database. But I have a problem with the XML output from the Web Service Task. It looks li this:
<?xml version="1.0" encoding="utf-16"?>
<ArrayOfBooking xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
</ArrayOfBooking>

and because of this xmlns="http://tempuri.org/" that is met almost everywhere I cannot create a proper XSD Schema , that is needed for the XML source. If I remove this xmlns="http://tempuri.org/" , then everything works fine . The web service I am calling has this namespace xmlns="http://tempuri.org/" in the [WebService(Namespace = "http://tempuri.org/")] . I am writing the web service so I can change it as well if this is needed. Do you have any ideas how to solve this xmlns="http://tempuri.org/" problem?

Thanks in advance.

Regards,
pc

Importing data using WebService

Hello,

i'm searching a way to update data using a web service.

For example, i want to store the temprature of cities in a database. I have a table with all the cities and an other table storing the temperature.

I have access to à web service that give temperature for the cities...

i hope you understand what i want to do... ;-)

thanks a lotThe web service task can access the web service and store the SOAP envelope as an XML file locally. You can then access the XML file in a data flow task using the XML Source Adapter and use the data in your data-flow as you would any other data.

-Jamie|||Ok !

Where can i find a tutorial for using webservice task ?|||BOL should always be your first port of call. Try ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/5c7206f1-7d6a-4923-8dff-3c4912da4157.htm and if there isn't enough there to help use the "Send Feedback" link.

-Jamie|||It's not clear from the question whether the web service gives you address for a specific city or for all cities. Likely it's the former in which case you'd likely want to call the web service through the script component that wraps the SOAP call and does a little of it's own caching to avoid redundant web service calls.

If the shoe fits, feel free to wear it.|||Hi all,

I am using the web service task with XML source to insert data from a web service into an SQL Server database. But I have a problem with the XML output from the Web Service Task. It looks li this:
<?xml version="1.0" encoding="utf-16"?>
<ArrayOfBooking xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
</ArrayOfBooking>

and because of this xmlns="http://tempuri.org/" that is met almost everywhere I cannot create a proper XSD Schema , that is needed for the XML source. If I remove this xmlns="http://tempuri.org/" , then everything works fine . The web service I am calling has this namespace xmlns="http://tempuri.org/" in the [WebService(Namespace = "http://tempuri.org/")] . I am writing the web service so I can change it as well if this is needed. Do you have any ideas how to solve this xmlns="http://tempuri.org/" problem?

Thanks in advance.

Regards,
pc