Best Practice-working with SQL Express Database

Hi,

I am new to working with Database using .net C#. i will really appreciate if someone can point me to a link where i can find best practices of working with sql express database with my c# web service applications.

preferably i would like to have answers for:

- to have one db or to have one for testing and one real db?

- db security

- use windows authentication or db user authentication

etc.

Best practices for SqlExpress will depend somewhat on your environment/requirements. Since you are developing a web service, I suggest asking the question in one of the asp.net forums (forums.asp.net).

SqlExpress has it's own forum here on the MSDN forums. I'll move the thread over there for more general info on Express best practices.sql

best practices stored procedures and triggers

Using SS2000 SP4. We have a .NET app that accesses the sql server database.
I've tried to keep all access via stored procedures and views and the user
only has execute permissions on the sp and select on the views.
The contractors have put a trigger on one of the tables.
CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyC
ontacts]
FOR INSERT, UPDATE
AS
IF (Select bitCompPrimaryContact From Inserted) = 1
Begin
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End
So, the user needs select and update permissions on the table. Since I
didn't want to give insert, update or delete permissions on any tables to th
e
user is there a way around it? What is a best practice approach. Do I just
not use update triggers?
Thanks,
--
Dan D.As long as you are using stored procedures you do not need to use this
trigger. Tell the contractors to put this code within the stored procedure
that updates the table tblCompanyContacts.
Keith Kratochvil
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> Using SS2000 SP4. We have a .NET app that accesses the sql server
> database.
> I've tried to keep all access via stored procedures and views and the user
> only has execute permissions on the sp and select on the views.
> The contractors have put a trigger on one of the tables.
> CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompan
yContacts]
> FOR INSERT, UPDATE
> AS
> IF (Select bitCompPrimaryContact From Inserted) = 1
> Begin
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End
> So, the user needs select and update permissions on the table. Since I
> didn't want to give insert, update or delete permissions on any tables to
> the
> user is there a way around it? What is a best practice approach. Do I just
> not use update triggers?
> Thanks,
> --
> Dan D.|||That sounds good. Thanks Keith.
--
Dan D.
"Keith Kratochvil" wrote:

> As long as you are using stored procedures you do not need to use this
> trigger. Tell the contractors to put this code within the stored procedur
e
> that updates the table tblCompanyContacts.
> --
> Keith Kratochvil
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
>
>|||I don't believe you don't need to grant any additional permissions on
the base table to users to execute a trigger. The "grant execute" on
the update or insert stored procedures should be enough.
Dan D. wrote:[vbcol=seagreen]
> That sounds good. Thanks Keith.
> --
> Dan D.
>
> "Keith Kratochvil" wrote:
>|||On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:

>Using SS2000 SP4. We have a .NET app that accesses the sql server database.
>I've tried to keep all access via stored procedures and views and the user
>only has execute permissions on the sp and select on the views.
>The contractors have put a trigger on one of the tables.
>CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompany
Contacts]
>FOR INSERT, UPDATE
>AS
>IF (Select bitCompPrimaryContact From Inserted) = 1
>Begin
>Update tblCompanyContacts Set bitCompPrimaryContact = 0
>Where numCompanyId = (Select numCompanyId From Inserted)
>And numContactID <> (Select numContactId From Inserted)
>End
Hi Dan,
First, fire the contractors. This trigger will fail on any multi-row
insert or update. No-one should write triggers like that and be paid for
it!

>So, the user needs select and update permissions on the table.
Says who? As long as the table is owned by the same user that owns the
trigger, the user doesn't need any permissions on the table - see the
repro script below.

> Since I
>didn't want to give insert, update or delete permissions on any tables to t
he
>user is there a way around it? What is a best practice approach. Do I just
>not use update triggers?
In most cases, I would recommend having all objects (tables, views,
stored procedures, triggers, functions, ...) owned by dbo. Then, you can
set permissions exactly as required. Due to all objects having the same
owner, all statements from within stored procedures, triggers, and
functions will work. (Unless you're using dynamic SQL).
Here's a repro to show that a user doesn't need permission on the table
if a trigger changes data in the table.
First, run the script below while connected with an accoount with
sufficient rights to add databases and logins:
USE master
go
DROP DATABASE test
go
sp_droplogin @.loginame = 'test'
go
CREATE DATABASE test
go
sp_addlogin @.loginame = 'test',
@.passwd = 'test',
@.defdb = 'test'
go
USE test
go
sp_grantdbaccess @.loginame = 'test'
go
CREATE TABLE tab (a int NOT NULL,
b int DEFAULT NULL)
go
CREATE VIEW vw
AS
SELECT a, b
FROM tab
go
CREATE TRIGGER trig
ON tab
FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT > 0
BEGIN
UPDATE tab
SET b = a + 15
WHERE b IS NULL
AND EXISTS
(SELECT *
FROM inserted
WHERE inserted.a = tab.a)
END
go
CREATE PROC ins_tab (@.a int)
AS
INSERT INTO tab (a)
VALUES (@.a)
go
DENY ALL ON tab TO test
DENY UPDATE, INSERT, DELETE ON vw TO test
GRANT SELECT ON vw TO test
GRANT EXECUTE ON ins_tab TO test
go
Then, open a new connection, log in with userid 'test' (use SQL Server
authentication, not integrated authentication) and execute the code
below:
-- Login as user 'test'
-- Fails - no permissions on tab
SELECT *
FROM tab
-- Succeeds - SELECT permission on vw granted
SELECT *
FROM vw
-- Fails - no INSERT permission on vw
INSERT INTO vw (a)
VALUES (3)
-- Succees - EXECUTE permission on ins_tab granted
EXECUTE ins_tab @.a = 3
-- Check that trigger has executed as well
SELECT *
FROM vw
go
Here's the output:
Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
a b
-- --
Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
a b
-- --
3 18
Hugo Kornelis, SQL Server MVP|||I see it now. Thanks for such a detailed explanation Hugo.
BTW, we did fire the contractors.
--
Dan D.
"Hugo Kornelis" wrote:

> On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:
>
> Hi Dan,
> First, fire the contractors. This trigger will fail on any multi-row
> insert or update. No-one should write triggers like that and be paid for
> it!
>
> Says who? As long as the table is owned by the same user that owns the
> trigger, the user doesn't need any permissions on the table - see the
> repro script below.
>
> In most cases, I would recommend having all objects (tables, views,
> stored procedures, triggers, functions, ...) owned by dbo. Then, you can
> set permissions exactly as required. Due to all objects having the same
> owner, all statements from within stored procedures, triggers, and
> functions will work. (Unless you're using dynamic SQL).
> Here's a repro to show that a user doesn't need permission on the table
> if a trigger changes data in the table.
> First, run the script below while connected with an accoount with
> sufficient rights to add databases and logins:
> USE master
> go
> DROP DATABASE test
> go
> sp_droplogin @.loginame = 'test'
> go
> CREATE DATABASE test
> go
> sp_addlogin @.loginame = 'test',
> @.passwd = 'test',
> @.defdb = 'test'
> go
> USE test
> go
> sp_grantdbaccess @.loginame = 'test'
> go
> CREATE TABLE tab (a int NOT NULL,
> b int DEFAULT NULL)
> go
> CREATE VIEW vw
> AS
> SELECT a, b
> FROM tab
> go
> CREATE TRIGGER trig
> ON tab
> FOR INSERT, UPDATE
> AS
> IF @.@.ROWCOUNT > 0
> BEGIN
> UPDATE tab
> SET b = a + 15
> WHERE b IS NULL
> AND EXISTS
> (SELECT *
> FROM inserted
> WHERE inserted.a = tab.a)
> END
> go
> CREATE PROC ins_tab (@.a int)
> AS
> INSERT INTO tab (a)
> VALUES (@.a)
> go
> DENY ALL ON tab TO test
> DENY UPDATE, INSERT, DELETE ON vw TO test
> GRANT SELECT ON vw TO test
> GRANT EXECUTE ON ins_tab TO test
> go
> Then, open a new connection, log in with userid 'test' (use SQL Server
> authentication, not integrated authentication) and execute the code
> below:
> -- Login as user 'test'
> -- Fails - no permissions on tab
> SELECT *
> FROM tab
> -- Succeeds - SELECT permission on vw granted
> SELECT *
> FROM vw
> -- Fails - no INSERT permission on vw
> INSERT INTO vw (a)
> VALUES (3)
> -- Succees - EXECUTE permission on ins_tab granted
> EXECUTE ins_tab @.a = 3
> -- Check that trigger has executed as well
> SELECT *
> FROM vw
> go
> Here's the output:
> Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
> a b
> -- --
> Msg 229, Level 14, State 5, Line 1
> INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
> a b
> -- --
> 3 18
>
> --
> Hugo Kornelis, SQL Server MVP
>|||The trigger they wrote also contained a rookie mistake. It was designed for
a single row at a time. When you are writing a trigger you always need to
plan on having multi row events occur at some point.
Snip--
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End snip--
See how the subqueries assume that only one item will be returned. This code
will raise an error if there is more than one row in the Inserted table (in
other words, if a multi row update or insert occurs) and that would cause
the original transaction to be rolled back.
Far better would be the following:
---
Update tblCompanyContacts Set bitCompPrimaryContact = 0
FROM tblCompanyContacts C
JOIN Inserted I
ON I.numCompanyId = C.numCompanyId
And I.numContactID <> C.numContactId
---
This way no matter how many rows have been affected by the update or insert
statement the trigger will now handle them.
All of the advice you received on moving the logic to the stored procedure
has merit. You should understand the following. The trigger will fire for
any update or insert on your table. Some folks like to use triggers to
handle some of their data integrity enforcement and not worry about in their
procs. If you do put it in your procs, you will need to put that logic into
every proc that will update or insert into that table. If this is a large #
of procs then you are setting up a shotgun surgery approach to maintenance.
The other thing to be aware of is that your trigger fires as part of your
original transaction, so an error in it will cause your original action to
be rolled back, as well as the locks from your original action are still
maintained during the trigger. In this case that is not a bad thing, since
you want this to happen as part of the update, and if it fails, you want the
update to fail as well.
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com|||So, is it probably safer to stay away from subqueries in triggers?
One of the things we have to be careful about with triggers is that we do a
lot of data manipulation on the backend. So we have to be sure that if we
insert 5M rows on the backend and not through the app, a trigger doesn't fir
e
and do something it shouldn't.
Thanks,
--
Dan D.
"David Lundell" wrote:

> The trigger they wrote also contained a rookie mistake. It was designed fo
r
> a single row at a time. When you are writing a trigger you always need to
> plan on having multi row events occur at some point.
> Snip--
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End snip--
> See how the subqueries assume that only one item will be returned. This co
de
> will raise an error if there is more than one row in the Inserted table (i
n
> other words, if a multi row update or insert occurs) and that would cause
> the original transaction to be rolled back.
> Far better would be the following:
> ---
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> FROM tblCompanyContacts C
> JOIN Inserted I
> ON I.numCompanyId = C.numCompanyId
> And I.numContactID <> C.numContactId
> ---
> This way no matter how many rows have been affected by the update or inser
t
> statement the trigger will now handle them.
> All of the advice you received on moving the logic to the stored procedure
> has merit. You should understand the following. The trigger will fire for
> any update or insert on your table. Some folks like to use triggers to
> handle some of their data integrity enforcement and not worry about in the
ir
> procs. If you do put it in your procs, you will need to put that logic int
o
> every proc that will update or insert into that table. If this is a large
#
> of procs then you are setting up a shotgun surgery approach to maintenance
.
> The other thing to be aware of is that your trigger fires as part of your
> original transaction, so an error in it will cause your original action to
> be rolled back, as well as the locks from your original action are still
> maintained during the trigger. In this case that is not a bad thing, since
> you want this to happen as part of the update, and if it fails, you want t
he
> update to fail as well.
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
>
>|||Subqueries are not the issue. It is the way the subqueries were written
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId IN(Select numCompanyId From Inserted)
And numContactID NOT IN (Select numContactId From Inserted)
This would also work, although it might not be as efficient as my earlier
example.
As for backend loading. I usually disable my triggers for the load and
reenable them afterwards. But only if I make certain to perform what my
trigger would perform. In this case setting the bitCompPrimaryContact = 0.
ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
Do the load
ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...[vbcol=seagreen]
> So, is it probably safer to stay away from subqueries in triggers?
> One of the things we have to be careful about with triggers is that we do
> a
> lot of data manipulation on the backend. So we have to be sure that if we
> insert 5M rows on the backend and not through the app, a trigger doesn't
> fire
> and do something it shouldn't.
> Thanks,
> --
> Dan D.
>
> "David Lundell" wrote:
>|||We're still working on a solution. We have many clients that we do mailings
for and we have hundreds of databases. I'm not sure it's the best way to try
and keep track of every trigger on every table.
We're an example of a small company that grew very fast without any
standards or best practices. Now we're trying to get everything under contro
l.
We recently had a case where a developer put a trigger on a table for an
application and didn't tell anyone about it. Adn to compound the problem, th
e
trigger didn't work like he thought it would. I guess he didn't test it
thoroughtly. When someone loaded some new data on the backend, the trigger
fired and updated 150,000+ rows that it shouldn't have. We didn't discovered
it until months later and we're trying to figure out how to roll it back.
Thanks for all of your help,
--
Dan D.
"David Lundell" wrote:

> Subqueries are not the issue. It is the way the subqueries were written
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId IN(Select numCompanyId From Inserted)
> And numContactID NOT IN (Select numContactId From Inserted)
> This would also work, although it might not be as efficient as my earlier
> example.
> As for backend loading. I usually disable my triggers for the load and
> reenable them afterwards. But only if I make certain to perform what my
> trigger would perform. In this case setting the bitCompPrimaryContact = 0.
> ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
> Do the load
> ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
>
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...
>
>

best practices stored procedures and triggers

Using SS2000 SP4. We have a .NET app that accesses the sql server database.
I've tried to keep all access via stored procedures and views and the user
only has execute permissions on the sp and select on the views.
The contractors have put a trigger on one of the tables.
CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
FOR INSERT, UPDATE
AS
IF (Select bitCompPrimaryContact From Inserted) = 1
Begin
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End
So, the user needs select and update permissions on the table. Since I
didn't want to give insert, update or delete permissions on any tables to the
user is there a way around it? What is a best practice approach. Do I just
not use update triggers?
Thanks,
--
Dan D.As long as you are using stored procedures you do not need to use this
trigger. Tell the contractors to put this code within the stored procedure
that updates the table tblCompanyContacts.
--
Keith Kratochvil
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> Using SS2000 SP4. We have a .NET app that accesses the sql server
> database.
> I've tried to keep all access via stored procedures and views and the user
> only has execute permissions on the sp and select on the views.
> The contractors have put a trigger on one of the tables.
> CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> FOR INSERT, UPDATE
> AS
> IF (Select bitCompPrimaryContact From Inserted) = 1
> Begin
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End
> So, the user needs select and update permissions on the table. Since I
> didn't want to give insert, update or delete permissions on any tables to
> the
> user is there a way around it? What is a best practice approach. Do I just
> not use update triggers?
> Thanks,
> --
> Dan D.|||That sounds good. Thanks Keith.
--
Dan D.
"Keith Kratochvil" wrote:
> As long as you are using stored procedures you do not need to use this
> trigger. Tell the contractors to put this code within the stored procedure
> that updates the table tblCompanyContacts.
> --
> Keith Kratochvil
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> > Using SS2000 SP4. We have a .NET app that accesses the sql server
> > database.
> > I've tried to keep all access via stored procedures and views and the user
> > only has execute permissions on the sp and select on the views.
> >
> > The contractors have put a trigger on one of the tables.
> >
> > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> > FOR INSERT, UPDATE
> > AS
> > IF (Select bitCompPrimaryContact From Inserted) = 1
> > Begin
> > Update tblCompanyContacts Set bitCompPrimaryContact = 0
> > Where numCompanyId = (Select numCompanyId From Inserted)
> > And numContactID <> (Select numContactId From Inserted)
> > End
> >
> > So, the user needs select and update permissions on the table. Since I
> > didn't want to give insert, update or delete permissions on any tables to
> > the
> > user is there a way around it? What is a best practice approach. Do I just
> > not use update triggers?
> >
> > Thanks,
> > --
> > Dan D.
>
>|||I don't believe you don't need to grant any additional permissions on
the base table to users to execute a trigger. The "grant execute" on
the update or insert stored procedures should be enough.
Dan D. wrote:
> That sounds good. Thanks Keith.
> --
> Dan D.
>
> "Keith Kratochvil" wrote:
> > As long as you are using stored procedures you do not need to use this
> > trigger. Tell the contractors to put this code within the stored procedure
> > that updates the table tblCompanyContacts.
> >
> > --
> > Keith Kratochvil
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> > > Using SS2000 SP4. We have a .NET app that accesses the sql server
> > > database.
> > > I've tried to keep all access via stored procedures and views and the user
> > > only has execute permissions on the sp and select on the views.
> > >
> > > The contractors have put a trigger on one of the tables.
> > >
> > > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> > > FOR INSERT, UPDATE
> > > AS
> > > IF (Select bitCompPrimaryContact From Inserted) = 1
> > > Begin
> > > Update tblCompanyContacts Set bitCompPrimaryContact = 0
> > > Where numCompanyId = (Select numCompanyId From Inserted)
> > > And numContactID <> (Select numContactId From Inserted)
> > > End
> > >
> > > So, the user needs select and update permissions on the table. Since I
> > > didn't want to give insert, update or delete permissions on any tables to
> > > the
> > > user is there a way around it? What is a best practice approach. Do I just
> > > not use update triggers?
> > >
> > > Thanks,
> > > --
> > > Dan D.
> >
> >
> >|||On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:
>Using SS2000 SP4. We have a .NET app that accesses the sql server database.
>I've tried to keep all access via stored procedures and views and the user
>only has execute permissions on the sp and select on the views.
>The contractors have put a trigger on one of the tables.
>CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
>FOR INSERT, UPDATE
>AS
>IF (Select bitCompPrimaryContact From Inserted) = 1
>Begin
>Update tblCompanyContacts Set bitCompPrimaryContact = 0
>Where numCompanyId = (Select numCompanyId From Inserted)
>And numContactID <> (Select numContactId From Inserted)
>End
Hi Dan,
First, fire the contractors. This trigger will fail on any multi-row
insert or update. No-one should write triggers like that and be paid for
it!
>So, the user needs select and update permissions on the table.
Says who? As long as the table is owned by the same user that owns the
trigger, the user doesn't need any permissions on the table - see the
repro script below.
> Since I
>didn't want to give insert, update or delete permissions on any tables to the
>user is there a way around it? What is a best practice approach. Do I just
>not use update triggers?
In most cases, I would recommend having all objects (tables, views,
stored procedures, triggers, functions, ...) owned by dbo. Then, you can
set permissions exactly as required. Due to all objects having the same
owner, all statements from within stored procedures, triggers, and
functions will work. (Unless you're using dynamic SQL).
Here's a repro to show that a user doesn't need permission on the table
if a trigger changes data in the table.
First, run the script below while connected with an accoount with
sufficient rights to add databases and logins:
USE master
go
DROP DATABASE test
go
sp_droplogin @.loginame = 'test'
go
CREATE DATABASE test
go
sp_addlogin @.loginame = 'test',
@.passwd = 'test',
@.defdb = 'test'
go
USE test
go
sp_grantdbaccess @.loginame = 'test'
go
CREATE TABLE tab (a int NOT NULL,
b int DEFAULT NULL)
go
CREATE VIEW vw
AS
SELECT a, b
FROM tab
go
CREATE TRIGGER trig
ON tab
FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT > 0
BEGIN
UPDATE tab
SET b = a + 15
WHERE b IS NULL
AND EXISTS
(SELECT *
FROM inserted
WHERE inserted.a = tab.a)
END
go
CREATE PROC ins_tab (@.a int)
AS
INSERT INTO tab (a)
VALUES (@.a)
go
DENY ALL ON tab TO test
DENY UPDATE, INSERT, DELETE ON vw TO test
GRANT SELECT ON vw TO test
GRANT EXECUTE ON ins_tab TO test
go
Then, open a new connection, log in with userid 'test' (use SQL Server
authentication, not integrated authentication) and execute the code
below:
-- Login as user 'test'
-- Fails - no permissions on tab
SELECT *
FROM tab
-- Succeeds - SELECT permission on vw granted
SELECT *
FROM vw
-- Fails - no INSERT permission on vw
INSERT INTO vw (a)
VALUES (3)
-- Succees - EXECUTE permission on ins_tab granted
EXECUTE ins_tab @.a = 3
-- Check that trigger has executed as well
SELECT *
FROM vw
go
Here's the output:
Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
a b
-- --
Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
a b
-- --
3 18
Hugo Kornelis, SQL Server MVP|||I see it now. Thanks for such a detailed explanation Hugo.
BTW, we did fire the contractors.:)
--
Dan D.
"Hugo Kornelis" wrote:
> On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:
> >Using SS2000 SP4. We have a .NET app that accesses the sql server database.
> >I've tried to keep all access via stored procedures and views and the user
> >only has execute permissions on the sp and select on the views.
> >
> >The contractors have put a trigger on one of the tables.
> >
> >CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> >FOR INSERT, UPDATE
> >AS
> >IF (Select bitCompPrimaryContact From Inserted) = 1
> >Begin
> >Update tblCompanyContacts Set bitCompPrimaryContact = 0
> >Where numCompanyId = (Select numCompanyId From Inserted)
> >And numContactID <> (Select numContactId From Inserted)
> >End
> Hi Dan,
> First, fire the contractors. This trigger will fail on any multi-row
> insert or update. No-one should write triggers like that and be paid for
> it!
> >So, the user needs select and update permissions on the table.
> Says who? As long as the table is owned by the same user that owns the
> trigger, the user doesn't need any permissions on the table - see the
> repro script below.
> > Since I
> >didn't want to give insert, update or delete permissions on any tables to the
> >user is there a way around it? What is a best practice approach. Do I just
> >not use update triggers?
> In most cases, I would recommend having all objects (tables, views,
> stored procedures, triggers, functions, ...) owned by dbo. Then, you can
> set permissions exactly as required. Due to all objects having the same
> owner, all statements from within stored procedures, triggers, and
> functions will work. (Unless you're using dynamic SQL).
> Here's a repro to show that a user doesn't need permission on the table
> if a trigger changes data in the table.
> First, run the script below while connected with an accoount with
> sufficient rights to add databases and logins:
> USE master
> go
> DROP DATABASE test
> go
> sp_droplogin @.loginame = 'test'
> go
> CREATE DATABASE test
> go
> sp_addlogin @.loginame = 'test',
> @.passwd = 'test',
> @.defdb = 'test'
> go
> USE test
> go
> sp_grantdbaccess @.loginame = 'test'
> go
> CREATE TABLE tab (a int NOT NULL,
> b int DEFAULT NULL)
> go
> CREATE VIEW vw
> AS
> SELECT a, b
> FROM tab
> go
> CREATE TRIGGER trig
> ON tab
> FOR INSERT, UPDATE
> AS
> IF @.@.ROWCOUNT > 0
> BEGIN
> UPDATE tab
> SET b = a + 15
> WHERE b IS NULL
> AND EXISTS
> (SELECT *
> FROM inserted
> WHERE inserted.a = tab.a)
> END
> go
> CREATE PROC ins_tab (@.a int)
> AS
> INSERT INTO tab (a)
> VALUES (@.a)
> go
> DENY ALL ON tab TO test
> DENY UPDATE, INSERT, DELETE ON vw TO test
> GRANT SELECT ON vw TO test
> GRANT EXECUTE ON ins_tab TO test
> go
> Then, open a new connection, log in with userid 'test' (use SQL Server
> authentication, not integrated authentication) and execute the code
> below:
> -- Login as user 'test'
> -- Fails - no permissions on tab
> SELECT *
> FROM tab
> -- Succeeds - SELECT permission on vw granted
> SELECT *
> FROM vw
> -- Fails - no INSERT permission on vw
> INSERT INTO vw (a)
> VALUES (3)
> -- Succees - EXECUTE permission on ins_tab granted
> EXECUTE ins_tab @.a = 3
> -- Check that trigger has executed as well
> SELECT *
> FROM vw
> go
> Here's the output:
> Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
> a b
> -- --
> Msg 229, Level 14, State 5, Line 1
> INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
> a b
> -- --
> 3 18
>
> --
> Hugo Kornelis, SQL Server MVP
>|||The trigger they wrote also contained a rookie mistake. It was designed for
a single row at a time. When you are writing a trigger you always need to
plan on having multi row events occur at some point.
Snip--
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End snip--
See how the subqueries assume that only one item will be returned. This code
will raise an error if there is more than one row in the Inserted table (in
other words, if a multi row update or insert occurs) and that would cause
the original transaction to be rolled back.
Far better would be the following:
---
Update tblCompanyContacts Set bitCompPrimaryContact = 0
FROM tblCompanyContacts C
JOIN Inserted I
ON I.numCompanyId = C.numCompanyId
And I.numContactID <> C.numContactId
---
This way no matter how many rows have been affected by the update or insert
statement the trigger will now handle them.
All of the advice you received on moving the logic to the stored procedure
has merit. You should understand the following. The trigger will fire for
any update or insert on your table. Some folks like to use triggers to
handle some of their data integrity enforcement and not worry about in their
procs. If you do put it in your procs, you will need to put that logic into
every proc that will update or insert into that table. If this is a large #
of procs then you are setting up a shotgun surgery approach to maintenance.
The other thing to be aware of is that your trigger fires as part of your
original transaction, so an error in it will cause your original action to
be rolled back, as well as the locks from your original action are still
maintained during the trigger. In this case that is not a bad thing, since
you want this to happen as part of the update, and if it fails, you want the
update to fail as well.
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com|||So, is it probably safer to stay away from subqueries in triggers?
One of the things we have to be careful about with triggers is that we do a
lot of data manipulation on the backend. So we have to be sure that if we
insert 5M rows on the backend and not through the app, a trigger doesn't fire
and do something it shouldn't.
Thanks,
--
Dan D.
"David Lundell" wrote:
> The trigger they wrote also contained a rookie mistake. It was designed for
> a single row at a time. When you are writing a trigger you always need to
> plan on having multi row events occur at some point.
> Snip--
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End snip--
> See how the subqueries assume that only one item will be returned. This code
> will raise an error if there is more than one row in the Inserted table (in
> other words, if a multi row update or insert occurs) and that would cause
> the original transaction to be rolled back.
> Far better would be the following:
> ---
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> FROM tblCompanyContacts C
> JOIN Inserted I
> ON I.numCompanyId = C.numCompanyId
> And I.numContactID <> C.numContactId
> ---
> This way no matter how many rows have been affected by the update or insert
> statement the trigger will now handle them.
> All of the advice you received on moving the logic to the stored procedure
> has merit. You should understand the following. The trigger will fire for
> any update or insert on your table. Some folks like to use triggers to
> handle some of their data integrity enforcement and not worry about in their
> procs. If you do put it in your procs, you will need to put that logic into
> every proc that will update or insert into that table. If this is a large #
> of procs then you are setting up a shotgun surgery approach to maintenance.
> The other thing to be aware of is that your trigger fires as part of your
> original transaction, so an error in it will cause your original action to
> be rolled back, as well as the locks from your original action are still
> maintained during the trigger. In this case that is not a bad thing, since
> you want this to happen as part of the update, and if it fails, you want the
> update to fail as well.
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
>
>|||Subqueries are not the issue. It is the way the subqueries were written
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId IN(Select numCompanyId From Inserted)
And numContactID NOT IN (Select numContactId From Inserted)
This would also work, although it might not be as efficient as my earlier
example.
As for backend loading. I usually disable my triggers for the load and
reenable them afterwards. But only if I make certain to perform what my
trigger would perform. In this case setting the bitCompPrimaryContact = 0.
ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
Do the load
ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...
> So, is it probably safer to stay away from subqueries in triggers?
> One of the things we have to be careful about with triggers is that we do
> a
> lot of data manipulation on the backend. So we have to be sure that if we
> insert 5M rows on the backend and not through the app, a trigger doesn't
> fire
> and do something it shouldn't.
> Thanks,
> --
> Dan D.
>
> "David Lundell" wrote:
>> The trigger they wrote also contained a rookie mistake. It was designed
>> for
>> a single row at a time. When you are writing a trigger you always need to
>> plan on having multi row events occur at some point.
>> Snip--
>> Update tblCompanyContacts Set bitCompPrimaryContact = 0
>> Where numCompanyId = (Select numCompanyId From Inserted)
>> And numContactID <> (Select numContactId From Inserted)
>> End snip--
>> See how the subqueries assume that only one item will be returned. This
>> code
>> will raise an error if there is more than one row in the Inserted table
>> (in
>> other words, if a multi row update or insert occurs) and that would cause
>> the original transaction to be rolled back.
>> Far better would be the following:
>> ---
>> Update tblCompanyContacts Set bitCompPrimaryContact = 0
>> FROM tblCompanyContacts C
>> JOIN Inserted I
>> ON I.numCompanyId = C.numCompanyId
>> And I.numContactID <> C.numContactId
>> ---
>> This way no matter how many rows have been affected by the update or
>> insert
>> statement the trigger will now handle them.
>> All of the advice you received on moving the logic to the stored
>> procedure
>> has merit. You should understand the following. The trigger will fire for
>> any update or insert on your table. Some folks like to use triggers to
>> handle some of their data integrity enforcement and not worry about in
>> their
>> procs. If you do put it in your procs, you will need to put that logic
>> into
>> every proc that will update or insert into that table. If this is a large
>> #
>> of procs then you are setting up a shotgun surgery approach to
>> maintenance.
>> The other thing to be aware of is that your trigger fires as part of your
>> original transaction, so an error in it will cause your original action
>> to
>> be rolled back, as well as the locks from your original action are still
>> maintained during the trigger. In this case that is not a bad thing,
>> since
>> you want this to happen as part of the update, and if it fails, you want
>> the
>> update to fail as well.
>> --
>> David Lundell
>> Principal Consultant and Trainer
>> www.MutuallyBeneficial.com
>> David@.MutuallyBeneficial.com
>>|||We're still working on a solution. We have many clients that we do mailings
for and we have hundreds of databases. I'm not sure it's the best way to try
and keep track of every trigger on every table.
We're an example of a small company that grew very fast without any
standards or best practices. Now we're trying to get everything under control.
We recently had a case where a developer put a trigger on a table for an
application and didn't tell anyone about it. Adn to compound the problem, the
trigger didn't work like he thought it would. I guess he didn't test it
thoroughtly. When someone loaded some new data on the backend, the trigger
fired and updated 150,000+ rows that it shouldn't have. We didn't discovered
it until months later and we're trying to figure out how to roll it back.
Thanks for all of your help,
--
Dan D.
"David Lundell" wrote:
> Subqueries are not the issue. It is the way the subqueries were written
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId IN(Select numCompanyId From Inserted)
> And numContactID NOT IN (Select numContactId From Inserted)
> This would also work, although it might not be as efficient as my earlier
> example.
> As for backend loading. I usually disable my triggers for the load and
> reenable them afterwards. But only if I make certain to perform what my
> trigger would perform. In this case setting the bitCompPrimaryContact = 0.
> ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
> Do the load
> ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
>
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...
> > So, is it probably safer to stay away from subqueries in triggers?
> >
> > One of the things we have to be careful about with triggers is that we do
> > a
> > lot of data manipulation on the backend. So we have to be sure that if we
> > insert 5M rows on the backend and not through the app, a trigger doesn't
> > fire
> > and do something it shouldn't.
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> > "David Lundell" wrote:
> >
> >> The trigger they wrote also contained a rookie mistake. It was designed
> >> for
> >> a single row at a time. When you are writing a trigger you always need to
> >> plan on having multi row events occur at some point.
> >>
> >> Snip--
> >> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> >> Where numCompanyId = (Select numCompanyId From Inserted)
> >> And numContactID <> (Select numContactId From Inserted)
> >> End snip--
> >>
> >> See how the subqueries assume that only one item will be returned. This
> >> code
> >> will raise an error if there is more than one row in the Inserted table
> >> (in
> >> other words, if a multi row update or insert occurs) and that would cause
> >> the original transaction to be rolled back.
> >>
> >> Far better would be the following:
> >> ---
> >> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> >> FROM tblCompanyContacts C
> >> JOIN Inserted I
> >> ON I.numCompanyId = C.numCompanyId
> >> And I.numContactID <> C.numContactId
> >> ---
> >> This way no matter how many rows have been affected by the update or
> >> insert
> >> statement the trigger will now handle them.
> >>
> >> All of the advice you received on moving the logic to the stored
> >> procedure
> >> has merit. You should understand the following. The trigger will fire for
> >> any update or insert on your table. Some folks like to use triggers to
> >> handle some of their data integrity enforcement and not worry about in
> >> their
> >> procs. If you do put it in your procs, you will need to put that logic
> >> into
> >> every proc that will update or insert into that table. If this is a large
> >> #
> >> of procs then you are setting up a shotgun surgery approach to
> >> maintenance.
> >> The other thing to be aware of is that your trigger fires as part of your
> >> original transaction, so an error in it will cause your original action
> >> to
> >> be rolled back, as well as the locks from your original action are still
> >> maintained during the trigger. In this case that is not a bad thing,
> >> since
> >> you want this to happen as part of the update, and if it fails, you want
> >> the
> >> update to fail as well.
> >> --
> >> David Lundell
> >> Principal Consultant and Trainer
> >> www.MutuallyBeneficial.com
> >> David@.MutuallyBeneficial.com
> >>
> >>
> >>
>
>|||On Tue, 23 May 2006 06:07:02 -0700, Dan D. wrote:
>So, is it probably safer to stay away from subqueries in triggers?
Hi Dan,
Only in the same way that not using a database at all prevents errors :)
Use subqueries if you need them - just be aware that a trigger will
always fires exactly once when a corresponding INSERT, UPDATE or DELETE
statement is executed. Design your triggers to work regardless of number
of rows affected. And test them with single-row, multi-row and zero-row
operations.
>One of the things we have to be careful about with triggers is that we do a
>lot of data manipulation on the backend. So we have to be sure that if we
>insert 5M rows on the backend and not through the app, a trigger doesn't fire
>and do something it shouldn't.
Bottom line when deciding if something belongs in a stored proc or in a
trigger is: is in integrity related?
Triggers should be used to enforce the integrity rules that can't be
enforced with standard SQL. For instance complicated constraints that
can't be exppressed in SQL constraints. Or computations that (for
whatever reason) can not be calculated in a view. The execution of a
trigger is about as hard to bypass as the checking of a constraint or
the "updating" of a calculated value in a view. That's because it should
be used for code that really should never be bypassed.
Bypassing the execution of stored procedures is much easier. Exactly how
easy depends on yoour security model as well - but there are always SOME
users able to update a table directly without executing the stored proc
that should be used to update the table.
--
Hugo Kornelis, SQL Server MVP

Best practices for SQL data access

Hi,

i am newbie in ASP.net world. i am using 3 tier application architechture for my web based application. data base is sql server 2000. i have looked at object and sql datasource objects but i think they are not suitable for my requirements. so i am planning to directly use ado.net to access data from database.( i.e. creating connection, then creating commands n executing them)

now what i am looking for is the best known practices for the above task. i have following solutions in my mind please let me know if i am missing some or which could be the best aproach.

careate one class which will handle all the database requests so that all the pages and business objects request that class to to do all the db related stuff. (creating connection, command n execution)

have a class which will return connection to your page or business object and then u can use that connection to do db related stuff.

some thing in between that you create a sqlcommand and pass it to a class which will take care of connections and execute you request.

what i am worried about more is the connections to database and the connection pooling n sharing stuff. i dont have any idea how they works.

please help me in this regard

thanks

Have you looked at the Data Access Application Block?

http://msdn2.microsoft.com/en-us/library/aa480458.aspx

|||

thanks i had a look through it, n it sound goods.

any other thoughs or ideas???

|||

aakbar:

thanks i had a look through it, n it sound goods.

any other thoughs or ideas???

Yes, follow those suggestions there...

|||

Outside of that, if was using ADO, I'd create a Business Logic class for each of the tables in my database using this class to generate all of the business logic which in turn would then call the ADO function within the DAL which would be your Data Application Blocks.

Best practice for SQL connections and Asp.Net

Hi.

We have developed as quite simple ASP.Net webpage that fetches a number of information from a SQL 2005 database. We are having some problems though, becuase of a firewall that is beetween the webserver and the SQL server, and I think this is because of bad code from my part. I'm not that experiensed yet, so I'm sure that there is much to learn.

Usualy when I do a query against a SQL database, I do something like this:

Function GO_FormatRecordBy(ByVal intRecordByAs Integer)
Dim dbQueryStringAs String
Dim dbCommandAs OleDbCommand
Dim dbQueryResultAs OleDbDataReader

dbQueryString ="SELECT Name FROM tblRegistrators WHERE tblRegistratorsID ='" & intRecordBy & "'"
dbCommand = New OleDbCommand(dbQueryString, dbConn)

dbConn.Open()

dbQueryResult = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)
dbQueryResult.Read()

dbConn.Close()

dbCommand = Nothing

Return dbQueryResult("Name")

End Function

Now, lets say that I have a DataList that I populate with Integer values, and I want to "resolve" the from another table, then i do a function like the one above. I guess that this means that I open and close quite alot of connections against the database server when I have a large tabel. Is there any better way of doing this? Chould one open a database connection globaly in lets say the ASA fil? Whould that be a better aproch?

When I added the CommandBehavior.CloseConnection to the ExecuteReader statment, I noticed that it was a bit faster, and I think there was fewer connections in the database, so maby there is more to the "closing connections" then I usualy do.

Any tips on this?

Best reagrds,
Johan Christensson

The thing with DataReaders is that you have to explicitly close them when you have finished with them. The CommandBehaviour.CloseConnection option makes sure that the connection is closed when you close the DataReader.

Since you are only retrieving one value in the example you have shown, you should use ExecuteScalar(), which doesn't need a DataReader. That would be the most efficient way of accomplishing this particular task.

Also, in answer to your question about global connections, forget you ever thought about it. That is a very poor idea. It forces everyone who accesses your app to use the same connection, and as traffic increases, delays occur as requests are queued.

I don't quite understand what you mean when you talk about resolving records from another table, but my instinct is that nested datalists or join queries might be a better way to go.

|||

Thanks for your awnser.

What I mean with "resolving records fro another tabel" is that lets say that I have one table that consists, in this case of a SiteID and a TechnicanID, when the DataList is generated, I invoke a function where the output in the DataList is looked up in another table and resolved as a name. I guess that this is quite stupid, since I guess that it would generate an extra query, in this case two querys for each row in the datalist. Right now I have a datalist/asp page that generated about 400 connections to the database, and because of this, the firewall blocks the webserver.

I guess that this would better be done using some kind of SQL statement, but I'm not sure on how. Lets say that I have the following SQL string:

dbQueryString ="SELECT SiteID, TechnicanID FROM tblRegistrators WHERE tblRegistratorsID ='" & intRecordBy & "'"

And I want the values from SiteID and TechnicanID to be the result from a query in another table, how do I do this?

Best reagrds,
Johan Christensson

|||

I think this will work for you:

dbQueryString = "Select tblSites.SiteName, tblTechnicians.TechnicianName from tblRegistrators inner join tblSites on tblRegistrators.SiteID = tblSites.SiteID inner join tblTechnicians on tblRegistrators.TechnicianID = tblTechnicians.TechnicianID where tblRegistrators.ID = '"&intRecordBy&"'"

you may want to look up some information on Joins (Inner and Outer primarily) they make life much simpler, and more complicated at the same time. Trying to sort out queries with upwards of 20 joins is one of the reasons I have so much grey hair

Hope this helps

-madrak

|||

Madrak has given you a solution using Joins in your SQL as I suggested earlier, but another approach could be to select all from the SiteID and TechnicianID tables into separate DataSets on one connection. Once the dataset has been populated, you can close the connection and work with it in a disconnected fashion, looping though it to your heart's content.

You may well have trouble getting to grips with Joins to start with, so I suggest that you make use of the Query Designer in SQL Management Studio (Express) to write the query for you. Simply add the 3 tables to the designer, then drag foreign keys onto primary keys to create relationships eg SiteID in tblRegistrators would be a foreign key and should be dragged to SiteID (primary key) in what I presume might be called tblSites. As soon as you do that, you will see that the textual part of the query builds the default inner join into itself.

But in essence, your instinct is correct. Hammering a database repeatedly in a loop is poor practice - especially if you aren't closing DataReaders correctly

|||

After some googeling I figured out that I should do some kind of nested statment, but the anwser from Madrak actualy was in a format that I understod it. :) Not only does it work, is fast as lightning. It took som trial and error in teh Query Designer before I got hold of it. The way I have done this previusly, always was very slow, so I have some tweeking to do i guess on some other webpages.

So thanks a milion.

A followup question on Mikesdotnetting awnser:

What is best here? To do the complete table using nested SQL statements or do it the DataSet way? I guess that would depend on the load on the webservers verses the SQL server, but is we ignore that right now, and say that you have a some application that you run on you computer. What way would you go then?

Thanks again.

Best regards,
Johan Christensson

|||

JohanCh:

A followup question on Mikesdotnetting awnser:

What is best here? To do the complete table using nested SQL statements or do it the DataSet way? I guess that would depend on the load on the webservers verses the SQL server, but is we ignore that right now, and say that you have a some application that you run on you computer. What way would you go then?

Oh, without a doubt I would use Joins in my SQL. I'm a firm believer in making as few requests of the database as possible. The Join approach requires just one request. DataSets require 3. You might think 3 is nothing compared to the 35,000 you were making (), but I look at it as 300% more than I need. And it's a lot of code less too.

Best method of doing Connection Strings

I am using SQL 2000 sp3a on Windows 2000 sp3. I have developed an Intranet application using asp.net/vb.net. Currently my connection string is:

data source=intraweb1;initial catalog=ASGWEB;password=blahblah;persist security info=True;user id=justauser;packet size=4096

So all my users are coming in with one SQL database id. Is this the best method for a combination of security and performance?

I do not allow anonymous to the website so I was thinking of setting up an application role and putting the domain users account in it. But from some other threads I was reading this does not work well with connection pooling.> Is this the best method for a combination of security and performance?

yeah, that's fine. I hardly ever do it otherwise - it's not fine-grained security-wise, but do you need it to be?

as for the connection pooling thing, yup - connection polling makes a pollfor the user id, so with multiple users you'd probably lose the beneficial effects, besides needing more CALs|||::besides needing more CALs

Using onedb server is does NOT save you CAL's. Read the licensing condition. You still need one CAL for every user. They say user - NOT user id. This is actually extremely clear, especially in the descriptions and comments.|||I had a discussion about this recently, and the concensus seemed to be one Device Access license for IIS to grab data if you're using one user ID. licencing is a nightmare though, and don't claim to be an expert on it by any means. I usually just ask MS whet the deal is and get multiple answers (!)

Best license program

I have a small company and i make online ERP software in ASP.NET for small
companies. Companies with one server en a maximum of 10 concurrent users.
What SQL server license should i advice for these companies? And how much
does it costs?
I've tried to understand the documents on the microsoft site but i did not
find an satisfying answer.
Thanks in advance.
RonaldIf your database won't be bigger than 2 GB, you can use MSDE which is free.
Otherwise the best is to use Standard Edition with per seat licensing if
your client don't have more than 25 users (not concurrent users, you need a
license for everyone who uses the server), or Standard Edition with a
processor license if there are more than 25 users.
--
Jacco Schalkwijk
SQL Server MVP
"Sandeman" <ilighters@.zeelandnet.nl> wrote in message
news:OAiA2bYlEHA.3452@.TK2MSFTNGP15.phx.gbl...
>I have a small company and i make online ERP software in ASP.NET for small
> companies. Companies with one server en a maximum of 10 concurrent users.
> What SQL server license should i advice for these companies? And how much
> does it costs?
> I've tried to understand the documents on the microsoft site but i did not
> find an satisfying answer.
> Thanks in advance.
> Ronald
>

Best license program

I have a small company and i make online ERP software in ASP.NET for small
companies. Companies with one server en a maximum of 10 concurrent users.
What SQL server license should i advice for these companies? And how much
does it costs?
I've tried to understand the documents on the microsoft site but i did not
find an satisfying answer.
Thanks in advance.
Ronald
If your database won't be bigger than 2 GB, you can use MSDE which is free.
Otherwise the best is to use Standard Edition with per seat licensing if
your client don't have more than 25 users (not concurrent users, you need a
license for everyone who uses the server), or Standard Edition with a
processor license if there are more than 25 users.
Jacco Schalkwijk
SQL Server MVP
"Sandeman" <ilighters@.zeelandnet.nl> wrote in message
news:OAiA2bYlEHA.3452@.TK2MSFTNGP15.phx.gbl...
>I have a small company and i make online ERP software in ASP.NET for small
> companies. Companies with one server en a maximum of 10 concurrent users.
> What SQL server license should i advice for these companies? And how much
> does it costs?
> I've tried to understand the documents on the microsoft site but i did not
> find an satisfying answer.
> Thanks in advance.
> Ronald
>

Best Development Machine Configuration Recommendations

What is the best / recommended setup for a development system where you want
to develop using .NET 2.0 using VS 2005 against both SSRS 2000 and SSRS
2005?VS 2005 does not create reports that are compatible with SRS 2000. However,
the SRS 2000 report designer works against both SRS 2000 and 2005.
Thanks
Tudor
"Jim" wrote:
> What is the best / recommended setup for a development system where you want
> to develop using .NET 2.0 using VS 2005 against both SSRS 2000 and SSRS
> 2005?
>
>|||But, and this is important, those reports cannot provide for any of the 2005
features like end user sorting, multi-valued parameters etc.
I suggest installing both development environments. To do this you need VS
2003 and VS 2005. They will install side by side and both be usable (that is
what I have done).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
wrote in message news:BEF6807F-D848-4C1E-AC6A-32706D9B8F33@.microsoft.com...
> VS 2005 does not create reports that are compatible with SRS 2000.
> However,
> the SRS 2000 report designer works against both SRS 2000 and 2005.
> Thanks
> Tudor
> "Jim" wrote:
>> What is the best / recommended setup for a development system where you
>> want
>> to develop using .NET 2.0 using VS 2005 against both SSRS 2000 and SSRS
>> 2005?
>>

benefits of SQL Server 2000

Could someone hit the high points of the benefits of using SQL Server (vs. other databases) with Visual Studio .NET and the .NET Framework.

ThanksThe biggest benefit is that Sql Server and MSDE have the greatest degree of integration with VS.Net and the .Net Framework. Having MSDE available is also a big plus because it makes it easy to deploy small scale applications using essentially the same database as Sql Server.

But the other major database products will work just fine with VS.Net.|||I don't mean to sound lazy, but could someone give me specifics such as examples of integration with VS.NET and functionality accomplished using SQL Server with .NET Framework. I'm trying to sell these products and was hoping to get out of doing all of the background research. I've used these products but I would like other people's opinions.

Thanks|||Wouldn't you be better able to sell them if you did the research yourself and had a better handle on the product?|||I'm trying to "sell" them as a platform to my company and would like examples that can be backed up by others, not just my own opinions.|||You might consider using come of the Case Studies as part of your research.

Microsoft Case Studies

Terri

beginning SQL

Hello, I would like to be able to construct a small SQL data base with Visual Basic.Net code for practice. I want to be able to add to the data base, subtract from the data base, perform all Sql procedures ( Select, delete, etc.) I would also like to include the ExecuteNonQuery, ExecuteScalar, and ExecuteReader methods. Can anybody out there recommend a good tutorial for me? Thank you so much. MarcAbelson_2000@.yahoo.com

These resources should be useful for your learning:

http://www.asp.net/learn/

http://www.asp.net/learn/data-access/

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx

http://msdn.microsoft.com/vstudio/express/sql/starterkit/default.aspx

Beginner Question - SQL Server

Hi, I am currently learning ASP.net 1.1 using the apress Beginning
ASP.NET 1.1 E-Commerce book.
I am working my way through but have hit a problem on chapter 3.
When I try to start the application i get the following error:
Server Error in '/JokePoint' Application.
Login failed for user 'IND055000013\ASPNET'
In the book it says that this is down to SQL Server Security and that I
need to enabled mixed mode authentication. I did this by editting the
regedit, however, the error still persists.
Any help would be much appreciated.
Thanks
Did you restarted the service ?
HTH, jens Suessmeyer.
|||yes, the error message in full is
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for
user 'IND055000013\ASPNET'.
Source Error:
Line 9: command.CommandType = CommandType.StoredProcedure
Line 10: ' Open the connection
Line 11: connection.Open()
Line 12: 'Return a SqlDataReader to the calling function
Line 13: Return
command.ExecuteReader(CommandBehavior.CloseConnect ion)
Source File: F:\MyCommerceSite\JokePoint\BusinessObjects\Catalo g.vb
Line: 11
Stack Trace:
[SqlException: Login failed for user 'IND055000013\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection (Boolean&
isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString
options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
JokePoint.Catalog.GetDepartments() in
F:\MyCommerceSite\JokePoint\BusinessObjects\Catalo g.vb:11
JokePoint.DepartmentsList.Page_Load(Object sender, EventArgs e) in
F:\MyCommerceSite\JokePoint\UserControls\Departmen tsList.ascx.vb:33
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Page.ProcessRequestMain() +750
Again any help would be very appreciated
|||Look in the BOl for more information:
"Allows a Microsoft=AE Windows NT=AE user or group account to connect to
Microsoft SQL Server=99 using Windows Authentication.
Syntax
sp_grantlogin [@.loginame =3D] 'login'"
--to give him permissions to access the server
"sp_grantdbaccess
Adds a security account in the current database for a Microsoft=AE SQL
Server=99 login or Microsoft Windows NT=AE user or group, and enables it
to be granted permissions to perform activities in the database.
Syntax
sp_grantdbaccess [@.loginame =3D] 'login'
[,[@.name_in_db =3D] 'name_in_db' [OUTPUT]]"
--to grant access in the database
"GRANT
Creates an entry in the security system that allows a user in the
current database to work with data in the current database or execute
specific Transact-SQL statements."
--to grant him specific privilegs e.g. Select on a table.
"sp_addrolemember
Adds a security account as a member of an existing Microsoft=AE SQL
Server=99 database role in the current database."
--to add him to a predefined role with specific permissions, e.g.
dbowner
sp_addrolemember 'db_owner','Domain\User'
Based ont he assumption that you have SQL Server MSDE or Express
installed, otherwise you could use a graphical interface for
configuring this.
HTH, jens Suessmeyer.

Beginner Question - SQL Server

Hi, I am currently learning ASP.net 1.1 using the apress Beginning
ASP.NET 1.1 E-Commerce book.
I am working my way through but have hit a problem on chapter 3.
When I try to start the application i get the following error:
Server Error in '/JokePoint' Application.
----
--
Login failed for user 'IND055000013\ASPNET'
In the book it says that this is down to SQL Server Security and that I
need to enabled mixed mode authentication. I did this by editting the
regedit, however, the error still persists.
Any help would be much appreciated.
ThanksDid you restarted the service ?
HTH, jens Suessmeyer.|||yes, the error message in full is
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for
user 'IND055000013\ASPNET'.
Source Error:
Line 9: command.CommandType = CommandType.StoredProcedure
Line 10: ' Open the connection
Line 11: connection.Open()
Line 12: 'Return a SqlDataReader to the calling function
Line 13: Return
command.ExecuteReader(CommandBehavior.CloseConnection)
Source File: F:\MyCommerceSite\JokePoint\BusinessObje
cts\Catalog.vb
Line: 11
Stack Trace:
[SqlException: Login failed for user 'IND055000013\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString
options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
JokePoint.Catalog.GetDepartments() in
F:\MyCommerceSite\JokePoint\BusinessObje
cts\Catalog.vb:11
JokePoint.DepartmentsList.Page_Load(Object sender, EventArgs e) in
F:\MyCommerceSite\JokePoint\UserControls
\DepartmentsList.ascx.vb:33
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Page.ProcessRequestMain() +750
Again any help would be very appreciated|||Look in the BOl for more information:
"Allows a Microsoft=AE Windows NT=AE user or group account to connect to
Microsoft SQL Server=99 using Windows Authentication.
Syntax
sp_grantlogin [@.loginame =3D] 'login'"
--to give him permissions to access the server
"sp_grantdbaccess
Adds a security account in the current database for a Microsoft=AE SQL
Server=99 login or Microsoft Windows NT=AE user or group, and enables it
to be granted permissions to perform activities in the database.
Syntax
sp_grantdbaccess [@.loginame =3D] 'login'
[,[@.name_in_db =3D] 'name_in_db' [OUTPUT]]"
--to grant access in the database
"GRANT
Creates an entry in the security system that allows a user in the
current database to work with data in the current database or execute
specific Transact-SQL statements."
--to grant him specific privilegs e.g. Select on a table.
"sp_addrolemember
Adds a security account as a member of an existing Microsoft=AE SQL
Server=99 database role in the current database."
--to add him to a predefined role with specific permissions, e.g.
dbowner
sp_addrolemember 'db_owner','Domain\User'
Based ont he assumption that you have SQL Server MSDE or Express
installed, otherwise you could use a graphical interface for
configuring this.
HTH, jens Suessmeyer.

Beginner Question - SQL Server

Hi, I am currently learning ASP.net 1.1 using the apress Beginning
ASP.NET 1.1 E-Commerce book.
I am working my way through but have hit a problem on chapter 3.
When I try to start the application i get the following error:
Server Error in '/JokePoint' Application.
----
Login failed for user 'IND055000013\ASPNET'
In the book it says that this is down to SQL Server Security and that I
need to enabled mixed mode authentication. I did this by editting the
regedit, however, the error still persists.
Any help would be much appreciated.
ThanksDid you restarted the service ?
HTH, jens Suessmeyer.|||yes, the error message in full is
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for
user 'IND055000013\ASPNET'.
Source Error:
Line 9: command.CommandType = CommandType.StoredProcedure
Line 10: ' Open the connection
Line 11: connection.Open()
Line 12: 'Return a SqlDataReader to the calling function
Line 13: Return
command.ExecuteReader(CommandBehavior.CloseConnection)
Source File: F:\MyCommerceSite\JokePoint\BusinessObjects\Catalog.vb
Line: 11
Stack Trace:
[SqlException: Login failed for user 'IND055000013\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString
options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
JokePoint.Catalog.GetDepartments() in
F:\MyCommerceSite\JokePoint\BusinessObjects\Catalog.vb:11
JokePoint.DepartmentsList.Page_Load(Object sender, EventArgs e) in
F:\MyCommerceSite\JokePoint\UserControls\DepartmentsList.ascx.vb:33
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Page.ProcessRequestMain() +750
Again any help would be very appreciated|||Look in the BOl for more information:
"Allows a Microsoft=AE Windows NT=AE user or group account to connect to
Microsoft SQL Server=99 using Windows Authentication.
Syntax
sp_grantlogin [@.loginame =3D] 'login'"
--to give him permissions to access the server
"sp_grantdbaccess
Adds a security account in the current database for a Microsoft=AE SQL
Server=99 login or Microsoft Windows NT=AE user or group, and enables it
to be granted permissions to perform activities in the database.
Syntax
sp_grantdbaccess [@.loginame =3D] 'login'
[,[@.name_in_db =3D] 'name_in_db' [OUTPUT]]"
--to grant access in the database
"GRANT
Creates an entry in the security system that allows a user in the
current database to work with data in the current database or execute
specific Transact-SQL statements."
--to grant him specific privilegs e.g. Select on a table.
"sp_addrolemember
Adds a security account as a member of an existing Microsoft=AE SQL
Server=99 database role in the current database."
--to add him to a predefined role with specific permissions, e.g.
dbowner
sp_addrolemember 'db_owner','Domain\User'
Based ont he assumption that you have SQL Server MSDE or Express
installed, otherwise you could use a graphical interface for
configuring this.
HTH, jens Suessmeyer.

Beginner needs help installing MSDE

Hi, I am trying to teach myself ASP.NET from the SAMS 24 Hrs book and I am trying to install MSDE for use with the web matrix project.

I think I succesfully installed it the first time I tried but entered a four digit SAPWD password not knowing it needed to be strong. I clicked on the data tab in the web matrix project as instructed to open the database and entered my user and password but it gave me some error message.

Now when I click the data tab it say I need to install MSDE on my computer and links me to www.asp.net.

Then when I try to install MSDE after it has downloaded, it gives me this error message. -- 'Setup failed to configure the server. Refer to the server error logs and setup error logs for more information.'--

I have tried uninstalling and reinstalling everything as well as everything else I can think of.

I would be forever grateful if someone could help me out with this.

Many Thanks

Mark
E-Mail: poolstar_uk@.hotmail.comTry this link. It might help.

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B829386

Goodluck|||Many Thanks for the advice but I am still having no luck with it.

Any other ideas? I'm not sure my pc will live to see many more days!|||Are you using the Web Matrix server or The IIS ?|||I appreciate your replies, thanks.

I am using the web matrix server.

I have tried installing IIS but it is asking for my windows disk and I cannot find it. I've just moved house and there are boxes and bags everywhere.

Do you think I could use IIS if I could find the disk? Or do you have any ideas on using the web matrix project?|||Follow this tutorial. It's from this website.

http://www.asp.net/webmatrix/guidedtour/Appendices/installMsde.aspx

Perfect example and exact step-by-step of what you need to do. Also, please post what your error logs state if this still doesn't work. You can specify where your logs dump to when you install MSDE from the command line.

Hope this helps. Good luck.|||Thanks everyone for your help, problem solved.

Many Thanks|||Hi poolstar, actually I have the same problem as yours, can u tell me how u solved the problem, thanks a lot man. can u email me this through this email jie_jeep@.yahoo.com|||I am having the same problem but the tutorial did not help.

How do you specify where the log dumps to?

I am using an XP machine networked to another XP machine for internet purposes. My machine is the slave and does not have its own modem. I normally turn off the connection unless I want to be hooked to the internet.

The instructions I had read that time said to install with sapwd=matrixsa instancename=matrixsa but did not add securitymode=sql so I tried the install without it (with the lan connection active). MSDE did install but then had no red x or green arrow in the white circle on the icon in the system tray. When I clicked on the icon, it was looking for the master machine and there were no possible selections in the dropdown box.

I uninstalled, restarted and tried it again with the securitymode and began to get the error again saying that setup failed to configure the server. I have tried three more times, restarting each time, and consistently get this error. All of the successive times, the lan connection has been off.

Any suggestions? Could this be caused by the lack of a modem in this machine and/or having the lan connection active?

Thanks,
Beth

beginner in MSDE

Hello
Two questions:
I have installed the MSDE included on the Visual Studio NET, I use the tools in Visual to create data bases, but what other interface can I use to create MSDE data bases if I don't have SQL Server neither access ?
I create a view in a database with the following command:
SELECT ind_emp, lastName + firstName AS name FROM employee
when I try to run the view I received a message that says "operator ADD is not valid to use with texts champs" Why is that?
Thanks a lot
hi Carmen,
"Carmen" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:D15E830D-7C49-46D8-8F27-E737000A399E@.microsoft.com...
> Hello
> Two questions:
> I have installed the MSDE included on the Visual Studio NET, I use the
tools in Visual to create data bases, but what other interface can I use to
create MSDE data bases if I don't have SQL Server neither access ?
> I create a view in a database with the following command:
> SELECT ind_emp, lastName + firstName AS name FROM employee
> when I try to run the view I received a message that says "operator ADD is
not valid to use with texts champs" Why is that?
> Thanks a lot
>
in order to manage MSDE instance in a visual way, you can have a look at a
free prj of mine, available at the link following my sign.m which provides a
user interface similar to Enterprise Manager..
further tools are listed at
http://www.microsoft.com/sql/msde/partners/default.asp and
http://www.aspfaq.com/show.asp?id=2442
as regard your second question, you can not concatenate text and blob
columns like that..
anyway, you probably have to change the datatype for your lastName +
firstName columns to VARCHAR datatype..
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Beginner help stored procedures, also .net

Hi,
I'm not qualified enough to be called a beginner -- I could use some help.
I've written a few fairly simple queries and run them through the query
analyzer. I'd like to know 2 things:
How can I turn them into stored procedures?
How would I then run those stored procedures through vb.net?
Any help would be appreciated.
Thanks,
ArtArt
CREATE PROCEDURE myFirstSp
@.par INT
AS
SELECT OrderDate FROM Orders WHERE OrderId=@.par
--usage
EXEC myFirstSp @.par=10248
You will find more info about stored procedures in the BOL.
"Art" <Art@.discussions.microsoft.com> wrote in message
news:DEF405CC-9E63-4D9A-8405-A281CC991ABD@.microsoft.com...
> Hi,
> I'm not qualified enough to be called a beginner -- I could use some help.
> I've written a few fairly simple queries and run them through the query
> analyzer. I'd like to know 2 things:
> How can I turn them into stored procedures?
> How would I then run those stored procedures through vb.net?
> Any help would be appreciated.
> Thanks,
> Art|||You can find samples and tutorials on MSDN.com and ASP.net
"Art" <Art@.discussions.microsoft.com> wrote in message
news:DEF405CC-9E63-4D9A-8405-A281CC991ABD@.microsoft.com...
> Hi,
> I'm not qualified enough to be called a beginner -- I could use some help.
> I've written a few fairly simple queries and run them through the query
> analyzer. I'd like to know 2 things:
> How can I turn them into stored procedures?
> How would I then run those stored procedures through vb.net?
> Any help would be appreciated.
> Thanks,
> Art

begin transaction in a sp or not

Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
doesn't handle transactions at all. We'll be moving to SQL 2005 soon but don
t
know when. I know I can use Try...Catch in SQL 2005 to do transactions or
actually handle it form the application.
My question is, should I use 'BEGIN TRANSACTION' in a sp or it automatically
runs as a transaction? Thank youIf you want transactional control, then you have to include all data
modification operations inside BEGIN TRAN and COMMIT TRAN. The stored
procedure by itself will not run as a transaction. You have to check for
errors after each data modification operation and decide wheter to continue
or rollback transaction. @.@.TRANCOUNT also comes in handy while handling
transactions. See SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||Try catch has nothing to do with if a transaction is used or not.Whether you
wrap your sp code in a transaction or not depends on what you are doing with
it. If it only does a single insert, update or delete then there is little
point to it. If you modify multiple tables and need them to be
transactionally consistant that is a different story. You might want to have
a looka t this:
http://www.sommarskog.se/error-handling-I.html
Andrew J. Kelly SQL MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||First, TRY...CATCH, in itself, has nothing to do with a TRANSACTION. In SQL
2005, TRY...CATCH allows efficient code management to determine if parts of
the TRANSACTION succeed or fail. And you can use a TRANSACTION in SQL 2000.
You are best served by limiting TRANSACTION to stored procedures ONLY when
transaction control is required for the business needs.
Not every action requires a TRANSACTION. A 'set' of actions (several
INSERT/UPDATE/DELETE statements) that need to be 'all or nothing' should be
explicitly designated to execute in the context of a TRANSACTION.
You may wish to read more about TRANSACTIONs in Books on Line.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you
Tranactions scoping is part of the busness logic of the applciation, and
should be implemented wherever the business logic lives. Limiting
transactions to inside stored procedures doesn't work if the application
needs to compose multiple stored procedure invocations into a single atomic
business operation.
David|||To add to the other responses, I suggest you specify SET XACT_ABORT ON when
the application is oblivious to explicit transactions in stored procedures.
This will automatically rollback the transaction and abort the batch in most
cases and avoid problems related to an open transaction following a command
timeout.
Hope this helps.
Dan Guzman
SQL Server MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you

begin transaction in a sp or not

Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
doesn't handle transactions at all. We'll be moving to SQL 2005 soon but dont
know when. I know I can use Try...Catch in SQL 2005 to do transactions or
actually handle it form the application.
My question is, should I use 'BEGIN TRANSACTION' in a sp or it automatically
runs as a transaction? Thank youIf you want transactional control, then you have to include all data
modification operations inside BEGIN TRAN and COMMIT TRAN. The stored
procedure by itself will not run as a transaction. You have to check for
errors after each data modification operation and decide wheter to continue
or rollback transaction. @.@.TRANCOUNT also comes in handy while handling
transactions. See SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||Try catch has nothing to do with if a transaction is used or not.Whether you
wrap your sp code in a transaction or not depends on what you are doing with
it. If it only does a single insert, update or delete then there is little
point to it. If you modify multiple tables and need them to be
transactionally consistant that is a different story. You might want to have
a looka t this:
http://www.sommarskog.se/error-handling-I.html
Andrew J. Kelly SQL MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||First, TRY...CATCH, in itself, has nothing to do with a TRANSACTION. In SQL
2005, TRY...CATCH allows efficient code management to determine if parts of
the TRANSACTION succeed or fail. And you can use a TRANSACTION in SQL 2000.
You are best served by limiting TRANSACTION to stored procedures ONLY when
transaction control is required for the business needs.
Not every action requires a TRANSACTION. A 'set' of actions (several
INSERT/UPDATE/DELETE statements) that need to be 'all or nothing' should be
explicitly designated to execute in the context of a TRANSACTION.
You may wish to read more about TRANSACTIONs in Books on Line.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you
Tranactions scoping is part of the busness logic of the applciation, and
should be implemented wherever the business logic lives. Limiting
transactions to inside stored procedures doesn't work if the application
needs to compose multiple stored procedure invocations into a single atomic
business operation.
David|||To add to the other responses, I suggest you specify SET XACT_ABORT ON when
the application is oblivious to explicit transactions in stored procedures.
This will automatically rollback the transaction and abort the batch in most
cases and avoid problems related to an open transaction following a command
timeout.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you

BEGIN TRANSACTION

have an application (c# net 1.1) that attaches to a MSDE 2000(fully patched)
machine the application runs for some time, but eventually enters a
begintransaction. it does not error, but it does not return from the call
either it just hangs.
have connected from another machine with enterprise manager and looked for
blocking processes etc but there are none.
It might run for hours, or a few minutes before the hang occurs
basically
write a log event
Begin transaction
write a log event.
all the code is in a try catch block, with logging, almost every line in the
procedure is now wrapped in a write a log event. it is hanging on the begin
transaction system, the connection is good.
hang in this case means forever, have to kill the application.
There are no other transactions running, and enterprise manager from a
remote machine can access the data when the application is hung.
Need some ideas as to what else to look for.
hi,
JR wrote:
> have an application (c# net 1.1) that attaches to a MSDE 2000(fully
> patched) machine the application runs for some time, but eventually
> enters a begintransaction. it does not error, but it does not return
> from the call either it just hangs.
> have connected from another machine with enterprise manager and
> looked for blocking processes etc but there are none.
> It might run for hours, or a few minutes before the hang occurs
> basically
> write a log event
> Begin transaction
> write a log event.
> all the code is in a try catch block, with logging, almost every line
> in the procedure is now wrapped in a write a log event. it is hanging
> on the begin transaction system, the connection is good.
> hang in this case means forever, have to kill the application.
> There are no other transactions running, and enterprise manager from a
> remote machine can access the data when the application is hung.
> Need some ideas as to what else to look for.
try having a look at
http://msdn.microsoft.com/library/de..._dbcc_5fhq.asp
if you can find some info about eventual pending transactions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea for your response
I have run DBCC OPENTRAN and no results are returned
Well actually the text "No active open transactions"
the call to begintransaction just locks and never returns
I really don't see anything blocking the begintransaction from working
Thanks
JR
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:47b3h7FemenlU1@.individual.net...
> hi,
> JR wrote:
> try having a look at
> http://msdn.microsoft.com/library/de..._dbcc_5fhq.asp
> if you can find some info about eventual pending transactions..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
JR wrote:
> Thanks Andrea for your response
> I have run DBCC OPENTRAN and no results are returned
> Well actually the text "No active open transactions"
> the call to begintransaction just locks and never returns
> I really don't see anything blocking the begintransaction from working
yep, it just seems non transaction at all has been opend and your
application just gets stuck somewhere..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||yep, stuck in the .begintransaction so now what ?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:47d6h2FerknmU1@.individual.net...
> hi,
> JR wrote:
> yep, it just seems non transaction at all has been opend and your
> application just gets stuck somewhere..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
JR wrote:
> yep, stuck in the .begintransaction so now what ?
perhaps in .net hierarchy in the language syntax..
if you can perform the included T-SQL command text without problem, I can
suppose the problem is elsewhere :-(
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||You might want to turn on profiler to see if the begin transaction statement
is received and completes in the database.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:47dj1jFeh0ueU1@.individual.net...
> hi,
> JR wrote:
> perhaps in .net hierarchy in the language syntax..
> if you can perform the included T-SQL command text without problem, I can
> suppose the problem is elsewhere :-(
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Thanks Roger
this is a good idea,
in the interim, have commented out the transaction altogether and
the code is running perfectly when it is not in a transaction.
Soon as the code uses a transaction it runs for a while and then hangs.
JR
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:ewr$5IGRGHA.5108@.TK2MSFTNGP09.phx.gbl...
> You might want to turn on profiler to see if the begin transaction
> statement is received and completes in the database.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:47dj1jFeh0ueU1@.individual.net...
>