다음을 통해 공유

How to verify if the issue is due to linked server or provider

  • 아티클

When we create a  linked server to a database Other than SQL server, like Sybase , Oracle , DB2 etc ,we see some errors and we are not sure whom to contact for support. Should we contact Microsoft support or the third party support.

In such an ambiguous situation we can verify ourselves, which support to contact.

 

Here is the example

Here linked server for Oracle database using Oracle provider "OraOLEDB.Oracle.1 " is created on SQL Server database. Linked server works fine in all situations except for the data like "CËuraçaö ".

When query is executed which displays the data in the column "CËuraçaö " , we may see some errors like .

Error message

The OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_TST2" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_TST2".

 

Here since we are creating linked server on SQL Server , we may contact Microsoft support seeking for help , but as the issue is with Oracle provider our support team may suggest to contact Oracle .

In such case , we can verify by running the script files whom to contact at first stage in order to save some time .

Here is the VBscript file , that will pull the data from the Oracle database using the provider " OraOLEDB.Oracle.1" and writes data to the text file .

VB Script Code snippet

 

-------------------------------------------------------------------------------------------

Dim strSQL

Dim dbMyDBConnection

Set dbMyDBConnection = CreateObject("ADODB.Connection")

dbMyDBConnection.ConnectionString =” Provider=OraOLEDB.Oracle.1;Password=Password;Persist Security Info=True;User ID=UserName;Data Source=DataSourceName"

dbMyDBConnection.Open

msgbox "Connection openned"

strSQL = "SELECT firstName FROM Employee"

Const ForAppending = 8

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objTextFile = objFSO.OpenTextFile ("C:\Oracledata.txt", ForAppending, True)

set objRS = dbMyDBConnection.Execute (strSQL)

objRS.MoveFirst

Do While Not objRS.EOF

For i = 0 to objRS.Fields.Count - 1

objTextFile.WriteLine(objRS.Fields(i))

Next

objRS.MoveNext

Loop

msgbox "Data written successfully"

------------------------------------------------------------------------------------------------

 

 

Copy the VB script in text file and save with extension filename.vbs to run the script file.

If this VB script file works fine without any error that gives us idea that issue is with Linked server and we can contact Microsoft

If the VB script itself fails with error , than we can contact the product team of the database/provider used .

Note : Similar test can be performed to any database by giving appropriate connection string.

This will always save time in making the process faster to resolve the issue and also saves some dollars .

 

Happy Coding !!

 

 

Author : Archana(MSFT), SQL Developer Engineer, Microsoft

Reviewed by : Ambujn(MSFT), SQL Developer Technical lead, Microsoft

Comments

  • Anonymous
    May 18, 2015
    Great Post!!!