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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Linked Server on SQL 2008 is slow

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

Hi,

I have 2 MS SQL servers one with SQL 2008(machine A) and another with MS SQL 2005 (machine B)

I created a linked server from Machine A to Machine B. Executed a simple query

Select * from [Machine B].Database.Xtable

It took nearly 30 seconds to bring back 496 rows of data.

Similarly created a linked server from machine B to machine A.Executed the simple query

Select * from [Machine A].Database.Xtable

It took less than a second to display the same 496 rows.

Is there any specific setting to be done on MS SQL 2008 to improve the performance.

Any help is appreciated.

Thanks

Hari




View Complete Post


More Related Resource Links

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

Problems with linked server to Analysis Services (SQL 2008)

  
I have problem with creating linked server from SQL database to Analsis services. BOth services are running on same machine. Operating system is Windows 2008. I create linked server (I use windows authentication and I am administrator on AS):  EXEC sp_addlinkedserver @server= 'OLAP_PRETOKI', @srvproduct = '', @provider='MSOLAP', @datasrc='localhost', @catalog='DWDatabase'  But when I try to test connection I get error (in the event log) and in the error log/dump I get this: 2010-09-03 13:48:28.41 Server Error: 17310, Severity: 20, State: 1. 2010-09-03 13:48:28.41 Server A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory. 2010-09-03 13:48:32.53 spid58 Open of fault log C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\log\exception.log failed. 2010-09-03 13:48:32.65 spid58 Using 'dbghelp.dll' version '4.0.5' 2010-09-03 13:48:32.66 spid58 SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 2010-09-03 13:48:32.66 spid58 * ******************************************************************************* 2010-09-03 13:48:32.66 spid58 * 2010-09-03 13:48:32.66 spid58 * BEGIN STACK DUMP: 2010-09-03 13:4

Problems with linked server to Analysis Services (SQL 2008)

  
I have problem with creating linked server from SQL database to Analsis services. BOth services are running on same machine. Operating system is Windows 2008. I create linked server (I use windows authentication and I am administrator on AS):  EXEC sp_addlinkedserver @server= 'OLAP_PRETOKI', @srvproduct = '', @provider='MSOLAP', @datasrc='localhost', @catalog='DWDatabase'  But when I try to test connection I get error (in the event log) and in the error log/dump I get this: 2010-09-03 13:48:28.41 Server Error: 17310, Severity: 20, State: 1. 2010-09-03 13:48:28.41 Server A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory. 2010-09-03 13:48:32.53 spid58 Open of fault log C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\log\exception.log failed. 2010-09-03 13:48:32.65 spid58 Using 'dbghelp.dll' version '4.0.5' 2010-09-03 13:48:32.66 spid58 SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 2010-09-03 13:48:32.66 spid58 * ******************************************************************************* 2010-09-03 13:48:32.66 spid58 * 2010-09-03 13:48:32.66 spid58 * BEGIN STACK DUMP: 2010-09-03 13:4

SQL Server 2008 R2 deploying any version of sharepoint results in extremly slow DB creation

  
Hello, When installing any version of Sharepoint(2007,2010) during the "Creating Configuration Database" Step it takes over 10 hours to complete.  Then creating a web app takes just as long. and the sites are extreemply slow.  Environment: 1) DB Server - SQL Server 2008 R2 on Windows Server 2008 R2 2) Webserver - to host Sharepoint 2010 on Windows Server 2008 R2 The Servers are on the same network and running with lots of horsepower, disk and memory.  Are there any know issues with this and should we install Cummulative Update 3? Maybe that has a fix.  We exhausted all our options and need justification to apply the CU3 hotfix(s). Thanks for your help.

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

Why call web serivce so slow on windows2003 sp2(or windows server 2008) iis6?

  

my web service reference a ATL COM dll(provide many interface to access MS Sql server).

i create a ATL Activex Control call the web service.

when i test IIS server, the problem appears:

1 when i use win2003 (no server packs). the activex call the web serivce fast, less than 1s.

2 when i use win2003 (sp1).   the activex call the web serivce fast less than 1s.

3 when i use win2003 (sp2).   the activex call the web serivce slow, test the same function will cost more than 3s.

4 when i use win2008. got the result same as 3.

Why ? Can anyone help me?

 

thank u all!
Chinese fairy tale

Unable to connect Excel 2003 from linked server in sql 2008 64 bit

  
Hi ALL,

I was unable to connect to excel 2003 with linked server created in sql server 2008 64 bit. This was working fine with sql server 2005 32 bit.

I know that there no 64 bit drivers for JET drivers, but i am looking for some alternative ways to do the same job.

I dont want to migrate to Excel 2007.

Thanks in advance for all your help.

Thanks,
Loknath

SQL Server 2008 client very slow on new Win7 PC

  

I installed SQL Server 2008 Management Studio on a brand new PC (HP ProBook 6540b with an i5 CPU and 2GB RAM) running Windows 7 x86. A script that runs some stored procedures on a Windows 2008 x64 server (also SQL 2008) and which returns about 10 result sets takes a fraction of a second on our old PC's, including one that runs Windows 7 (the rest are XP and Vista). But on this PC, it takes 30 seconds! I installed the client tools the same way as always, and applied SP1. I disabled the Windows Firewall and anti virus, and then disabled the Aero interface, and it is still slow (now about 15 seconds). Non-SQL functions are faster, e.g. copying files over the network is almost 3x faster than on my old PC, so there doesn't seem to be a general speed problem with the PC.

Do I need to change any settings for Windows 7? I ran the configuration manager, but the settings look the same as on the old PC. I disabled shared memory (I think this is just for a local database server?), but this made no difference.

Any ideas? Thanks!

 

 


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

Distributed transaction using linked server not working in SQL Server 2008 64 bit

  

Hi. I have had an issue trying to get distributed transactions to work in SQL Server 2008 using a linked server. The error message I get is

OLE DB provider "SQLNCLI10" for linked server "pod1" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "pod1" was unable to begin a distributed transaction.

My Environment:

Windows Server 2008 Enterprise 64 bit, SQL Server 2008 Enterprise 64 bit

Problem occurs with multiple different builds of SQL Server 2008 - I have been able to reproduce the problem with 10.0.1600.0, 10.0.1779.0 (CU 2), as well as 10.0.2531 (SP1)

 

I am aware that other people have had this issue and have reviewed all existing posts. I have verified that the MSDTC is configured correctly on both machines. I have also used DTCTester to verify that DTC is working correctly on all machines in question. None of the mentioned resolutions has solved this problem for me.

I am not seeing this problem occur on my 32 bit test machines - it is only occurring if at least one of the two machines is 64 bit.

 


The Application is performaing very slow after upgrade to SQL Server 2008 x64

  

Hello All,

I am writing you all after I tried a lot of solutions to resolve my Issue, please find below my issue details..

Task: Upgrade from SQL Server 2000 Ent. Edition to SQL Server 2008 X64 for IA on windows server 2008 Clustered environment (Active\Active)

SQL Server 2008 Servers HW configuration:

  • 24 GB Of Ram
  • 2x Process or dual core
  • SAN storage
  • up to 1 Tera Storage

How to create linked Server to connect from SQL Server 2008 to SQL Server 2000

  

How to create linked Server to connect from SQL Server 2008 to SQL Server 2000.

I following the steps to create the linked server in the GUI but failed with the following error message.

I used the full computer network name as the 'Linked Server' name

 OLE DB provider "SQLNCLI10" for linked server "my_server_network_name" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "my_server_network_name" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "my_server_network_name". The provider supports the interface, but returns a failure code when it is used.

Thanks a lot for your help.

Tom


SQL Server 2008 R2 Express 64bit Linked Server

  

Windows Server 2008 R2 Standard 64bit.
Sql Server 2008 R2 Express with Advanced Services 64bit.

I am converting an Access 2000 database to SQL Server. I developed a script that creates a linked server to import the data into SQL Server and manipulate it into the new structure. All works well on my 32 bit developement machine.

We just had the above new server built. The linked server is created without error, but trying to access the data in the Access database results in the error

'OLE DB provider Microsoft.Jet.OLEDB.4.0 cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode."

 In searching, it seems that there is no 64 bit provider for jet.

Once the new system is up and running, the application also requires some regular imports of Access and Excel files, so this is not a one-time problem.

I can convert all Access and Excel files to 2007 version (ACE) if needed. However, I did try adding a linked server using the ACE provider and got the following error when trying to access the linked server

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered. (Microsoft SQL Server, Error: 7403)

What are my options?

Do I need to uninstall 64bit SQL and install 32bit?

 


sql 2008 linked server

  

I am trying to create a linked server on our new sql 2008 machine.  I get the following error when I try to create it (login failed for user xxxx. (Microsoft SQL Server, Error: 18456).  I can sign-on to the server using ssms using the same username/password (on the same machine I am trying to create the linked server on).  I have tried creating other linked server and I get the same result.

Any ideas?

 

Thanks,

T


Linked server issue on test box, post upgrade 2005 to 2008 R2

  

In an effort to do some testing prior to upgrading our environment to 2008 R2, I made a test instance on our Dev box.  2005 instance, copied as many things as I could think of from various other instances.  Made a basic linked server to our main cluster, had a repeating job to email me results of a query across that link every few hours.

 

Everything was working fine until the upgrade finished.  It completed at ~7pm.  The email at 6PM came through fine (while the upgrade was in progress), the email at 8pm didn't come through.  Checked various things (DB mail was still working, tested fine).  It couldn't access the data across the linked server.  I tried deleting that link and remaking it.  Errors out.  Tried running the same scripts we use to create our standard linked servers, error out.  The only ones that I can set up and function are links to other instances on the same box.

I've looked around at other fixes for this error message and none seem to make any difference.  Log in with Domain cred's, log in with the sa account, no difference.  I can connect from other instances & servers back to this one, just not outbound from this one.  And it worked prior to the upgrade I applied to it.  All other instances on the box are 2005 as well.

Here is the message I ge

Suggestion about exporting data into Access database from sql server 2008 without Linked Server

  

Hello,

    We have a website that was using Sql server 2005. There was a option in Website where when a user clicks a button, Some tables data will be exported to an access database. To achieve this, we have setup a linked server to Access database. We have moved from sql server 2005 to 2008 (64bit). We did not want to use that option of having a linked server now. I am looking for different ways to achieve the task. We also donot want to use OPENROWSET and OPENDATASOURCE .

 

Can someone  give some insight about this?

 

Thanks,

Nag


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