[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Linked Server stored procedure running VERY SLOW

Posted on 2003-12-04
5
Medium Priority
?
801 Views
Last Modified: 2006-11-17
I have two servers, each running win2k advance, and sql server 2000 enterprise.  Service pack 3 on both SQL Servers.  If I run the following query, I get the results back instantly

select * from linkserver.db.dbo.table where pk = 1

but if I run

declare @pk int
set @pk = 1
select * from linkserver.db.dbo.table where pk = @pk

it takes 7 minutes to return.  Any ideas what the problem is?  


0
Comment
Question by:kprestage
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9876787
what is the column type of the pk column


have you tried an openquery query instead?

 
0
 
LVL 9

Author Comment

by:kprestage
ID: 9876852
The parameter is an int data type and the column on the table is int too.  Open query wont work in this situation.  I need to get it working as a linked server.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 9876908
how long does

declare @pk int
set @pk = 1
declare @sqlstr varchar(8000)
set @sqlstr =
'select * from linkserver.db.dbo.table where pk =' + convert(varchar(10), @pk)
exec (@sqlstr)

take?

0
 
LVL 9

Author Comment

by:kprestage
ID: 9876936
less than 1 second
0
 
LVL 9

Author Comment

by:kprestage
ID: 9877106
LowFatSpread...

You didnt fix my problem, but I appreciate the help!  I think I found the solution...  The linked server were set up using the SQL Server option rather than the OLEDB Provider for SQL Server.   I created a new linked server and selected OLEDB Provider for SQL Server and then click on Provider Properties.  I set the checkbox for dynamic parameters = true for the provider, clicked apply, and then cancelled adding the new linked server.  Making that change fixed the problem on my existing linked servers!  Thanks again for your help.  
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question