Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Connecting to 2 dB on the same server? (Read 9982 times)
Soren
YaBB Newbies
*


-

Posts: 4
Connecting to 2 dB on the same server?
02.08.06 at 14:20:55
 
Is it possible to connect to different tables in differet databases on the same server using SQL*XL?
 
Kind regards Soren
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Connecting to 2 dB on the same server?
Reply #1 - 03.08.06 at 07:19:06
 
At the moment SQL*XL can connect to 1 database only. You need to disconnect and connect to the other database if you need to access the second table in the second database.
 
However, there are ways to access the tables using 1 connection.
When using Oracle databases you can use a database link in the first database to point to the second database. You can access the second table using: select * from mytable@second_database;
 
If the database type is SQL server this is possible too with the use of a linked server. If the database is Access this is possible through external tables.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Soren
YaBB Newbies
*


-

Posts: 4
Re: Connecting to 2 dB on the same server?
Reply #2 - 03.08.06 at 07:30:56
 
Thank you for the fast answer. I dont know what you mean by a linked server (using a MS SQL server). Could you by any chance send me a bit of SQL code showing how it shoul look?
 
Kind regards Soren
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Connecting to 2 dB on the same server?
Reply #3 - 03.08.06 at 10:09:47
 
The best is to add a linked server to your database in SQL Server's Enterprise Manager. In the security section you will find the Linked Servers section. Add a new linked server and it will take you through the steps to define the connection, etc.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Soren
YaBB Newbies
*


-

Posts: 4
Re: Connecting to 2 dB on the same server?
Reply #4 - 03.08.06 at 12:02:29
 
OK now i got a linked server. But how do i add it to an exsiting dB. As i see it i can only define one dB in the linked server, and i see no possebilities to add any server in the dB's i need the data from.  
 
Kind regards Soren
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Connecting to 2 dB on the same server?
Reply #5 - 03.08.06 at 13:16:14
 
When you have your linked server and you have named it e.g. MyLinkedServer you can use the tables inside of it in your select statements. Use the openquery statement in SQL server to execute queries in your remote database.  
 
Example:
Suppose you have linked to a server you called MyLinkedServer and that you want to query the table MyTable inside of it.
You can use the following SQL:
select * from OpenQuery(MyLinkedServer,"Select * from MyTable")
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Soren
YaBB Newbies
*


-

Posts: 4
Re: Connecting to 2 dB on the same server?
Reply #6 - 04.08.06 at 07:57:22
 
Is it true that thoug i can not see the databases in the linked server (using enterprise manager), it is possible to access these databases using the SQL script? I guess i then either have failed in the setup of the linked server or the SQLstatement in SQL*XL, wich now looks like:
 
select * from OpenQuery(MyLinkedServer, "Select * from dbo.MyFirstDatabase.MyTable")  
 
Kind regards Soren
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Connecting to 2 dB on the same server?
Reply #7 - 04.08.06 at 22:34:32
 
It looks like you have created a linked server to your original database. You have probably confused  yourself with the technicalities to specify the linked server. It is complex actually. I will explain once more, perhaps it becomes more clear.
 
Linked servers are nicknames for other databases that you would like to access. When you have given your other databases a nickname you can run a query in that database using the openquery statement. You can regard the result of the openquery statement as a view that you can query.  
 
Example.
Suppose I have a spreadsheet in which I have defined a table called mytable.  
In the security, linked servers section of the enterprise manager I make a new linked server which I call myxllink. I am specifying the connection string etc to connect to the spreadsheet I have prepared.
To query my table mytable inside the spreadsheet from SQL Server I use the openquery function:
openquery(myxllink,'select * from mytable').
This can be regarded as a view in the SQL Server. So if I run this select statement I am returning all rows from the mytable table:
select * from openquery(myxllink,'select * from mytable').
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1