kprestage
asked on
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?
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?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
less than 1 second
ASKER
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.
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.
have you tried an openquery query instead?