display SQL Server: updates

Showing posts with label updates. Show all posts

Friday, March 30, 2012

How do I make a view read-only

I want to create a view that can not be used for record inserts, updates or
deletes - by anyone, including administrators. Is there an option that can
be used in creating the view so I don't have to go through all the users
setting up deny permissions?http://developer.mimer.com/documentation/html_92/Mimer_SQL_Engine_DocSet/Data_manipulation6.html
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:40734E47-0774-4810-959C-5212C74089A7@.microsoft.com...
>I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that
> can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||If you don't want to change the view definition you can add an INSTEAD_OF
trigger for INSERT, DELETE and UPDATE that do nothing.
--
RubÃ©n GarrigÃ³s
Solid Quality Mentors
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:40734E47-0774-4810-959C-5212C74089A7@.microsoft.com...
>I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that
> can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||Hi Bev
I really think this should be covered by the permissions you grant to the
view, trying to stop administrators updating the view would just mean only
mean they would need to hit the base tables instead.
John
"Bev Kaufman" wrote:
> I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||There is nothing you can do to deny administrators rights to your view.
Your best bet is to create an INSTEAD OF TRIGGER that just generates an
error message whenever someone tries to modify the view.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:40734E47-0774-4810-959C-5212C74089A7@.microsoft.com...
>I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that
> can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||Thank you for all your suggestions. I came up with this solution: I added
DISTINCT to the syntax. Since the select list included the unique
identifier, I get the same view results, but now the view is not editable.
"Bev Kaufman" wrote:
> I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||Keep in mind that this solution might not always possible. The INSTEAD OF
trigger solution is much more general purpose. Also in many cases, adding a
DISTINCT can have a negative impact on query performance if there is no
guaranteed uniqueness. Does your uniqueifier have a unique index? That is
the only way that SQL Server knows the values are already unique and doesn't
add extra processing for the DISTINCT.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:89E10E73-53AD-466B-93F1-7C8B9E6C3E0F@.microsoft.com...
> Thank you for all your suggestions. I came up with this solution: I added
> DISTINCT to the syntax. Since the select list included the unique
> identifier, I get the same view results, but now the view is not editable.
> "Bev Kaufman" wrote:
>> I want to create a view that can not be used for record inserts, updates
>> or
>> deletes - by anyone, including administrators. Is there an option that
>> can
>> be used in creating the view so I don't have to go through all the users
>> setting up deny permissions?sql

Wednesday, March 28, 2012

how do i look through the log to see updates to tables in sql sever 2005?

hi. changes were made to a dababase and we need to read the log file to see how much exactly was changed and what. how do i read the log file in sql server 2005 with out buying 3rd party software?I posted a reply in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=655927&SiteID=1|||

hi winston,

if you want a cheaper alternative to know what has changed
you can use a database snapshot solution
as a baseline for comparison.

there's no easy way of looking at the logs
you have to use a 3rd party tools such as lumigent or apex sql

joey

|||thanks lads. i ended up getting ApexSQL Log.

How do I know when a patch is available for SQL Server?

I installed Service Pack a3. It seems that Windows Automatic Updates is not
able to catch any patch(not sp) for SQL Server. Is there anyway that I can
make the patching automatic(at least notify me when a patch for SQL Server is
available)?
Thanks in advance!
Bo Sun
I am sure that you don't want the SP auto patched you SQL server!
Sign up with http://www.microsoft.com/security/default.mspx for email
update.
"Bo Sun" <Bo Sun@.discussions.microsoft.com> wrote in message
news:234E0FB7-91CC-4103-9BEF-6F9D067BB44E@.microsoft.com...
>I installed Service Pack a3. It seems that Windows Automatic Updates is not
> able to catch any patch(not sp) for SQL Server. Is there anyway that I can
> make the patching automatic(at least notify me when a patch for SQL Server
> is
> available)?
> Thanks in advance!
> Bo Sun
|||> I installed Service Pack a3. It seems that Windows Automatic Updates is
not
> able to catch any patch(not sp) for SQL Server.
This is because most post-SP3a updates are hotfixes for specific
bugs/problems, not security patches (and are not relevant for all users).
You can stay pretty much on top of new hotfixes here:
http://www.aspfaq.com/2160 ...
Remember, also, that windows update is for windows, not SQL Server. I
believe they are planning similar functionality for SQL Server 2005, but
don't hold me to that.
http://www.aspfaq.com/
(Reverse address to reply.)
|||Hi
Patching SQL Server is currently not possible with Windows Update. This may
change in the future.
Regards
Mike
"Bo Sun" wrote:

> I installed Service Pack a3. It seems that Windows Automatic Updates is not
> able to catch any patch(not sp) for SQL Server. Is there anyway that I can
> make the patching automatic(at least notify me when a patch for SQL Server is
> available)?
> Thanks in advance!
> Bo Sun

How do I know when a patch is available for SQL Server?

I installed Service Pack a3. It seems that Windows Automatic Updates is not
able to catch any patch(not sp) for SQL Server. Is there anyway that I can
make the patching automatic(at least notify me when a patch for SQL Server is
available)?
Thanks in advance!
Bo SunI am sure that you don't want the SP auto patched you SQL server!
Sign up with http://www.microsoft.com/security/default.mspx for email
update.
"Bo Sun" <Bo Sun@.discussions.microsoft.com> wrote in message
news:234E0FB7-91CC-4103-9BEF-6F9D067BB44E@.microsoft.com...
>I installed Service Pack a3. It seems that Windows Automatic Updates is not
> able to catch any patch(not sp) for SQL Server. Is there anyway that I can
> make the patching automatic(at least notify me when a patch for SQL Server
> is
> available)?
> Thanks in advance!
> Bo Sun|||> I installed Service Pack a3. It seems that Windows Automatic Updates is
not
> able to catch any patch(not sp) for SQL Server.
This is because most post-SP3a updates are hotfixes for specific
bugs/problems, not security patches (and are not relevant for all users).
You can stay pretty much on top of new hotfixes here:
http://www.aspfaq.com/2160 ...
Remember, also, that windows update is for windows, not SQL Server. I
believe they are planning similar functionality for SQL Server 2005, but
don't hold me to that.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hi
Patching SQL Server is currently not possible with Windows Update. This may
change in the future.
Regards
Mike
"Bo Sun" wrote:
> I installed Service Pack a3. It seems that Windows Automatic Updates is not
> able to catch any patch(not sp) for SQL Server. Is there anyway that I can
> make the patching automatic(at least notify me when a patch for SQL Server is
> available)?
> Thanks in advance!
> Bo Sun

Friday, March 9, 2012

How do I find out what prevents my log file from being truncated

Hi
I think I need a little help on Log file architecture.
I have a database where I temporarily disabled my log backup schedules. We
did some updates on the database, and I didn't wanted the backup jobs to
kick in in the middle of the update.
Now I've enabled the log backups again, but of course my logfiles has now
grown to a size that it will never reach again in normal production. I'd
therefore like to shrink the logfile to a more feasible size, but I don't
seem to have much luck with that.
I've backed up the logfile, but when I then issue a DBCC SHRINKFILE command,
I get the message -
'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
use.'
If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
open transaction but that reports no active transactions.
My question is now, how do I find out that it is that prevents me from
shrinking the file. I assume that I'll have to chekc the Virtual Log Files,
but how do I do this? I've looked in BOL but I can't find any description of
how I can "see" the logfile "structure"? I know that this has been covered
in this newsgroup before, but I can't find any threads that helps me right
now.
Regards
SteenSteen
What target size have you specified in DBCC SHRINKFILE command? Try to
increase it.
DECLARE @.db INT
SELECT @.db=db_id()
DBCC LOGINFO (@.db)
Pay attention on status column . If you see that value=2 is the last row
that means you have 'opened' ',in process' transaction
Perfom dummy inserst on order to move this value=2 at the top and then try
to shrink file
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE
command,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log
Files,
> but how do I do this? I've looked in BOL but I can't find any description
of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>|||Steen
Have a look at these articles.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Regards
John
"Steen Persson" wrote:
> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE command,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log Files,
> but how do I do this? I've looked in BOL but I can't find any description of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>
>|||Hi Uri
Thanks for your reply.
I have actually tried to increase the target size, but that still doesn't
seems to do the trick. Also if I look at the physical file, it is approx. 21
GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 11
% i.e. the file should in theory only be taking up approx. 2 GB. Also if I
run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
records has the status 2. I can't find any describtion in BOL of the LOGINFO
command, so I'm not quite sure how to read it, but does the status 0 means
that it's "old" transactions that can be removed?
It might very well be that I still have some active transcations that
prevents my logfile from shrinking, but it puzzles me that I don't seems to
be able to "see" these transcations anywhere.
Regards
Steen
Uri Dimant wrote:
> Steen
> What target size have you specified in DBCC SHRINKFILE command? Try to
> increase it.
> DECLARE @.db INT
> SELECT @.db=db_id()
> DBCC LOGINFO (@.db)
> Pay attention on status column . If you see that value=2 is the last
> row that means you have 'opened' ',in process' transaction
> Perfom dummy inserst on order to move this value=2 at the top and
> then try to shrink file
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
>> Hi
>> I think I need a little help on Log file architecture.
>> I have a database where I temporarily disabled my log backup
>> schedules. We did some updates on the database, and I didn't wanted
>> the backup jobs to kick in in the middle of the update.
>> Now I've enabled the log backups again, but of course my logfiles
>> has now grown to a size that it will never reach again in normal
>> production. I'd therefore like to shrink the logfile to a more
>> feasible size, but I don't seem to have much luck with that.
>> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE
>> command, I get the message -
>> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are
>> in use.'
>> If I then run DBCC SQLPERF (Logspace) it tells me that log file
>> space used is 0.68127996 %. I've then tried to run a DBCC OPENTRAN
>> to see if I've any open transaction but that reports no active
>> transactions.
>> My question is now, how do I find out that it is that prevents me
>> from shrinking the file. I assume that I'll have to chekc the
>> Virtual Log Files, but how do I do this? I've looked in BOL but I
>> can't find any description of how I can "see" the logfile
>> "structure"? I know that this has been covered in this newsgroup
>> before, but I can't find any threads that helps me right now.
>> Regards
>> Steen|||The file can only be shrunk from the end towards the beginning. You need to get 0 at the end. backup
the log to see if that makes you have 0 at the end. If not, do some dummy modifications so the head
of the log moves toward the beginning (in 2000, the shrink command should do this for you). Then
backup again. When you have 0 at the end, you can shrink it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hi Uri
> Thanks for your reply.
> I have actually tried to increase the target size, but that still doesn't
> seems to do the trick. Also if I look at the physical file, it is approx. 21
> GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 11
> % i.e. the file should in theory only be taking up approx. 2 GB. Also if I
> run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
> records has the status 2. I can't find any describtion in BOL of the LOGINFO
> command, so I'm not quite sure how to read it, but does the status 0 means
> that it's "old" transactions that can be removed?
> It might very well be that I still have some active transcations that
> prevents my logfile from shrinking, but it puzzles me that I don't seems to
> be able to "see" these transcations anywhere.
> Regards
> Steen
>
>
> Uri Dimant wrote:
>> Steen
>> What target size have you specified in DBCC SHRINKFILE command? Try to
>> increase it.
>> DECLARE @.db INT
>> SELECT @.db=db_id()
>> DBCC LOGINFO (@.db)
>> Pay attention on status column . If you see that value=2 is the last
>> row that means you have 'opened' ',in process' transaction
>> Perfom dummy inserst on order to move this value=2 at the top and
>> then try to shrink file
>>
>> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
>> news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
>> Hi
>> I think I need a little help on Log file architecture.
>> I have a database where I temporarily disabled my log backup
>> schedules. We did some updates on the database, and I didn't wanted
>> the backup jobs to kick in in the middle of the update.
>> Now I've enabled the log backups again, but of course my logfiles
>> has now grown to a size that it will never reach again in normal
>> production. I'd therefore like to shrink the logfile to a more
>> feasible size, but I don't seem to have much luck with that.
>> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE
>> command, I get the message -
>> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are
>> in use.'
>> If I then run DBCC SQLPERF (Logspace) it tells me that log file
>> space used is 0.68127996 %. I've then tried to run a DBCC OPENTRAN
>> to see if I've any open transaction but that reports no active
>> transactions.
>> My question is now, how do I find out that it is that prevents me
>> from shrinking the file. I assume that I'll have to chekc the
>> Virtual Log Files, but how do I do this? I've looked in BOL but I
>> can't find any description of how I can "see" the logfile
>> "structure"? I know that this has been covered in this newsgroup
>> before, but I can't find any threads that helps me right now.
>> Regards
>> Steen
>|||Hi Tibor
I've also read that SQL 2000 fills in the "dummy" records when I do the
SHRINKFILE, but I think I've missed something. You say that the dummys are
being inserted by the SHRINKFILE command and then I have to backup again and
then I can run the SHRINKFILE once more. I haven't got that little "twist"
until now, so it sounds like it's the 2nd. backup I'm missing. When I think
about it I should have thought about that, but from BOL I had got the
impression that SQL2000 did it "behind the scenes".
After I've done the backup once more, it works like a charm....:-).
Thanks for your inputs everybody...
Regards
Steen
Tibor Karaszi wrote:
> The file can only be shrunk from the end towards the beginning. You
> need to get 0 at the end. backup the log to see if that makes you
> have 0 at the end. If not, do some dummy modifications so the head of
> the log moves toward the beginning (in 2000, the shrink command
> should do this for you). Then backup again. When you have 0 at the
> end, you can shrink it.
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx.gbl...
>> Hi Uri
>> Thanks for your reply.
>> I have actually tried to increase the target size, but that still
>> doesn't seems to do the trick. Also if I look at the physical file,
>> it is approx. 21 GB. When running DBCC SQLPERF(LogSpace) it tells me
>> that space in use is 11 % i.e. the file should in theory only be
>> taking up approx. 2 GB. Also if I run DBCC LOGINFO it gives me 1641
>> records, and only the last approx 200 records has the status 2. I
>> can't find any describtion in BOL of the LOGINFO command, so I'm not
>> quite sure how to read it, but does the status 0 means that it's
>> "old" transactions that can be removed?
>> It might very well be that I still have some active transcations that
>> prevents my logfile from shrinking, but it puzzles me that I don't
>> seems to be able to "see" these transcations anywhere.
>> Regards
>> Steen
>>
>>
>> Uri Dimant wrote:
>> Steen
>> What target size have you specified in DBCC SHRINKFILE command? Try
>> to increase it.
>> DECLARE @.db INT
>> SELECT @.db=db_id()
>> DBCC LOGINFO (@.db)
>> Pay attention on status column . If you see that value=2 is the last
>> row that means you have 'opened' ',in process' transaction
>> Perfom dummy inserst on order to move this value=2 at the top and
>> then try to shrink file
>>
>> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
>> news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
>> Hi
>> I think I need a little help on Log file architecture.
>> I have a database where I temporarily disabled my log backup
>> schedules. We did some updates on the database, and I didn't wanted
>> the backup jobs to kick in in the middle of the update.
>> Now I've enabled the log backups again, but of course my logfiles
>> has now grown to a size that it will never reach again in normal
>> production. I'd therefore like to shrink the logfile to a more
>> feasible size, but I don't seem to have much luck with that.
>> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE
>> command, I get the message -
>> 'Cannot shrink log file 2 (xxxxxx) because all logical log files
>> are in use.'
>> If I then run DBCC SQLPERF (Logspace) it tells me that log file
>> space used is 0.68127996 %. I've then tried to run a DBCC OPENTRAN
>> to see if I've any open transaction but that reports no active
>> transactions.
>> My question is now, how do I find out that it is that prevents me
>> from shrinking the file. I assume that I'll have to chekc the
>> Virtual Log Files, but how do I do this? I've looked in BOL but I
>> can't find any description of how I can "see" the logfile
>> "structure"? I know that this has been covered in this newsgroup
>> before, but I can't find any threads that helps me right now.
>> Regards
>> Steen

Friday, March 30, 2012

How do I make a view read-only

How do I make a view read-only

Wednesday, March 28, 2012

how do i look through the log to see updates to tables in sql sever 2005?

How do I know when a patch is available for SQL Server?

How do I know when a patch is available for SQL Server?

Friday, March 9, 2012

How do I find out what prevents my log file from being truncated

How do I find out what prevents my log file from being truncated

How do I find out what prevents my log file from being truncated

display SQL Server

Blog Archive

About Me