Re: No results from query in some contexts

From: Iain (Iain_at_idclTAKEAWAY.co.uk)
Date: 11/24/05 

Date: Thu, 24 Nov 2005 16:05:40 +0000

On Thu, 24 Nov 2005 09:49:19 -0500, Andrew J. Kelly wrote:

> Can you at least show us the code? My guess would be one of two things.
> Either you are connecting to a different db or the ASP app is not formatting
> the query properly. More specifically it may be treating the value in the
> WHERE clause as the wrong datatype. But without seeing your query and the
> DDL for the table we can make guesses all day long.

Thanks for your replies!

A sample stored procedure is

ALTER PROCEDURE dbo.Test
AS
        SET NOCOUNT ON
        select * from aspnet_Users
        RETURN

(I have FormsAuthentication running and the asphet_Users database has about
half a dozen records in).

If I execute this sproc from SQL Server Management studio connected to the
database, I get my half dozen records back.

If I execute the same procedure from within visual studio 2005 I get

ApplicationId UserId
UserName
LoweredUserName
MobileAlias IsAnonymous LastActivityDate
---- --- --
No rows affected.
(1 row(s) returned)
@RETURN_VALUE = 0

( I don't understand the one row returned, though!)

If I open up the table in visual studio 2005 I see the records.

If run the query in VS 2005 as a new query (select * from aspnet_Users) I
get the results I expect.

In both cases the connections are with Windows Authentication.

If I run my app and step through the code which calls one of the sprocs
(not this one a more complex one, but one which works in the Management
studio) I see that it returns no entries in the result set.

I do not see any errors being raised at any point - it's as ifhte result
set from a sproc just gets lost in some cases!

Having said all that the application is accessing some tables through
sprocs because the asp.net forms authentication is working, which means it
is getting data from the tables made by the aspnet membership wizards.

What is confusing me is that I get no data back. If i got an exception
because of security I'd see what to do!

Iain 

-- 
Iain Downs (DirectShow MVP)
Commercial Software Therapist
www.idcl.co.uk

Relevant Pages

  • VS2005 Remote Debug connection problem
    ... I got error message when trying connecting to remote debugging machine using ... Microsoft Visual Studio Remote Debugging Monitor named 'machine_name'. ...
    (microsoft.public.vsnet.debugging)
  • Re: No results from query in some contexts
    ... maybe even read permission on that table as well. ... ensure you are connecting and executing that proc as you expect. ... > If I execute this sproc from SQL Server Management studio connected to the ... > If I open up the table in visual studio 2005 I see the records. ...
    (microsoft.public.sqlserver.security)
  • Changing ComboBox Data
    ... I am writing the application in Visual Studio 2005 and am connecting ... have this choice change the information bound to my combo box. ... instead of displaying product description, ...
    (microsoft.public.dotnet.languages.vb)
  • Re: No results from query in some contexts
    ... Try giving execute permission directly to that account and ... > ensure you are connecting and executing that proc as you expect. ... The sproc works (and I can print the ROWCOUNT ... Iain Downs ...
    (microsoft.public.sqlserver.security)
  • Re: How to add a filter?
    ... I tried to compile including the library and now it shows ... I think that I have understood the fact of connecting both filters but ...
    (microsoft.public.win32.programmer.directx.video)