Oracle DBA to learn MS SQL: shared

Showing posts with label shared. Show all posts

Monday, March 19, 2012

Oracle with Reporting Services

I am trying to connect to an Oracle database using Reporting Services.
I create a new Shared Data Source, give it a name, then hit the edit
button. Set the provider to Microsoft OLE DB Provider for Oracle, on
connection tab I enter
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = server.somewhere.com) (PORT=99999)) (CONNECT_DATA = (SID = ServiceName)))
I enter the username and password, hit the test and everything tests
out ok. I ok out, go to create a new report and when I go to use the
DataSource I get an error of
A connection cannot be made to the database.
Set and test the connection string.
System.Data.OracleClient requires Oracle client software version 8.1.7
or greater.
Any help would be appreciated. Thanks.OK, are you using RS 2000. Here is how it works in RS 2000. When creating
the query it uses OLE DB if you are using the graphical editor (4 panes). If
you use the generic it uses the dotnet provider. The generic is 2 panes
(there is a button to switch to generic to the right of the ...). When
running the report it uses the dotnet provider. The reason for this was that
VS 2003 query designer did not have support for the dotnet provider so they
did this workaround. The dotnet provider requires 8.1.7 client to be
installed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jdcamp" <camsam50@.hotmail.com> wrote in message
news:1145550738.077059.160290@.t31g2000cwb.googlegroups.com...
>I am trying to connect to an Oracle database using Reporting Services.
> I create a new Shared Data Source, give it a name, then hit the edit
> button. Set the provider to Microsoft OLE DB Provider for Oracle, on
> connection tab I enter
> (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST => server.somewhere.com) (PORT=99999)) (CONNECT_DATA = (SID => ServiceName)))
> I enter the username and password, hit the test and everything tests
> out ok. I ok out, go to create a new report and when I go to use the
> DataSource I get an error of
> A connection cannot be made to the database.
> Set and test the connection string.
> System.Data.OracleClient requires Oracle client software version 8.1.7
> or greater.
> Any help would be appreciated. Thanks.
>|||I ran into this exact same problem yesterday...you need to install the
Oracle Client Tools on the report server. w/o the client tools your
windows box doesn't natively understand how to connect to the oracle
db.
After you install the Oracle Client on the report server you will need
to update your tnsnames.ora and sqlnet.ora files with the correct host
settings. Your Oracle dba's should have these but here's some examples
to get you going in case they dont...
If you use the default install of the client tools you will find these
files in the following location (C:\oracle\ora90\network\ADMIN)
tnsnames.ora example
================================<Server Alias> = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server.somewhere.com)(PORT =9999))
)
(CONNECT_DATA = (SERVICE_NAME = <ServiceName>)
)
)
sqlnet.ora example
================================SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)
Hope this helps!
Cheers
--
Ben Sullins

Monday, March 12, 2012

Oracle to SQL Server Migration using Server Migration Assistant for Oracle V2.0 - Help Needed

Hi all,

I am basically a front end guy and we need to do a dB migration from Oracle 9i to SQL Server (sitting in a shared environment).

For the start we wanna try out this tool. This tool provided by the MSN comes along with an extension pack that needs to be installed along with the tool.

http://www.microsoft.com/downloads/details.aspx?FamilyId=E35CEE88-C919-463F-B020-81468CD231DA&displaylang=en

It’s mentioned there that

Server component installation:
Locate a SQL Server machine which you want to migrate to. Run SSMAExtPack.exe on that machine locally.It will install a sysdb database with procedure and functions that emulate some of the Oracle system packages. You don't need to do anything on the server machine. All conversion tasks are done from the client SSMA tool.

Concern:

Since ours is a SQL server in a distributed environment it wont be possible for us to install the extension pack in all the systems that hosts the SQL server.

Queries

1) Is it necessary to install the extension pack in all the system our SQL Server is sitting?

2) Would it work if we install the tool in a separate system that holds the <extension pack+msn migration tool>, SQL Client and the Oracle client alone and complete the DB migration?

I need suggestion from the MSN tech support or anyone else with prior experiece whether its feasible to use the tool for our DB migration.

Note: Our Oracle DB contains only Tables and Indexes and there are no stored procedures. All the queries are dynamically built from the front end itself.

Sorry if I had posted this question in a wrong forum. I am new to this DB migration stuff.

If you cannot install the SQLpack on shared SQL, then you can try installing SSMAExtPack.exe on a client machine and create an empty database for the tables from Oracle, then arrange to port those tables back to shared SQL server.|||

Thanks Sathya for the suggestion..

So I reckon we need to create a DB on a stand alone SQL Server and transfer the tables and indexes from Oracle using the migration assistant.

From the standalone DB we need to transfer it to the shared environment. How do we migrate the tables from a standalone DB to the shared environment?

Can we use the SQL Server Data Transformation Services to migrate from temp SQL server DB to the shared environment or can you suggest any other tool provided by MSN you can suggest?

|||DTS is your friend in this case to transfer the data, but make sure to script out any triggers or SPs associated in this migration.

Oracle to SQL Server Migration using Server Migration Assistant for Oracle V2.0 - Help Neede