assistance please....

What is the easiest way to check the date and time of service pack updates?

What do you mean by that `?

Assistance creating my first trigger

I hope you guys can help. I am looking to create a trigger that watches for
updates to the sch_status column and fires when the update changes the value
to 4. The code that will be executed is below with addition of some code to
e-mail the results. It is the actual code to create the update trigger on the
schedule table I need some help with.
select s.sch_id,
'Date Scheduled'=convert(varchar(17),s.sch_date,113),
s.e_subject,
'Number Processed'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id and dl.delivery_status=2),
'Total Number'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id),
'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
convert(varchar(12),getdate(),112) +'.log'
from schedule s
where s.sch_status = 4
order by s.sch_idHi
All what you have to do it join the virtual table INSERTED in your query on
the primary key.
It is not recommended that you send e-mails from within a trigger, as it
hurts performance, has horrible consequences with locking and blocking, and
if the e-mail sending fails, your update will get rolled back (and you might
have sent e-mails to 1/2 your customer base). Rather write a record into a
queue type table where another process comes along a sends the e-mails.
Books On Line has some good examples on CREATE TRIGGER and how to use the
virtual tables INSERTED and DELETED.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> I hope you guys can help. I am looking to create a trigger that watches
for
> updates to the sch_status column and fires when the update changes the
value
> to 4. The code that will be executed is below with addition of some code
to
> e-mail the results. It is the actual code to create the update trigger on
the
> schedule table I need some help with.
> select s.sch_id,
> 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> s.e_subject,
> 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
where
> dl.sch_id = s.sch_id and dl.delivery_status=2),
> 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> dl.sch_id = s.sch_id),
> 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> convert(varchar(12),getdate(),112) +'.log'
> from schedule s
> where s.sch_status = 4
> order by s.sch_id|||In case it leads to a different outcome I plan to have the select statement
and the e-mail (not sql mail) in a stored procedure and have it called by the
trigger.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> All what you have to do it join the virtual table INSERTED in your query on
> the primary key.
> It is not recommended that you send e-mails from within a trigger, as it
> hurts performance, has horrible consequences with locking and blocking, and
> if the e-mail sending fails, your update will get rolled back (and you might
> have sent e-mails to 1/2 your customer base). Rather write a record into a
> queue type table where another process comes along a sends the e-mails.
> Books On Line has some good examples on CREATE TRIGGER and how to use the
> virtual tables INSERTED and DELETED.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> > I hope you guys can help. I am looking to create a trigger that watches
> for
> > updates to the sch_status column and fires when the update changes the
> value
> > to 4. The code that will be executed is below with addition of some code
> to
> > e-mail the results. It is the actual code to create the update trigger on
> the
> > schedule table I need some help with.
> >
> > select s.sch_id,
> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> > s.e_subject,
> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
> where
> > dl.sch_id = s.sch_id and dl.delivery_status=2),
> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> > dl.sch_id = s.sch_id),
> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> > convert(varchar(12),getdate(),112) +'.log'
> > from schedule s
> > where s.sch_status = 4
> > order by s.sch_id
>
>|||You still have the same issues (concurrency/performance), even if you
execute a proc in order to send email from the trigger. I suggest you heed
Mike's suggestion and employ a queue-type table so that you can send the
actual email asynchronously. It's fairly simple to implement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
> In case it leads to a different outcome I plan to have the select
> statement
> and the e-mail (not sql mail) in a stored procedure and have it called by
> the
> trigger.
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> All what you have to do it join the virtual table INSERTED in your query
>> on
>> the primary key.
>> It is not recommended that you send e-mails from within a trigger, as it
>> hurts performance, has horrible consequences with locking and blocking,
>> and
>> if the e-mail sending fails, your update will get rolled back (and you
>> might
>> have sent e-mails to 1/2 your customer base). Rather write a record into
>> a
>> queue type table where another process comes along a sends the e-mails.
>> Books On Line has some good examples on CREATE TRIGGER and how to use the
>> virtual tables INSERTED and DELETED.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
>> > I hope you guys can help. I am looking to create a trigger that watches
>> for
>> > updates to the sch_status column and fires when the update changes the
>> value
>> > to 4. The code that will be executed is below with addition of some
>> > code
>> to
>> > e-mail the results. It is the actual code to create the update trigger
>> > on
>> the
>> > schedule table I need some help with.
>> >
>> > select s.sch_id,
>> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
>> > s.e_subject,
>> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
>> where
>> > dl.sch_id = s.sch_id and dl.delivery_status=2),
>> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
>> > dl.sch_id = s.sch_id),
>> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
>> > convert(varchar(12),getdate(),112) +'.log'
>> > from schedule s
>> > where s.sch_status = 4
>> > order by s.sch_id
>>|||Another possibility is to have a SQL Agent job set up to execute your stored
procedure. This does not mean you need to use SQL Agent mail--the stored
procedure can still call xp_sendmail directly--however, you can have the
trigger fire off msdb.sp_startjob. The execution of this stored procedure
circumvents some of the concurrency/consistency issues in that it makes the
call to launch the process but runs the job synchronously.
If there is a problem with starting the job execution, you will get a
failure notice and your transaction will rollback but you would not have
emailed anyone yet.
Sincerely,
Anthony Thomas
"Dan Guzman" wrote:
> You still have the same issues (concurrency/performance), even if you
> execute a proc in order to send email from the trigger. I suggest you heed
> Mike's suggestion and employ a queue-type table so that you can send the
> actual email asynchronously. It's fairly simple to implement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
> > In case it leads to a different outcome I plan to have the select
> > statement
> > and the e-mail (not sql mail) in a stored procedure and have it called by
> > the
> > trigger.
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> All what you have to do it join the virtual table INSERTED in your query
> >> on
> >> the primary key.
> >>
> >> It is not recommended that you send e-mails from within a trigger, as it
> >> hurts performance, has horrible consequences with locking and blocking,
> >> and
> >> if the e-mail sending fails, your update will get rolled back (and you
> >> might
> >> have sent e-mails to 1/2 your customer base). Rather write a record into
> >> a
> >> queue type table where another process comes along a sends the e-mails.
> >>
> >> Books On Line has some good examples on CREATE TRIGGER and how to use the
> >> virtual tables INSERTED and DELETED.
> >>
> >> Regards
> >> --
> >> Mike Epprecht, Microsoft SQL Server MVP
> >> Zurich, Switzerland
> >>
> >> IM: mike@.epprecht.net
> >>
> >> MVP Program: http://www.microsoft.com/mvp
> >>
> >> Blog: http://www.msmvps.com/epprecht/
> >>
> >> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> >> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> >> > I hope you guys can help. I am looking to create a trigger that watches
> >> for
> >> > updates to the sch_status column and fires when the update changes the
> >> value
> >> > to 4. The code that will be executed is below with addition of some
> >> > code
> >> to
> >> > e-mail the results. It is the actual code to create the update trigger
> >> > on
> >> the
> >> > schedule table I need some help with.
> >> >
> >> > select s.sch_id,
> >> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> >> > s.e_subject,
> >> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
> >> where
> >> > dl.sch_id = s.sch_id and dl.delivery_status=2),
> >> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> >> > dl.sch_id = s.sch_id),
> >> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> >> > convert(varchar(12),getdate(),112) +'.log'
> >> > from schedule s
> >> > where s.sch_status = 4
> >> > order by s.sch_id
> >>
> >>
> >>
>
>|||In reviewing this thread, I see we forgot to mention that the proc/script to
actually send the email can be asynchronously scheduled with a SQL Agent
job. Thanks for pointing that out.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
If Russel doesn't want a sp_startjob failure to rollback the insertion, the
job can be scheduled to run periodically rather than on demand.
Another method is to create a startup proc that runs continuously with a
WAITFOR DELAY loop.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:948EC3BF-AB7C-4669-BC74-AD81E2E0C71C@.microsoft.com...
> Another possibility is to have a SQL Agent job set up to execute your
> stored
> procedure. This does not mean you need to use SQL Agent mail--the stored
> procedure can still call xp_sendmail directly--however, you can have the
> trigger fire off msdb.sp_startjob. The execution of this stored procedure
> circumvents some of the concurrency/consistency issues in that it makes
> the
> call to launch the process but runs the job synchronously.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
> Sincerely,
>
> Anthony Thomas
>
> "Dan Guzman" wrote:
>> You still have the same issues (concurrency/performance), even if you
>> execute a proc in order to send email from the trigger. I suggest you
>> heed
>> Mike's suggestion and employ a queue-type table so that you can send the
>> actual email asynchronously. It's fairly simple to implement.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
>> > In case it leads to a different outcome I plan to have the select
>> > statement
>> > and the e-mail (not sql mail) in a stored procedure and have it called
>> > by
>> > the
>> > trigger.
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> >> Hi
>> >>
>> >> All what you have to do it join the virtual table INSERTED in your
>> >> query
>> >> on
>> >> the primary key.
>> >>
>> >> It is not recommended that you send e-mails from within a trigger, as
>> >> it
>> >> hurts performance, has horrible consequences with locking and
>> >> blocking,
>> >> and
>> >> if the e-mail sending fails, your update will get rolled back (and you
>> >> might
>> >> have sent e-mails to 1/2 your customer base). Rather write a record
>> >> into
>> >> a
>> >> queue type table where another process comes along a sends the
>> >> e-mails.
>> >>
>> >> Books On Line has some good examples on CREATE TRIGGER and how to use
>> >> the
>> >> virtual tables INSERTED and DELETED.
>> >>
>> >> Regards
>> >> --
>> >> Mike Epprecht, Microsoft SQL Server MVP
>> >> Zurich, Switzerland
>> >>
>> >> IM: mike@.epprecht.net
>> >>
>> >> MVP Program: http://www.microsoft.com/mvp
>> >>
>> >> Blog: http://www.msmvps.com/epprecht/
>> >>
>> >> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> >> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
>> >> > I hope you guys can help. I am looking to create a trigger that
>> >> > watches
>> >> for
>> >> > updates to the sch_status column and fires when the update changes
>> >> > the
>> >> value
>> >> > to 4. The code that will be executed is below with addition of some
>> >> > code
>> >> to
>> >> > e-mail the results. It is the actual code to create the update
>> >> > trigger
>> >> > on
>> >> the
>> >> > schedule table I need some help with.
>> >> >
>> >> > select s.sch_id,
>> >> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
>> >> > s.e_subject,
>> >> > 'Number Processed'=(select count(dl.sch_id) from distribution_list
>> >> > dl
>> >> where
>> >> > dl.sch_id = s.sch_id and dl.delivery_status=2),
>> >> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl
>> >> > where
>> >> > dl.sch_id = s.sch_id),
>> >> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
>> >> > convert(varchar(12),getdate(),112) +'.log'
>> >> > from schedule s
>> >> > where s.sch_status = 4
>> >> > order by s.sch_id
>> >>
>> >>
>> >>
>>

Assign permissions to allow updates but deny select on table

I have a table that I want to deny users to select, but allow inserts,
updates and deletes. So in the permissions for that table, I have
given the security Group Update, Insert and Delete rights and Denied
Select access. I connect to the table via odbc and have been able to
get the Insert queries working fine. However, my attempts to run
Update or Delete queries continually produce errors.
Here is a sample query I'm trying to run
DELETE tblPasswordsforDirector.PassMasterID
FROM tblPasswordsforDirector
WHERE (((tblPasswordsforDirector.PassMasterID)=339));
Here is a summary of the odbc error I get:
Select Permission denied on column 'password' of object
tblPasswordsforDirector.
In troubleshooting, I tried to use the COLUMN permissions to give
Select access to the two columns I don't care if the users see, and
then Deny Select access on the two columns I don't want them to see
(one of which happens to be the 'password' column shown in error
above).
I guess I'm a little confused at why a DELETE query requires Select
permissions. And if it does, then why does SQL give you the ability to
Allow DELETE permissions and Deny SELECT permissions on the same
table?
Any help would be most appreciated.
Thanks in advance.Tanzen (aaron.nasby@.gmail.com) writes:
> I have a table that I want to deny users to select, but allow inserts,
> updates and deletes. So in the permissions for that table, I have
> given the security Group Update, Insert and Delete rights and Denied
> Select access. I connect to the table via odbc and have been able to
> get the Insert queries working fine. However, my attempts to run
> Update or Delete queries continually produce errors.
> Here is a sample query I'm trying to run
> DELETE tblPasswordsforDirector.PassMasterID
> FROM tblPasswordsforDirector
> WHERE (((tblPasswordsforDirector.PassMasterID)=339));
> Here is a summary of the odbc error I get:
> Select Permission denied on column 'password' of object
> tblPasswordsforDirector.
> In troubleshooting, I tried to use the COLUMN permissions to give
> Select access to the two columns I don't care if the users see, and
> then Deny Select access on the two columns I don't want them to see
> (one of which happens to be the 'password' column shown in error
> above).
I'm not sure what you did, but this appears to work as you want:
CREATE LOGIN Julle WITH PASSWORD = 'Vetebulle'
go
CREATE USER Julle
go
CREATE TABLE spezial (a int NOT NULL,
secret varchar(23) NOT NULL)
go
GRANT DELETE, INSERT, UPDATE, SELECT ON spezial TO Julle
go
DENY SELECT ON spezial (secret) TO Julle
go
INSERT spezial (a, secret) VALUES (1, ')(2344')
INSERT spezial (a, secret) VALUES (13, '25344')
go
EXECUTE AS LOGIN = 'Julle'
go
DELETE spezial WHERE a = 13
go
REVERT
go
DROP TABLE spezial
DROP USER Julle
DROP LOGIN Julle
I tested this on SQL 2005. I also ran a similar script on SQL 2000.
Could you post a repro that demonstrates the problem?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Some of the commands you show in your example don't seem to work for
me. I am running SQL 2000. Forgive my ignorance, but I'm not sure what
those commands should be in SQL 2000|||Tanzen (aaron.nasby@.gmail.com) writes:
> Some of the commands you show in your example don't seem to work for
> me. I am running SQL 2000. Forgive my ignorance, but I'm not sure what
> those commands should be in SQL 2000
I hope you've now learnt that you should always say which version of SQL
Server you are using. :-)
Below is the SQL 2000 version of the script that I posted. By the way,
you did not include the full text of your error message.
sp_addlogin Julle, 'Vetebulle'
go
sp_adduser Julle
go
CREATE TABLE spezial (a int NOT NULL,
secret varchar(23) NOT NULL)
go
GRANT DELETE, INSERT, UPDATE, SELECT ON spezial TO Julle
go
DENY SELECT ON spezial (secret) TO Julle
go
INSERT spezial (a, secret) VALUES (1, ')(2344')
INSERT spezial (a, secret) VALUES (13, '25344')
go
SETUSER 'Julle'
go
DELETE spezial WHERE a = 13
go
SETUSER
go
DROP TABLE spezial
EXEC sp_dropuser Julle
EXEC sp_droplogin Julle
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Jul 11, 3:24 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> Tanzen (aaron.na...@.gmail.com) writes:
> I hope you've now learnt that you should always say which version of SQL
> Server you are using. :-)
> Below is the SQL 2000 version of the script that I posted. By the way,
> you did not include the full text of your error message.
> sp_addlogin Julle, 'Vetebulle'
> go
> sp_adduser Julle
> go
> CREATE TABLE spezial (a int NOT NULL,
> secret varchar(23) NOT NULL)
> go
> GRANT DELETE, INSERT, UPDATE, SELECT ON spezial TO Julle
> go
> DENY SELECT ON spezial (secret) TO Julle
> go
> INSERT spezial (a, secret) VALUES (1, ')(2344')
> INSERT spezial (a, secret) VALUES (13, '25344')
> go
> SETUSER 'Julle'
> go
> DELETE spezial WHERE a = 13
> go
> SETUSER
> go
> DROP TABLE spezial
> EXEC sp_dropuser Julle
> EXEC sp_droplogin Julle
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodte
chnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousv
ersions/books.mspx
Your commands were very helpful. I was able to change the script for
my existing table and confirmed the security settings are working when
I use query analyzer. If I try to do a select queryI am denied
permission. If I run a Delete query, it succeeds.
I am using an MS Access 2003 database with an ODBC connection to the
tables to perform the same delete queries, but they fail. The error
when I run the delete query from MS Access 2003 is:
--
ODBC --call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Select permissio
n
denied on column 'password' of object 'tblPasswordsforDirector',
database 'Passwords_IT", owner 'dbo'. (#230) [Microsoft][ODBC SQL
Server Driver][SQL server]SELECT permission denied on column
'UserName' of object 'tblPasswordsforDirector', database
'Passwords_IT', owner 'dbo'. (#230)
--
I'm assuming I need to post this to an Access forum.
So unless you have any ideas, I'll move this on to another forum.
Thank you again for your help.|||Tanzen (aaron.nasby@.gmail.com) writes:
> I am using an MS Access 2003 database with an ODBC connection to the
> tables to perform the same delete queries, but they fail. The error
> when I run the delete query from MS Access 2003 is:
> --
> ODBC --call failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server] Select permiss
ion
> denied on column 'password' of object 'tblPasswordsforDirector',
> database 'Passwords_IT", owner 'dbo'. (#230) [Microsoft][ODBC SQL
> Server Driver][SQL server]SELECT permission denied on column
> 'UserName' of object 'tblPasswordsforDirector', database
> 'Passwords_IT', owner 'dbo'. (#230)
> --
> I'm assuming I need to post this to an Access forum.
Yes, they may be more helpful. But you could use SQL Server profiler
to see what statements that Access is submitting. Maybe it's better to
use a stored procedure instead. But the Access folks may know how tell
Access to do the right thing.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx