connecting to Oracle

[Darth Vodka]

hi

i can connect an Access db to Oracle via ODBC and see a table fine...

but when i try and do this in a query in SS 2005, it doesn't like it

giving me

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

now i'm pretty sure one of two things is happening here:-

1) the database is set not to allow connections from SS? this seems possible but it seems silly that i can do something in Access but not SS
2) i'm being stupid!

any suggestions welcome...

 

[boblarson]

hi

i can connect an Access db to Oracle via ODBC and see a table fine...

but when i try and do this in a query in SS 2005, it doesn't like it

giving me



now i'm pretty sure one of two things is happening here:-

1) the database is set not to allow connections from SS? this seems possible but it seems silly that i can do something in Access but not SS
2) i'm being stupid!

any suggestions welcome...

You have to install, onto SQL Server, something from Oracle (I can't remember what exactly) to link between SQL Server and Oracle.

 

[Darth Vodka]

You have to install, onto SQL Server, something from Oracle (I can't remember what exactly) to link between SQL Server and Oracle.

aha, thanks Bob

think it may be this

http://support.microsoft.com/kb/280106

You must install the Oracle client software on the computer that is running SQL Server where the linked server is set up.

will ask the DBA...(i'm just a blagger with access to sql server!)

 

[Darth Vodka]

aha, found a guru at my work and he suggested 3 things

1) just use SSIS, management studio looks at sql server stuff, but SSIS should be able to connect via ODBC
2) as bob suggested, get the DBA to load the driver onto the SQL server box
3) use BCP

i'm currently tinkering in SSIS and it looks to work, wahey!

 

[Darth Vodka]

hmm now stuck on a different error

i got the ODBC to work on a sql comonent, so i bravely ploughed on with a Data Flow object

the source object, a Datareader component, keep giving me the "inconsistent metadata" error and it is completely stumping me why??

anyone got suggestions for getting rid of "inconsistent metadata" ??