sp_databases called from code

4 Aug 2006 1:26 PM

Naria

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

4 Aug 2006 2:05 PM

Tav

Naria wrote:
Show quote

> 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.

Hi Naria,

There doesn't seem anything wrong with the code to me. Have you tried
this connection string:

"DRIVER={SQL
SERVER};SERVER=SERVER;DATABASE=Master;UserID=naria;Password=mypsd;"

or try this HTML page on the web server:

<html>
<body onload="DoMe()">
<script language="VBScript">
Sub DoMe()

    DSN = "DRIVER={SQL
SERVER};SERVER=SERVER;DATABASE=Master;UserID=naria;Password=mypsd;"

    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = DSN
    cmd.CommandType = 4 'adCmdStoredProc
    cmd.CommandText = "sp_databasesCOPY"

    Set rs = cmd.Execute

    rs.MoveFirst

    Do while NOT (rs.EOF)

        document.all.debug.innerHTML = document.all.debug.innerHTML &

rs("DATABASE_NAME") & "<br>"

rs.MoveNext

Loop

End Sub
</script>
<div id="debug"></div>
</html>

Regards,

-Tav.-
Tavis Pitt

4 Aug 2006 3:29 PM

Naria

Thank you but it was the permissions on the dbs themselves.

Show quote

"Tav" wrote:

>
> Naria wrote:
> > 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.
>
> Hi Naria,
>
> There doesn't seem anything wrong with the code to me. Have you tried
> this connection string:
>
> "DRIVER={SQL
> SERVER};SERVER=SERVER;DATABASE=Master;UserID=naria;Password=mypsd;"
>
> or try this HTML page on the web server:
>
> <html>
> <body onload="DoMe()">
> <script language="VBScript">
> Sub DoMe()
>
>     DSN = "DRIVER={SQL
> SERVER};SERVER=SERVER;DATABASE=Master;UserID=naria;Password=mypsd;"
>
>     Set cmd = CreateObject("ADODB.Command")
>     cmd.ActiveConnection = DSN
>     cmd.CommandType = 4 'adCmdStoredProc
>     cmd.CommandText = "sp_databasesCOPY"
>
>     Set rs = cmd.Execute
>
>     rs.MoveFirst
>
>     Do while NOT (rs.EOF)
>
>         document.all.debug.innerHTML = document.all.debug.innerHTML &
> rs("DATABASE_NAME") & "<br>"
>
>         rs.MoveNext
>
>     Loop
>
> End Sub
> </script>
> <div id="debug"></div>
> </html>
>
> Regards,
>
> -Tav.-
> Tavis Pitt
>
>