How connect and read using a Linked Server?

At work we mainly use MS SQL Server and VB 6 to develop applications.  Recently I have been asked to write a small VB 6 application to allow a user to retrieve some records on a regular basis from a Linked Server that is connected to some Oracle tables.

Although I have written many VB 6 apps that read and write to MS SQL Server, I have not used a Linked Server in any read and write operation.  The linked server appears under the Security section of one of our SQL Groups.

Can someone please provide some information or example of how to set up the connection and recordset and command to read from the Linked Server tables?

Thank you
LVL 7
EYoungAsked:
Who is Participating?
 
3_SCommented:
http://www.oracle.com/technology/software/products/sql/index.html
Oracle sql developper is this comparable with the query analyzer of sql server. Only the one is from Oracle the other from Microsoft.

In oracle you can implement row-level security. Please speak with the other user who showed you the 13 rows.  
If you use oracle sql developper and there you only get 1 record back, then you can be sure that this is not a problem of sql server, linked server nor VB.
So if you ask me what's the problem, there are still two possiblities:
-FMSH_REQUISITION_HEADER is not a table
-the user you use has not the proper rights.

I can not give you further assistence on both without extra input of you. So please speak with the oracle guy.
0
 
lofCommented:
Hi, you can connect to you MS SQL server as usual and then when querying you may use 4 part table name like that:

select * from linkedserver.databasethere.dbo.tablename

0
 
EYoungAuthor Commented:
I am getting an error when trying to open the recordset.

This connection code works fine:

    Set cnF21 = New ADODB.Connection
    cnF21.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=LinkedServer;Data Source=TTA-DM1"
    cnF21.Open

This recordset open generates an error:
    Set rsF21 = New ADODB.Recordset
    rsF21.Source = "select * from linkedserver.F21.dbo.FMSH_REQUISITION_HEADER"
    Set rsF21.ActiveConnection = cnF21
    rsF21.LockType = adLockReadOnly
    rsF21.CursorType = adOpenForwardOnly
    rsF21.Open

This is the error:
"Run-time error '3709'
The connection cannot be used to perform this operation.  It is either closed or invalid in this context."

Not sure what is the problem with the connection string.  Should I be using an OLE DB connection provider instead of the ODBC?

Thanks for the hellp.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
EYoungAuthor Commented:
I changed the cnF21.connection string to:
    cnF21.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DataMarts;Data Source=TTA-DM1"

This generated the following error:
Run-time error '-214721900 (80040e14)':
Could not find server 'linkedserver' in sysservers.  Execute sp-applinkedserver to add the server to sysservrs.

I am hesitant to execute the sp-applinkedserver as this is a production environment at work.

Any ideas?
0
 
muhremehrCommented:
have look at:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23465186.html?sfQueryTermInfo=1+2005+link+server+sql

so if you want to query MSSQL, use:
SELECT * FROM openquery(linked_server_name,'your_query')
or
EXEC ('your_query') AT [Linked_server_name];
GO
0
 
lofCommented:
Didn't you mean sp_addlinkedserver?
Do you have the linked server added correctly? Does it work?
Does your query work when run from SQL Server Management Studio?

It looks like there is no linked server or problem with permissions
0
 
muhremehrCommented:
yes it works, try it
0
 
3_SCommented:
If your linked database is another database than a sql server, then you must use the openquery command as muhremehr posted.
Do you know to which database you link? (Oracle, DB2,..)
0
 
EYoungAuthor Commented:
It's an Oracle database as noted in original question
0
 
EYoungAuthor Commented:
The following connection code in my VB6 program works without error:
    Set cnF21 = New ADODB.Connection
    cnF21.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DataMarts;Data Source=TTA-DM1"
    cnF21.Open

The following recordset code generates an error:
    Set rsF21 = New ADODB.Recordset
    rsF21.Source = "EXEC ('select * from FMSH_REQUISITION_HEADER')"
    Set rsF21.ActiveConnection = cnF21
    rsF21.Open

The error message is:
Run-time error...
Invalid object name 'FMSH_REQUISITION_HEADER'.

The MS SQL Server Group is "TTA-DM1".
One of the databases in TTA-DM1 is called Datamarts.
The LinkedServer in TTA-DM1 that I am trying to access is F21 and it has a table called 'FMSH_REQUISITION_HEADER'.
I am trying to select all the records in this table and write them out to a table in MS SQL Server.

I can run the "EXEC ('select * from FMSH_REQUISITION_HEADER')" command successfully from within MS SQL Analyzer successfully.  The same command in the VB 6 code generates the above error.

Any help would be appreciated.
Thank you
0
 
EYoungAuthor Commented:
OK, this code seems to work for two of the three Oracle tables:

    Set cnF21 = New ADODB.Connection
    cnF21.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DataMarts;Data Source=TTA-DM1"
    cnF21.Open

    rsF21.Source = "Select * from OpenQuery(F21,'select * from FMSH_REQUISITION_HEADER')"
    Set rsF21.ActiveConnection = cnF21
    rsF21.Open

The "HEADER" table has about a dozen records but when I execute the above commands for the HEADER table, only the first record is selected.  Any idea why I am getting only the first record from the HEADER table yet I am getting all of the records from the other two tables in Oracle?

I have increased the points to 200.
0
 
3_SCommented:
Hi,

FMSH_REQUISITION_HEADER is a table and not a view? only to make sure.

I suppose you use rs.movenext to go to the next record in your recordset? Can you give some more code.
What is the value of rs.recordcount?

0
 
3_SCommented:
Should be  rsF21 instead of rs (as I used in previous post) to match your code.
 rsF21.movenext
 rsF21.recordcount
0
 
EYoungAuthor Commented:
Correct , FMSH_REQUISITION_HEADER is a table and not a view.
Yes, I would use rs.movenext to go to the next record in rsF21.
The rsF21.recordcount = -1
Below is the code.  Thank you for the help.
Note:  The code is only partially complete.  I will untimately be reading from F21 to DM1.

Private Sub Form_Load()
    'Is this application already running?
    If App.PrevInstance = True Then
        MsgBox App.EXEName & " is already running.", vbOKOnly, "Error"
        cmdExit_Click
    End If
End Sub

Private Sub cmdExtract_Click()
    lblStatus.Caption = ""
    lblStatus.Visible = True
    lblStatus.Refresh
   
    'Open connection to F21 database
    lblStatus.Caption = "Connecting to F21..."
    lblStatus.Refresh
    srConnect_to_F21
   
    'Open connection to DM1 database
    lblStatus.Caption = "Connecting to DM1..."
    lblStatus.Refresh
    srConnect_to_DM1
   
    'Extract FMSH_REQUISITION_HEADER
    lblStatus.Caption = "Extracting Header..."
    lblStatus.Refresh
    srClear_Extract_and_Save_Header_Records
   
    'Extract FMSH_REQUISITION_DETAIL
    lblStatus.Caption = "Extracting Detail..."
    lblStatus.Refresh
    srClear_Extract_and_Save_Detail_Records
   
    'Extract FMSH_REQUISITION_COMMENT
    lblStatus.Caption = "Extracting Comment..."
    lblStatus.Refresh
    srClear_Extract_and_Save_Comment_Records
   
    'Successfully extracted
    lblStatus.Caption = "Successfully extracted 3 tables"
    lblStatus.Refresh
   
    cmdExit.SetFocus
End Sub

Private Sub srConnect_to_F21()
    'Set cnF21 connection
    On Error GoTo cnF21_ErrHandler
    Set cnF21 = New ADODB.Connection
    cnF21.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DataMarts;Data Source=TTA-DM1"
    cnF21.Open
   
    On Error Resume Next
    Exit Sub

cnF21_ErrHandler:
    MsgBox "Error initializing connection to F21.", vbInformation, "Note"
    Exit Sub
End Sub

Private Sub srConnect_to_DM1()
    'Set cnDM1 connection
    On Error GoTo cnDM1_ErrHandler
    Set cnDM1 = New ADODB.Connection
    cnDM1.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DataMarts;Data Source=TTA-DM1"
    cnDM1.Open
   
    On Error Resume Next
    Exit Sub

cnDM1_ErrHandler:
    MsgBox "Error initializing connection to DM1.", vbInformation, "Note"
    cmdExit_Click
End Sub

Private Sub srClear_Extract_and_Save_Header_Records()
    'Extract the Header table
   
    Set rsF21 = New ADODB.Recordset
    rsF21.Source = "Select * from OpenQuery(F21,'select * from FMSH_REQUISITION_HEADER')"
    Set rsF21.ActiveConnection = cnF21
    rsF21.Open
   
   
    If Not rsF21.EOF Then
        MsgBox rsF21.RecordCount
    End If
End Sub

Private Sub srClear_Extract_and_Save_Detail_Records()
    'Extract the Header table
   
End Sub

Private Sub srClear_Extract_and_Save_Comment_Records()
    'Extract the Header table
   
End Sub

Private Sub cmdExit_Click()
    'Close all Connections
    Set cnF21 = Nothing
    Set cnDM1 = Nothing
   
    'Close all Recordsets
    Set rsF21 = Nothing
    Set rsDM1 = Nothing
   
    'Get out of Dodge
    End
End Sub
0
 
3_SCommented:
If you take understanding sub you only get 1 record back with the msgbox in the loop? Is this correct.

If you execute the query Select * from OpenQuery(F21,'select * from FMSH_REQUISITION_HEADER') in query analyzer within sql server, how many records are returned then?

rsF21.Open , , adOpenForwardOnly, adLockReadOnly (change this if you need to be able to change values in rsF21)

Your code does not seem to have issues. Post back the result of the query anlayser.
Private Sub srClear_Extract_and_Save_Header_Records()
    'Extract the Header table
    Dim rsF21 As ADODB.Recordset
    
    Set rsF21 = New ADODB.Recordset
    rsF21.Source = "Select * from OpenQuery(F21,'select * from FMSH_REQUISITION_HEADER')"
    Set rsF21.ActiveConnection = cnF21
    rsF21.Open , , adOpenForwardOnly, adLockReadOnly
        
    If Not rsF21.EOF Then
        MsgBox rsF21.RecordCount
    End If
    Do While Not rsF21.EOF
        MsgBox rsF21.Fields(0)
        rsF21.MoveNext
    Loop

End Sub

Open in new window

0
 
EYoungAuthor Commented:
1.  Yes, only one record comes back with the msgbox. Here is that code section:
    Set rsF21 = New ADODB.Recordset
    rsF21.Source = "Select * from OpenQuery(F21,'select * from FMSH_REQUISITION_HEADER')"
    Set rsF21.ActiveConnection = cnF21
    rsF21.Open
   
    Do Until rsF21.EOF
        MsgBox rsF21.Fields("Company_Id")
        rsF21.MoveNext
    Loop


2.  Yes, only one record (same record) comes back when I execute the following in the SQL Query Analyzer:
Select * from OpenQuery(F21,'select * from FMSH_REQUISITION_HEADER')


3.  I changed the code to include the "adOpenForwardOnly, adLockReadOnly" as follows and it still only returns one record:
    Set rsF21 = New ADODB.Recordset
    rsF21.Source = "Select * from OpenQuery(F21,'select * from FMSH_REQUISITION_HEADER')"
    Set rsF21.ActiveConnection = cnF21
    rsF21.Open , , adOpenForwardOnly, adLockReadOnly
   
    Do Until rsF21.EOF
        MsgBox rsF21.Fields("Company_Id")
        rsF21.MoveNext
    Loop


4.  Do the above answers address your questions?  If not, please ask.  I have asked the user to look at the Oracle "Header" table and it does show 13 records not 1.

Thanks for sticking with me on this.

On another note, I have another question regarding Crystal Reports 2008 that I just posted:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_25000168.html
0
 
EYoungAuthor Commented:
I tried another test in Query Analyzer.  I added a "where requisition_no <> 304089" and then ran the query.  The results showed a different header record.  So it is seeing the other records, it is just not selecting more than one.

This hard to understand.
0
 
3_SCommented:
You mean you get differenct resultsets back in the query anlayser?
If this is the case you must use the following to go to the next resultset in your recordset:


"adOpenForwardOnly, adLockReadOnly" is only added to make a minimum lock on your records in the database. With this you cannot not change/update the values in your recordset. I don't know what you want to accomplish with the resultset in your program, so maybe this will need to change.

But I'm not sure that you have multiple resultset, since you only get back one record in the query analyser. There you should see the 13 records you mentioned. (the number of records you get in the query analyser = the records you will get in the recordset)
    Dim intCount As Integer
    intCount = 1
    Do Until rsF21 Is Nothing
        MsgBox "Contents of recordset #" & intCount
        Do Until rsF21.EOF
            MsgBox rsF21.Fields(0)
            rsF21.MoveNext
        Loop
        Set rsF21 = rsF21.NextRecordset
        intCount = intCount + 1
    Loop

Open in new window

0
 
EYoungAuthor Commented:
1.  I do get a different resultset back in the query analyzer when I "qualify" the following select statement:  rsF21.Source = "Select * from OpenQuery(F21,'select * from FMSH_REQUISITION_HEADER' where requisition_no <> '8077289')".  This select statement asks for a selection of records from the Header table that do not match the first record that has a requisition_no = '8077289'.  Still the resultset just returns 1 record.  Hope that is clear.  Put another way, no matter how I select, I just get one record back from the Header table.  I can get a different record by using the "Where clause" but I still just get back one record.

2.  I do understand the use of:  "adOpenForwardOnly, adLockReadOnly".  I am using the selections to just read the Oracle tables.  Once I can successfully read the Oracle tables (all records), I will be using the resultsets to populate MS SQL Server tables.  Just a read from Oracle and some writes to MS SQL Server.

3.  I tried the following code and it just returned one record:
Private Sub srClear_Extract_and_Save_Header_Records()
    'Extract the Header table
   
    Set rsF21 = New ADODB.Recordset
    rsF21.Source = "Select * from OpenQuery(F21,'select * from FMSH_REQUISITION_HEADER')"
    Set rsF21.ActiveConnection = cnF21
    rsF21.Open , , adOpenForwardOnly, adLockReadOnly
   
    Dim intCount As Integer
    intCount = 1
    Do Until rsF21 Is Nothing
        MsgBox "Contents of recordset #" & intCount
        Do Until rsF21.EOF
            MsgBox rsF21.Fields(0)
            rsF21.MoveNext
        Loop
        Set rsF21 = rsF21.NextRecordset
        intCount = intCount + 1
    Loop
End Sub

Any ideas?  Thank you again for sticking with me on this and Happy Holidays.
0
 
3_SCommented:
1
To get more than one record back in VB, your first have to look with query analyser how you can get back more than one record. Since the other selects (in your other tables) return more than one record, I think there must be a problem not related to VB

2. You don't need to use  "adOpenForwardOnly, adLockReadOnly".  But it can help you to minimize locking in your database. If you only do a read, use this.

3. As posted with point 1, first get it right in query analyzer. If you use a where clause
where requisition_no in ('xxx','yyy')
do you get back two records?  (adapt xxx and yyy with valid keys.)
0
 
EYoungAuthor Commented:
1.  I agree.  Just not sure how to solve the problem.

2.  OK.  Good advice.  I'll use the two options as I only need to read the Oracle table.

3.  Not sure I follow your example.  The Header table contains 20+ fields in Oracle.  Two of the fields are Company_No and Company_Name.  Can you make up an example?  I agree with your logic that I need to get it right first in Query Analyzer then it should work in VB.

Thank you for sticking with this.
0
 
3_SCommented:
I would advice you to use the Oracle SQL Developer to make your query. With that you should be able to make your query work. (so no problem can occur due to linked server)
If that works try you query with the sql query analyzer. Then VB will work too.
To make the linked server connection. Did you enter a static user to make the connection, or does it has to pass the credentials of the asking user?

An example of the sql command could be
select requisition_no,company_no,company_name from FMSH_REQUISITION_HEADER
This should return all the records in the table FMSH_REQUISITION_HEADER
0
 
3_SCommented:
Have you had any luck with retrieving the records from the table FMSH_REQUISITION_HEADER.
Are you able to get more then on record with (select requisition_no,company_no,company_name from FMSH_REQUISITION_HEADER) in
-Oracle SQL Developer?
-Query Analyzer?
-VB6?

You said 'The Header table contains 20+ fields in Oracle'. Fields does not equal records. You really mean you have to get multiple records? right?
0
 
EYoungAuthor Commented:
I have not tried your suggestion as I do not understand your directions.  What is the "Oracle SQL Developer"?

The other user who, I think, may use the Oracle SQL Developer has shown me that there are 13 records in Oracle.  We just can't get them out using MS SQL Analyzer or VB.

The Header table in Oracle has 13 records and 20+ fields.  Sorry about the confusion.  (I am getting confused and I think I am loosing my marbles.)
0
 
EYoungAuthor Commented:
I will follow up with the other user today.  Thanks.
0
 
EYoungAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.