Are stored procedures practical using ASP only?

[Dragonhunter]

Hello,

The aspfaq.com seems to really push stored procedures, and I hear the
same advice here all the time. So I want to take the advice.

Is it possible to create and practically maintain, delete, use, etc..
stored procedures soley from asp (i.e., no GUI or console- like being
hosted on Brinkster)?

The tutorial on aspfaq.com mentions that stored procedures can be
created from asp code- how? Do you just send the stored procedure you'd
type into the GUI with oConn.execute() instead?

Thanks!

 

[Bob Barrows]

Hello,

The aspfaq.com seems to really push stored procedures, and I hear the
same advice here all the time. So I want to take the advice.

Is it possible to create and practically maintain, delete, use, etc..
stored procedures soley from asp (i.e., no GUI or console- like being
hosted on Brinkster)?

The tutorial on aspfaq.com mentions that stored procedures can be
created from asp code- how? Do you just send the stored procedure
you'd type into the GUI with oConn.execute() instead?

Thanks!

You should design, create and test stored procedures in the native database
environment where they will run. With SQL Server, it would be criminal to
not take advantage of the query optimization tools provided by Query
Analyzer. There are few people with the ability to build optimized queries
from scratch, knowing exactly what indexes to build and knowing that those
indexes will be used without recourse to the execution plans and index
analyses provided by query analyzer. Are you one of those people? If not,
stop thinking about creating and debugging your stored procedures from ASP,
unless you want to guarantee that your application will not perform
optimally.

However, if you must, you can execute a CREATE PROCEDURE script via ADO.
Look up the syntax in BOL.

Bob Barrows

 

[middletree]

As Bob says, making a SP is easier with SQL Server tools. If you can't
afford 10,000 bucks for it, fret not. You can go to the MS site and obtain
the developer edition for, like, really cheap. Not sure exactly. If nothing
else, you can buy the Action Pack for $300 and get lots of software,
including SQL Server.

 

[Dragonhunter]

Sorry, I don't know what you mean by 'optimized query'. Im used to getting
data, I guess in a simple way, out of my database using asp and sql statements
executed from asp. I usually do select statements matching a certain ID or
username. I don't usually select with some huge sql statement a page long.
Would I still benefit from an 'optimized query?'

I can't really afford sql server even at $300. I'm using Access right now but
after reading the aspfaq and peoples comments here I want to try 'the real
way'. My database works fine right now with the website but I have the feeling
it will break when 10 users are trying to do things at the same time,
especially after reading the stuff about Access in aspfaq.com I'm hosted on
Brinkster and was hoping to use their $27/mo sql server plan if and when Access
breaks, but there is no console or GUI obviously. Could I debug my SQL
statements like I normally do in ASP and then put a header and footer of some
sort around it and turn it into a stored procedure via asp?

Thanks!!

 

[Bob Barrows]

Since you are not using SQL Server, this is not as relevant. You are,
however, much better off debugging your queries in the Access environment
using the Query Builder tool. This helps in later debugging since any errors
in running the query are likely to be in your asp code, not in the query
itself. Also, you can use the Access performance analyzer to see if you
should create indexes on your tables to improve the performance of your
queries, something you can't do from ASP.

See these threads for further pointers:
http://tinyurl.com/n6vf
http://tinyurl.com/n6vm

 

[Curt_C [MVP]]

Generate one in SQL and export it....
It'll give you the exact syntax to recreate it through code

 

[Chris Hohmann]

Sorry, I don't know what you mean by 'optimized query'. Im used to getting
data, I guess in a simple way, out of my database using asp and sql statements
executed from asp. I usually do select statements matching a certain ID or
username. I don't usually select with some huge sql statement a page long.
Would I still benefit from an 'optimized query?'

I can't really afford sql server even at $300. I'm using Access right now but
after reading the aspfaq and peoples comments here I want to try 'the real
way'. My database works fine right now with the website but I have the feeling
it will break when 10 users are trying to do things at the same time,
especially after reading the stuff about Access in aspfaq.com I'm hosted on
Brinkster and was hoping to use their $27/mo sql server plan if and when Access
breaks, but there is no console or GUI obviously. Could I debug my SQL
statements like I normally do in ASP and then put a header and footer of some
sort around it and turn it into a stored procedure via asp?

