Board index » Visual Studio » sp not returning recordset to ASP/VBS page

Visual Studio37
Hi, I basically copied and editted sp_databases to only show me dbs over a

certain size and tested it in Query Analyzer and it works fine. I wrote an

asp page (and tried it through VBScript as well) that calls the stored

procedure. I checked with Profiler and the sp is getting executed by my asp

page so I don't think its an issue with my permissions but its not returning

a recordset nor generating any errors (and i checked SQL server logs and

Event logs as well). The recordset is simply null.

=============VBScript=================

DSN = "Provider=SQLOLEDB; Data Source=SERVER; Initial Catalog=Master; User

ID=naria; Password=mypsd;"

Set conn = CreateObject("ADODB.Connection")

Set cmd = CreateObject("ADODB.Command")

Set rs = CreateObject("ADODB.Recordset")

conn.Open(myDSN)

cmd.ActiveConnection = conn

cmd.CommandText = "sp_databasesCOPY"

cmd.CommandType = adCmdStoredProc ' I tried with 4 also

cmd.ActiveConnection = conn

Set rs = cmd.Execute()

rs.movefirst

Do while NOT (rs.Eof)

msgbox("hello")

'some code here to write to a file but the Hello doesn't even show

rs.movenext

Loop

=======================



what am i doing wrong? Any feedback would be appreciated.



Thanks in advance

Ayla


-

Hi Naria,



can you remove this line "rs.movefirst" and try again.You are basically

trying to come backward with a ForwardOnly (which is deafult)

recordset.



rgds

Babu



Naria presented the following explanation :



-

I just tried that. No love. I tried it in both ASP and VBScript. At this

point, I'm starting to think it might be my stored procedure (its a copy of

sp_databases with a filter on size, so just an additional WHERE clause).



"Babu VT" wrote:

Naria wrote:

really don't need a Command object, but I'll get into that later. I suspect

you need to add "SET NOCOUNT ON" to your procedure. Without that command,

the informational "x records affected" messages that you see in QA are

returned to ADO as closed recordsets.



If that's not the problem, could you show us the CREATE PROCEDURE script?



And this post talks about my preferred techniques for executing stored

procedures from ASP, along with my rationalizations for those preferences:

http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en" rel="nofollow" target="_blank">groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701&

--

Microsoft MVP - ASP/ASP.NET

Please reply to the newsgroup. This email account is my spam trap so I

don't check it very often. If you must reply off-line, then remove the

"NO SPAM"





-

Hello Mr. Barrows, I have read a lot of your posts. Here's my SP and its a

copy of sp_databases



===========================

CREATE proc sp_databasesCOPY

as

set nocount on

declare @name sysname

declare @SQL nvarchar(600)



/* Use temporary table to sum up database size w/o using group by */

create table #databases (

DATABASE_NAME sysname NOT NULL,

size float NOT NULL)



declare c1 cursor for

select name from master.dbo.sysdatabases

where has_dbaccess(name) = 1 -- Only look at databases to which we have

access



open c1

fetch c1 into @name



while @@fetch_status>= 0

begin

select @SQL = 'insert into #databases

select N'''+ @name + ''', sum(size) from '

+ QuoteName(@name) + '.dbo.sysfiles'

/* Insert row for each database */

execute (@SQL)

fetch c1 into @name

end

deallocate c1



select

DATABASE_NAME,

DATABASE_SIZE = (size*8/1024)/1024,/* Convert from 8192 byte pages to MB */

REMARKS = convert(varchar(254),null) /* Remarks are NULL */

from #databases

where ((size*8/1024)/1024)>10 /* Yes, i have huge db's*/



order by 1

GO



===========================



Thank you so much for helping me!





"Bob Barrows [MVP]" wrote:

Naria wrote:

Could ths be the problem? When you run the procedure in QA, are you running

under the naria account?



--

Microsoft MVP - ASP/ASP.NET

Please reply to the newsgroup. This email account is my spam trap so I

don't check it very often. If you must reply off-line, then remove the

"NO SPAM"





-

Naria wrote:

Is this a typo in this email? I don't see where you've defined myDSN

anywhere. Do you have "On Error Resume Next" in your code? If so, comment it

out while debugging.





--

Microsoft MVP - ASP/ASP.NET

Please reply to the newsgroup. This email account is my spam trap so I

don't check it very often. If you must reply off-line, then remove the

"NO SPAM"





-

I think you're a genius, let me take a look at that portion.



"Bob Barrows [MVP]" wrote:

Naria wrote:

:-)

Call me Bob.



I just tried your code which worked as expected after I fixed the "myDSN'

problem. I made sure I was using a user account that had access to all the

databases.

--

Microsoft MVP - ASP/ASP.NET

Please reply to the newsgroup. This email account is my spam trap so I

don't check it very often. If you must reply off-line, then remove the

"NO SPAM"





-

Thank you so much, it worked!



"Bob Barrows [MVP]" wrote: