Friday, March 30, 2012
pros and cons of using stored procedures for VB app ?
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 ?
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 ?
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
Monday, March 26, 2012
Proper use of Event Notifications
Hi.
I'm developing an app that uses Service Broker queues to allow a customer to create "events" that fire using a timer or a query notification. When these events fire, a message is sent to a Service Broker queue for processing. Because there is much managed code involved in processing these messages, I decided to use the External Activator application and an Event Notification to process these messages. My question is "what is the difference between using the External Activator application to launch another application (which simply RECEIVEs a message from the target queue and processes it) and creating a windows service that simply monitors the target queue (with a WAITFOR = -1 clause) and processes it?"
I guess I'm not sure how using the QUEUE_ACTIVATION Event Notification is really helping me.
Thanks,
Chris
If all you need is a single instance of your service and don't mind it running all the time, you could implement this as a Windows Service that does a WAITFOR with no timeout. But if you want multiple instances of your service to be dynamically activated depending on the rate of incoming messages and how quickly your service is able to consume them, the external activator becomes useful. The main purpose of the external activator is to make services scalable.
The external activator is also capable of monitoring multiple queues, each configured with its own service program. So if you had 10 services, you do not need to have 10 windows services running even when queues are idle. You will have a single external activator running which will dynamically launch the service programs as messages arrive.
Hope that helps,
Rushi
|||Rushi,
After doing some more digging into the External Activator, I understand more clearly now. It seems that the scalability benefits are the real key for us. That and doing a WAITFOR with an indefinite timeout isn't so easy in a Windows Service.
Thanks,
Chris
|||The external activator does some of the hard things, like maintaining a recovery log so that if the process was to terminate and it came back up, it would recover state and not miss any notifications thus ensuring that queued messages do not get orphaned.Friday, March 23, 2012
prompted for username and password
I've a .net app written to view reports. The app is set up in iis
with the anonymous access checked and integrated windows
authentication checked for the default web site (my app - it's a
dedicated web server) and the same with the Reports and ReportServer
virtual directories within the Default Web Site.
I can get to my app's start page fine and browse the site I've
written, but when I try to view a report from the app I am prompted
for a username and password. Why is this and how can I get rid of
it'
I've added new roles for everyone and iusr_<machinename> but still I
can't get rid of these prompts.
Any ideas'
Gearóidlook at internet explorer configuration. Security / Intranet / and deselect
"Ask for..."
"Gearoid" wrote:
> hi,
> I've a .net app written to view reports. The app is set up in iis
> with the anonymous access checked and integrated windows
> authentication checked for the default web site (my app - it's a
> dedicated web server) and the same with the Reports and ReportServer
> virtual directories within the Default Web Site.
> I can get to my app's start page fine and browse the site I've
> written, but when I try to view a report from the app I am prompted
> for a username and password. Why is this and how can I get rid of
> it'
> I've added new roles for everyone and iusr_<machinename> but still I
> can't get rid of these prompts.
> Any ideas'
> Gearóid
>|||Thanks for the suggestion but that doesn't seem to be the problem. If
I run the report server from my own machine and try to access reports
from other machines accross the network there's no problem. But if I
deploy the reports to a web server (still on the same network) I get
prompted for the username / password.
Does this sound like a firewall issue with the web server? Or an IIS
issue? Or a Report Server Config issue?
I am truly stumpted here...
"LeCroix" <LeCroix@.discussions.microsoft.com> wrote in message news:<4B95B3D1-745F-4D8F-A127-5EEA073F7626@.microsoft.com>...
> look at internet explorer configuration. Security / Intranet / and deselect
> "Ask for..."
> "Gearoid" wrote:
> > hi,
> >
> > I've a .net app written to view reports. The app is set up in iis
> > with the anonymous access checked and integrated windows
> > authentication checked for the default web site (my app - it's a
> > dedicated web server) and the same with the Reports and ReportServer
> > virtual directories within the Default Web Site.
> >
> > I can get to my app's start page fine and browse the site I've
> > written, but when I try to view a report from the app I am prompted
> > for a username and password. Why is this and how can I get rid of
> > it'
> >
> > I've added new roles for everyone and iusr_<machinename> but still I
> > can't get rid of these prompts.
> >
> > Any ideas'
> >
> > Gearóid
> >
Tuesday, March 20, 2012
progress indication from ssis app in VB
Hi
am calling an ssis app from my vb programme. It takes about 5 minutes to run. What is the best way to provide progress feedback to my vb user.
Cheers
Ants
I
Ants Hurdley wrote:
Hi
am calling an ssis app from my vb programme. It takes about 5 minutes to run. What is the best way to provide progress feedback to my vb user.
Cheers
Ants
I
Package.Execute() has an overloaded constructor that allows you to pass in a IDTSEvents object. As far as I'm aware this allows you to get events out of the executing package - after which you can surface them to your VB app.
Take a look here for more info:
DtsContainer.Execute Method
(http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtscontainer.execute.aspx)
-Jamie
|||There is also a sample event listener code at
http://msdn2.microsoft.com/en-us/library/ms136090.aspx|||
Hi
Thanks for this - I am not sure if I am understanding this correctly as I am new to this but I want to show the progress as the package is executed. I.e. my Package has 15 steps, so I would like to show a progress bar or something to let the user know that things are happening. Not just show a success or failire.
Cheers
|||The sampel only demonstrates overriding the OnError event, but you can override any of the other events, such as OnPreExecute and OnPostExecute, which will give you begin and end notices for each container. See here for more information on the DefaultEvents class-
DefaultEvents Members (Microsoft.SqlServer.Dts.Runtime)
(http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.defaultevents_members.aspx)
Ants Hurdley wrote:
Hi
Thanks for this - I am not sure if I am understanding this correctly as I am new to this but I want to show the progress as the package is executed. I.e. my Package has 15 steps, so I would like to show a progress bar or something to let the user know that things are happening. Not just show a success or failire.
Cheers
Well that's not really a SSIS question. SSIS can tell you its progress and its up to you how you display that information in your app. A progress bar is an option I guess.
Note that BIDS uses the same mechanism to populate the "Package Execution" tab when you debug a package .
-Jamie
Friday, March 9, 2012
Programmatically Deleting an Instance
I'm trying to just get a basic NS app up and running. While I can get through the samples just fine, it seems there are no samples that programmatically manage Instances/Applications, etc. I've googled around and not found anything either. Essentially it looks to me that everyone uses the IDF/ADF files. The SDK has some very sparse documentation on it, so I've started there and tried to get things working.
I have code to create the instance/application programmatically, but when I want to drop the whole thing and create it again, upon calling instance.Create() I get an exception (actually this is what the innerException says) saying:
Notification Services failed to read the NSVersionInfo table.
I’m following the example in the SDK (doing Disable/UnregisterLocal/Drop). Interestingly when I do the Disable/Unregister/Drop in SQL Management Studio it works fine. It's also interesting to note that when I ignore the exception it comes up once more, but after that the instance and associated databases seem to be created.
Here’s the short version of my code (getFreshInstance() is the entry method):
private void configureInstance(nmo.Instance instance)
{
prototypeApplication = new nmo.Application(instance, applicationName);
AddDeliveryChannels(instance);
configureApp(prototypeApplication);
instance.Applications.Add(prototypeApplication);
}
private void configureApp(nmo.Application app)
{
AddGenerator(app);
AddDistributor(app);
}
private void checkAndDrop(string name)
{
if (notificationServices.Instances.Contains(name))
{
nmo.Instance instance = new nmo.Instance(notificationServices, name);
instance.Refresh();
instance.Disable();
instance.UnregisterLocal();
instance.Drop();
}
}
private nmo.Instance getFreshInstance()
{
nmo.Instance instance = new nmo.Instance(notificationServices, instanceName);
checkAndDrop(instanceName);
configureInstance(instance);
instance.Create();
instance.RegisterLocal();
instance.Enable();
return instance;
}
Anyone know what I'm doing wrong?
Ok, seems you've asked a question not many folks in the wild know the answer to :-).
I've tracked down what is probably the answer care of Shyam Pather:
He’s creating a new in-memory object to reflect his instance, like this:
Nmo.Instance instance = new Nmo.Instance(…, “InstanceName”);
Instead, he should do something like:
Smo.Server s = new Smo.Server(…);
Nmo.Instance instance = s.NotificationServices.Instances[“InstanceName”];
That will get him an instance object that’s already populated with the metadata from the database and actually represents the existing instance.
The FlightNMO sample that comes with SQL Server 2005 should also provide you a working example of how to make the API calls.
Hope that helps,
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
I just gave that snippet a try and unfortunately, I ended up with the same error when I attempt to enable. The need for this functionality in my project has been removed by a change in design, so I'm no longer really worried about this, but I would still be curious to know the cause of this or hear confirmation of someone else running into this.
Now that you mention it, I vaguely remember seeing the FlightNMO sample when I first looked at NS, but had since forgotten it. Thanks! I'm sure that'll prove quite useful later. As far as this particular functionality though, I'm afraid FlightNMO doesn't ever delete the instance, only create.
Thanks for your help,
-Francis
Wednesday, March 7, 2012
Programmatically checking the database compatibility level?
You can query the catalog views below to get the database compatibility level information:
-- SQL Server 2005 only
select compatibility_level from sys.databases where name = @.dbname
-- SQL70/2000/2005
select cmptlevel from master.dbo.sysdatabases where name = @.dbname
|||That's it. Thanks!
Saturday, February 25, 2012
Programmatic access to the code SQLCMD uses to parse sql into batches
Hi,
I am thinking of writing a C# Windows app to iterate a set of sql scripts and submit them to SQL Server. Many scripts have "go" as a batch delimiter. I could probably figure out a regular expression to parse for that delimiter, but it seems tricky (need to exclude 'go', -- go, /* go */, etc). Obviously SQLCMD and Management Studio have a way to do this. I was wondering if that's exposed in an API anywhere. I looked into the SMO object model, but I couldn't find it.
Thanks much,
Mike
Hi,
thats no mafic, just use the SET PARSEONLY statement before your statements to check.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
As far as I can tell "set parseonly on" just checks the syntax. I want to get back a collection of strings to submit, each one a proper batch. Eg:
select 'Inside quotes, go or " go " or '' go '' is not a delimiter ' as go -- not go here
select 'Second go '
go
select 'Another batch here /* go */ go '
go
In query analyzer or management studio this appropriately returns 3 result sets, presumably executed as 2 batches. I would like to identify the first 2 lines as one batch, and the second select as another batch.
Mike