Thanks!!

Consider Web Matrix.
1. It a FREE (as in beer) community based development environment for
ASP.NET
2. One of its FREE (as in beer) optional components is Microsoft SQL
Server Desktop Engine (MSDE). This is a scaled down version of SQL
Server, but the database engine is the same so anything you develop for
MSDE will work when you move to SQL Server on your hosting provider. The
only limitation is connection throttling, which limits you to five (5)
simultaneous connections, if memory serves. This should not be a problem
in a local development environment
3. Many (although not all) database administration tasks can be managed
directly from the Web Matrix interface. This is important since MSDE
does not come with a GUI Admin interface of its own like SQL Server
Enterprise Manager. For those tasks that you cannot perform through Web
Matrix, you can use the oSQL command-line utility that comes with MSDE.
Knowing how to do things from the command line is good for you! It'll
put hair on your chest.
4. Web Matrix is a nice way to familiarize yourself with ASP.NET and
..NET in general if you are so inclined.

Here's a link: http://asp.net/webmatrix

HTH
-Chris

 

[Dragonhunter]

My last thread I started veered from this question, which I don't think was
answered (my fault for asking too much).

Is it possible to create stored procedures from ASP on a hosted server (like
Brinkster) and if so, how?

Thanks again.

 

[Bob Barrows]

My last thread I started veered from this question, which I don't
think was answered (my fault for asking too much).

Is it possible to create stored procedures from ASP on a hosted
server (like Brinkster) and if so, how?

OK, I don't know why it matters who is hosting your site, but ...

From my previous reply:
"However, if you must, you can execute a CREATE PROCEDURE script via ADO."

Like this:
cn.execute "create procedure myfirstprocedure as select * from
pubs..authors",,129

The above applies to SQL Server stored procedures. You need to use ADOX to
create saved queries in Access databases. See here for info on using ADOX:
http://msdn.microsoft.com/library/en-us/ado270/htm/pg_adox_fundamentals.asp

I must reiterate that you will be selling yourself short if you don't
develop your procedures (SQL Server) or saved queries (Access) in the native
database environment. Even if you have to develop them locally and use asp
to install them on the hosted database.

Bob Barrows

 

[Bob Barrows]

BTW, there are tools you can use to remotely administer SQL Server databases
via asp. Here's a couple I found via a google search:
http://www.codeproject.com/useritems/sqldmo1.asp
http://sqlserveradvisor.com/doc/12569
http://www.fawcette.com/javapro/2002_12/online/servletsjsp_bkurniawan_12_16_02/

 

[mark4asp]

My last thread I started veered from this question, which I don't think was
answered (my fault for asking too much).

Is it possible to create stored procedures from ASP on a hosted server (like
Brinkster) and if so, how?

Thanks again.

Yes. Of course. But the stored procedures will be associated with the
database you are using.

SP support varies a lot with you database. In some it is excellent in
others only rudimentary.

... if so, how?

You create them normally in the database - you can even use the DDL to
do this by sending SQL scripts in ASP but I'd use the normal database
interface - whatever that is. SPs are invoked via ADO and specific SP
support depends on the driver.

The stored procedures are easier to use than SQL scripts because:
1. you can test the SQL script on the database before putting it into
a stored proc.
2. There are fewer problems handling parameters - with SPs you don't
need to remove quotes.
3. Processing speed is increased, especially with complex data
processing operations, which is faster when done by the database.
Ideally you can do all your data processing in SQL in SPs so you no
longer need to make a server call for each line of SQL script.
4. The SPs execute faster than scripts - sometimes much faster.

The nominal disadvantage is that the ASP app is more portable with the
SQL code in scripts inside the ASP but, to me, this is purely
theoretical as SQL syntax varies a lot between databases and it is not
as easy as people imagine to just change the database engine to some
other.