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... ThanksThanks 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:
>> 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
>|||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:
>> 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|||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:
>> 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|||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:
>> 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|||WANNABE wrote:
> 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?
>
That's a common mistake - the SP doesn't "contain" the ALTER statement.
The ALTER statement is an instruction (also known as a DDL statement)
to SQL Server to modify an existing stored procedure, changing its
contents to the code specified after the ALTER statement.
Example:
ALTER PROCEDURE MyProcedure -- this is NOT part of the SP
AS -- nor is this
BEGIN -- SP starts here
SELECT * FROM MyTable -- more SP code
END -- SP ends here
Another way:
DROP PROCEDURE MyProcedure -- NOT part of the SP
GO -- NOT part of the SP
CREATE PROCEDURE MyProcedure -- NOT part of the SP
AS -- NOT part of the SP
BEGIN -- SP starts here
SELECT * FROM MyTable -- more SP code
END -- SP ends here
Make sense?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I think it makes sense, but I question my understanding of it. Anything
before a BEGIN or maybe a SELECT if there is no BEGIN, would not actually be
a part of what is compiled in the SP, but rather an instruction to the
compiler. BUT if that is so, when I open a SP to view and I can see the
ALTER instruction, then I think that it would need to be a part of the SP
object that was saved. (Am I correct in calling a SP an Object?) If the
instruction are not compiled, is there something hidden that tells the
system when to execute the instructions and when not to'
===================================================="Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A50455.7020306@.realsqlguy.com...
> WANNABE wrote:
>> 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?
> That's a common mistake - the SP doesn't "contain" the ALTER statement.
> The ALTER statement is an instruction (also known as a DDL statement) to
> SQL Server to modify an existing stored procedure, changing its contents
> to the code specified after the ALTER statement.
> Example:
> ALTER PROCEDURE MyProcedure -- this is NOT part of the SP
> AS -- nor is this
> BEGIN -- SP starts here
> SELECT * FROM MyTable -- more SP code
> END -- SP ends here
> Another way:
> DROP PROCEDURE MyProcedure -- NOT part of the SP
> GO -- NOT part of the SP
> CREATE PROCEDURE MyProcedure -- NOT part of the SP
> AS -- NOT part of the SP
> BEGIN -- SP starts here
> SELECT * FROM MyTable -- more SP code
> END -- SP ends here
> Make sense?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||WANNABE wrote:
> I think it makes sense, but I question my understanding of it. Anything
> before a BEGIN or maybe a SELECT if there is no BEGIN, would not actually be
> a part of what is compiled in the SP, but rather an instruction to the
> compiler. BUT if that is so, when I open a SP to view and I can see the
> ALTER instruction, then I think that it would need to be a part of the SP
> object that was saved. (Am I correct in calling a SP an Object?) If the
> instruction are not compiled, is there something hidden that tells the
> system when to execute the instructions and when not to'
>
The GUI is adding the ALTER statement for you, so that you can make a
change to the sproc and then execute the ALTER script, thus saving your
change.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
.....
Jens Suessmeyer wrote: