DB Tool List Table: How to access tables which are in different SQL database ?

[chad_vi]

Hi, All,

I'm working on a database application (SQL server) and is evaluating the NI DB Tool kit for this project.

One of the requirement is that I need to access tables which are in two different database

(say Table A in DB 1 and Table B in DB 2).

Our IT guys has linked Table A in DB1 to DB 2 and I verfied this when I use the SQL server managment studio.

When DB 2 tables are populated, Table A from DB1 is also there. I can also do the same thing using MS Access.

Table A in DB1 is avalaible to me enven though I only connect to DB 2.

Here comes the problem.

When I use DB Tool List Table.vi to access DB2, it does NOT list Table A in DB1. It only list the tables in

the database (DB2) which I make connection to (using DB Tool Open Connection.vi with a file DSN)

So my work around right now is to open two seperate connection to DB1 and DB2. However, this approach

obviously creates a problem when I have to access seperate database constantly in my application.

What would be a solution to this ? I've search the forum but only see one post that's somewhat related to

my question. (And it was posted on 2004) Perhaps I need to alter the code in the orignial VI (DB Tool List Table.vi)??

My IT guy told me he has not encountered this scenario since he writes codes in other enviroment such as

VB and others, and he's always been successful by linking tables to different database. 

I hope my question is sound and clear since I really don't know much about database terminology.

Any comment/suggestion is much appreciated !!!

Thanks

Chad

[G_K]

Hi Chad,

you can open two seperate connection to DB1 and DB2( you are already doing it)  and keep the reference in memory, when you want to access the DB1,DB2  do it twice using a FOR loop with that reference stored, finally combine the data from DB Tool List Table.vi (or any other DB operation).

also close the DB connection whenever you want,,

hope this solve your need..

[chad_vi]

To G.K :

Thanks for the suggestion. I think the fundamental question to ask is still why doesn't DB Tool List Table.vi show a

"linked" table (from a seperate DB) in the DB I make connection to ? The linked table works just ine in other API

such as visual, microsoft access, SQL server management studio, then why shouldn't it work in LabView!?

Maybe I need to get the licensed version and take a peak at the code behind this VI.

The issue with making seperate connections is that I need to open and close connection for every time I access a

differenet database. In my example scenario there are only two database. In realtiy, I have data stored in 4

different database. (the databases are maintained by the IT department so it doesn't have much flexibility)

Anyway. I will probably end up doing something like you suggested if there is no other solution to it.

[josborne]

Hi Chad,

I am a SQL Server expert and can help you out.  But I need a bit more info.

One solution is to do exactly what GK mentioned.  Use two separate connections.  But there are limitations to this solution (e.g. you can't JOIN two tables accessed via separate connections).  If you want to access the data using one connection, here is how we get started:

First, we'll need more info: 

- Are the two databases contained on the same SQL Server instance?  Or are the databases on separate instances?  I assume they are on separate servers, otherwise this wouldn't really be an issue.  But its good to know because it will affect how you build your SQL statements.

- Ask your IT people specifically how they "linked Table A in DB1 to DB 2".  I assume they used "linked servers". 

Based on the answers to these questions, I can help you configure your SQL statements correctly.  My guess is that the problem is that you are not fully qualifying the tables in your SQL statements.  If you are using linked servers, and you connect to DB2, you will need to qualify your statement by appending the remote server name in this way:

SELECT * FROM servername.databasename.schemaname.tablename

[josborne]

HEre's some good info about what I was talking about:

http://technet.microsoft.com/en-us/library/ms188279.aspx

[chad_vi]

To josborne:

To answer your question:

- Are the two databases contained on the same SQL Server instance?  Or are the databases on separate instances?  I assume they are on separate servers, otherwise this wouldn't really be an issue.  But its good to know because it will affect how you build your SQL statements.

Yes they are on separate instances. 

- Ask your IT people specifically how they "linked Table A in DB1 to DB 2".  I assume they used "linked servers". 

Maybe I used the wrong terminology "linked." They created a "View of Table A (DB1)" in DB2 using the management studio.

Here is a screen shot of that. As you can see, dbo.VISUAL_WORK_ORDER is seen under LABVIEW database in the management studio.

I also see the same table when I make connection to database using MS Access.

Could you elaborate on "configure your SQL statement correctly" 😃 ? The purpose of using LabView's took kit is so that I can do

minimum SQL statements. Are you talking about modifying LabView's native VI (DB Tool List Table.vi) ?

Thanks for the information. SQL is just something new to me.

[josborne · Accepted solution]

OK.  This is some information I can work with.  Good.

Your IT people created a view for you called "VISUAL_WORK_ORDER".

A view is NOT a table.  It is a "virtual table".  Views are used to gather up data (usually) in multiple different tables.  

But I am guessing that the code inside DB Tool List Table.vi only returns a list of tables.  Views aren't tables.  So "VISUAL_WORK_ORDER" does not appear in the list. 

HOWEVER, this shouldn't really matter.  The view is there!  And it can be queried just like any table.  So you can use DB Tools Select Data.VI ... and wire in "VISUAL_WORK_ORDER" as the table name.  This will return the contents of the view.  Viola!

[chad_vi]

To josborne:

I guess I never bothered to try other VI to find out what you said. You are absolutely correct. I try to

use DB Tool List Columns.vi with the VISUAL_WORK_ORDER table name and it DOES return the

correct columns information. So it must be that DB Tool List Table.vi will only return "tables", not "virtual tables".

Hey..Thanks. Now I know I can still query the virtual table provided that I use the correct table names. A little

inconvenient, but not a deal breaker.

Ahh...my sky is blue again !!!

Thanks for all who share their thoughts !!!

[G_K]

Hi Jasborne,

you proved that you are a SQL Server expert 

Chad,

happy to hear your sky is blue again..and i beleive that this is bcoz of the radiation effect of NI (BLUE)..

and i also believe this will happen for all the wire fighters who are here...

Regards

G K