The good thing about synonyms if that they can be rebuilt quite easily with a script. Maybe I'd check out that route.

Yup it's the print command... unless you hit a real weird "bug" bu using order by newid() in the concatenation. Otherwise it's the print issue.

Just concatenate the code in varchar(max) var and exec that... fully automated.

My first instinct was to use synonyms but I don't really save any work saved over there. Would be a pain to refactor the application to use them...

I'd do that in the dataset sql side. I would not waste my time trying to do that in SSRS.

CREATE PROCEDURE dbo.SearchResellers

@Location VARCHAR(100)--100??

, @Type VARCHAR(100)--100?

AS

SET NOCOUNT ON

SELECT

...

In that case a scan will never be a problem.

It'll work, but it'll never be very fast and there's not much we can do to help.

Same problem. The design of the UI is wrong, table design is wrong and there's not much you can do to tune this except maybe full text indexing.

Again my...

Why don't you tables for those 2 columns? The design doesn't seem to make much sense at the moment.

Can you post the tables definitions (create table script)?

The order by is not the problem. You don't understand the way it works or the requirement is wrong.

Well the real question is do you use string litterals or do you have ids for all the values in both dropdowns?

I haven't done any testing on that type of...

You're bound to get a scan unless you're willing to move away from LIKE '%<search>%'

Now the real question is how fast do you need this to go and how fast...

He doesn't need to ge to the view once per perdiod, once overall is enough!

Then maybe I misunderstood the question. can you give more details and examples of what you need help with?