Queries not using remote indexes with variables

I've made this question once.

I'll make it again, maybe I'll have more luck this time.

The problem is with simple queries against linked servers not using indexes in the remote server when I'm using varibles.


for example :

select * from linked.db.dbo.table where indexed_column = 1

works fine. The filter is applied remotely, and the query is fast.

if i do

declare @a int
set @a = 1

select * from linked.db.dbo.table where indexed_column = @a


the filter is apllied in the local server, not in the remote, so the query becomes slow.

I've played with linked server options (collation compatible, etc, etc ) with no success.

Any ideas ?

Thanks

It's not the ideal situation, because I've simplified the query in the example given.
In my real situation I got local joins and other stuff, so I won't be able to do simply as pointed in the KB.

Anyway, I can rewrite a little bit my code and put the remote rows in a table variable first with your suggestion.

So far is the best I got.

Thanks Luis.

I didn't find a way to put the remote rows in a local table variable with any of the three methods proposed by the KB article.

Cause every one of them is based on exec.

If you say "local joins", do you mean you're joining remote and local tables?<br />And are you evaluating the variable in expressions with both local and remote columns?<br /><br />In either case, the local SQL Server probably decides it<br /><br />I would look at changing the reference to the remote table into ...<br /><br />(1) a derived table, containing the variable in its own WHERE statement,<br /><br />or (2) a UDF that returns a table, defined in the remote database, which takes the variable as its parameter,<br /><br />or (3) use the OPENQUERY syntax - not one that I'm familiar with, but it should work just fine -<br /><br />or (4) a sproc defined in the remote database and a local temp table (remember to apply appropriate indexes) ...<br />using the <b>INSERT INTO #Tbl EXEC remoteserver.remotedb.owner.sproc &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />arameter&gt;</b> syntax.<br /><br />or (5) the same as (4), but using <b>EXEC remoteserver.remotedb.dbo.sp_ExecuteSQL ......</b> for maximum flexibility.<br /><br />If you need the same filtered set of data in successive queries, options (4) and (5) would be best.

yes. "local joins" means joins beteween local table and remote.

but what I'm trying to do now is, bring the remote rows locally somehow and then join with that.

I'm trying option 2, using a remote UDF tabled valued, but I can't seem to do the remote select.

select * from remote_server.database.dbo.function ( @parameter )

I keep getting Incorrect syntax near '('.

Is is possible to do that ?
I mean a remote select from a parametrized tabled valued UDF ?

I was trying from SQL 2000 to SQL 2005.

Now I've tried from SQL 2005 to SQL 2005 and got :

Server: Msg 4122, Level 16, State 1, Line 11
Remote table-valued function calls are not allowed.


A little more explicit.

So i guess it can't be done with UDF.

This is becoming a nightmare.

The UDF aproach dind't work.
The table variable we all know won't work.

Now I've tried with a #temporary table.

But all this work I have to do it inside a table valued function.
And when I try to do the exec into #tmp stuff I get :

Server: Msg 2772, Level 16, State 1
Cannot access temporary tables from within a function.

So (4) and (5) don't work inside a UDF table valued function.

If I try to "cheat" it doing the insert into #tmp using all dynamic SQL , I got :

Server: Msg 558, Level 16, State 2
Remote function calls are not allowed within a function.

It seems there is no way to get around this.
All this work sholud be done inside a table valued UDF. This is not negotiable.

The idea with the temp table was to define it in your local sproc, and make the remote server execute a query that returns the results into the local temp table:

CREATE TABLE #Tbl (................)

INSERT INTO #Tbl
EXEC linked.db.dbo.sp_ExecuteSQL
N'SELECT * FROM dbo.table t WHERE t.indexed_column = @a', N'@a INT', @a

Then you can process the results locally, like:

SELECT * FROM #Tbl

Make sure your temp table has a PK and proper indexes, certainly if there's a lot of data coming from the remote server.

I understand that.
But the problem is I don't have a local sproc, but a local UDF table valued function.
I can't change that now, the system is running and relying on that.

And somehow I can't use #temp tables inside a UDF table valued function.
Is there some way to get around this ?

Like I've already suggewsted: do this in a stored procedure.

And you don't have to use the remote UDF.

Do you have any problem calling sp_ExecuteSQL on the remote server? This will return a rowset, which in my sample code is inserted into the local temp table. You then use the temp table for further local processing.

It seems I'm not making myself clear.

All the insert into #tmp and exec remote stuff is perfect and it works fine on its own.

BUT, all that work I have to do it inside a _LOCAL_ UDF table valued function that existed before all the remote server stuff.

The local UDF table valued function was used BEFORE the remote server and there's no way to avoid it.

And the problem is I can't use temp tables inside a UDF. ( I didn't know that until yesterday ).
And I can't use a table variable cos of course I can't put the remote rows inside a table variable.

So I'm seriously stuck here...

Any ideas ?

OK, so it's a local UDF. Where is this UDF being used? Can't see why it must be a UDF from what you've told us so far.

It has to do with the system structure. We use table variables and table valued UDFs.
It's the most flexible way ( except for this damned thing of not letting me use #temp tables.... )

It can't be changed now.

You are aware that you can use the table that you define as the return value of the UDF, as a temp table within the UDF itself? So no need to declare a separate table (which indeed is not allowed in a UDF).

create function dbo.test1()
returns @test table (col1 varchar(10))
as
begin

insert into @test values ('a')
insert into @test values ('b')

return

end
go

select * from dbo.test1()

Yes. Actually the local table valued UDF it's like that.

But inside that pre-existantant table valued UDF I have to do the remote exec and stuff first into some table.
Then some joining with local tables and then feed the resulting table.

And where do I am supposed to store the remote rows ?
The only place is inside a #temp table. And I can't use it inside a function.

It's really annoying.

Yes, but what kind of object is it from which you're calling the table-valued UDFs? It sounds like a system designed by an object-oriented programmer, instead of a database developer.

It's called from sprocs.

And how come those sprocs are carved in stone?

The thing is it's more flexible with a table valued UDF, because I can select some columns from it, filter rows, etc.

If it were an sproc the resulting rowset can't be "manipulated".

I can understand that, but why can't those sprocs have temp tables where you put the results from the UDFs?

Also remember that when you refer to that UDF twice in a procedure, it is retrieving the data twice - it's not a recordset object like in a regular programming language.

Because I don't like and avoid #temp tables. It's a lot cleanear table valued UDFs.

The secoind point it's perfectly clear.

Yes, but you're running into the limitations here. And if you don't like temp tables, your sprocs can always use table variables instead.[<img src='/community/emoticons/emotion-5.gif' alt='' />]

I've resolved.<br /><br />Instead of changing my system phylosophy and design, I've got the damned remote filter work remotely as it should be in the first place.<br /><br />I've set DynamicParameters=1 and NestedQueries=1 in SQLOLEDB registry as suggested in :<br /><br /<a target="_blank" href=http://www.dbforums.com/archive/index.php/t-508636.html>http://www.dbforums.com/archive/index.php/t-508636.html</a><br />and<br /<a target="_blank" href=http://sqlpass.org/Forums/messageview.cfm?catid=359&threadid=7543>http://sqlpass.org/Forums/messageview.cfm?catid=359&threadid=7543</a><br /><br />and magically the original <br /><br />select * from linked.db.dbo.table where indexed_column = @a<br /><br />Started to apply the filter remotely and not locally.<br /><br />I'm good or what ? <br />well, at least at googling around <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br />

Good for you!

Good for all of us.

Next time somebody came around asking the same thing somebody will remember this.

Thank you for posting the resolution.

Sometimes I have to give back something to the community for a change [<img src='/community/emoticons/emotion-1.gif' alt='' />]

Apologies, that came out wrong. Thanks for sharing the knowledge!

Any info on possible side-effects when changing that registry setting?

No problem buddy.<br /><br />What "side-effects" ? Once something works I never look back [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Actually I should see why those registry settings where different than the supposedly should be.<br /><br />I don't think that may adversely affect something, as they were set to more restricted values.<br />Now the provider is "more capable" than before.<br /><br />Well, any investigation on this won't hurt me, right ? [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />

That's the spirit!

Tell my bosses that's not wasted time [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br />

So you like to live dangerously, Mr Bond?

All the problems were due to that damned server was migrated (ages ago.....) from 7.0 to 8.0<br />I did't remeber that though.<br /><br />I guess that's why those SQLOLEDB settings were set.<br /><br />Strange I didn't check the linked stuff problem from another server.<br /><br />Another "How I didn't see it?" after the resolution cames to the light [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Won't be the last, huh ? [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br />

There are lots of legacy systems out there, both in software and hardware, so it's good to know about this type of complication.

Thanks!