please provide me some resources

Where I can read up on the best way to provide a data view of SQL 2000 data
for a web page.
My Part in this is to provide a mean for the web guy to read an inventory
list from the database and then he will set that up on the web page... I
feel like such a rookie... I would really appreciate any links or info
that can be provided... Thanks
Hi
"WANNABE" wrote:

> Where I can read up on the best way to provide a data view of SQL 2000 data
> for a web page.
> My Part in this is to provide a mean for the web guy to read an inventory
> list from the database and then he will set that up on the web page... I
> feel like such a rookie... I would really appreciate any links or info
> that can be provided... Thanks
>
This will depend on what you want to write the web page in! For instance if
it is going to be in ASP.NET try starting at
http://classicasp.aspfaq.com/general/where-can-i-find-out-about-net.html or
for classic ASP try
http://classicasp.aspfaq.com/general/how-do-i-use-asp-to.html
John
|||Thanks John, The web page will be hosted on a Linux box, I think ?pearl? or
?apache?, these things are said with little familiarity to the Linux and web
area. Does that change things much ?
There is much of this that I really don't understand well and I don't expect
anyone to explain it to me but if you can point me in the right direction as
to where to read / learn. A consultant on the outside will access our
network through a DMZ Linux box. So he will not have any domain rights. I
need to provide something (I've created a view) so he can retrieve data to
be viewed from the web site. This should be real-time data. I think this
can be done with the view I created and by creating a SQL login with rights
only to read the view. Is this Right? Am I on the right track or way
off?
=================================================
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:522F2747-B3A8-4C43-B025-1C3DD20C2E97@.microsoft.com...
> Hi
> "WANNABE" wrote:
> This will depend on what you want to write the web page in! For instance
> if
> it is going to be in ASP.NET try starting at
> http://classicasp.aspfaq.com/general/where-can-i-find-out-about-net.html
> or
> for classic ASP try
> http://classicasp.aspfaq.com/general/how-do-i-use-asp-to.html
> John
>
|||Hi
"WANNABE" wrote:

> Thanks John, The web page will be hosted on a Linux box, I think ?pearl? or
> ?apache?, these things are said with little familiarity to the Linux and web
> area. Does that change things much ?
Although I think there may be something that supports ASP on linux, you may
want to go the JSP/javabeans route. Microsoft do have a JDBC driver for SQL
Server. One thing you will need to consider is how the system will be
maintained and how much that will cost after initial development. I don't
know of any specific website for this, but if you search for them using
Google and/or buy yourself a good book you should be ok.

> There is much of this that I really don't understand well and I don't expect
> anyone to explain it to me but if you can point me in the right direction as
> to where to read / learn. A consultant on the outside will access our
> network through a DMZ Linux box. So he will not have any domain rights. I
> need to provide something (I've created a view) so he can retrieve data to
> be viewed from the web site. This should be real-time data. I think this
> can be done with the view I created and by creating a SQL login with rights
> only to read the view. Is this Right? Am I on the right track or way
> off?
The user associated to the SQL Login, will require SELECT privileges on the
view. If you have set that up then you should be ok. Check what groups this
user is in to make sure that he does not have extra privileges through group
membership.
John
|||WANNABE wrote:
> Thanks John, The web page will be hosted on a Linux box, I think ?pearl? or
> ?apache?, these things are said with little familiarity to the Linux and web
> area. Does that change things much ?
> There is much of this that I really don't understand well and I don't expect
> anyone to explain it to me but if you can point me in the right direction as
> to where to read / learn. A consultant on the outside will access our
> network through a DMZ Linux box. So he will not have any domain rights. I
> need to provide something (I've created a view) so he can retrieve data to
> be viewed from the web site. This should be real-time data. I think this
> can be done with the view I created and by creating a SQL login with rights
> only to read the view. Is this Right? Am I on the right track or way
> off?
>
You're going to need something to allow the Linux box to talk to SQL
Server. Look up FreeTDS, I've used this in the past to connect PHP
pages to SQL Server. Assuming that's in place, you simply need to
create a SQL login that has the necessary rights, and the web pages will
use that login to execute queries against your database. I would
suggest creating stored procedures instead of embedded queries, it will
be easier for YOU to debug and optimize.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks Tracy, You suggested "creating stored procedures instead of embedded
queries" and I'm just guessing, are views considered embedded queries? Now
I am still in training for my MCDBA, but I thought that a view was much like
a stored procedure in that it is executed at the server, but it would
provide the most a display of the most current data in an efficient manner.
I hope I don't sound stupid, but if I do please let me know.
I'm sure there are key differences between SPs and Views, but I'm not sure I
know what they are..
==============================================
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3B567.7000903@.realsqlguy.com...
> WANNABE wrote:
> You're going to need something to allow the Linux box to talk to SQL
> Server. Look up FreeTDS, I've used this in the past to connect PHP pages
> to SQL Server. Assuming that's in place, you simply need to create a SQL
> login that has the necessary rights, and the web pages will use that login
> to execute queries against your database. I would suggest creating stored
> procedures instead of embedded queries, it will be easier for YOU to debug
> and optimize.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||WANNABE wrote:
> Thanks Tracy, You suggested "creating stored procedures instead of embedded
> queries" and I'm just guessing, are views considered embedded queries? Now
> I am still in training for my MCDBA, but I thought that a view was much like
> a stored procedure in that it is executed at the server, but it would
> provide the most a display of the most current data in an efficient manner.
> I hope I don't sound stupid, but if I do please let me know.
> I'm sure there are key differences between SPs and Views, but I'm not sure I
> know what they are..
Every query is executed "at the server". A view is nothing more than a
virtual table that can be used to simplify a complex query. A stored
procedure is a pre-compiled collection of one or more queries.
Assume that you have three tables containing related information. Each
time your web page needs data from these tables, they must be joined
together. Your options for obtaining that data are:
1. Embedded query
The web page directly issues the following query:
SELECT t1.col2 AS col1, t2.col2 AS col2, t3.col2 AS col3
FROM t1
INNER JOIN t2
ON t1.key = t2.key
INNER JOIN t3
ON t2.key = t3.key
2. View
The view is defined as:
CREATE VIEW MyView
AS
SELECT t1.col2 AS col1, t2.col2 AS col2, t3.col2 AS col3
FROM t1
INNER JOIN t2
ON t1.key = t2.key
INNER JOIN t3
ON t2.key = t3.key
The web page issues this query:
SELECT col1, col2, col3
FROM MyView
3. Stored Procedure
The sproc is defined as:
CREATE PROCEDURE MySproc
AS
SELECT t1.col2 AS col1, t2.col2 AS col2, t3.col2 AS col3
FROM t1
INNER JOIN t2
ON t1.key = t2.key
INNER JOIN t3
ON t2.key = t3.key
The web page issues this query:
EXEC MySproc
All three return the same results, and in all three cases, the "work" is
done on the server. Now, let's assume that a fourth table is created,
and this fourth table needs to become part of this join. Option #1 will
require you to modify the web page to include the new table. Both
options #2 and #3 allow you to add the table without modifying the query
in the web page. This is a simple example, however, and more than
likely you'll need to modify the web page to accept new columns returned
from the new table.
The real beauty of using Option #3 is in performance tuning. By using a
stored proc, you have the flexibility of experimenting with temp tables,
subqueries, etc. to optimize performance, without the need to modify the
web page. There are additional security benefits to using stored procs
as well, because you don't have to grant permissions directly to the tables.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||THAT IS A GREAT LESSON, THANK YOU!!
Sorry, I did sound stupid. When I made the comment about the query being
executed at the server, I was thinking about sending a query from the web
page, which I think would also be executed at the server, which doesn't make
much more sense. The analogy that I was trying to make was the efficiency
of executing a query that is stored on the server, and querying from a
remote location, such as a web page.
Are Views and SPs considered embedded queries? and are SPs compiled to run
faster?
================================================== ============
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3C710.9070205@.realsqlguy.com...
> WANNABE wrote:
> Every query is executed "at the server". A view is nothing more than a
> virtual table that can be used to simplify a complex query. A stored
> procedure is a pre-compiled collection of one or more queries.
> Assume that you have three tables containing related information. Each
> time your web page needs data from these tables, they must be joined
> together. Your options for obtaining that data are:
> 1. Embedded query
> The web page directly issues the following query:
> SELECT t1.col2 AS col1, t2.col2 AS col2, t3.col2 AS col3
> FROM t1
> INNER JOIN t2
> ON t1.key = t2.key
> INNER JOIN t3
> ON t2.key = t3.key
> 2. View
> The view is defined as:
> CREATE VIEW MyView
> AS
> SELECT t1.col2 AS col1, t2.col2 AS col2, t3.col2 AS col3
> FROM t1
> INNER JOIN t2
> ON t1.key = t2.key
> INNER JOIN t3
> ON t2.key = t3.key
> The web page issues this query:
> SELECT col1, col2, col3
> FROM MyView
> 3. Stored Procedure
> The sproc is defined as:
> CREATE PROCEDURE MySproc
> AS
> SELECT t1.col2 AS col1, t2.col2 AS col2, t3.col2 AS col3
> FROM t1
> INNER JOIN t2
> ON t1.key = t2.key
> INNER JOIN t3
> ON t2.key = t3.key
> The web page issues this query:
> EXEC MySproc
> All three return the same results, and in all three cases, the "work" is
> done on the server. Now, let's assume that a fourth table is created, and
> this fourth table needs to become part of this join. Option #1 will
> require you to modify the web page to include the new table. Both options
> #2 and #3 allow you to add the table without modifying the query in the
> web page. This is a simple example, however, and more than likely you'll
> need to modify the web page to accept new columns returned from the new
> table.
> The real beauty of using Option #3 is in performance tuning. By using a
> stored proc, you have the flexibility of experimenting with temp tables,
> subqueries, etc. to optimize performance, without the need to modify the
> web page. There are additional security benefits to using stored procs as
> well, because you don't have to grant permissions directly to the tables.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||WANNABE wrote:
> THAT IS A GREAT LESSON, THANK YOU!!
> Sorry, I did sound stupid. When I made the comment about the query being
> executed at the server, I was thinking about sending a query from the web
> page, which I think would also be executed at the server, which doesn't make
> much more sense. The analogy that I was trying to make was the efficiency
> of executing a query that is stored on the server, and querying from a
> remote location, such as a web page.
> Are Views and SPs considered embedded queries? and are SPs compiled to run
> faster?
>
An "embedded query" is a SQL statement that is contained within the
source code of an external application, such as a web page, or a VB app.
Understand that views and SP's consist of queries that are hidden from
the calling app, in this case the "embedded query" would be the SELECT
statement that queries the view, or the EXEC statement that runs the SP.
Regarding compilation - EVERY query that is run against a SQL Server is
compiled. With a stored proc, the execution plan that results from
compiling the sproc is saved, so that subsequent calls to the SP, with
similar parameters, can re-use that same execution plan without the
overhead of compiling again. This can be significant for a SP that is
executed hundreds of times per second.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks So Much for all your help. One more Question please;
If a SP contains a statement "ALTER PROCEDURE" Does this cause a
recompilation or What does that statement do?
================================================== =========
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A4E415.3030807@.realsqlguy.com...
> WANNABE wrote:
> An "embedded query" is a SQL statement that is contained within the source
> code of an external application, such as a web page, or a VB app.
> Understand that views and SP's consist of queries that are hidden from the
> calling app, in this case the "embedded query" would be the SELECT
> statement that queries the view, or the EXEC statement that runs the SP.
> Regarding compilation - EVERY query that is run against a SQL Server is
> compiled. With a stored proc, the execution plan that results from
> compiling the sproc is saved, so that subsequent calls to the SP, with
> similar parameters, can re-use that same execution plan without the
> overhead of compiling again. This can be significant for a SP that is
> executed hundreds of times per second.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com