Your original suggestion works great when the parameter passed in returns no results - which was my original question that you've successfully answered. I was just providing more information...

No - there is nothing else going on inside the stored procedure. The stored procedure simply contains the query I have in my original post.

I completely understand that it was a shot in the dark - was just posting what my findings were. I appreciate the responses! In terms of the stored...

I created the index, however unfortunately it won't help me in the long run. The reality is, is that most of the time the query does returns data - it...

That makes sense - I will give that a shot. Thanks!

Thanks Lynn. I like that idea - however one question I have is wouldn't the use of 'like' in the query prevent the new nonclustered index from being utilized...

Thanks for the response, but I'm fairly certain parameter sniffing isn't the case here. I also explored that option as that was my first thought however if I free...

Anyone have thoughts on this? Does SQL Server just retain the statistics that were current prior to the index rebuild for a specific partition?

I was never suggesting to use dynamic sql when not necessary. Of course if it isn't necessary, you wouldn't use it. The code I posted above was from...

Using dynamic sql is the way to go. Here's an example code snippet:

SELECT @sqlCMD = 'USE [' + @databaseNm + '];

INSERT INTO #tmpOrphanUsers (dropCmdTxt, Completed)

SELECT '+''''+ 'USE ['...

I work in the heart of downtown in the city I live in, so I have to use a public ramp. I have two spots that I park in,...

Good catch NJ-DBA - sounds like that is indeed the case in regards to the rule allowing access from some and not others.

You need open a rule in Windows Firewall UDP Local port 1434 on your SQL Server - sounds like this should solve your issue.

Is Windows Firewall disabled on both the client and the server ?(Not suggesting this, just curious as to what your current configuration is.) And you're sure you aren't using...

I typically see this in a client-server situation when one of the following is true:

1. The instance name is spelled wrong (probably not the case here)

2. UDP remote port 1434...