Problems with what should be a simple query (Cursors and temp tables)

[palagrim]

Good Morning!

First off, let me get my disclaimer out of the way - I'm a relative newbie at this SQL lark, so please ignore the kludgy-ness of this code etc. Thanks ;-)

Firstly, here's my query:

SET DATEFORMAT DMY

declare @issuecounter int; -- Declare
declare @previssue int; -- Declare
declare @date smalldatetime -- Declare

DECLARE @issues TABLE (issues int, dates smalldatetime) 
-- Declare

DECLARE issue Cursor scroll FOR -- Create the cursor
      select articleIssue, articleDate from cr_newsletterArticles 
          where newsletter = @newsletter -- Which Newsletter
          order by articleIssue desc ;

   open issue -- Open the cursor
   SET @previssue = 0 -- Set our comparison variable to 0

   FETCH next FROM issue -- Grab the first recordset from the cursor
      into @issuecounter, @date -- Stick it into our variable
	WHILE @@fetch_status = 0 -- While we're notatEOF
	 begin -- begin
              if @issuecounter != @previssue 		BEGIN -- begin
                insert into @issues(issues,dates)
	values(@issuecounter, @date)
	SET @previssue = @issuecounter -- set comparison variable to issuecounter
	END
             fetch next from issue -- Grab the next recordset
	        into @issuecounter, @date
             END -- End
             CLOSE issue -- close cursor
             deallocate issue
             select * from @issues
GO

When I run this query in Query Analyser, it seems to work fine. IE. I get a table returned with the correct values. However, when I try and get this data into ASP via the old objRS.open "EXECUTE stored procedure <value>" command, no data is returned...

I'm not sure if this is an ASP problem, or a SQL problem... So, I altered the query slightly, and instead of a temporary table, I used a static one in the database, and it still didn't work - even though when I open the table in Enterprise manager the content is there...

Also, if I run the query via an ASP objCommand.execute "procedure <value> " and then use objRS.open "SELECT * from table" then it pulls the data in properly then...

If I was dealing with millions of recordsets in my original table, then I can understand that maybe the ASP isn't waiting long enough for SQL to return its final table. But I'm currently only dealing with about 50... I'd rather use the temporary table route rather than static incase of multiple hits at the procedure at the same time...

Any ideas? Please go easy - I refer you back to my disclaimer ;-)

Thanks...

 

[ChrisCarroll]

Your code doesn't look kludgier that the normal standard of T-SQL to me ...

Since the code you give doesn't actually create a stored procedure, (i.e. it's not of the form:

Create Procedure procName( @newsletter int)
As

) could you assure us that
(1) you have created an sp to run this code and
(2) that when you run the sp from query analyzer it works and
(3) that the web server is not lacking permissions to run the sp in question?


Chris

 

[palagrim]

1) I have done the CREATE PROCEDURE thing in the actual stored procedure on the server. I have write access to the server and haven't had any issues creating sp's before. Just didn't cut n' paste it.

2) It's executing fine from QA. No problems - returns the correct 2 column table. The sp is being ran from the ASP side and I know this 'cos when I was dumping the data in to a proper static table, it was filling it with the correct results. But it doesn't seem to return the final select to the ASP code.

3) The web server doesn't have any problems running any of the other sp's I've written. I have perms to run everything. (It's for an intranet thing I'm playing with and this is something like stored procedure 12 or something - admitedly, most of those are just basic select/insert jobs)

Thanks for any help you can give -
It's really doing my head in ;-)

 

[palagrim]

Er.... Problem solved....

SET NOCOUNT ON at the top of the query... It appears that SQL was returning a recordset to ASP everytime it added a row to the table... Which was causing ASP to have a fit...

Thank you for replying to my post! Much appreciated!