Block a truncate command

Is there any way to block a truncate command on a table via a trigger or
something else in MS SQL Server 2000?
Thanks,
--
JasonHere's a quote from Books Online:
"TRUNCATE TABLE permissions default to the table owner, members of the
symin fixed server role, and the db_owner and db_ddladmin fixed database
roles, and are not transferable."
Are you worried about the above users executing a TRUNCATE TABLE command?
Also note that this command cannot be run against a table that is referenced
by foreign key.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:9E2D92A7-C397-4538-9574-1133F9B37B33@.microsoft.com...
> Is there any way to block a truncate command on a table via a trigger or
> something else in MS SQL Server 2000?
> Thanks,
> --
> Jason|||If you don't want the table truncated, then don't issue the truncte in the
first place.
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:9E2D92A7-C397-4538-9574-1133F9B37B33@.microsoft.com...
> Is there any way to block a truncate command on a table via a trigger or
> something else in MS SQL Server 2000?
> Thanks,
> --
> Jason|||No.
Try better user/permissions management.
Do you have people that have been given symin or dbo privileges and are
going to go in and issue unauthorized TRUNCATE commands? I think you need
to fix that problem instead of trying to patch the symptom...
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:9E2D92A7-C397-4538-9574-1133F9B37B33@.microsoft.com...
> Is there any way to block a truncate command on a table via a trigger or
> something else in MS SQL Server 2000?
> Thanks,
> --
> Jason|||I want them to be able to truncate some tables, but not others.
--
Jason
"Aaron Bertrand [SQL Server MVP]" wrote:

> No.
> Try better user/permissions management.
> Do you have people that have been given symin or dbo privileges and are
> going to go in and issue unauthorized TRUNCATE commands? I think you need
> to fix that problem instead of trying to patch the symptom...
>
>
> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
> news:9E2D92A7-C397-4538-9574-1133F9B37B33@.microsoft.com...
>
>|||But only the table owner can truncate anyhow. Are you saying that these user
s act as dbo, the object
owner, db_owner or symin in your system?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:76E0D204-6026-4BDB-8B54-F384FF67556A@.microsoft.com...
>I want them to be able to truncate some tables, but not others.
> --
> Jason
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

block a parameter

I have parametro call client, who is a list of clients, taken from the data
base to traves of a query, access the report through the URL, by means of
Internet to explorer, in some cases desire of passing parameter clients
already selected and who the user who is seeing the report cannot modify it,
As blockade the parameter?
--
ING. JOSE DAVID GALVIZ MUÑOZ
MCAD
DCE Tercera EstrellaHi Jose,
You can, either when designing the report or through Report Manager set the
parameter to No prompt. When you do this, the parameter will not be
displayed to the user, so the user will not be able to change the value.
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"José David Galviz M" <joda_26@.hotmail.com> wrote in message
news:Okp1lnQcEHA.644@.tk2msftngp13.phx.gbl...
>I have parametro call client, who is a list of clients, taken from the data
> base to traves of a query, access the report through the URL, by means of
> Internet to explorer, in some cases desire of passing parameter clients
> already selected and who the user who is seeing the report cannot modify
> it,
> As blockade the parameter?
> --
> ING. JOSE DAVID GALVIZ MUÑOZ
> MCAD
> DCE Tercera Estrella
>|||Thanks
--
ING. JOSE DAVID GALVIZ MUÑOZ
MCAD
DCE Tercera Estrella
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> escribió en el
mensaje news:O%23bJnl1cEHA.1000@.TK2MSFTNGP12.phx.gbl...
> Hi Jose,
> You can, either when designing the report or through Report Manager set
the
> parameter to No prompt. When you do this, the parameter will not be
> displayed to the user, so the user will not be able to change the value.
> -Lukasz
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "José David Galviz M" <joda_26@.hotmail.com> wrote in message
> news:Okp1lnQcEHA.644@.tk2msftngp13.phx.gbl...
> >I have parametro call client, who is a list of clients, taken from the
data
> > base to traves of a query, access the report through the URL, by means
of
> > Internet to explorer, in some cases desire of passing parameter clients
> > already selected and who the user who is seeing the report cannot modify
> > it,
> > As blockade the parameter?
> >
> > --
> > ING. JOSE DAVID GALVIZ MUÑOZ
> > MCAD
> > DCE Tercera Estrella
> >
> >
>

blobs?

I have searched high and low for this answer, but still to no avail...

Does anyone have a reliable way of storing serialized data into a table which does not use direct SQL on the client side? I am using Stored Procedures and simply need to be able to read out of the table the object written in.

Inserting appears fine, but reading only seems to come back with 16 bytes of data and I just can't figure out why. Whats the recommended way of doing this?

Matt.You're not reading the byte array properly. See if this article helps:

http://www.dotnetbips.com/displayarticle.aspx?id=60|||just thought I would let everyone know that I solved my serialization problem...

...I was creating a SqlParameter object with a SqlDbType.Image and giving it a size of 16, which meant every time I performed a DB op with that entry I got no more than 16 bytes of data back. very annoying.

anyway, Iv'e removed that from the new SqlParameter() and it works.

I had even read in various places that it's not possible to do serialization to the db using things like SQL, but I may have been just very paranoid by then...

cheers folks,
m.

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
David Portas|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).

> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
David Portas
|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).

> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

Blobs: File System or DB?

The conventional wisdom on where best to store large binary objects used to
be to store them on the file system because they were slowly processed in
the database. Is this still the case with the latest SQL Server?
We are designing a new system that will store many thousands of TIF files.
We would like to store them in the database because they would then be
coordinated with Backup, Restore, and Transaction issues. How much slower
is the processing of varbinary fields in the db versus the file system?
Thanks,
T"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:Oy9qbdKSIHA.6060@.TK2MSFTNGP05.phx.gbl...
> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
> Thanks,
> T
>
The following article discusses file system storage vs SQL Server 2005
BLOBs:
ftp://ftp.research.microsoft.com/pub/tr/TR-2006-45.pdf
In SQL Server 2008 there is a new feature called FILESTREAM that directly
addresses the problem. FILESTREAM permits both SQL and file-system access to
binary data while managing the files as if they were part of the database
for backup and transaction control purposes. From my own experience I have
found that FILESTREAM can outperform file-system access for files larger
than about 2MB (YMMV).
http://msdn2.microsoft.com/en-us/library/bb895234(SQL.100).aspx
--
David Portas|||> The conventional wisdom on where best to store large binary objects used
> to be to store them on the file system because they were slowly processed
> in the database. Is this still the case with the latest SQL Server?
If you mean SQL Server 2005, then yes; if you mean SQL Server 2008, then
no... as David pointed out, FILESTREAM will address part of this problem
(but I am a little skeptical about performance of backups, concurrency, and
translation layer).
> We are designing a new system that will store many thousands of TIF files.
> We would like to store them in the database because they would then be
> coordinated with Backup, Restore, and Transaction issues. How much slower
> is the processing of varbinary fields in the db versus the file system?
Naturally I would expect a layer of overhead, depending on the number and
size of files you have, in translating the data back and forth between the
database and the application(s).
But, more importantly, and again depending on the number and size of files
you have, I am very sensitive to using expensive disk. I would be very
careful to place the files in a filegroup which is not on a very expensive
SAN (which your database might be using). High performance, fault-tolerant
disk space is very expensive, and it seems a waste to take this space away
from your database to use for, essentially, flat files. If you are using
locally attached storage then this isn't really an issue, but if the file
usage is going to be large, you might consider using a less expensive array
for the files themselves. (You can always keep backups of directory
structures as well, it;'s just very difficult to keep them point-in-time in
sync with the database.) Also keep this in mind if you use a 3rd party for
database hosting; they typically charge a lot more per TB of database
storage, versus flat file storage, so in that case make sure you weigh the
benefits you get from synchronizing your files and data, versus the hit it
takes on your budget.
A

BLOBS vs. regular files

Over the weekend I was told that my SQL Server was going to start doubling as
a file server. I guess now the other option is to store files as BLOBS. I
have no experience with either scenario. Can anyone give me the procs/ cons
of doing it either way?
--
TIA,
ChrisRChris,
If they meant a file server for Word documents, executables, and so forth,
then the SQL Server will not be any help to you. You will have a server
running the SQL Server process and also hosting file services. This will
affect how you configure resources on the server since it will no longer be
dedicated.
If you are going to be getting only files associated with a database, then
there are plenty of discussions and opinions.
Try Googling 'blob file sql versus' and read the several hundred comments.
:-)
Here is a link to the thinking behind what the TerraServer guys did.
www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx
RLF
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR|||The pro for BLOBs is very simple, the "files" are part of the database, are
kept in sync transactionally, participate in the backups, and can be
processed by SQL. In other words, they have all the traditional benefits of
a database system.
The cons are more extensive. They don't appear in the filesystem namespace.
Before they can be processed by a regular program they have to be copied
into a temporary file, they aren't structured as efficiently as files for
bulk copying (and can't be directly copied by kernel mode services). In
other words, performance is generally poor by comparison.
Now as a database guy I'd be inclined to store just about everything in the
database, get the management/integrity benefits, and give up some
performance. But realistically
usage would dictate which model I would use. The more the object will need
to be processed by an external file-based application the more likely I'd be
to store it in the file system. The more important it was to keep it
tightly bound to the data in the database system the more likely I'd be to
store it in a blob. For example, say you want to store an employee's photo
with their personnel data. I'd store that in a Blob in the database.
But if I were creating a library of photographs that were going to be
primarily accessed by Photoshop, and the purpose of the database was to make
searching etc easier, then I'd probably keep the photographs in the
filesystem with pointers from the database. Of course, then I'd have to
create more complex backup/resore procedures for my shop.
--
Hal Berenson, President
PredictableIT, LLC
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8BCD21FE-97EC-4B1E-84FA-57A40844A4A1@.microsoft.com...
> Over the weekend I was told that my SQL Server was going to start doubling
> as
> a file server. I guess now the other option is to store files as BLOBS. I
> have no experience with either scenario. Can anyone give me the procs/
> cons
> of doing it either way?
> --
> TIA,
> ChrisR