What would be the best Recovery Model for: a database which is 4 gig in size and imports via MSAccess queries and also stored procedures approximately 400,000 meg of data each month (and some other update queries are run against it) and it is also queried off of for totals on weekly basis?
The problem is that the SQL Server box only has 512 meg of memory and the tranlog on this database grows tremendously each import and when update queries are run against it. This tends to slow things down a bit on our other databases. We are getting a new SQL Server box but until then, what would be the best recovery model? I currently have it as Bulk-Logged and allow the tranlog to grow by 10% (with a base of 250 meg). The tranlog grows to up to 5-10 gig and in order to shrink it, I have to change the recovery model to Simple, and then back to Bulk-Logged in order to shrink it (I've tried all the dbcc shrinkdatabase, dbcc shrinkfile, dbcc showcontig, and dbcc checkdb commands as well as BACKUP LOG dbName WITH TRUNCACTE_ONLY and nothing will shrink it unless I change the recovery model to simple.)Perhaps you could turn off transaction logging during bulk data loads.|||Thanks for the repy blindman,
I tried that and it slowed down the importing to a crawl (1-2 hours verses 15 minutes). I know I'm pushing the limits on the SQL Server box.
Showing posts with label via. Show all posts
Showing posts with label via. Show all posts
Sunday, March 25, 2012
Best Protocol?
What is the best protocol (Named Pipes, TCP/IP or VIA) to use when connecting to a SQL Server 2005 on:
a) LAN (100MBps+)
b) VPN (via Internet)
c) Internet
And why?
The answer you're looking for is here.http://msdn2.microsoft.com/en-us/library/ms187892.aspx|||
I do not believe the article about choosing a connection type to the server answers the question.
In my opinion it is ovbious that the LAN is the best just because of the TCP/IP connection method being able to take advantage of low overhead, hi bandwidth connections.
VPN is guaranteed to be slower than the LAN because the protocol is sitting on top of TCP/IP and will inherently add more overhead.
As far as Internet goes, I am not sure what the difference b/w LAN and Internet is going to be as far as which protocol to use.
Hope this helps.
|||oj (MVP): Your comment was very helpfull, thank you. I had trouble finding this article myself.
Leifton: Yours was not so.
From what I understand, this is my conclusion (in a very cut-down nutshell).
VIA works best with VIA hardware, enough said.
Named Pipes works best over normal LAN.
TCP/IP works best over VPN and Internet.
Best Protocol?
What is the best protocol (Named Pipes, TCP/IP or VIA) to use when connecting to a SQL Server 2005 on:
a) LAN (100MBps+)
b) VPN (via Internet)
c) Internet
And why?
The answer you're looking for is here.http://msdn2.microsoft.com/en-us/library/ms187892.aspx|||
I do not believe the article about choosing a connection type to the server answers the question.
In my opinion it is ovbious that the LAN is the best just because of the TCP/IP connection method being able to take advantage of low overhead, hi bandwidth connections.
VPN is guaranteed to be slower than the LAN because the protocol is sitting on top of TCP/IP and will inherently add more overhead.
As far as Internet goes, I am not sure what the difference b/w LAN and Internet is going to be as far as which protocol to use.
Hope this helps.
|||oj (MVP): Your comment was very helpfull, thank you. I had trouble finding this article myself.
Leifton: Yours was not so.
From what I understand, this is my conclusion (in a very cut-down nutshell).
VIA works best with VIA hardware, enough said.
Named Pipes works best over normal LAN.
TCP/IP works best over VPN and Internet.
Thursday, March 22, 2012
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...
>
>
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
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
Friday, February 24, 2012
Best approach with DTS
Let me see if I can explain this.
I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB.
Step 1.
The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working.
Step 2
The next tables data needs to be limited from the data retrieved in step 1 (Id like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side.
Step 3
The returned rows here, need to be limited to key values returned from step 2
Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step.
What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step.
I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc.
What is the best approach?I think DTS is better in this regard.
You need to workout to re-arrange data based upon the requirement.
Once data is imported you can contro updations from SQL side using normal TSQL.|||I think I'm going to continue to limit the selection on the db2 side based on sub queries. Initially set it up to drop and create the tables each time, and after that's all done, modify to import into temp tables from dts and then use sql to update the existing tables from the temp tables, I think this is the approach I'm going to take.
I'm open to ideas for alternatives
I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB.
Step 1.
The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working.
Step 2
The next tables data needs to be limited from the data retrieved in step 1 (Id like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side.
Step 3
The returned rows here, need to be limited to key values returned from step 2
Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step.
What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step.
I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc.
What is the best approach?I think DTS is better in this regard.
You need to workout to re-arrange data based upon the requirement.
Once data is imported you can contro updations from SQL side using normal TSQL.|||I think I'm going to continue to limit the selection on the db2 side based on sub queries. Initially set it up to drop and create the tables each time, and after that's all done, modify to import into temp tables from dts and then use sql to update the existing tables from the temp tables, I think this is the approach I'm going to take.
I'm open to ideas for alternatives
Best approach SQL Reporting Authentication - WorkGroup
Hi there
Situration
1.Development Server is on WorkGroup, not main Domain. (win 2003
server(std))
2.Accessing dev server via \\Main Domain\Administrator
3.SQL 2K (Std)
SQL Reporting
1.Using SQL authentication for shared resource, rather win
authentication.
2.Shared folder, access set to everyone.
Current Situration
1.Have created 5 reports, each has a url within Domino Notes
application. Click on anyone of them (assuming
\\Main Domain\Administrator\) --> reports work ok.
2.Log-on as non-administrator --> click on url, enter site ok, but
popup appears asking for authentication of user
e.g. name, pwd & domain.
Constraints
1.Development Box cannot be joined to main domain i.e. Non-Wins
authentication
2.Host name e.g. http:\\wtdev01\reportserver --> dealt with WINS
server, not host file !
Requirement
Any user clicks on URL for the report, will open the report and be run
without authentication issues.
Tried
1.Custom Authentication (e.g. Form Authentication) - issue cannot use
Standard version of SQL Server ! Needs to be Enterprise. Dead end.
Any ideas.This is where Anonymous access may do the trick for you if you don't need RS
to differentiate the users. If you do, then one of your constraints needs to
go away.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"ImraneA" <i.akram@.weir.co.uk> wrote in message
news:24b9d612.0411010439.7f2e15d4@.posting.google.com...
> Hi there
> Situration
> 1.Development Server is on WorkGroup, not main Domain. (win 2003
> server(std))
> 2.Accessing dev server via \\Main Domain\Administrator
> 3.SQL 2K (Std)
> SQL Reporting
> 1.Using SQL authentication for shared resource, rather win
> authentication.
> 2.Shared folder, access set to everyone.
> Current Situration
> 1.Have created 5 reports, each has a url within Domino Notes
> application. Click on anyone of them (assuming
> \\Main Domain\Administrator\) --> reports work ok.
> 2.Log-on as non-administrator --> click on url, enter site ok, but
> popup appears asking for authentication of user
> e.g. name, pwd & domain.
> Constraints
> 1.Development Box cannot be joined to main domain i.e. Non-Wins
> authentication
> 2.Host name e.g. http:\\wtdev01\reportserver --> dealt with WINS
> server, not host file !
> Requirement
> Any user clicks on URL for the report, will open the report and be run
> without authentication issues.
> Tried
> 1.Custom Authentication (e.g. Form Authentication) - issue cannot use
> Standard version of SQL Server ! Needs to be Enterprise. Dead end.
> Any ideas.|||Give that a try. Thanks.
Situration
1.Development Server is on WorkGroup, not main Domain. (win 2003
server(std))
2.Accessing dev server via \\Main Domain\Administrator
3.SQL 2K (Std)
SQL Reporting
1.Using SQL authentication for shared resource, rather win
authentication.
2.Shared folder, access set to everyone.
Current Situration
1.Have created 5 reports, each has a url within Domino Notes
application. Click on anyone of them (assuming
\\Main Domain\Administrator\) --> reports work ok.
2.Log-on as non-administrator --> click on url, enter site ok, but
popup appears asking for authentication of user
e.g. name, pwd & domain.
Constraints
1.Development Box cannot be joined to main domain i.e. Non-Wins
authentication
2.Host name e.g. http:\\wtdev01\reportserver --> dealt with WINS
server, not host file !
Requirement
Any user clicks on URL for the report, will open the report and be run
without authentication issues.
Tried
1.Custom Authentication (e.g. Form Authentication) - issue cannot use
Standard version of SQL Server ! Needs to be Enterprise. Dead end.
Any ideas.This is where Anonymous access may do the trick for you if you don't need RS
to differentiate the users. If you do, then one of your constraints needs to
go away.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"ImraneA" <i.akram@.weir.co.uk> wrote in message
news:24b9d612.0411010439.7f2e15d4@.posting.google.com...
> Hi there
> Situration
> 1.Development Server is on WorkGroup, not main Domain. (win 2003
> server(std))
> 2.Accessing dev server via \\Main Domain\Administrator
> 3.SQL 2K (Std)
> SQL Reporting
> 1.Using SQL authentication for shared resource, rather win
> authentication.
> 2.Shared folder, access set to everyone.
> Current Situration
> 1.Have created 5 reports, each has a url within Domino Notes
> application. Click on anyone of them (assuming
> \\Main Domain\Administrator\) --> reports work ok.
> 2.Log-on as non-administrator --> click on url, enter site ok, but
> popup appears asking for authentication of user
> e.g. name, pwd & domain.
> Constraints
> 1.Development Box cannot be joined to main domain i.e. Non-Wins
> authentication
> 2.Host name e.g. http:\\wtdev01\reportserver --> dealt with WINS
> server, not host file !
> Requirement
> Any user clicks on URL for the report, will open the report and be run
> without authentication issues.
> Tried
> 1.Custom Authentication (e.g. Form Authentication) - issue cannot use
> Standard version of SQL Server ! Needs to be Enterprise. Dead end.
> Any ideas.|||Give that a try. Thanks.
Sunday, February 19, 2012
Benchmark of ODBC vs Oracle Net8 for Connectivity
Do any one have any unbiased benchmarks of the performance differences of
connecting an Oracle 9i DB to MS SQL Server 2000 via ODBC vs NET8?
References for any pros and cons.
Any guidance would be appreciated!!!!
Don't think you can really compare the two in terms of
connecting with SQL Server. You need to use ODBC or OLE DB
to connect to an Oracle database. The server needs to have
the Oracle client installed. You don't use just the Oracle
components (such as Net 8) to connect to Oracle but the
Oracle client components are a part of the connectivity when
using ODBC or OLE DB against an Oracle database.
-Sue
On Fri, 10 Sep 2004 15:05:02 -0700, "Mike"
<Mike@.discussions.microsoft.com> wrote:
>Do any one have any unbiased benchmarks of the performance differences of
>connecting an Oracle 9i DB to MS SQL Server 2000 via ODBC vs NET8?
>References for any pros and cons.
>Any guidance would be appreciated!!!!
connecting an Oracle 9i DB to MS SQL Server 2000 via ODBC vs NET8?
References for any pros and cons.
Any guidance would be appreciated!!!!
Don't think you can really compare the two in terms of
connecting with SQL Server. You need to use ODBC or OLE DB
to connect to an Oracle database. The server needs to have
the Oracle client installed. You don't use just the Oracle
components (such as Net 8) to connect to Oracle but the
Oracle client components are a part of the connectivity when
using ODBC or OLE DB against an Oracle database.
-Sue
On Fri, 10 Sep 2004 15:05:02 -0700, "Mike"
<Mike@.discussions.microsoft.com> wrote:
>Do any one have any unbiased benchmarks of the performance differences of
>connecting an Oracle 9i DB to MS SQL Server 2000 via ODBC vs NET8?
>References for any pros and cons.
>Any guidance would be appreciated!!!!
Monday, February 13, 2012
Beginner question - working with URL variables
I want to default one of my report parameters to a value passed in via the
URL. How do i reference a URL variable in the report designer?
TIA,
BrianThanks for your help Teros. I think I tried what you are suggesting, but
everytime i run the report, passing in the URL variable never seems to have
any affect on the value.
Here's what i got:
Parameter1: This is the variable that i will want to change with a URL
variable, default value is blank, no prompt.
Parameter2: Changeable parameter on the report, defaults to
Parameter1.value.
Then when I call the report, i try passing in a value for Parameter1:
http://127.0.0.1/ReportServer?ReportName&Parameter1=22
The report comes up with Parameter2 still showing blank.
Brian
"Teros" <Teros@.discussions.microsoft.com> wrote in message
news:9A646724-4C92-464B-A4C4-6BE2C6F7A98A@.microsoft.com...
> If you have your url such as:
> http://127.0.0.1/ReportServer?ReportNameX&categoryY=22
> Where ReportNameX is the report and categoryY is your parameter, you
should be able to reference it within your report by setting up a
coresponding parameter named categoryY and then using the usual
Parameters!categoryY.Value . So to have it default that value for a second
parameter, I would think you'd set up your URL passed parameter, then call
that with the Parameters!blah.value in your default value spot for the 2nd
parameter.
> Hope that helps (and works! :) )
> - T
> "G" wrote:
> > I want to default one of my report parameters to a value passed in via
the
> > URL. How do i reference a URL variable in the report designer?
> >
> > TIA,
> > Brian
> >
> >
> >|||That seems ridiculously complicated for something that should be common
practice. Passing a URL variable into a report and manipulating that
variable has to be a trivial task.
I must be doing something wrong, but not a clue what.
> Hmm... Might be that parameters can't control parameters in the same
report? Quick workaround might be to have a "shell" report that brings in
the URL parameter, then have your main report as a subreport in the shell
(taking up the entire space, so it's transparent to the end user) and pass
the second parameter based on the outer first?
> Did that make any sense?
> - T
> "G" wrote:
> > Thanks for your help Teros. I think I tried what you are suggesting, but
> > everytime i run the report, passing in the URL variable never seems to
have
> > any affect on the value.
> >
> > Here's what i got:
> >
> > Parameter1: This is the variable that i will want to change with a URL
> > variable, default value is blank, no prompt.
> > Parameter2: Changeable parameter on the report, defaults to
> > Parameter1.value.
> >
> > Then when I call the report, i try passing in a value for Parameter1:
> > http://127.0.0.1/ReportServer?ReportName&Parameter1=22
> >
> > The report comes up with Parameter2 still showing blank.
> >
> > Brian
> >
> >
> > "Teros" <Teros@.discussions.microsoft.com> wrote in message
> > news:9A646724-4C92-464B-A4C4-6BE2C6F7A98A@.microsoft.com...
> > > If you have your url such as:
> > >
> > > http://127.0.0.1/ReportServer?ReportNameX&categoryY=22
> > >
> > > Where ReportNameX is the report and categoryY is your parameter, you
> > should be able to reference it within your report by setting up a
> > coresponding parameter named categoryY and then using the usual
> > Parameters!categoryY.Value . So to have it default that value for a
second
> > parameter, I would think you'd set up your URL passed parameter, then
call
> > that with the Parameters!blah.value in your default value spot for the
2nd
> > parameter.
> > >
> > > Hope that helps (and works! :) )
> > > - T
> > >
> > > "G" wrote:
> > >
> > > > I want to default one of my report parameters to a value passed in
via
> > the
> > > > URL. How do i reference a URL variable in the report designer?
> > > >
> > > > TIA,
> > > > Brian
> > > >
> > > >
> > > >
> >
> >
> >|||I think that you have the concept of how to do this not quite right. You are
not wanting to reference the variable passed on the url, you want the url to
set the report parameter you have already defined. Steps to get this
working. First create a report with report parameters and make sure the
report is working.
Easiest is just have a blank report with two text boxes. Set the textboxes
as an expression. Using the expression builder you can set it to you
parameter. Open up the report, you will be prompted for a parameter. Fill it
in and make sure the parameter shows up on your report. Now try it via a
URL.
Bruce L-C
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:O5yEaYTWEHA.4056@.TK2MSFTNGP11.phx.gbl...
> That seems ridiculously complicated for something that should be common
> practice. Passing a URL variable into a report and manipulating that
> variable has to be a trivial task.
> I must be doing something wrong, but not a clue what.
>
> > Hmm... Might be that parameters can't control parameters in the same
> report? Quick workaround might be to have a "shell" report that brings in
> the URL parameter, then have your main report as a subreport in the shell
> (taking up the entire space, so it's transparent to the end user) and pass
> the second parameter based on the outer first?
> >
> > Did that make any sense?
> > - T
> >
> > "G" wrote:
> >
> > > Thanks for your help Teros. I think I tried what you are suggesting,
but
> > > everytime i run the report, passing in the URL variable never seems to
> have
> > > any affect on the value.
> > >
> > > Here's what i got:
> > >
> > > Parameter1: This is the variable that i will want to change with a URL
> > > variable, default value is blank, no prompt.
> > > Parameter2: Changeable parameter on the report, defaults to
> > > Parameter1.value.
> > >
> > > Then when I call the report, i try passing in a value for Parameter1:
> > > http://127.0.0.1/ReportServer?ReportName&Parameter1=22
> > >
> > > The report comes up with Parameter2 still showing blank.
> > >
> > > Brian
> > >
> > >
> > > "Teros" <Teros@.discussions.microsoft.com> wrote in message
> > > news:9A646724-4C92-464B-A4C4-6BE2C6F7A98A@.microsoft.com...
> > > > If you have your url such as:
> > > >
> > > > http://127.0.0.1/ReportServer?ReportNameX&categoryY=22
> > > >
> > > > Where ReportNameX is the report and categoryY is your parameter, you
> > > should be able to reference it within your report by setting up a
> > > coresponding parameter named categoryY and then using the usual
> > > Parameters!categoryY.Value . So to have it default that value for a
> second
> > > parameter, I would think you'd set up your URL passed parameter, then
> call
> > > that with the Parameters!blah.value in your default value spot for the
> 2nd
> > > parameter.
> > > >
> > > > Hope that helps (and works! :) )
> > > > - T
> > > >
> > > > "G" wrote:
> > > >
> > > > > I want to default one of my report parameters to a value passed in
> via
> > > the
> > > > > URL. How do i reference a URL variable in the report designer?
> > > > >
> > > > > TIA,
> > > > > Brian
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
URL. How do i reference a URL variable in the report designer?
TIA,
BrianThanks for your help Teros. I think I tried what you are suggesting, but
everytime i run the report, passing in the URL variable never seems to have
any affect on the value.
Here's what i got:
Parameter1: This is the variable that i will want to change with a URL
variable, default value is blank, no prompt.
Parameter2: Changeable parameter on the report, defaults to
Parameter1.value.
Then when I call the report, i try passing in a value for Parameter1:
http://127.0.0.1/ReportServer?ReportName&Parameter1=22
The report comes up with Parameter2 still showing blank.
Brian
"Teros" <Teros@.discussions.microsoft.com> wrote in message
news:9A646724-4C92-464B-A4C4-6BE2C6F7A98A@.microsoft.com...
> If you have your url such as:
> http://127.0.0.1/ReportServer?ReportNameX&categoryY=22
> Where ReportNameX is the report and categoryY is your parameter, you
should be able to reference it within your report by setting up a
coresponding parameter named categoryY and then using the usual
Parameters!categoryY.Value . So to have it default that value for a second
parameter, I would think you'd set up your URL passed parameter, then call
that with the Parameters!blah.value in your default value spot for the 2nd
parameter.
> Hope that helps (and works! :) )
> - T
> "G" wrote:
> > I want to default one of my report parameters to a value passed in via
the
> > URL. How do i reference a URL variable in the report designer?
> >
> > TIA,
> > Brian
> >
> >
> >|||That seems ridiculously complicated for something that should be common
practice. Passing a URL variable into a report and manipulating that
variable has to be a trivial task.
I must be doing something wrong, but not a clue what.
> Hmm... Might be that parameters can't control parameters in the same
report? Quick workaround might be to have a "shell" report that brings in
the URL parameter, then have your main report as a subreport in the shell
(taking up the entire space, so it's transparent to the end user) and pass
the second parameter based on the outer first?
> Did that make any sense?
> - T
> "G" wrote:
> > Thanks for your help Teros. I think I tried what you are suggesting, but
> > everytime i run the report, passing in the URL variable never seems to
have
> > any affect on the value.
> >
> > Here's what i got:
> >
> > Parameter1: This is the variable that i will want to change with a URL
> > variable, default value is blank, no prompt.
> > Parameter2: Changeable parameter on the report, defaults to
> > Parameter1.value.
> >
> > Then when I call the report, i try passing in a value for Parameter1:
> > http://127.0.0.1/ReportServer?ReportName&Parameter1=22
> >
> > The report comes up with Parameter2 still showing blank.
> >
> > Brian
> >
> >
> > "Teros" <Teros@.discussions.microsoft.com> wrote in message
> > news:9A646724-4C92-464B-A4C4-6BE2C6F7A98A@.microsoft.com...
> > > If you have your url such as:
> > >
> > > http://127.0.0.1/ReportServer?ReportNameX&categoryY=22
> > >
> > > Where ReportNameX is the report and categoryY is your parameter, you
> > should be able to reference it within your report by setting up a
> > coresponding parameter named categoryY and then using the usual
> > Parameters!categoryY.Value . So to have it default that value for a
second
> > parameter, I would think you'd set up your URL passed parameter, then
call
> > that with the Parameters!blah.value in your default value spot for the
2nd
> > parameter.
> > >
> > > Hope that helps (and works! :) )
> > > - T
> > >
> > > "G" wrote:
> > >
> > > > I want to default one of my report parameters to a value passed in
via
> > the
> > > > URL. How do i reference a URL variable in the report designer?
> > > >
> > > > TIA,
> > > > Brian
> > > >
> > > >
> > > >
> >
> >
> >|||I think that you have the concept of how to do this not quite right. You are
not wanting to reference the variable passed on the url, you want the url to
set the report parameter you have already defined. Steps to get this
working. First create a report with report parameters and make sure the
report is working.
Easiest is just have a blank report with two text boxes. Set the textboxes
as an expression. Using the expression builder you can set it to you
parameter. Open up the report, you will be prompted for a parameter. Fill it
in and make sure the parameter shows up on your report. Now try it via a
URL.
Bruce L-C
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:O5yEaYTWEHA.4056@.TK2MSFTNGP11.phx.gbl...
> That seems ridiculously complicated for something that should be common
> practice. Passing a URL variable into a report and manipulating that
> variable has to be a trivial task.
> I must be doing something wrong, but not a clue what.
>
> > Hmm... Might be that parameters can't control parameters in the same
> report? Quick workaround might be to have a "shell" report that brings in
> the URL parameter, then have your main report as a subreport in the shell
> (taking up the entire space, so it's transparent to the end user) and pass
> the second parameter based on the outer first?
> >
> > Did that make any sense?
> > - T
> >
> > "G" wrote:
> >
> > > Thanks for your help Teros. I think I tried what you are suggesting,
but
> > > everytime i run the report, passing in the URL variable never seems to
> have
> > > any affect on the value.
> > >
> > > Here's what i got:
> > >
> > > Parameter1: This is the variable that i will want to change with a URL
> > > variable, default value is blank, no prompt.
> > > Parameter2: Changeable parameter on the report, defaults to
> > > Parameter1.value.
> > >
> > > Then when I call the report, i try passing in a value for Parameter1:
> > > http://127.0.0.1/ReportServer?ReportName&Parameter1=22
> > >
> > > The report comes up with Parameter2 still showing blank.
> > >
> > > Brian
> > >
> > >
> > > "Teros" <Teros@.discussions.microsoft.com> wrote in message
> > > news:9A646724-4C92-464B-A4C4-6BE2C6F7A98A@.microsoft.com...
> > > > If you have your url such as:
> > > >
> > > > http://127.0.0.1/ReportServer?ReportNameX&categoryY=22
> > > >
> > > > Where ReportNameX is the report and categoryY is your parameter, you
> > > should be able to reference it within your report by setting up a
> > > coresponding parameter named categoryY and then using the usual
> > > Parameters!categoryY.Value . So to have it default that value for a
> second
> > > parameter, I would think you'd set up your URL passed parameter, then
> call
> > > that with the Parameters!blah.value in your default value spot for the
> 2nd
> > > parameter.
> > > >
> > > > Hope that helps (and works! :) )
> > > > - T
> > > >
> > > > "G" wrote:
> > > >
> > > > > I want to default one of my report parameters to a value passed in
> via
> > > the
> > > > > URL. How do i reference a URL variable in the report designer?
> > > > >
> > > > > TIA,
> > > > > Brian
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
Friday, February 10, 2012
Begginer Confusion
Hey everyone,
I am brand new to SQL and I need some help getting started how to learn about relational databases and how to make the databases via Microsoft SQL(I have visual Studios PRO) and access and use them via C#.
I believe that the program that I am writing will be significantly better with a database. What I am doing is making a program that stores user input. But each user input that a person stores can have a reference to another user input, hence why I think that a relational database to store this information will be work out well. I also just want to learn more about databases because I think that it will be a good step in me learning more about programming.
What I need from you guys is a point into a direction for a book or a specific spot in a website that will introduce and teach me how to design these relational databases via Microsoft SQL. I have programmed alot with Java and recently switched over to C#, using the .net framework and found it to be great. I need to find out how to make these databases but also how to connect and access them through my application that will be coded in C#. I have "Programming in C#" by O'Reilly but it really doesn't go in that much about SQL databases.
The question that I really have come from the limited knowledge that I have gained looking online and talking to my brother (progamming whiz). A database is managed by a DBMS and clients can interact with the DBMS to get to the database. The question to me is how does one interact with a DBMS through a programming language like C#. I guess I am just really confused at the this link. If anyone could point me into the direction of a book to answer these question but help teach me to apply it to my own program, I would really appreciate it. Thank your for time.
P.S.
I have found some books online but I dont really know which one I really need. One that I found is called "Learning SQL on SQL Server 2005". If you have read, I would appreciate your opinion. Thanks
|||
I am brand new to SQL and I need some help getting started how to learn about relational databases and how to make the databases via Microsoft SQL(I have visual Studios PRO) and access and use them via C#.
I believe that the program that I am writing will be significantly better with a database. What I am doing is making a program that stores user input. But each user input that a person stores can have a reference to another user input, hence why I think that a relational database to store this information will be work out well. I also just want to learn more about databases because I think that it will be a good step in me learning more about programming.
What I need from you guys is a point into a direction for a book or a specific spot in a website that will introduce and teach me how to design these relational databases via Microsoft SQL. I have programmed alot with Java and recently switched over to C#, using the .net framework and found it to be great. I need to find out how to make these databases but also how to connect and access them through my application that will be coded in C#. I have "Programming in C#" by O'Reilly but it really doesn't go in that much about SQL databases.
The question that I really have come from the limited knowledge that I have gained looking online and talking to my brother (progamming whiz). A database is managed by a DBMS and clients can interact with the DBMS to get to the database. The question to me is how does one interact with a DBMS through a programming language like C#. I guess I am just really confused at the this link. If anyone could point me into the direction of a book to answer these question but help teach me to apply it to my own program, I would really appreciate it. Thank your for time.
P.S.
I have found some books online but I dont really know which one I really need. One that I found is called "Learning SQL on SQL Server 2005". If you have read, I would appreciate your opinion. Thanks
Here are two excellent resources for 'getting your feet wet'.
The ASP.NET site has quite a few videos about other topics -but if you scroll down, you will find some for incorporating databases in the project.
http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx
http://www.asp.net/learn/videos/default.aspx?tabid=63#sql
The Microsoft Press 'Step by Step' series are good for starting out.
|||Thank you for both of those sources. I will check them out right now. Also, one more question. If I use the SQL database for my application, will all the clients, and everyone that downloads my program have to have SQL installed on their computer so that it will work?|||
If it is a web based application, the data server is usually located with the web server -all users only require a browser.
If it is a stand-alone Windows based application, you probably would distribute the database (and the server) with your application. (Most likely using the free SQL Server Express.)
BDE Connection via ODBC to MS SQL Server fails
Hi,
I have a strange problem accessing a MS SQL Database from my network.
I'm using an application that is based on BDE which uses ODBC to connect to a MS SQL DB.
When I tried to connect from the network with BDE,I got this error message:
BDE Error : 13059
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
On the server side I'm using W2003 EE ,MS SQL 2000 Standard with SP4, BDE5.11.
On the client side it's an XP SP2 with MS SQL Client installed, same BDE 5.11
When I create an alias using ODBC and mapping to my server, it connects and the communication with the Database Server succeds:
TESTS COMPLETED SUCCESSFULLY!
When I'm trying to connect using BDE Admin and double clicking on the + sign of the alias I created usind ODBC admin I get the message I listed above.
Any help would be appreciated,
Thanks.May check this http://support.plato.com/kb/tip.asp?psid=23252 link fyi.|||Thank you for replying me...in the meantime i solved the problem.
It was the incorrect settings in the registry for BDE. After erasing the keys from the registry and having reinstalled the BDE everything worked fine.
There are some files used by BDE when connecting to SQL server which are not installed when using Delphi 5 or later :(
However thank you for the usefull link.
best regards,
Sanyi|||Glad the issue is resolved and appreciate your feedback on posting the solution, that helps.
I have a strange problem accessing a MS SQL Database from my network.
I'm using an application that is based on BDE which uses ODBC to connect to a MS SQL DB.
When I tried to connect from the network with BDE,I got this error message:
BDE Error : 13059
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
On the server side I'm using W2003 EE ,MS SQL 2000 Standard with SP4, BDE5.11.
On the client side it's an XP SP2 with MS SQL Client installed, same BDE 5.11
When I create an alias using ODBC and mapping to my server, it connects and the communication with the Database Server succeds:
TESTS COMPLETED SUCCESSFULLY!
When I'm trying to connect using BDE Admin and double clicking on the + sign of the alias I created usind ODBC admin I get the message I listed above.
Any help would be appreciated,
Thanks.May check this http://support.plato.com/kb/tip.asp?psid=23252 link fyi.|||Thank you for replying me...in the meantime i solved the problem.
It was the incorrect settings in the registry for BDE. After erasing the keys from the registry and having reinstalled the BDE everything worked fine.
There are some files used by BDE when connecting to SQL server which are not installed when using Delphi 5 or later :(
However thank you for the usefull link.
best regards,
Sanyi|||Glad the issue is resolved and appreciate your feedback on posting the solution, that helps.
BDE admin connecting problem, How do I connect via IP address.
Hi, I am connecting from clientside with sql server 2005 installed trying to connect to a database running on a server with sql server 2000 installed. this is not a 2000 to 2005 compatibility issue.
I can connect locally fine to the server and log in using the sa account, however for BDE admin i HAVE to use the server name instead of ip address. If i try to use the ip address of the server, the connection dosnt get established. *this is all happening localy on our own network.
I can connect locally fine to the server and log in using the sa account, however for BDE admin i HAVE to use the server name instead of ip address. If i try to use the ip address of the server, the connection dosnt get established. *this is all happening localy on our own network.
How to I use ip address to connect instead of the server name? I will need to connect remotely outside of our network later on, and i dont think I can use the servername. Does anyone have a solution? thanks.BDE = borland database engine|||I suggest creating an ODBC DSN and using that from BDE rather than the SQLLINK driver|||thanks for the input, we figured out the problem though, it seems the server uses a proprietary listening port, say.. "3314" set up for their server. we were able to connect after setting up an alias for sql server.|||I have more questions
1) does SQL server 2005 come with a client only tool?
2) is there a way to connect via an alias with an application without installing sql server 2005?
thanks in advance!|||
1/ Yes its called SQL Server Management Studio (or Express Edition of SSMS) for management and there are dev tools, config tools etc
2/ you need to install SQL Server Computer Manager to config alias's
Subscribe to:
Comments (Atom)