Re: Different results between stored procedure and query analyzer

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 07/19/04 

Date: Mon, 19 Jul 2004 11:28:25 -0400

Scott,

Can you please post DDL for the tables (in the form of CREATE TABLE
statements), some sample data (in the form of INSERT statements), the full
code of the sproc, exactly the syntax you're using to call the sproc, and
exactly what you're running from QA (including DECLARE @loginname, etc)?
This way, we can either verify your problem and help you fix it more easily,
or in the process of preparing all of this you might figure out where you
are going wrong.

"Scott McNair" <scott.mcnair@sfmco.takethispartout.com> wrote in message
news:Xns952B69696AEE3sfmco@207.46.248.16...
> I'm running into a strange behavior on Query Analyzer. I've got the
> same line of code in a stored procedure and in QA:
>
> SELECT count(*) as Unread FROM tbl_productimages where imagetracking not
> in (select imagetracking from tbl_tracking where loginname = @loginname)
> and disabled is null
>
> However when I run it with the SP, I get "55" as a result (which is
> correct), but when I run it in QA, I get "0" as a result.
>
> Both instances are running as SA, and I'm running it in the same
> database. If I try running individual portions of the line, I get
> results (i.e., "select imagetracking from tbl_tracking where loginname =
> @loginname" returns proper results and "SELECT count(*) as Unread FROM
> tbl_productimages where disabled is null" returns proper results). Also
> I changed "not in" to "in" and it worked, so it looks like the problem
> is with the word "not".
>
> I've had two other co-workers look over the line, and they've played
> with the syntax until they were blue in the face with no better results.
>
> I really think I'm running into some sort of QA bug, but I'd like to get
> some feedback from the experts before I say that for sure.
>
> Regards,
> Scott
>
> PS imagetracking is a uniqueidentifier, if that matters at all.

Relevant Pages

  • Re: SQL Table and Excel - Maybe not the best way??
    ... you need to cut and paste real queries that run without syntax ... Apparently you are running a query, ... posting), and the results you want to see from the sample data, it will be ...
    (microsoft.public.sqlserver.programming)
  • Re: COUNT function not working with GROUP BY
    ... us more info, and maybe even some sample data and sample output, or at least ... SQL Server MVP ... > FROM Base_Contacts LEFT OUTER JOIN ... > syntax must be wrong because the ...
    (microsoft.public.sqlserver.server)
  • Re: "pivoting" when the column selectors are inequalities?
    ... Or maybe I'll go back to another forum where I got the same answer, ... This isn't about a "preferred questioning style". ... syntax and questions about relationships. ... You don't need to post DDL ...
    (microsoft.public.sqlserver.programming)
  • Re: Different results between stored procedure and query analyzer
    ... > sproc, and exactly what you're running from QA (including DECLARE ... I swear, though, that with the exception of the sample data, this is ... SELECT countas Unread FROM tbl_productimages where imagetracking not ... in (select imagetracking from tbl_tracking where loginname = @loginname) ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 2000 Sproc Role security bypassed when using QBF?
    ... I should add that the dynamically built Varchar is run using the syntax ... EXECUTE function requires the additional SELECT permission that sprocs ... I've got a Query By Form sproc that is used in a search process by ...
    (microsoft.public.sqlserver.security)