Link SQL2000 and Oracle

hi, does anyone know how to link SQL2000 to Oracle? because i need to use a sql statement which involve both the SQL2000 table and an Oracle table.

Thanks in advance.You have two choices here:

either you create two connections and retrieve from each database what is needed into datasets and use that data then (works without a problem), or you set up a linked database in SQL server that points to the Oracle database ...
If you do not know how to do that second one, go for the first solution ...|||thanks for the solutions..

link SQL 2005 server

Any body tryed to link SQL2000 to SQL2005 or other way arround.

add sql2k to sql2005, and vice versa, as linked server is a supported scenario.

for example, from a connection to your sql 2005, run the following commands to add sql2k as a linked server:

sp_addlinkedserver 'sql2k'
sp_setnetname 'sql2k', <machine name>\<instance name>

where 'sql2k' is the linked server name of your choice, <machine name> is the hostname of the machine where your sql 2000 is installed, and <instance name> is the name of your sql 2000 instance. If it is a default instance, then simply
sp_setnetname 'sql2k', <machine name>sql

link SQL 2005 server

Any body tryed to link SQL2000 to SQL2005 or other way arround.

add sql2k to sql2005, and vice versa, as linked server is a supported scenario.

for example, from a connection to your sql 2005, run the following commands to add sql2k as a linked server:

sp_addlinkedserver 'sql2k'
sp_setnetname 'sql2k', <machine name>\<instance name>

where 'sql2k' is the linked server name of your choice, <machine name> is the hostname of the machine where your sql 2000 is installed, and <instance name> is the name of your sql 2000 instance. If it is a default instance, then simply
sp_setnetname 'sql2k', <machine name>

Link Server to SQL2000 from SQL2005 64bit

Hello All,
I setup a link to my SQL2000, everything seem fine except when I try to
query the SQL 2000 database. I get the following error:
OLE DB provider "SQLNCLI" for linked server "ces012" returned message
"Unspecified error".
OLE DB provider "SQLNCLI" for linked server "ces012" returned message "The
stored procedure required to complete this operation could not be found on
the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
"SQLNCLI" for linked server "ces012". The provider supports the interface,
but returns a failure code when it is used.
I search on the web and it tells me to run the instcat.sql on the SQL2000
server.
Do you see any problem after I run this script? Are there anyway I can back
out?
Thanks.
It should be no problem, I would take a backup of the Master database first.
You can run the script if you have SP3 or SP4 applied on SQL 2000. You can
restore your master if you want to back out.
You can refer to this article: http://support.microsoft.com/kb/906954
Ayad Shammout
"David" <David@.discussions.microsoft.com> wrote in message
news:DDC6A798-9099-47FB-A2FC-EEF7ABF1D1B4@.microsoft.com...
> Hello All,
> I setup a link to my SQL2000, everything seem fine except when I try to
> query the SQL 2000 database. I get the following error:
> OLE DB provider "SQLNCLI" for linked server "ces012" returned message
> "Unspecified error".
> OLE DB provider "SQLNCLI" for linked server "ces012" returned message "The
> stored procedure required to complete this operation could not be found on
> the server. Please contact your system administrator.".
> Msg 7311, Level 16, State 2, Line 1
> Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
> "SQLNCLI" for linked server "ces012". The provider supports the interface,
> but returns a failure code when it is used.
> I search on the web and it tells me to run the instcat.sql on the SQL2000
> server.
> Do you see any problem after I run this script? Are there anyway I can
> back
> out?
> Thanks.
>
sql

Link Server to SQL2000 from SQL2005 64bit

Hello All,
I setup a link to my SQL2000, everything seem fine except when I try to
query the SQL 2000 database. I get the following error:
OLE DB provider "SQLNCLI" for linked server "ces012" returned message
"Unspecified error".
OLE DB provider "SQLNCLI" for linked server "ces012" returned message "The
stored procedure required to complete this operation could not be found on
the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
"SQLNCLI" for linked server "ces012". The provider supports the interface,
but returns a failure code when it is used.
I search on the web and it tells me to run the instcat.sql on the SQL2000
server.
Do you see any problem after I run this script? Are there anyway I can back
out?
Thanks.It should be no problem, I would take a backup of the Master database first.
You can run the script if you have SP3 or SP4 applied on SQL 2000. You can
restore your master if you want to back out.
You can refer to this article: http://support.microsoft.com/kb/906954
Ayad Shammout
"David" <David@.discussions.microsoft.com> wrote in message
news:DDC6A798-9099-47FB-A2FC-EEF7ABF1D1B4@.microsoft.com...
> Hello All,
> I setup a link to my SQL2000, everything seem fine except when I try to
> query the SQL 2000 database. I get the following error:
> OLE DB provider "SQLNCLI" for linked server "ces012" returned message
> "Unspecified error".
> OLE DB provider "SQLNCLI" for linked server "ces012" returned message "The
> stored procedure required to complete this operation could not be found on
> the server. Please contact your system administrator.".
> Msg 7311, Level 16, State 2, Line 1
> Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider
> "SQLNCLI" for linked server "ces012". The provider supports the interface,
> but returns a failure code when it is used.
> I search on the web and it tells me to run the instcat.sql on the SQL2000
> server.
> Do you see any problem after I run this script? Are there anyway I can
> back
> out?
> Thanks.
>

Link Server from SQL2005 to SQL2000

I have the follwoing scenario, where the query returns an error, when we tried to upgrade oru production SQL2000 enviroment to SQL2005.

SQL2000 Env:

Microsoft SQL Server Developer Edition

Microsoft Windows NT 5.2 (3790)

8.00.2175 SP4

SQL2005 Env:

Microsoft SQL Server Developer Edition

Microsoft Windows NT 5.2 (3790)

9.00.3054.00

Notice the removal of the two RTRIMs.

Any help welcome

Gertus

Current Connection was on the SQL2005 machine:

Remote Link Server SQL2000 SP4

Working:

SELECT * FROM Entity -- on SQL2005

WHERE

(EntityRef) + Source NOT IN

(SELECT (ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY) --this is on sql2000

AND Source = 'EU'

Not-working:

SELECT * FROM Entity-- on SQL2005

WHERE

RTRIM(EntityRef) + Source NOT IN

(SELECT RTRIM(ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY)

AND Source = 'EU'

Error :: >>

OLE DB provider "SQLNCLI" for linked server "MRIEU" returned message "Cannot create new connection because in manual or distributed transaction mode.".

Msg 7320, Level 16, State 2, Line 1

Cannot execute the query "SELECT TOP 1 1 FROM "MRIOB"."dbo"."ENTITY" "Tbl1005"" against OLE DB provider "SQLNCLI" for linked server "MRIEU".

Did you try searching the MS knowledge base?

http://support.microsoft.com/kb/272358/en-us

|||

Sorry that is not the problem, I am runnign this query in sql storedproc, it this is an error on the link server code internal of Microsoft.

Can any Microsoft person help here please?

Regards

Gert