• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 822
  • Last Modified:

Linked Server stored procedure running VERY SLOW

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
kprestage
Asked:
kprestage
  • 3
  • 2
1 Solution
 
LowfatspreadCommented:
what is the column type of the pk column


have you tried an openquery query instead?

 
0
 
kprestageAuthor Commented:
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
 
LowfatspreadCommented:
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
 
kprestageAuthor Commented:
less than 1 second
0
 
kprestageAuthor Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now