How do I query a database on a seperate machine?

First, let me start by saying I don't know SQL very well at all. I want to create a Live Report that will access a database on a SQL server that is seperate from the one on which our LL database resides "but" which is on the same network. I understand that this can be done but I don't what the syntax would be. Would someone out there be able to give me some examples? Any help would be greatly appreciated.

Find more posts tagged with

Comments

x-fercuser10_-_(deleted)

Two ways I know will work:1) SQL "USE database" statement. Put it in a stored procedure. (I'm not sure whether LiveReport supports multiple lines - I always use stored procedures)2) Use standard SQL database syntax as follows:SELECT * FROM databasename.databaseowner.databasetable

Development_Base4_(Base4User10_(Delete)_2261135)

If you are in a heterogeneous environment i.e. your source database is Oracle and target is SQL Server, here is my suggestion:In your Oracle db you need to create a db_link pointing to your SQL database. To do that you need to install and configure the Oracle Transparent Gateway on NT machine.Your SQL statement would look similar to this:SQL> select * from "mssuser"@SQLSERV1;where SQLSERV1 is the database link created in the Oracle db and mssuser is the user accessing SQL Server.IvetaBase4 Inc. 905-677-0532

Jose_Garcia

Thank you very for your reply but I'm afraid you might have misunderstood my question or I may have misunderstood your answer. Both the solutions you have suggested work if I'm querying a database on the same server. But the database I'm trying to reach exists on a different machine in the network. I ran the stored procedure "addlinkedservers" after which I was able to successfully query the database within the "Query Analyser" however when I took that same query placed it inside a Live Report and tried to run it I got this Livelink error message. "[Changed database context to 'Livelink'. Any suggestions?

Esam_Al_Dakheel_(tiikbaseEsam_(Delete)_2175389)

Hi there,We have the same problem where we wanted to access an oracle dbase from our SQl Livelink Dbase. We contacted support for this and they said that it cannot be done. If you want to follow up with support, our reference call number was 3183OTUK1 assigned from the UK. If you do find a solution, I would greatly appreciated if you inform me.Good luck.Esam

Jose_Garcia

Hello,Yes we were told the same thing by Opentext support over here. Apparently this is something that's possible with Oracle but not with MSSQL.

Trevor_O'_Connell_(trevoroconnell_(Delete)_2297769

Hmmm, did they try Exec sp_addlinkedserverI've had no problem.Trevor

Norman_Leung_(ImvecAdmin_(Delete)_2148619)

Could you please explain the solution step by step?Thank you.