Bad List ?

I recently saw a list of "possibly" bad practices which included the
following. Can anyone expand as to why ?
Using DTS packages because they may be scheduled.
Using Enteprise manager to make data and schema changes
Embedding sql in applications
Thanks !"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
>I recently saw a list of "possibly" bad practices which included the
>following. Can anyone expand as to why ?
> Using DTS packages because they may be scheduled.
> Using Enteprise manager to make data and schema changes
> Embedding sql in applications
> Thanks !
>
>
I can't speak to the DTS package issue, but using SEM to make data and
schema changes is not always in your best interest. While it will work, the
way SEM handles schema changes may not be the quickest or best way to make
those changes in your system. As an example, turn on SQL Profiler and see
what the SEM does when you rename a table.
Embedding SQL in applications is generally verboten for a variety of
reasons. The first and foremost is that you are now creating a tightly
coupled application. What happens if you have an INSERT statement embedded
in your front-end. If you now make a schema change to the table referenced
by that INSERT statement, the INSERT may now fail. (Wrong number of columns
for example.) In an n-tier system, you should generally be looking to
achieve a loosely coupled, highly cohesive system. A second issue with
embedded SQL is that you now have to fix two programs and deploy those
programs. A third item could be security. Depending on how you have things
set up, you may have fewer and less appetizing security options if you are
using embedded SQL.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick,
What about embedding SQL in VBScript within a DTS package ? Does your
opinion change ? Would you prefer that the VBScript execute a stored
proedure ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%232QTJepEFHA.3536@.TK2MSFTNGP15.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:H31Qd.2002$0y6.1071@.bignews1.bellsouth.net...
>>I recently saw a list of "possibly" bad practices which included the
>>following. Can anyone expand as to why ?
>> Using DTS packages because they may be scheduled.
>> Using Enteprise manager to make data and schema changes
>> Embedding sql in applications
>> Thanks !
>>
> I can't speak to the DTS package issue, but using SEM to make data and
> schema changes is not always in your best interest. While it will work,
> the way SEM handles schema changes may not be the quickest or best way to
> make those changes in your system. As an example, turn on SQL Profiler
> and see what the SEM does when you rename a table.
> Embedding SQL in applications is generally verboten for a variety of
> reasons. The first and foremost is that you are now creating a tightly
> coupled application. What happens if you have an INSERT statement
> embedded in your front-end. If you now make a schema change to the table
> referenced by that INSERT statement, the INSERT may now fail. (Wrong
> number of columns for example.) In an n-tier system, you should generally
> be looking to achieve a loosely coupled, highly cohesive system. A second
> issue with embedded SQL is that you now have to fix two programs and
> deploy those programs. A third item could be security. Depending on how
> you have things set up, you may have fewer and less appetizing security
> options if you are using embedded SQL.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
> Thanks Rick,
> What about embedding SQL in VBScript within a DTS package ? Does your
> opinion change ? Would you prefer that the VBScript execute a stored
> proedure ?
>
It depends. ;-)
If the DTS package is a run-once type of deal, then I have no problems with
the embedded SQL.
If however, the package is something you will run often, I would rethink it
a bit and probably go with some sprocs that are called from your DTS
package.
Rick|||Thanks Rick,
Basically, the DTS packages simply create xml files from data stored in
tables, and as each file is created, an embedded sql call is made to update
the rows that were just used to create the xml file. No user intervention
is required. The packages are run once each day.
Sound OK ?
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:O9x2gj3EFHA.2828@.TK2MSFTNGP09.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:tW1Qd.2007$0y6.29@.bignews1.bellsouth.net...
>> Thanks Rick,
>> What about embedding SQL in VBScript within a DTS package ? Does your
>> opinion change ? Would you prefer that the VBScript execute a stored
>> proedure ?
>
> It depends. ;-)
> If the DTS package is a run-once type of deal, then I have no problems
> with the embedded SQL.
> If however, the package is something you will run often, I would rethink
> it a bit and probably go with some sprocs that are called from your DTS
> package.
>
> Rick
>
>