Poor Performance after upgrading from 2000 to 2005

I have a problem that I hope someone will be able to provide some insite
into, or some tips to help me.
We recently upgraded databases on an SQL server 2000 to an SQL 2005 server.
The hardware on both machines is exactly the same.
The SQL 2000 machine, although it has 64 bit hardware, the 32bit editions of
windows and SQL2000 were installed on it. The server has 4 gigs of ram.
For the upgrade, we installed Windows 2003 server x64, and SQL Server 2005
x64 on the 2nd server. To move the databases from the 2000, to the 2005
server, the data and log files were copied over to the 2005 server,
preserving the same directory structure as the original server. The the
databases were then attached to the sql2005 server.
The first problem arose approx 2 hours after the upgrade. The computer ran
its self out of memory. I assumed this was due to the 64 bit being able to
address more memory than the 32 bit edition. I then set the "maximum server
memory" setting to 2944. After restarting the server, the memory use in the
machine now peaks at around 3.9 gigs.
However, the problem now is that after a few hours of use, users are
complaning that transactions that used to execute in a matter of seconds in
the old system are in some cases now taking upwards of a minute.
Does anyone have any ideas of what can be done to help bring the performance
back to its original levels? Remember that other than using Windows
32bit/SQL 2000 32bit to Windows 64Bit/Sql2005 64bit everthing else is
exactly the same. I have ordered an additional 4 gig RAM (to bring total to
8) to see if that helps, but in all honesty I am completly lost.
Thanks for any insite,
Ryan
Try updating statistics on all tables WITH FULLSCAN.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:OrMi8gmGHHA.3616@.TK2MSFTNGP06.phx.gbl...
I have a problem that I hope someone will be able to provide some insite
into, or some tips to help me.
We recently upgraded databases on an SQL server 2000 to an SQL 2005 server.
The hardware on both machines is exactly the same.
The SQL 2000 machine, although it has 64 bit hardware, the 32bit editions of
windows and SQL2000 were installed on it. The server has 4 gigs of ram.
For the upgrade, we installed Windows 2003 server x64, and SQL Server 2005
x64 on the 2nd server. To move the databases from the 2000, to the 2005
server, the data and log files were copied over to the 2005 server,
preserving the same directory structure as the original server. The the
databases were then attached to the sql2005 server.
The first problem arose approx 2 hours after the upgrade. The computer ran
its self out of memory. I assumed this was due to the 64 bit being able to
address more memory than the 32 bit edition. I then set the "maximum server
memory" setting to 2944. After restarting the server, the memory use in the
machine now peaks at around 3.9 gigs.
However, the problem now is that after a few hours of use, users are
complaning that transactions that used to execute in a matter of seconds in
the old system are in some cases now taking upwards of a minute.
Does anyone have any ideas of what can be done to help bring the performance
back to its original levels? Remember that other than using Windows
32bit/SQL 2000 32bit to Windows 64Bit/Sql2005 64bit everthing else is
exactly the same. I have ordered an additional 4 gig RAM (to bring total to
8) to see if that helps, but in all honesty I am completly lost.
Thanks for any insite,
Ryan
|||Hello Ryan,
There could be many reasons for this but the most common is that the
statistics often need to be recomputed when a database is upgraded. Try
rebuilding all the statistics and see if that makes a difference.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||I will do that, because it certainly can't hurt. However, that senario does
not explain why when the service is first started, everything runs normally,
but the slowdown occurs slowly over the following couple of hours, (or could
it)? It seems I have to re-start the server service every couple of hours in
order to make the system usable.
-Ryan
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:iV7IXEpGHHA.2488@.TK2MSFTNGHUB02.phx.gbl...
> Hello Ryan,
> There could be many reasons for this but the most common is that the
> statistics often need to be recomputed when a database is upgraded. Try
> rebuilding all the statistics and see if that makes a difference.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||On Thu, 7 Dec 2006 19:28:57 -0600, "Ryan" <iQDevelopers@.nospam.nospam>
wrote:
>Does anyone have any ideas of what can be done to help bring the performance
>back to its original levels? Remember that other than using Windows
>32bit/SQL 2000 32bit to Windows 64Bit/Sql2005 64bit everthing else is
>exactly the same. I have ordered an additional 4 gig RAM (to bring total to
>8) to see if that helps, but in all honesty I am completly lost.
>Thanks for any insite,
Sounds like the working set is expanding, can you verify that with
task manager or perfmon?
Frankly, sounds like a major bug, SQLServer or maybe your app (?) on
the same box has a memory leak?
J.
|||Indeed updat statistics, rebuild youre indexes.
After upgarde , SQL2005 may need to recompile some storedprocedures(new
queryplan).
This might cause a delays.
Just my 2 cents.
|||Last night I ran the sp_updatestats stored procedure on some of the busier
databases on that machine. (I plan do do the rest of them tonite). I also
moved approx half the databases to another server (same configuration). As
of now, things seem to be working ok (so far. Its been running under load
for about 6 hours now). I am not sure if it is due to the updated
statistics, or the fact that each machine has only half the load as the
original problem machine.
Once I get the additional ram into the original server, and increase the
setting in SQL for max memory use, I'll start miagrating them back to see
if the problem re-occurs. Thanks for all the responses. If anyone has any
other ideas of things I can look into as well, please let me know.
Thanks,
-Ryan
|||In my migration from SQL 7.0 to SQL 2000,I found that sp_updatestats didn't
really do the job. When I ran UPDATE STATISTICS WITH FULLSCAN, that helped
a lot.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:O%23Im9CxGHHA.2456@.TK2MSFTNGP06.phx.gbl...
Last night I ran the sp_updatestats stored procedure on some of the busier
databases on that machine. (I plan do do the rest of them tonite). I also
moved approx half the databases to another server (same configuration). As
of now, things seem to be working ok (so far. Its been running under load
for about 6 hours now). I am not sure if it is due to the updated
statistics, or the fact that each machine has only half the load as the
original problem machine.
Once I get the additional ram into the original server, and increase the
setting in SQL for max memory use, I'll start miagrating them back to see
if the problem re-occurs. Thanks for all the responses. If anyone has any
other ideas of things I can look into as well, please let me know.
Thanks,
-Ryan
|||Ok. I'll do that. Thanks for the tip.
Do you know if there is any way to do it on all tables at once? I ended up
writing a script that uses a cursor to loop all the tables. Is that the best
way to do it?
Thanks,
-Ryan
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Ow3ADB0GHHA.3468@.TK2MSFTNGP04.phx.gbl...
> In my migration from SQL 7.0 to SQL 2000,I found that sp_updatestats
> didn't
> really do the job. When I ran UPDATE STATISTICS WITH FULLSCAN, that
> helped
> a lot.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
|||It's one of the few times when a cursor is appropriate. ;-) You can also
use the undocumented proc:
sp_MSforeachtable 'update statistics ? with fullscan'
Under the covers, it uses a cursor.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:e6nNzv1GHHA.3468@.TK2MSFTNGP04.phx.gbl...
Ok. I'll do that. Thanks for the tip.
Do you know if there is any way to do it on all tables at once? I ended up
writing a script that uses a cursor to loop all the tables. Is that the best
way to do it?
Thanks,
-Ryan
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Ow3ADB0GHHA.3468@.TK2MSFTNGP04.phx.gbl...
> In my migration from SQL 7.0 to SQL 2000,I found that sp_updatestats
> didn't
> really do the job. When I ran UPDATE STATISTICS WITH FULLSCAN, that
> helped
> a lot.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada

Poor Performance after upgrading from 2000 to 2005

I have a problem that I hope someone will be able to provide some insite
into, or some tips to help me.
We recently upgraded databases on an SQL server 2000 to an SQL 2005 server.
The hardware on both machines is exactly the same.
The SQL 2000 machine, although it has 64 bit hardware, the 32bit editions of
windows and SQL2000 were installed on it. The server has 4 gigs of ram.
For the upgrade, we installed Windows 2003 server x64, and SQL Server 2005
x64 on the 2nd server. To move the databases from the 2000, to the 2005
server, the data and log files were copied over to the 2005 server,
preserving the same directory structure as the original server. The the
databases were then attached to the sql2005 server.
The first problem arose approx 2 hours after the upgrade. The computer ran
its self out of memory. I assumed this was due to the 64 bit being able to
address more memory than the 32 bit edition. I then set the "maximum server
memory" setting to 2944. After restarting the server, the memory use in the
machine now peaks at around 3.9 gigs.
However, the problem now is that after a few hours of use, users are
complaning that transactions that used to execute in a matter of seconds in
the old system are in some cases now taking upwards of a minute.
Does anyone have any ideas of what can be done to help bring the performance
back to its original levels? Remember that other than using Windows
32bit/SQL 2000 32bit to Windows 64Bit/Sql2005 64bit everthing else is
exactly the same. I have ordered an additional 4 gig RAM (to bring total to
8) to see if that helps, but in all honesty I am completly lost.
Thanks for any insite,
RyanTry updating statistics on all tables WITH FULLSCAN.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:OrMi8gmGHHA.3616@.TK2MSFTNGP06.phx.gbl...
I have a problem that I hope someone will be able to provide some insite
into, or some tips to help me.
We recently upgraded databases on an SQL server 2000 to an SQL 2005 server.
The hardware on both machines is exactly the same.
The SQL 2000 machine, although it has 64 bit hardware, the 32bit editions of
windows and SQL2000 were installed on it. The server has 4 gigs of ram.
For the upgrade, we installed Windows 2003 server x64, and SQL Server 2005
x64 on the 2nd server. To move the databases from the 2000, to the 2005
server, the data and log files were copied over to the 2005 server,
preserving the same directory structure as the original server. The the
databases were then attached to the sql2005 server.
The first problem arose approx 2 hours after the upgrade. The computer ran
its self out of memory. I assumed this was due to the 64 bit being able to
address more memory than the 32 bit edition. I then set the "maximum server
memory" setting to 2944. After restarting the server, the memory use in the
machine now peaks at around 3.9 gigs.
However, the problem now is that after a few hours of use, users are
complaning that transactions that used to execute in a matter of seconds in
the old system are in some cases now taking upwards of a minute.
Does anyone have any ideas of what can be done to help bring the performance
back to its original levels? Remember that other than using Windows
32bit/SQL 2000 32bit to Windows 64Bit/Sql2005 64bit everthing else is
exactly the same. I have ordered an additional 4 gig RAM (to bring total to
8) to see if that helps, but in all honesty I am completly lost.
Thanks for any insite,
Ryan|||Hello Ryan,
There could be many reasons for this but the most common is that the
statistics often need to be recomputed when a database is upgraded. Try
rebuilding all the statistics and see if that makes a difference.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||I will do that, because it certainly can't hurt. However, that senario does
not explain why when the service is first started, everything runs normally,
but the slowdown occurs slowly over the following couple of hours, (or could
it)? It seems I have to re-start the server service every couple of hours in
order to make the system usable.
-Ryan
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:iV7IXEpGHHA.2488@.TK2MSFTNGHUB02.phx.gbl...
> Hello Ryan,
> There could be many reasons for this but the most common is that the
> statistics often need to be recomputed when a database is upgraded. Try
> rebuilding all the statistics and see if that makes a difference.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||On 08.12.2006 08:34, Ryan wrote:
> I will do that, because it certainly can't hurt. However, that senario doe
s
> not explain why when the service is first started, everything runs normall
y,
> but the slowdown occurs slowly over the following couple of hours, (or cou
ld
> it)? It seems I have to re-start the server service every couple of hours
in
> order to make the system usable.
Could be paging. Make sure your SQL Server memory settings do not grant
more mem than physically available minus some space for operating system
and other programs.
robert|||On Thu, 7 Dec 2006 19:28:57 -0600, "Ryan" <iQDevelopers@.nospam.nospam>
wrote:
>Does anyone have any ideas of what can be done to help bring the performanc
e
>back to its original levels? Remember that other than using Windows
>32bit/SQL 2000 32bit to Windows 64Bit/Sql2005 64bit everthing else is
>exactly the same. I have ordered an additional 4 gig RAM (to bring total to
>8) to see if that helps, but in all honesty I am completly lost.
>Thanks for any insite,
Sounds like the working set is expanding, can you verify that with
task manager or perfmon?
Frankly, sounds like a major bug, SQLServer or maybe your app (?) on
the same box has a memory leak?
J.|||Indeed updat statistics, rebuild youre indexes.
After upgarde , SQL2005 may need to recompile some storedprocedures(new
queryplan).
This might cause a delays.
Just my 2 cents.|||Last night I ran the sp_updatestats stored procedure on some of the busier
databases on that machine. (I plan do do the rest of them tonite). I also
moved approx half the databases to another server (same configuration). As
of now, things seem to be working ok (so far. Its been running under load
for about 6 hours now). I am not sure if it is due to the updated
statistics, or the fact that each machine has only half the load as the
original problem machine.
Once I get the additional ram into the original server, and increase the
setting in SQL for max memory use, I'll start miagrating them back to see
if the problem re-occurs. Thanks for all the responses. If anyone has any
other ideas of things I can look into as well, please let me know.
Thanks,
-Ryan|||In my migration from SQL 7.0 to SQL 2000,I found that sp_updatestats didn't
really do the job. When I ran UPDATE STATISTICS WITH FULLSCAN, that helped
a lot.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:O%23Im9CxGHHA.2456@.TK2MSFTNGP06.phx.gbl...
Last night I ran the sp_updatestats stored procedure on some of the busier
databases on that machine. (I plan do do the rest of them tonite). I also
moved approx half the databases to another server (same configuration). As
of now, things seem to be working ok (so far. Its been running under load
for about 6 hours now). I am not sure if it is due to the updated
statistics, or the fact that each machine has only half the load as the
original problem machine.
Once I get the additional ram into the original server, and increase the
setting in SQL for max memory use, I'll start miagrating them back to see
if the problem re-occurs. Thanks for all the responses. If anyone has any
other ideas of things I can look into as well, please let me know.
Thanks,
-Ryan|||Ok. I'll do that. Thanks for the tip.
Do you know if there is any way to do it on all tables at once? I ended up
writing a script that uses a cursor to loop all the tables. Is that the best
way to do it?
Thanks,
-Ryan
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Ow3ADB0GHHA.3468@.TK2MSFTNGP04.phx.gbl...
> In my migration from SQL 7.0 to SQL 2000,I found that sp_updatestats
> didn't
> really do the job. When I ran UPDATE STATISTICS WITH FULLSCAN, that
> helped
> a lot.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada

Poor Performance after upgrading from 2000 to 2005

I have a problem that I hope someone will be able to provide some insite
into, or some tips to help me.
We recently upgraded databases on an SQL server 2000 to an SQL 2005 server.
The hardware on both machines is exactly the same.
The SQL 2000 machine, although it has 64 bit hardware, the 32bit editions of
windows and SQL2000 were installed on it. The server has 4 gigs of ram.
For the upgrade, we installed Windows 2003 server x64, and SQL Server 2005
x64 on the 2nd server. To move the databases from the 2000, to the 2005
server, the data and log files were copied over to the 2005 server,
preserving the same directory structure as the original server. The the
databases were then attached to the sql2005 server.
The first problem arose approx 2 hours after the upgrade. The computer ran
its self out of memory. I assumed this was due to the 64 bit being able to
address more memory than the 32 bit edition. I then set the "maximum server
memory" setting to 2944. After restarting the server, the memory use in the
machine now peaks at around 3.9 gigs.
However, the problem now is that after a few hours of use, users are
complaning that transactions that used to execute in a matter of seconds in
the old system are in some cases now taking upwards of a minute.
Does anyone have any ideas of what can be done to help bring the performance
back to its original levels? Remember that other than using Windows
32bit/SQL 2000 32bit to Windows 64Bit/Sql2005 64bit everthing else is
exactly the same. I have ordered an additional 4 gig RAM (to bring total to
8) to see if that helps, but in all honesty I am completly lost.
Thanks for any insite,
RyanTry updating statistics on all tables WITH FULLSCAN.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:OrMi8gmGHHA.3616@.TK2MSFTNGP06.phx.gbl...
I have a problem that I hope someone will be able to provide some insite
into, or some tips to help me.
We recently upgraded databases on an SQL server 2000 to an SQL 2005 server.
The hardware on both machines is exactly the same.
The SQL 2000 machine, although it has 64 bit hardware, the 32bit editions of
windows and SQL2000 were installed on it. The server has 4 gigs of ram.
For the upgrade, we installed Windows 2003 server x64, and SQL Server 2005
x64 on the 2nd server. To move the databases from the 2000, to the 2005
server, the data and log files were copied over to the 2005 server,
preserving the same directory structure as the original server. The the
databases were then attached to the sql2005 server.
The first problem arose approx 2 hours after the upgrade. The computer ran
its self out of memory. I assumed this was due to the 64 bit being able to
address more memory than the 32 bit edition. I then set the "maximum server
memory" setting to 2944. After restarting the server, the memory use in the
machine now peaks at around 3.9 gigs.
However, the problem now is that after a few hours of use, users are
complaning that transactions that used to execute in a matter of seconds in
the old system are in some cases now taking upwards of a minute.
Does anyone have any ideas of what can be done to help bring the performance
back to its original levels? Remember that other than using Windows
32bit/SQL 2000 32bit to Windows 64Bit/Sql2005 64bit everthing else is
exactly the same. I have ordered an additional 4 gig RAM (to bring total to
8) to see if that helps, but in all honesty I am completly lost.
Thanks for any insite,
Ryan|||Hello Ryan,
There could be many reasons for this but the most common is that the
statistics often need to be recomputed when a database is upgraded. Try
rebuilding all the statistics and see if that makes a difference.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I will do that, because it certainly can't hurt. However, that senario does
not explain why when the service is first started, everything runs normally,
but the slowdown occurs slowly over the following couple of hours, (or could
it)? It seems I have to re-start the server service every couple of hours in
order to make the system usable.
-Ryan
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:iV7IXEpGHHA.2488@.TK2MSFTNGHUB02.phx.gbl...
> Hello Ryan,
> There could be many reasons for this but the most common is that the
> statistics often need to be recomputed when a database is upgraded. Try
> rebuilding all the statistics and see if that makes a difference.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||On 08.12.2006 08:34, Ryan wrote:
> I will do that, because it certainly can't hurt. However, that senario does
> not explain why when the service is first started, everything runs normally,
> but the slowdown occurs slowly over the following couple of hours, (or could
> it)? It seems I have to re-start the server service every couple of hours in
> order to make the system usable.
Could be paging. Make sure your SQL Server memory settings do not grant
more mem than physically available minus some space for operating system
and other programs.
robert|||On Thu, 7 Dec 2006 19:28:57 -0600, "Ryan" <iQDevelopers@.nospam.nospam>
wrote:
>Does anyone have any ideas of what can be done to help bring the performance
>back to its original levels? Remember that other than using Windows
>32bit/SQL 2000 32bit to Windows 64Bit/Sql2005 64bit everthing else is
>exactly the same. I have ordered an additional 4 gig RAM (to bring total to
>8) to see if that helps, but in all honesty I am completly lost.
>Thanks for any insite,
Sounds like the working set is expanding, can you verify that with
task manager or perfmon?
Frankly, sounds like a major bug, SQLServer or maybe your app (?) on
the same box has a memory leak?
J.|||Last night I ran the sp_updatestats stored procedure on some of the busier
databases on that machine. (I plan do do the rest of them tonite). I also
moved approx half the databases to another server (same configuration). As
of now, things seem to be working ok (so far. Its been running under load
for about 6 hours now). I am not sure if it is due to the updated
statistics, or the fact that each machine has only half the load as the
original problem machine.
Once I get the additional ram into the original server, and increase the
setting in SQL for max memory use, I'll start miagrating them back to see
if the problem re-occurs. Thanks for all the responses. If anyone has any
other ideas of things I can look into as well, please let me know.
Thanks,
-Ryan|||In my migration from SQL 7.0 to SQL 2000,I found that sp_updatestats didn't
really do the job. When I ran UPDATE STATISTICS WITH FULLSCAN, that helped
a lot.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:O%23Im9CxGHHA.2456@.TK2MSFTNGP06.phx.gbl...
Last night I ran the sp_updatestats stored procedure on some of the busier
databases on that machine. (I plan do do the rest of them tonite). I also
moved approx half the databases to another server (same configuration). As
of now, things seem to be working ok (so far. Its been running under load
for about 6 hours now). I am not sure if it is due to the updated
statistics, or the fact that each machine has only half the load as the
original problem machine.
Once I get the additional ram into the original server, and increase the
setting in SQL for max memory use, I'll start miagrating them back to see
if the problem re-occurs. Thanks for all the responses. If anyone has any
other ideas of things I can look into as well, please let me know.
Thanks,
-Ryan|||Ok. I'll do that. Thanks for the tip.
Do you know if there is any way to do it on all tables at once? I ended up
writing a script that uses a cursor to loop all the tables. Is that the best
way to do it?
Thanks,
-Ryan
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Ow3ADB0GHHA.3468@.TK2MSFTNGP04.phx.gbl...
> In my migration from SQL 7.0 to SQL 2000,I found that sp_updatestats
> didn't
> really do the job. When I ran UPDATE STATISTICS WITH FULLSCAN, that
> helped
> a lot.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada|||It's one of the few times when a cursor is appropriate. ;-) You can also
use the undocumented proc:
sp_MSforeachtable 'update statistics ? with fullscan'
Under the covers, it uses a cursor.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:e6nNzv1GHHA.3468@.TK2MSFTNGP04.phx.gbl...
Ok. I'll do that. Thanks for the tip.
Do you know if there is any way to do it on all tables at once? I ended up
writing a script that uses a cursor to loop all the tables. Is that the best
way to do it?
Thanks,
-Ryan
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Ow3ADB0GHHA.3468@.TK2MSFTNGP04.phx.gbl...
> In my migration from SQL 7.0 to SQL 2000,I found that sp_updatestats
> didn't
> really do the job. When I ran UPDATE STATISTICS WITH FULLSCAN, that
> helped
> a lot.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada

Pointer to List of Namespaces for MSXML 4.0?

Hi,
Can anyone provide or point to a list of supported namespaces (and
standards) in MSXML 4.0. Having enormous trouble finding a simple list
of supported namespaces. For instance, it's taken me a hell of a long
time to work out that the reason position() isn't working is that the
page is currently referencing "http://www.w3.org/TR/WD-xsl" instead of
"http://www.w3.org/1999/XSL/Transform".
Incidiently, does the W3C have a page that lists current (and
superseded) namespaces. It would be very helpful!!
Cheers,
James
Versions 3 and below support the two you mentioned for transforms and only
those (other versions might I'm not sure). The WD-xsl is obsolete and
shouldn't be used for transforms.
Even XSLT 2.0 uses the http://www.w3.org/1999/XSL/Transform namespace,
although Microsoft doesn't support version 2.0.
Joe Fawcett - XML MVP
http://joe.fawcett.name
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1161256895.279470.189640@.m7g2000cwm.googlegro ups.com...
> Hi,
> Can anyone provide or point to a list of supported namespaces (and
> standards) in MSXML 4.0. Having enormous trouble finding a simple list
> of supported namespaces. For instance, it's taken me a hell of a long
> time to work out that the reason position() isn't working is that the
> page is currently referencing "http://www.w3.org/TR/WD-xsl" instead of
> "
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0"
>
<xsl:param name="person" />
<xsl:template match="/">
<html>
<head>
<title>Finding an age using an XSLT parameter</title>
</head>
<body>
<xsl:apply-templates select="/Ages/Person[@.name=$person]" />
</body>
</html>
</xsl:template>
<xsl:template match="Person">
<p>The age of <xsl:value-of select="$person" /> is <xsl:value-of
select="@.age"/> </p>
</xsl:template>
</xsl:stylesheet>".
> Incidiently, does the W3C have a page that lists current (and
> superseded) namespaces. It would be very helpful!!
> Cheers,
> James
>

Pointer to List of Namespaces for MSXML 4.0?

Hi,
Can anyone provide or point to a list of supported namespaces (and
standards) in MSXML 4.0. Having enormous trouble finding a simple list
of supported namespaces. For instance, it's taken me a hell of a long
time to work out that the reason position() isn't working is that the
page is currently referencing "http://www.w3.org/TR/WD-xsl" instead of
"http://www.w3.org/1999/XSL/Transform".
Incidiently, does the W3C have a page that lists current (and
superseded) namespaces. It would be very helpful!!
Cheers,
JamesVersions 3 and below support the two you mentioned for transforms and only
those (other versions might I'm not sure). The WD-xsl is obsolete and
shouldn't be used for transforms.
Even XSLT 2.0 uses the http://www.w3.org/1999/XSL/Transform namespace,
although Microsoft doesn't support version 2.0.
Joe Fawcett - XML MVP
http://joe.fawcett.name
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1161256895.279470.189640@.m7g2000cwm.googlegroups.com...
> Hi,
> Can anyone provide or point to a list of supported namespaces (and
> standards) in MSXML 4.0. Having enormous trouble finding a simple list
> of supported namespaces. For instance, it's taken me a hell of a long
> time to work out that the reason position() isn't working is that the
> page is currently referencing "http://www.w3.org/TR/WD-xsl" instead of
> "
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0"
>
<xsl:param name="person" />
<xsl:template match="/">
<html>
<head>
<title>Finding an age using an XSLT parameter</title>
</head>
<body>
<xsl:apply-templates select="/Ages/Person[@.name=$person]" />
</body>
</html>
</xsl:template>
<xsl:template match="Person">
<p>The age of <xsl:value-of select="$person" /> is <xsl:value-of
select="@.age"/> </p>
</xsl:template>
</xsl:stylesheet>".
> Incidiently, does the W3C have a page that lists current (and
> superseded) namespaces. It would be very helpful!!
> Cheers,
> James
>

Pls help! Delete records in VB.net

Currently i writing a program to delete Records in the SQL database using VB.net language...
BUT i not sure whether i am right?
Pls provide me with the coding using a command object to delete records in SQL database...thank...however i tried the codes below but not working ...Pls help

Dim StrConnection As String = "workstation id=""ET-T15404-PC1"";integrated security=SSPI; etc

Dim objConnection As New SqlConnection(strConnection)
Dim strSQL As String = "Delete Seller.Admin FROMSeller WHERE Seller.no=tb.no"
Dim dbComm As New SqlCommand(strSQL, objConnection)

objConnection.Open()
dbComm.ExecuteNonQuery
objConnecion.Close()Check out this Quickstart for information on how to accomplish this:Server-Side Data Access: Deleting Data in a SQL Database

Terri

Please provide me with a solution

I am developer who codes for both front end and back end.

HEre is the situation. I have two databases sqlnts1 and sqlnts2. I need to update certain tables in sqlnts1 and then certain tables in sqlnts2 and then back to the sqlnts1 to update few more tables. This is the program logic.

When I tried executing the stored procedure written in sql server 2000 I am provided with the following message

"[OLE/DB provider returned message: Cannot start more transactions on this session.]"

To set this problem correct the only option that I have is to set xact_abort on. But, setting this option on I cannot handle the errors in code. That is this option lets the server handle the system errors.

I want to handle the errors using @.@.error programatically.

Also when I try to commit the transaction that is started before updating sqlnts1 at the end of the stored procedure it provides me with the following error.

"No transaction or savepoint of that name was found".

To work around this problem I used set xact_abort on before making updates to sqlnts2 and set it back to off after finishing updates to sqlnts2.

This way it works, but am sure given that there occurs an error in updating sqlnts2 server tables - its gonna create problems. I want to handle them by myself in the code.

Please help.

Here is the program flow. I tried beginning the distributed transaction too. Case is also checked and begin and end tran are ensured to have the same case.

Begin distributed transaction abc / begin tran abc

Update tables in sqlnts1

set xact abort on

update tables in sqlnts2

set xact abort off

Update tables in sqlnts1

commit tran / rollback tran abc

Thanks,
SubhaAre these 2 DBs on the same server or different servers?|||The sqlnts1 and 2 are two servers.

I would look forward ot hear from you.
Thanks,
Subha

Originally posted by sbaru
Are these 2 DBs on the same server or different servers?|||What is the level of nested transactions you are going into ?
find max value of @.trancount at which the error
"[OLE/DB provider returned message: Cannot start more transactions on this session.]"
occurs|||I have only one transaction on. I don't open multiple transactions.
@.trancount is only 1

Originally posted by Enigma
What is the level of nested transactions you are going into ?
find max value of @.trancount at which the error
"[OLE/DB provider returned message: Cannot start more transactions on this session.]"
occurs

Please provide me with a solution

I am developer who codes for both front end and back end.

HEre is the situation. I have two databases sqlnts1 and sqlnts2. I need to update certain tables in sqlnts1 and then certain tables in sqlnts2 and then back to the sqlnts1 to update few more tables. This is the program logic.

When I tried executing the stored procedure written in sql server 2000 I am provided with the following message

"[OLE/DB provider returned message: Cannot start more transactions on this session.]"

To set this problem correct the only option that I have is to set xact_abort on. But, setting this option on I cannot handle the errors in code. That is this option lets the server handle the system errors.

I want to handle the errors using @.@.error programatically.

Also when I try to commit the transaction that is started before updating sqlnts1 at the end of the stored procedure it provides me with the following error.

"No transaction or savepoint of that name was found".

To work around this problem I used set xact_abort on before making updates to sqlnts2 and set it back to off after finishing updates to sqlnts2.

This way it works, but am sure given that there occurs an error in updating sqlnts2 server tables - its gonna create problems. I want to handle them by myself in the code.

Please help.

Here is the program flow. I tried beginning the distributed transaction too. Case is also checked and begin and end tran are ensured to have the same case.

Begin distributed transaction abc / begin tran abc

Update tables in sqlnts1

set xact abort on

update tables in sqlnts2

set xact abort off

Update tables in sqlnts1

commit tran / rollback tran abc

Thanks,
SubhaCrosspost

http://www.dbforums.com/t970872.html (http://www.dbforums.com/t970872.html)|||I could not see any reply posted by enigma??????

Originally posted by subkrish
I am developer who codes for both front end and back end.

HEre is the situation. I have two databases sqlnts1 and sqlnts2. I need to update certain tables in sqlnts1 and then certain tables in sqlnts2 and then back to the sqlnts1 to update few more tables. This is the program logic.

When I tried executing the stored procedure written in sql server 2000 I am provided with the following message

"[OLE/DB provider returned message: Cannot start more transactions on this session.]"

To set this problem correct the only option that I have is to set xact_abort on. But, setting this option on I cannot handle the errors in code. That is this option lets the server handle the system errors.

I want to handle the errors using @.@.error programatically.

Also when I try to commit the transaction that is started before updating sqlnts1 at the end of the stored procedure it provides me with the following error.

"No transaction or savepoint of that name was found".

To work around this problem I used set xact_abort on before making updates to sqlnts2 and set it back to off after finishing updates to sqlnts2.

This way it works, but am sure given that there occurs an error in updating sqlnts2 server tables - its gonna create problems. I want to handle them by myself in the code.

Please help.

Here is the program flow. I tried beginning the distributed transaction too. Case is also checked and begin and end tran are ensured to have the same case.

Begin distributed transaction abc / begin tran abc

Update tables in sqlnts1

set xact abort on

update tables in sqlnts2

set xact abort off

Update tables in sqlnts1

commit tran / rollback tran abc

Thanks,
Subha

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

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

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... ThanksHi
"WANNABE" wrote:

> Where I can read up on the best way to provide a data view of SQL 2000 dat
a
> 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/genera...-about-net.html or
for classic ASP try
http://classicasp.aspfaq.com/genera...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/genera...-about-net.html
> or
> for classic ASP try
> http://classicasp.aspfaq.com/genera...use-asp-to.html
> John
>|||Hi
"WANNABE" wrote:

> Thanks John, The web page will be hosted on a Linux box, I think ?pearl? o
r
> ?apache?, these things are said with little familiarity to the Linux and w
eb
> 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 expe
ct
> 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 right
s
> 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? o
r
> ?apache?, these things are said with little familiarity to the Linux and w
eb
> area. Does that change things much '
> There is much of this that I really don't understand well and I don't expe
ct
> 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 right
s
> 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 embedd
ed
> queries" and I'm just guessing, are views considered embedded queries? No
w
> I am still in training for my MCDBA, but I thought that a view was much li
ke
> 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 ma
ke
> 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 ru
n
> 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

Please Provide INSIGHT - why Winforms or WebParts with RS?

I would appreciate some clairvoyance on this subject:
Why is it necessary to use WINFORMS or WEBPARTS to execute a Reporting Services Report? My naive understanding is that RS is an enterprise reporting tool...
If I want to develop an OLTP application I use something different and if I need to print (via RS) I provide a button that goes to a "un" hyperlink that then provides me the rich functionality of RS.....
So it seems for sure I am missing something here - please and sinncerely give me some insight!
Best reagrds,
Joe

Sorry for the delayed posting, hopefully it can still be useful.

I'm notentirelysure I understand your post so bear with me;
In fact you don't need to use WinForms nor WebParts, you can simply provide the user a url that directly points to the report or let's say an Excel output...

Sample url for Excel output:
http://<your server>/reportserver?/<your path>/<your report name>t&rs:Command=Render&rs:Format=XML&rc:Parameters=False&rc:XSLT=<your xslt file>.xslt&rc:MIMEType=application/vnd.ms-excel&rc:FileExtension=xls
Sample url for a Report output:
http://<your server>/Reportserver?/<your path>/<your report name>&rs:Command=Render
On the other hand you can create your own custom interface to output the reports in the way you see fit, such as data-filled dropdowns or any other control. You can also have any of these controls interact with the report itself
Unfortunately that's a bit more involved and I will not detail it in this post, I may in the near future write an article on this.