.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

SQL 2005 Linked Server to Oracle 10g - Very Slow

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :
 

Hi,

 

Anyone have some recommendations for a slow linked server from SQL 2005 to Oracle 10g?

 

Here is the situation.  We have an application that runs on SQL 2005 - Win 2003, and in certain forms it makes calls to an Oracle 10g database running on Sun Unix.  This database was upgraded from SQL 2000 Win 2000.  We have a test system mirroring production and have the same problems.

 

- All indexes are being used.

- Server configuration is correct.

- Changed the Oracle 10g driver from the main driver to the Instant Client - much smaller but same result.

- Read an old post about same issue but the person was connecting to 8i so they were using a different driver.

- I have been running profiler and the problem seems to be a remote scan on the Oracle side.  We are using the OPENQUERY call from the application.

- All other parts of the app are running fine, just the outside connection.

- We are using the Windows Firewall and all DCOM components are set as Microsoft recommends.  I don't think this is an issue because the test server is not using Windows Firewall and we have the same issue on that server.

 

Any suggestions would be greatly appreciated.  I have run into the wall :-)

 

thanks

Matt




View Complete Post


More Related Resource Links

Importing Data from Oracle 10g to Sql Server 2005 using Linked Server

  
Hi,   I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :   OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error". OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.   Please let me know.   Thanks

No Way to retrieve data from oracle ref:_cursor over T-SQL and linked Server?

  
Hello, for an migration projekt we want to compare results from sp's from oracle and sqlsserver. Same calls should retrieve same results. Also we want build an automatic test for this. But is there now way to retrieve results from oracle sp's whit rev_cursor over linked Server? No one answer to this: http://social.msdn.microsoft.com/forums/en-us/sqldataaccess/thread/2BAC6743-8701-4476-8F36-0377A5761525   greetings Michael

Linked Server to DB2 from SQL 2005 IA64BIT computer

  
Has anyone created a linked server to DB2 on AS/400 from SQL Server 2005 IA64 BIT? We were hoping to use the new Microsoft OLEDB Provider for DB2...The problem is the driver looks like it will only work on x64 computers."The single setup program will install the provider and tools on both x86 and x64 computers" We were able to install the driver on the IA64 computer and were able to successfully create a data source from the Data Access Tool. Oddly enough when we attempted to create a linked server in SSMS we received an error, we also noticed that the DB2OLEDB provider is not listed in SSMS. This is becoming a real problem so any ideas on how we can get this driver to work or suggestions on an alternative would be greatly appreciated.   thx.art.  

SQL Server 2005 X64 Linked Server error

  
We use Windows 2003 Server (64) on AMD64 and SQL Server 2005 Developer Edition x64 + SP1  Problem: I can not execute any sql on a linked server using the native Provider (SQLCLNI) Example (create a linked server on the same machine, other database (msdb) and try to execute any simple select using OPENQUERY  sp_addlinkedserver @server='ls',@srvproduct='SQLNCLI',@provider='SQLNCLI' ,@provstr='Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=localhost;'  Select * from OPENQUERY(ls,'Select * from Sysobjects')  Msg 7356, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI" for linked server "ls" supplied inconsistent metadata for a column. The column "name" (compile-time ordinal 1) of object "Select * from Sysobjects" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.

Oracle 11g R2 64-bit linked server difficulty

  
We are re-integrating a business unit back into IT support this weekend. They have a Oracle production system that's been upgraded to 11G. The previous linked server worked fine. I'm trying to attach from an XP laptop that has a full blown Sql Server 2005 running. I've seen all the post about "in-process" and it doesn't work. I'm getting either 7303 or 7399 regardless of whether I use the Oracle or Microsoft driver. I can successfully connect and query the database from sql plus with no problem. I can create the linked server fine, it's just when I go to query that it errors. "The test connection to the linked server failed." An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "OraAGS". OLE DB provider "OraOLEDB.Oracle" for linked server "OraAGS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303 Could this be a problem with a 64-bit server connecting from a 32 bit client?? Thanks in advance..

XMLDML insert is dog slow for real inserts in SQL Server 2005

  
I put together a little sample after noticing my XML solution was taking more than three seconds every time I inserted something over 20 records with a reasonable number of attributes. XML updating is apparently not ready for production code in 2005. This is faster in SQL Server 2008, and might work for some applications, but you'd actually write the code a little differently there. In any case, it's far slower than writing out the string yourself. Anyhow, if anyone asks you why you're not using XML.modify('insert in SQL Server 2005, you can run this for them.  DECLARE @now DATETIME, @then DATETIME, @elapsed INT, @now_disp NCHAR(24); SET @now = GETDATE(); SET @now_disp = CONVERT(NCHAR(24), @now, 126); RAISERROR('starting timer at %s', 11, 1, @now_disp) WITH NOWAIT; DECLARE @root XML, @insert NVARCHAR(MAX), @counter INT, @child XML, @att NCHAR(3), @attdex tinyint; SET @root = '<root/>'; SET @counter = 24; WHILE @counter < 26 BEGIN SET @counter = @counter + 1 SET @child = CAST('<child/>' AS xml) SET @attdex = 0; WHILE @attdex < @counter BEGIN SET @attdex = @attdex + 1; SET @att = CHAR(96 + @attdex) + 'ow'; SET @insert = 'SET @child.modify(''insert attribute ' + @att + ' {"value"} into (/child)[1]'');'; EXEC sys.sp_executesql @insert, N'@child xml OUT', @child

Data Transfer in C# slow when targeting SQL Server 2005 using SQL Server SMO version 10 (distributed

  

 

Hello everyone,

I have a strange issue regarding SQL Server SMO Transfer. I am using the Transfer class to create a copy of a database. I will explain the different cases I have tried so far and maybe someone can help me figure out what's going on.

First case

I run my C# code from an assembly that references version 9.0.242.0 of SMO assemblies that are in my GAC

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SqlEnum

If I target a database hosted on a Sql Server 2005 database engine, it works perfectly and fast.

But of course, as soon as I try to target a Sql Server 2008 database engine, it complains that it is not compatible. No surprise there.

 

Second case

I run the exact same C# code from an assembly that references version 10.0.0.0 of SMO assemblies that are also present in my GAC

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.Smo.Extended
  • Microsoft.SqlServer.SqlEnum

If I target a database hosted on a Sql Server 2008 database engine, it works perfectly and fast.

However, if I try to target the sam

Oracle 11g R2 64-bit linked server difficulty

  

We are re-integrating a business unit back into IT support this weekend. They have a Oracle production system that's been upgraded to 11G. The previous linked server worked fine.

I'm trying to attach from an XP laptop that has a full blown Sql Server 2005 running. I've seen all the post about "in-process" and it doesn't work. I'm getting either 7303 or 7399 regardless of whether I use the Oracle or Microsoft driver. I can successfully connect and query the database from sql plus with no problem. I can create the linked server fine, it's just when I go to query that it errors.

"The test connection to the linked server failed."

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "OraAGS".
OLE DB provider "OraOLEDB.Oracle" for linked server "OraAGS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303

Could this be a problem with a 64-bit server connecting from a 32 bit client??

Thanks in advance..


Data Transfer in C# slow when targeting SQL Server 2005 using SQL Server SMO version 10 (distributed

  

 

Hello everyone,

I have a strange issue regarding SQL Server SMO Transfer. I am using the Transfer class to create a copy of a database. I will explain the different cases I have tried so far and maybe someone can help me figure out what's going on.

First case

I run my C# code from an assembly that references version 9.0.242.0 of SMO assemblies that are in my GAC

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SqlEnum

If I target a database hosted on a Sql Server 2005 database engine, it works perfectly and fast.

But of course, as soon as I try to target a Sql Server 2008 database engine, it complains that it is not compatible. No surprise there.

 

Second case

I run the exact same C# code from an assembly that references version 10.0.0.0 of SMO assemblies that are also present in my GAC

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.Smo.Extended
  • Microsoft.SqlServer.SqlEnum

If I target a database hosted on a Sql Server 2008 database engine, it works perfectly and fast.

However, if I try to target the sam

Slow sql-server after upgrade from SQL-Server 2005 x64 standard to SQL-Server 2008 R2 x64 enterprise

  

We moved our databases to new hardware today (using copy database wizard)

 

The new hardware should be better than the old one.

We went from 28 GB ram to 128 GB.

We went from 16 cores to 32 cores (with even more cache etc.). We also moved from external SAN with 40 disks to intern disks (8 Samsung SS805 100gb ssd slc)

                             According to our own metrics we could get twice the IO using IO m

Trouble with: Linked Server to Oracle using OraOLEDB.ORacle Provider

  

Hi--

 

I am running SQL Server 2005 on Win2k3:

Microsoft SQL Server Management Studio      9.00.2047.00
Microsoft Analysis Services Client Tools      2005.090.2047.00
Microsoft Data Access Components (MDAC)      2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML      2.6 3.0 4.0 6.0
Microsoft Internet Explorer      6.0.3790.1830
Microsoft .NET Framework      2.0.50727.42
Operating System      5.2.3790


I have the OraOLEDB.Oracle provider installed to the (C:\oraclexe) directory.

I am having problems querying from linked oracle server.  When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password.   So it at least knows that.  when i set the correct password and run a query I get this error:

(i replaced the real server name with "someServer".)

Msg 7399, Level 16, State 1, Line 1

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

Msg 7303, Level 16, State 1, Line 1

C

Instructions for setting up a Sybase ASE Server as a SQL Server 2005 Linked Server?

  

I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005.  The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67.  I have already installed the Sybase client software on the server.

I also created a SystemDSN on the SQL Server to connect to the Sybase server.  I tested the connection and it was able to connect.

I ran the following code to create the linked server:

<code>

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'Sybase System DSN'

Linked server from SQL Server 2008 R2 to SQL Server 2005 not working

  

I'm trying to create a linked server from SQL Server r2 to SQL Server 2005 (both 64 bit Enterprise) using the following script

 EXEC master.dbo.sp_addlinkedserver
 @server = N'TestLS'
 ,@srvproduct=''
 ,@provider='SQLOLEDB.1'
 ,@datasrc=N'dw_prod'
 ,@provstr='Integrated Security=SSPI;';
 
 GO

SELECT
    *
 FROM
    [TestLS].[I_OLAP].[dbo].[FACT_AUDIT]
I verified that the Kerberos is working.

When I try to run a four part query as below it gives me the error

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
OLE DB provider "SQLNCLI10" for linked server "TestLS" returned message "Invalid connection string attribute".


If I try to set up the linked server against localhost (i.e. SQL 2008 R2, it works just fine).


Thanks,
GB


GBM

Not able to see microsoft oledb provider for oracle in sql server 2005 providers

  

Hi,

can any one help me, environment window server 2003  64 bit and sql server 2005 sp3 64 bit and i have installed oracle client  10g 32 bit. problem is, I am not able to see the provider name  while creating linked server.


Slow performance with SQL Server 2005 executing parameterized queries with LIKE and an aggregate com

  

We have a slow-performing query and, after some analysis, have narrowed the cause down to using parametrized queries in conjunction with LIKE and aggregate comparisons used in the WHERE clause.  If we use a non-parametrized query (but keep the WHERE clause the same) the query performs much faster and, in fact, executes a different plan.  Using OPTIMIZE FOR UNKNOWN is not an option for us as we are using SQL Server 2005.  Obviously, we'd like to use parametrized queries to prevent SQL injection but the slow perf is unacceptable.  We need the LIKE to support wildcard scenarios.  If we need to we'll use dynamic SQL (scrubbed as much as we can) in lieu of parametrized queries.  We're wondering, however, is there another option that would give us the perf without sacrificing safety?  I can provide a sample database, query, plans, etc. if necessary...I'm assuming SQL Server generates a less efficient plan with the parametrized query as it can't make any assumptions about the parameter values...

Mike Bouck


Slow performance with SQL Server 2005 executing parameterized queries with LIKE and an aggregate com

  

We have a slow-performing query and, after some analysis, have narrowed the cause down to using parametrized queries in conjunction with LIKE and aggregate comparisons used in the WHERE clause.  If we use a non-parametrized query (but keep the WHERE clause the same) the query performs much faster and, in fact, executes a different plan.  Using OPTIMIZE FOR UNKNOWN is not an option for us as we are using SQL Server 2005.  Obviously, we'd like to use parametrized queries to prevent SQL injection but the slow perf is unacceptable.  We need the LIKE to support wildcard scenarios.  If we need to we'll use dynamic SQL (scrubbed as much as we can) in lieu of parametrized queries.  We're wondering, however, is there another option that would give us the perf without sacrificing safety?  I can provide a sample database, query, plans, etc. if necessary...I'm assuming SQL Server generates a less efficient plan with the parametrized query as it can't make any assumptions about the parameter values...

Mike Bouck


SQL Server 2005 rebuild indexes find corruption or cross linked files

  

Does anyone know if this is a known issue? What do you do when this happens? We had to go to a good backup from 3 days prior.

From the application log it appears there were cross linked files. Is this caused by the OS or Sql Server?

Pat M

Reno


PatM Reno
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend