Running T-SQL from ASP, resulting in more than one grid

Some T-SQL commands gives, as I've understood, more than one result set. For instance, running "sp_spaceused" in Q.A. displays more than one grid.
I'm trying to fetch the second grid when running sp_spaceused from ASP, but it's only the columns of the first grid that the ADO connection can display (referring to the second grid returns an error message).

How can I fetch the second grid?You need to use the nextrecordset method.|||Greetings,

One thing to note on this subject is that you must be using MDAC2.5 or higher and unless you are coding in .NET then you can not disconnect the recordset, you must leave it open. Until .NET there are also certain properties that must be used on the recordset object for it to work properly. Goto www.microsoft.com and type "nextrecordset" to find documentation then type "nextrecordset bug" to see workarounds for problems you might have.

HTH!|||Disconnecting a recordset in asp (pre .net) is very rare and a performance hit, so I would be surprised if this is an issue.

Check out the following article on asp performance with ss2k:

article (http://www.sql-server-performance.com/asp_sql_server.asp)|||Originally posted by rnealejr
Disconnecting a recordset in asp (pre .net) is very rare and a performance hit, so I would be surprised if this is an issue.

Check out the following article on asp performance with ss2k:

article (http://www.sql-server-performance.com/asp_sql_server.asp)

Neal,

The article has some valid points but as a developer it is our job to determine which is the best approach for the situation. There are generally reasons why a developer chooses a particular implementation, at least there should be, the same as why databases are denormalized. Just my own opinion...|||Thanks - nextrecordset worked fine. If I just had had some sort of methods&attributes references available, I wouldn't have had to ask...

My little ASP now works fine when I connect to the database server I have on my machine. However, I can't connect to another SQL Server machine, I get an error message saying that WIndows NT Anonymous user isn't defined or similar. I've set up things (SQL Server authorities and the connection string) the way it's supposed to be, but still I get this message. The database only allows WIndows NT authentication, and my user is defined as Dbo for the database.
The Personal Web Server allows Windows NT authentication (no anonymous login), which should work, but still SQL Server says I', trying to login anonymously?|||Here are a couple of articles that should clear up the process for you:

article 1 (http://support.microsoft.com/default.aspx?scid=KB;en-us;247931&)

art 2 (http://support.microsoft.com/default.aspx?scid=KB;en-us;307002&)|||I've already checked those. The message I get is:

Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

My Personal Web Server is defined to use WIndows NT authentication, no anonymous login allowed.
Same problem if I set anonymous login with my userid as the user to run under.|||Originally posted by Coolberg
I've already checked those. The message I get is:

Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

My Personal Web Server is defined to use WIndows NT authentication, no anonymous login allowed.
Same problem if I set anonymous login with my userid as the user to run under.

If you are using SQL Server 2k and you are defining a username and password in your connection string then check this. Open Enterprise manager and right click on the server icon (the one with the green arrow) NOT the databases listed under that server. You will see a tab named SECURITY, click that tab. Make sure that under Authentication it is marked as "SQL SERVER and WINDOWS" not just WINDOWS. In SQL Server 2k it defaults to Windows Only and if this is checked then you can not define a username and password. Instead you define the property "Trusted_Connection=true; or Trusted_Connection=SSPI;"

Let me know if this resolves your problem.|||> In SQL Server 2k it defaults to Windows Only and if this is checked
> then you can not define a username and password. Instead you
> define the property "Trusted_Connection=true; or
> Trusted_Connection=SSPI;"

We're always using WIndows Only here.
The connection string etc is:

set conn = createobject("ADODB.Connection.2.6")

conn.provider = "SQLOLEDB"

conn.connectionstring = "data source=server1;initial catalog=pubs;Integrated Security=SSPI;Trusted_Connection=Yes;"

conn.open

set rs= conn.execute("sp_spaceused")

I've created a little VisualBasic6 with similar connection, and that one works fine for any SQL server and database where the NT group my user is listed in is defined as Dbo. It's just for ASP it doesn't work.
In VB6, I've coded:

strConn = "Provider=MSDASQL;Driver={SQL Server};" & _
"Server=server5;Database=Northwind;"
Set cnPubs = New ADODB.Connection
cnPubs.CursorLocation = adUseClient
cnPubs.Open strConn|||Try running a trace, using the profiler, on the users trying to connect to sql server. When you try to load your asp page you should see the name of the user trying to hit the server. I am guessing, without being able to see anything, that somehow it is still using the anonymous account. Anyway take a look at that and let me know.

Just for your own information. In all my web apps I create accounts for different user types and use them in the connection strings depending on what they are allowed to connect to. Especially since you are using stored procs you can just specify which users have rights to run the stored procs and don't assign them rights to the tables.|||Originally posted by KrustyDeKlown
Try running a trace, using the profiler, on the users trying to connect to sql server. When you try to load your asp page you should see the name of the user trying to hit the server. I am guessing, without being able to see anything, that somehow it is still using the anonymous account. Anyway take a look at that and let me know.


Yes, the NTUserName is "ANONYMOUS LOGON" and the LoginName is
"NT AUTHORITY\ANONYMOUS LOGON". Not a surprise ... ;-)
Running the VB6 application (the one that works fine )shows the correct
NT user.
Must be the Personal Web Server that makes an anonymous logon, despite the fact that I've turned anonymous access off and turned using WIndows integrated authentication on, all other ways turned off.
The first time I run the ASP in MSIE, I get a pop-up panel asking for userid, password, and domain which I fill in correctly.

Or could some setting in MSIE interfer...?|||Well, under MSIE security settings: TOOLS/SECURITY/INTRANET scroll all the way down to the bottom it will ask how you would like to authenticate.

Are you using personal webserver or IIS on your local machine? If it is IIS you might want to double check all your settings (I know you already have) but it is possible that you have overlooked something. If its personal webserver, I can't remember but I didn't think it had NT Authentication mode.

If you are using VB6 that is an easy one... the process runs under the account you are logged in as. However, IIS runs as its own process and therefore will not assume your account role unless specifically told to do so.

Good Luck!|||It's P.W.S.

Now it works - I opened MSIE's security settings and told it to automatically log in to Internet and Intranet zone sites, instead of asking for a userid, password and domain first.

Can't understand why I got an anonymous login when MSIE asked for the same info as it should get when not asking...