pros and cons of using stored procedures for VB app ?

Hi,
Just wondering what sort of problems and advantages people have found using
stored procedures. I have an app developed in VB6 & VB.NET and our
developers are starting to re-write some of the code in stored procedures
(im advocating encryption of them). When deploying an application however
stored procedure seem to add another level of complexity to installation. In
future we also plan to have an basic ASP app with some of the functionality
of the VB app.
What are the pros and cons of using stored procedures in the situation?
PRO
- can call same stored procedure for ASP pages which saves us writing again
CON
- more complex deployment (i.e sending a bug fix will involve shipping DLL
and SQL script for stored procedure).
Thanks for any input
Scott
pros and cons of using stored procedures for VB app ?
Performance, security, maintainability (db can be maintained
independently of app changes) are some of the advantages. Your
colleagues are right. ALL data access for updates, inserts, deletes and
selects should be done through procs unless you have exceptional
reasons not to. This is the design pattern followed by all good SQL
developers. See:
http://msdn.microsoft.com/library/de...un_1a_6x45.asp
http://www.sql-server-performance.co...procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/archi.../17/38095.aspx
A potential disadvantage if you are a software vendor is that the code
in the DB is exposed to the customer. The protection offered by the
WITH ENCRYPTION option is pretty worthless from a security perspective
- it can easily be circumvented. I don't see this as a major issue
myself because the best protection for your intellectual property ought
to be a licence agreement, not a software obstruction that hampers the
customer's DBA in doing his job.
David Portas
SQL Server MVP
|||many thanks for the reply David. That helps clear a few things up.
You suggested that the "ENCRYPTION" can easily be reversed. My main worry is
customer/users amending deployed stored procedures - hence we thought the
"with enctyption" method would be the best way to protect against this.
Would you use a licence agreement to stop this also ?
cheers
scott
|||Even without decrptying it, the WITH ENCRYPTION option won't stop the
DBA replacing procs. Some decryption solutions are here by the way:
http://www.planetsourcecode.com/vb/s...d=505&lngWId=5
http://www.securiteam.com/tools/6J00S003GU.html
"Users" (non administrators) should not have ddladmin role so should
never be able to modify procs, unless you are talking about a single
user setup where the end user has total control.
You could verify the existence of each proc or even recreate them at
install time for each release. You might also verify them as part of
your support diagnostics. In principle you could store and verify their
CHECKSUMs based on the text in the syscomments table. Be cautious about
how you interpret any checksum though. I can imagine legitimate acts of
scripting and re-creating proc could easily introduce extra line breaks
and whitespace that would change the checksum.
David Portas
SQL Server MVP
|||thanks again. this quote kind of says it in a nutshell...
"since encrypting something that can be decrypted without user interaction
(i.e., entering of a password) isn't anything else than the infamous
security by obscurity."
thanks for your help
Scott

pros and cons of using stored procedures for VB app ?

Hi,
Just wondering what sort of problems and advantages people have found using
stored procedures. I have an app developed in VB6 & VB.NET and our
developers are starting to re-write some of the code in stored procedures
(im advocating encryption of them). When deploying an application however
stored procedure seem to add another level of complexity to installation. In
future we also plan to have an basic ASP app with some of the functionality
of the VB app.
What are the pros and cons of using stored procedures in the situation?
PRO
- can call same stored procedure for ASP pages which saves us writing again
CON
- more complex deployment (i.e sending a bug fix will involve shipping DLL
and SQL script for stored procedure).
Thanks for any input
Scott
pros and cons of using stored procedures for VB app ?Performance, security, maintainability (db can be maintained
independently of app changes) are some of the advantages. Your
colleagues are right. ALL data access for updates, inserts, deletes and
selects should be done through procs unless you have exceptional
reasons not to. This is the design pattern followed by all good SQL
developers. See:
http://msdn.microsoft.com/library/d.../>
1a_6x45.asp
http://www.sql-server-performance.c..._procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/arch...1/17/38095.aspx
A potential disadvantage if you are a software vendor is that the code
in the DB is exposed to the customer. The protection offered by the
WITH ENCRYPTION option is pretty worthless from a security perspective
- it can easily be circumvented. I don't see this as a major issue
myself because the best protection for your intellectual property ought
to be a licence agreement, not a software obstruction that hampers the
customer's DBA in doing his job.
David Portas
SQL Server MVP
--|||many thanks for the reply David. That helps clear a few things up.
You suggested that the "ENCRYPTION" can easily be reversed. My main worry is
customer/users amending deployed stored procedures - hence we thought the
"with enctyption" method would be the best way to protect against this.
Would you use a licence agreement to stop this also ?
cheers
scott|||Even without decrptying it, the WITH ENCRYPTION option won't stop the
DBA replacing procs. Some decryption solutions are here by the way:
http://www.planetsourcecode.com/vb/...6J00S003GU.html
"Users" (non administrators) should not have ddladmin role so should
never be able to modify procs, unless you are talking about a single
user setup where the end user has total control.
You could verify the existence of each proc or even recreate them at
install time for each release. You might also verify them as part of
your support diagnostics. In principle you could store and verify their
CHECKSUMs based on the text in the syscomments table. Be cautious about
how you interpret any checksum though. I can imagine legitimate acts of
scripting and re-creating proc could easily introduce extra line breaks
and whitespace that would change the checksum.
David Portas
SQL Server MVP
--|||thanks again. this quote kind of says it in a nutshell...
"since encrypting something that can be decrypted without user interaction
(i.e., entering of a password) isn't anything else than the infamous
security by obscurity."
thanks for your help
Scottsql

pros and cons of using stored procedures for VB app ?

Hi,
Just wondering what sort of problems and advantages people have found using
stored procedures. I have an app developed in VB6 & VB.NET and our
developers are starting to re-write some of the code in stored procedures
(im advocating encryption of them). When deploying an application however
stored procedure seem to add another level of complexity to installation. In
future we also plan to have an basic ASP app with some of the functionality
of the VB app.
What are the pros and cons of using stored procedures in the situation?
PRO
- can call same stored procedure for ASP pages which saves us writing again
CON
- more complex deployment (i.e sending a bug fix will involve shipping DLL
and SQL script for stored procedure).
Thanks for any input
Scott
pros and cons of using stored procedures for VB app ?Performance, security, maintainability (db can be maintained
independently of app changes) are some of the advantages. Your
colleagues are right. ALL data access for updates, inserts, deletes and
selects should be done through procs unless you have exceptional
reasons not to. This is the design pattern followed by all good SQL
developers. See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1a_6x45.asp
http://www.sql-server-performance.com/stored_procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx
A potential disadvantage if you are a software vendor is that the code
in the DB is exposed to the customer. The protection offered by the
WITH ENCRYPTION option is pretty worthless from a security perspective
- it can easily be circumvented. I don't see this as a major issue
myself because the best protection for your intellectual property ought
to be a licence agreement, not a software obstruction that hampers the
customer's DBA in doing his job.
--
David Portas
SQL Server MVP
--|||many thanks for the reply David. That helps clear a few things up.
You suggested that the "ENCRYPTION" can easily be reversed. My main worry is
customer/users amending deployed stored procedures - hence we thought the
"with enctyption" method would be the best way to protect against this.
Would you use a licence agreement to stop this also ?
cheers
scott|||Even without decrptying it, the WITH ENCRYPTION option won't stop the
DBA replacing procs. Some decryption solutions are here by the way:
http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5
http://www.securiteam.com/tools/6J00S003GU.html
"Users" (non administrators) should not have ddladmin role so should
never be able to modify procs, unless you are talking about a single
user setup where the end user has total control.
You could verify the existence of each proc or even recreate them at
install time for each release. You might also verify them as part of
your support diagnostics. In principle you could store and verify their
CHECKSUMs based on the text in the syscomments table. Be cautious about
how you interpret any checksum though. I can imagine legitimate acts of
scripting and re-creating proc could easily introduce extra line breaks
and whitespace that would change the checksum.
--
David Portas
SQL Server MVP
--|||thanks again. this quote kind of says it in a nutshell...
"since encrypting something that can be decrypted without user interaction
(i.e., entering of a password) isn't anything else than the infamous
security by obscurity."
thanks for your help
Scott

Project in Share Point

Hello,

I have got a project where I am asked to utilize SharePoint features as much as possible. This project is earlier developed in .Net using Sharepoint Object Model. But now .Net should be eliminated as much as possible.

The requirements are :

Display a list of projects for the logged in user (windows logged in user). The list should have search criteria: project name, customer, project manager, project period.

On click of a project in the list, project details should be displayed Details like customer, team, start date, highlights, news. On click of a team member's name, Details of the team member (Name, office location, project joining, releaving dates, photo ) should be displayed.

To do this in Sharepoint my major hurdle is to display project list for windows logged in user. How do I do this in Sharepoint?

Expecting ideas/workarounds from all... Please be kind enough for that.

Thanks !!!!!!!!!!!!!!!!!!

Hi -
You'll most likely receive more responses in the Sharepoint forums.

http://forums.microsoft.com/TechNet/default.aspx?ForumGroupID=330&SiteID=17

HTH...

Joe|||Thanks a lot for the link Joe.

Project in Share Point

Hello,

I have got a project where I am asked to utilize SharePoint features as much as possible. This project is earlier developed in .Net using Sharepoint Object Model. But now .Net should be eliminated as much as possible.

The requirements are :

Display a list of projects for the logged in user (windows logged in user). The list should have search criteria: project name, customer, project manager, project period.

On click of a project in the list, project details should be displayed Details like customer, team, start date, highlights, news. On click of a team member's name, Details of the team member (Name, office location, project joining, releaving dates, photo ) should be displayed.

To do this in Sharepoint my major hurdle is to display project list for windows logged in user. How do I do this in Sharepoint?

Expecting ideas/workarounds from all... Please be kind enough for that.

Thanks !!!!!!!!!!!!!!!!!!

Hi -
You'll most likely receive more responses in the Sharepoint forums.

http://forums.microsoft.com/TechNet/default.aspx?ForumGroupID=330&SiteID=17

HTH...

Joe|||Thanks a lot for the link Joe.

Programmatically altering object code

Has anyone developed a way to programmatically alter the code for multiple
stored procs, functions, etc.?
For example: suppose your database contains hundreds of stored procedures
which access a certain table. Along comes a requirement to rename this
table. It's a lot of tedious work to have to individually open each proc in
QA/EM and change the code. I would like to be able to simply type something
like:
EXEC sp_alter_proc_code 'OldTable', 'NewTable'
And presto, all procs that contain SQL statements that query OldTable are
now pointing to NewTable!
Note that you cannot run an UPDATE statement against syscomment's text field
(e.g., UPDATE syscomments SET text = REPLACE(text, 'OldTable', 'NewTable')),
since this is a computed field.
One solution that I had experimented with was to capture the contents of
syscomment's text column into a variable, run REPLACE against that variable,
and EXEC it. But the problem with that is, a variable can hold only 8000
characters.
I would be interested to hear if anyone has developed a workable solution
for this.>It's a lot of tedious work to have to individually open each proc in
>QA/EM and change the code.
Script all the procs out into a single file as CREATEs (without the
delete!) Open the script in Query Analyzer. Change all the CREATE
PROCs to ALTER PROC. Change all occurances of the old table name to
the new table name - there is a REPLACE ALL feature.
Execute the script.
Roy Harvey
Beacon Falls, CT|||CadeBryant wrote:
> Has anyone developed a way to programmatically alter the code for
> multiple stored procs, functions, etc.?
> For example: suppose your database contains hundreds of stored
> procedures which access a certain table. Along comes a requirement
> to rename this table. It's a lot of tedious work to have to
> individually open each proc in QA/EM and change the code. I would
> like to be able to simply type something like:
> EXEC sp_alter_proc_code 'OldTable', 'NewTable'
> And presto, all procs that contain SQL statements that query OldTable
> are now pointing to NewTable!
> Note that you cannot run an UPDATE statement against syscomment's
> text field (e.g., UPDATE syscomments SET text = REPLACE(text,
> 'OldTable', 'NewTable')), since this is a computed field.
> One solution that I had experimented with was to capture the contents
> of syscomment's text column into a variable, run REPLACE against that
> variable, and EXEC it. But the problem with that is, a variable can
> hold only 8000 characters.
> I would be interested to hear if anyone has developed a workable
> solution for this.
If you are managing your stored procedures using version control
software outside of SQL Server, then you can simply perform a find and
replace. You really should not be managing your procedures using SQL
Server. If you are, I would recommend you extract all procedures and get
them in some form of version control software before beginning this
process.
David Gugick - SQL Server MVP
Quest Software

Programmatic logon to replication agent?

Greetings,
I have developed custom replication software that I have been running
in house for 2 years now. During replication operations I have been
SETting IDENT_INSERT OFF and then back ON again (after replication
completes). I have also been disabling and re-enabling my
CONSTRAINTS. I have since learned that it is better to use the "NOT
FOR REPLICATION" option in these cases to avoid the additional
overhead.
As I understand the "NOT FOR REPLICATION" option, it will do
everything that I want it to do during my replication process so long
as I have logged on as a replication agent.
1. What do I have to do in my custom program to authenticate it as a
replication agent? (My goal is for the NOT FOR REPLICATION settings
to "kick in" only during the scheduled execution of my program.) I am
coding in C# using .NET.
2. Will I have to somehow create a replication agent profile or is
there a default one that I can use?
3. Since my program runs all day in the background, will it be
necessary to "log off" as a replication agent during dormant periods?
(I don't want my program to interfere with the smooth runnings of
daily business operations occurring during normal business hours.)
I have spent much time looking for this answer both online and in the
SQL Server help. Your recommendation will be greatly appreciated.
Mario T. Lanza
Clarity Information Architecture, Inc.
2004.09
Mario,
if you use queued updating subscribers, the Identity (Yes, not for
replication) property is sent to the subscriber during the initial snapshot.
The insert stored procedure that the distribution agent uses is different to
the normal one, and at the start has the statement:
BEGIN
set identity insert "tablename" on
....
As you see, this is no different to what you are already using, and is not
inbuilt into the replication agent as such.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'm sorry but I am using a custom application that handles all aspects
of replication. It builds parameterized insert/update SQL
dynamically. Although I could set identity insert ON then OFF before
and after each row, I would rather do it before and after all the rows
of the table have been inserted/updated. The issue lies in the fact
that the application may be manually or abnormally terminated prior to
my clean up code.
One of the main things my clean up code does is:
1. Restores the identity insert to OFF
2. Restores all CHECK constraints
I have a CHECK constraint on each table which throws an exception if
the proposed identity value is outside of the assigned range at the
store location.
As such, I'm not sure you answered my question.
What do I do so that the "NOT FOR REPLICATION" settings are observed
only when my *custom* replication agent application is running.
Bear in mind I am not using anything provided by Microsoft for
replication.
Thanks.
Mario
|||Mario,
I was comparing your situation to transactional queued
updating subscribers. In this replication setup there is
no global identity insert setting for the agent - it is
set on an individual row insert, in the text of a stored
procedure. However, another poster has mentioned the same
thing in the context of merge replication, where the
agent itself appears to be able to make a global setting
of identity insert. He wanted to be able to execute his
code and 'pretend' to be the merge agent in order to take
advantage of tihs setting, but was unable to do so. Quite
how this is enabled is undocumented and not in the public
domain AFAIK.
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That's unfortunate. I was hoping that someone had overcome this issue.
In any case, thanks for responding. I appreciate your effort.
Mario
|||I had the same problem and could not find any solution. I finally found
a merge replication guru guy. He is actually a contractor in Toronto
and selling a special component which is totally able to pretend to be
merge agent.
It is simple to use but may be a little bit expensive for start-up
companies. Since we desperately needed that feature the company just
paid for it instead of digging for weeks over weeks.
It gets SQL Server credentials as class properties and you call
ExecuteSQL method, it executes it as if it is merge agent.
For example I can execute the following SQL against my DB using that
component:
INSERT myTable (IdentityField, column1, column2) VALUES (5, 'test',
test')
and it works!!
you do not need to say SET IDENTITY_INSERT ON/OFF or disable any
constraints.
If you need to contact that merge replication consultant just send me
an email.
Nury Sword
NurySword@.hotmail.com
MCSD - MCDBA
Toronto