Compression

Hi everyone,
I am developing a web app and wanting to store documents (doc, xls,
pdf, ppt...etc.) within the DB (MSSQL). Is it advisable in general (I
don't know exactly how big the docs are going to be) to compress the
files before putting them into the DB? How does this affect Full Text
Searching? On a wider note what's the attitude of people in terms of
storing documents in the DB versus on the file system. There seems to
be a lot of differing opinions. Any links to resources would be most
appreciated.
Thanks for your help in advance.
Jose
Jose,
First of all, can I assume that you are using SQL Server 2000? If so, could
you post the full output of: SELECT @.@.version -- as this is very help info
in understanding SQL FTS issues.
Secondly, what exactly do you mean by "compress[ing] the files before
putting them into the DB?" Do you mean to store them as ZIP files in columns
defined with the IMAGE datatype? or are you thinking about placing the FT
Catalog folder (SQL00060005) & files on a compressed drive. As for the
latter, I've not done any testing with compressed drive, but Hilary Cotter
has and from past postings on this subject, he's indicated that the
performance gains is mimumal. As for the former, you will need to purchase a
3rd Party IFilter for the WinZIP ZIP file format and then assuming you are
using SQL Server 2000, you can store the zip files in an IMAGE datatype and
define a "file extension" column that will have the value of "zip" and the
ZIP IFilter will be called by the MSSearch and the mssdmn (Search Filter
Daemon) processes to FT Index the contents of the zip file that contains MS
Word documents. You can purchase the ZIP IFilter from either of the
following two vendors:
http://www.4-share.com/content/products.htm
http://www.ifiltershop.com/zipfilter.html
I've personally have been using the 4-Share ZIP IFilter for over a year and
find it works very well for me.
As for "storing documents in the DB versus on the file system", this is very
much a open and very actively discussed subject, below is one past posting
I've made on this subject with links included...
"There are many issues related to storing the files on disk (faster access,
easy copy/move, etc.) vs. storing the files in the database (transaction
control, change control, audit of changes, etc) than just efficiency and
scalability, although, those are important points... There is a ASPFAQ
website that has a number of references
(http://www.aspfaq.com/show.asp?id=2149) and links to KB articles (I've not
checked them all), but one major reason for storing the files in a SQL
Server table's column defined with an IMAGE datatype, is that in SQL Server
2000 you can take advantage of the new Full-Text Search (FTS) feature to FT
Index the contents of supported file types, primarily MS Office, HTML and
3rd party IFilter's like Adobe's PDF files.
As this is one of those FAQ type questions that have been known to start
religious wars or at the very least a flame war, and while Sharon didn't ask
about other application issues that this question often generates as it is
often an open-ended question, i.e., one that never seems to be answered to
everyone's satisfactions as it usually "depends" upon the application or
upon how one defines the word "best"...
As for the Terra Server, checkout the "about page"
(http://terraserver-usa.com/about.aspx?n=AboutBody) on the site that
explains how MS and others did it and yes, they used the IMAGE datatype,
specifically
http://terraserver-usa.com/about.asp...utTechDbschema (The Imagery table
contains the "blob" [image] field where the imagery data is stored.
Jose, depending upon how many files you have, how frequently they change,
how large they are, what the app is, etc. you may want to review the Terra
Server web site and consider the following rule of thumb that they used:
< 1 million images or big images (> 1MB) put them in the file system.
> 1 million and < 1 MB images, put them in SQL Server.
Note, you can also use "Text-in-Row" if the files are >7000 bytes on avg.
For everything in between, either way will work, depending upon if you need
transactional control over your files. Additionally, if you store the files
in a TEXT or IMAGE column, you can also store related metadata about that
file in SQL Server as well for increased searching capabilities. Also, and
obviously with SQL Server you get built-in support for validating the
consistency of the database, indices, backup, restore, etc. As for loading
&/or extracting the files from SQL Server there are now many methods of
doing this via BCP, BULK INSERT as well as ADO Stream DTS too, if you need
to transform your files in some manner.
Hope this helps, as the primary consideration should always be what is best
for your application...
Regards,
John
"Jose Perez" <jlpv@.totalise.co.uk> wrote in message
news:3724a9d9.0404141212.480e1630@.posting.google.c om...
> Hi everyone,
> I am developing a web app and wanting to store documents (doc, xls,
> pdf, ppt...etc.) within the DB (MSSQL). Is it advisable in general (I
> don't know exactly how big the docs are going to be) to compress the
> files before putting them into the DB? How does this affect Full Text
> Searching? On a wider note what's the attitude of people in terms of
> storing documents in the DB versus on the file system. There seems to
> be a lot of differing opinions. Any links to resources would be most
> appreciated.
> Thanks for your help in advance.
> Jose
|||John,
Thanks a lot for the detailed reply which you gave - it is most
appreciated. In answer to your question, yes I am running SQL Server
2000 (Version Information - Microsoft SQL Server 2000 - 8.00.760
(Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft
Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)).
To clarify, I meant storing documents as "ZIP files in columns defined
with the IMAGE datatype". Thanks for the URLs regarding the ZIP
IFilter - I will investigate them. Your post was very helpful -
thanks.
Jose
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#qtcv8pIEHA.964@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> Jose,
> First of all, can I assume that you are using SQL Server 2000? If so, could
> you post the full output of: SELECT @.@.version -- as this is very help info
> in understanding SQL FTS issues.
> Secondly, what exactly do you mean by "compress[ing] the files before
> putting them into the DB?" Do you mean to store them as ZIP files in columns
> defined with the IMAGE datatype? or are you thinking about placing the FT
> Catalog folder (SQL00060005) & files on a compressed drive. As for the
> latter, I've not done any testing with compressed drive, but Hilary Cotter
> has and from past postings on this subject, he's indicated that the
> performance gains is mimumal. As for the former, you will need to purchase a
> 3rd Party IFilter for the WinZIP ZIP file format and then assuming you are
> using SQL Server 2000, you can store the zip files in an IMAGE datatype and
> define a "file extension" column that will have the value of "zip" and the
> ZIP IFilter will be called by the MSSearch and the mssdmn (Search Filter
> Daemon) processes to FT Index the contents of the zip file that contains MS
> Word documents. You can purchase the ZIP IFilter from either of the
> following two vendors:
> http://www.4-share.com/content/products.htm
> http://www.ifiltershop.com/zipfilter.html
> I've personally have been using the 4-Share ZIP IFilter for over a year and
> find it works very well for me.
> As for "storing documents in the DB versus on the file system", this is very
> much a open and very actively discussed subject, below is one past posting
> I've made on this subject with links included...
> "There are many issues related to storing the files on disk (faster access,
> easy copy/move, etc.) vs. storing the files in the database (transaction
> control, change control, audit of changes, etc) than just efficiency and
> scalability, although, those are important points... There is a ASPFAQ
> website that has a number of references
> (http://www.aspfaq.com/show.asp?id=2149) and links to KB articles (I've not
> checked them all), but one major reason for storing the files in a SQL
> Server table's column defined with an IMAGE datatype, is that in SQL Server
> 2000 you can take advantage of the new Full-Text Search (FTS) feature to FT
> Index the contents of supported file types, primarily MS Office, HTML and
> 3rd party IFilter's like Adobe's PDF files.
> As this is one of those FAQ type questions that have been known to start
> religious wars or at the very least a flame war, and while Sharon didn't ask
> about other application issues that this question often generates as it is
> often an open-ended question, i.e., one that never seems to be answered to
> everyone's satisfactions as it usually "depends" upon the application or
> upon how one defines the word "best"...
> As for the Terra Server, checkout the "about page"
> (http://terraserver-usa.com/about.aspx?n=AboutBody) on the site that
> explains how MS and others did it and yes, they used the IMAGE datatype,
> specifically
> http://terraserver-usa.com/about.asp...utTechDbschema (The Imagery table
> contains the "blob" [image] field where the imagery data is stored.
> Jose, depending upon how many files you have, how frequently they change,
> how large they are, what the app is, etc. you may want to review the Terra
> Server web site and consider the following rule of thumb that they used:
> < 1 million images or big images (> 1MB) put them in the file system.
> Note, you can also use "Text-in-Row" if the files are >7000 bytes on avg.
> For everything in between, either way will work, depending upon if you need
> transactional control over your files. Additionally, if you store the files
> in a TEXT or IMAGE column, you can also store related metadata about that
> file in SQL Server as well for increased searching capabilities. Also, and
> obviously with SQL Server you get built-in support for validating the
> consistency of the database, indices, backup, restore, etc. As for loading
> &/or extracting the files from SQL Server there are now many methods of
> doing this via BCP, BULK INSERT as well as ADO Stream DTS too, if you need
> to transform your files in some manner.
> Hope this helps, as the primary consideration should always be what is best
> for your application...
> Regards,
> John
>
> "Jose Perez" <jlpv@.totalise.co.uk> wrote in message
> news:3724a9d9.0404141212.480e1630@.posting.google.c om...

Complex DB Search Forms (Store Proc vs. Complex Where)

I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.

Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.

I build clauses like this (i.e., 4 fields shown):

SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )

My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.You can achieve the exact same result either way you do it. It is more object oriented to pass the parameters into the SP and then build your WHERE clause. Are you good at TSQL? If so then why not put them in a SP. If you have never written an SP before then stay with what you know and write the code in your page.

I have done it both ways many many many times, it all depends on the situation. You will have to write the same code in the SP than where it is now, just different syntax.

HTH|||Ok, that makes me feel better. I know it's generally prefered to use SP, but I've never written one. If I did make one, I guess it'd be like a function with 15 parameters?

I'm doing enough learning with ASP.NET, so I'll hold off on SP's for now.

I'll have to find me a good SP book. Performance isn't an issue now, but hopefully it will be!|||You are not really giving up that much performance. My applications generally use very large databases (11 million+ records) with very complex and dynamic Where clauses. If all tables are indexed properly then the performance difference is not very evident.

Use your current constraints as your guide (time, etc.).|||If you are curious how this issue was resolved at this ASP.NET Forum (which we are all posting at now), I am posting below the stored procedure used to search the fourms. You can see that a parameter @.SearchTerms is used to hold the whole where clause. I like this way because it is easier to build dynamically the where clause in c# or vb.net and then make use of the better performance that sp provides. Furthermore this sp below gives you the ability to display only a given number of results (very convenient to use with a datagrid with paging)

CREATE PROCEDURE forums_GetSearchResults
(
@.SearchTerms nvarchar(500),
@.Page int,
@.RecsPerPage int,
@.UserName nvarchar(50)
)
AS
CREATE TABLE #tmp
(
ID int IDENTITY,
PostID int
)
DECLARE @.sql nvarchar(1000)
SET NOCOUNT ON
SELECT @.sql = 'INSERT INTO #tmp(PostID) SELECT PostID ' +
'FROM Posts P (nolock) INNER JOIN Forums F (nolock) ON F.ForumID = P.ForumID ' +
@.SearchTerms + ' ORDER BY ThreadDate DESC'
EXEC(@.sql)
-- ok, all of the rows are inserted into the table.
-- now, select the correct subset
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)
DECLARE @.MoreRecords int
SELECT @.MoreRecords = COUNT(*) FROM #tmp -- WHERE ID >= @.LastRec

-- Select the data out of the temporary table
IF @.UserName IS NOT NULL
SELECT
T.PostID,
P.ParentID,
P.ThreadID,
P.PostLevel,
P.SortOrder,
P.UserName,
P.Subject,
P.PostDate,
P.ThreadDate,
P.Approved,
P.ForumID,
F.Name As ForumName,
MoreRecords = @.MoreRecords,
Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
P.Body,
P.TotalViews,
P.IsLocked,
HasRead = 0 -- not used
FROM
#tmp T
INNER JOIN Posts P (nolock) ON
P.PostID = T.PostID
INNER JOIN Forums F (nolock) ON
F.ForumID = P.ForumID
WHERE
T.ID > @.FirstRec AND ID < @.LastRec AND
(P.ForumID NOT IN (SELECT ForumID from PrivateForums) OR
P.ForumID IN (SELECT ForumID FROM PrivateForums WHERE RoleName IN (SELECT RoleName from UsersInRoles WHERE username = @.UserName)))
ELSE
SELECT
T.PostID,
P.ParentID,
P.ThreadID,
P.PostLevel,
P.SortOrder,
P.UserName,
P.Subject,
P.PostDate,
P.ThreadDate,
P.Approved,
P.ForumID,
F.Name As ForumName,
MoreRecords = @.MoreRecords,
Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
P.Body,
P.TotalViews,
P.IsLocked,
HasRead = 0 -- not used
FROM
#tmp T
INNER JOIN Posts P (nolock) ON
P.PostID = T.PostID
INNER JOIN Forums F (nolock) ON
F.ForumID = P.ForumID
WHERE
T.ID > @.FirstRec AND ID < @.LastRec AND
P.ForumID NOT IN (SELECT ForumID from PrivateForums)

SET NOCOUNT OFF|||::You can achieve the exact same result either way you do it.

No, not EXACTLY the same result.

::It is more object oriented to pass the parameters into the SP and then build your WHERE
::clause.

Hmpf. Please dont tell me you believe this.

I mean, frankly, in how far is it more OBJECT ORIENTED to actually write a non-objectoriented METHOD (an SP is nothing more)?

More strucutre, maybe, better practice, maybe, but not more "object oriented".

Some negatives:

* Be sure to have automatic recompile switched on for this SP, as otherwise you just say goodbye to performance for your type of query.

Let see the last post:

::Furthermore this sp below gives you the ability to display only a given number of results
::(very convenient to use with a datagrid with paging)

Yea, righ. It is not exactly like you could not do it right in dynamic SQL either. Frankly, the correct way to ask for only X result is "SELECT TOP X". I somehow miss this on the SP.

* YOu dont need a temp tabl. Use query materialization to use actually use the optimizer. The use of a temp table is about the last way I would have tried to solve this.
* Frankly, the approach of selecting ALL posts into a temp table, just to then only select a subset of this sounds horrible performance wise.
* The "IF" makes not really a lot of sense - the two cases can be put into one pretty easily.|||>>No, not EXACTLY the same result.

Is the end result not a Query ?|||thona,

perhaps you are right about the IF statement. I think this stored procedure together with the ASP.NET Forums are written by a microsoft team (so it says in the agreement that comes with the free download of the forums).

Perhaps there is a better way. could you please give example of "Use query materialization to use actually use the optimizer" because I am not very familiar with this technique?

SELECT TOP ... will only work the first time you select from the database. but how would you handle displaying next result sets?
That is why the proc takes @.page and @.recsperpage parameter in order to determine from which to which result to return. If the user sees 10 results per page and he is requesting the second result set then the proc will calculate @.FirstRec and @.LastRec. But you cannot use these two in a querry against the whole database. you can apply them in a second select only against the relevant results.

If I am wrong, I will be grateful if you help me understand my mistake as efficient searching is very important indeed

Complete Newbie Question

I have just installed Reporting Services and created a few reports that
I want to run in a web page - that seems easy enough to do but my boss
has asked me to provide a batch reporting solution.
He wants users to be able to request a report and the request be
written away to a sql db. I have a windows service that picks up all
new requests and fires up some .exe's depending on what sort of request
it is e.g password reset , password reminder etc..
When the reports are run I need them to be saved as PDF's into a
specific folder that can be accessed later from a web page.
Now all this is written and working using ActiveReports for .NET but we
hate that package and want to use sql reporting services. Can it be
done in sql reporting services and if so how do I do it - I mean I need
some real code examples
any help would be brilliant
cheers
JimI have been using log4net to log to SQL DB. You can log the web request when
reports are rendered. It is upto you to write the logic to who views the
page( for eg using session object who the user was like,
Session["UserName"]). log4net allows you to log when certain events are
triggered blah blah... Check out:
http://logging.apache.org/log4net/release/config-examples.html
Look for ADONETAppender which is what you want.
HTH
Rajesh Meenrajan
MCSD.NET
http://meenrajan.blogspot.com
"JimW13UK" wrote:
> I have just installed Reporting Services and created a few reports that
> I want to run in a web page - that seems easy enough to do but my boss
> has asked me to provide a batch reporting solution.
> He wants users to be able to request a report and the request be
> written away to a sql db. I have a windows service that picks up all
> new requests and fires up some .exe's depending on what sort of request
> it is e.g password reset , password reminder etc..
> When the reports are run I need them to be saved as PDF's into a
> specific folder that can be accessed later from a web page.
> Now all this is written and working using ActiveReports for .NET but we
> hate that package and want to use sql reporting services. Can it be
> done in sql reporting services and if so how do I do it - I mean I need
> some real code examples
> any help would be brilliant
> cheers
> Jim
>

compilation error while adding web reference RS05

hi,
if i try to add a web reference in my c# application to reporting services
2005 i got a compilation error.
a part of the displayed message:
--
source error:
line 4: <%@. Import Namespace="System.Xml" %> <-- is a red line
error message:
CS0234: The type or namespace name 'Xml' does not exist in the namspace
'System' (are you missing an assembly reference?
do anybody know a solution for this problem?
thx
MartinTake a look at this forum:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=10847
particularly the post from AkhilaL MSFT
Jens Konerow

compatibility question

just a question....

my web project is using SQL Express2005 and ASP.NET and C#.
my web hosting company only have MSsql2000. would there be any conflict with regards to my database? im sorry if i sound dumb. im a newbie to this.

thanks a lot!
There will not be a conflict but you may have to make code changes, there are a fair number of features in SQL Express that will not work with SQL 2000. I suggest downloading a copy of MSDE for yourself(which is free) so you can test before going live with the hoster.|||i am only using one mdf file and my asp.net program just deals with the common add, edit, delete transactions. would these transactions differ from version to version of MSSQL?

honestly i did not think there would be a problem coz these transactions are very common. pls correct me if i am wrong.
|||

It depends. Are you using user instances(its in the connection string)? are you using varchar(max), xml types? Are you using the new security features?

Any of the above will prevent the app from working on a sql server 200 machine.

|||

Euan Garden wrote:

It depends. Are you using user instances(its in the connection string)? are you using varchar(max), xml types? Are you using the new security features?

Any of the above will prevent the app from working on a sql server 200 machine.

this is my connection string:
<add name="conn_name" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbase_name.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

sql2000 doesnt support user instance, does it?

__
i just installed MSDE to make a test. i tried attaching my mdf file (created by SQL Express) to the MSDE but i got this error message:

"An error occured when attaching the database"

i attached that mdf file to the SQL Express and i encountered no problem at all. do you think i will encounter the same problem with my hosting company?

thanks for your time Euan.|||

2 issues here;

1/ MSDE does NOT support user instances so you need to change your conection string to a regular one.

2/ You are trying to attach a SQL2005 db to a SQL2000 machine, this will not work, you will need to move the data and schema onto a SQL Server 2000 implementation.

|||can I implement the membership control (provided by ASP.NET) on MSDE? i can try migrating from SQLEXxpress to MSDE but im not sure if membership control works on that MSDE. sorry if i really sound dumb. im a newbie.|||There is a tool that comes with ASP.Net that supports installation of the databases, aspnet_regsql.exe is its name, check and see if it can install the tables for membership control, I believe it supports SQL 2000 ie MSDE.|||

Euan Garden wrote:

There is a tool that comes with ASP.Net that supports installation of the databases, aspnet_regsql.exe is its name, check and see if it can install the tables for membership control, I believe it supports SQL 2000 ie MSDE.

i just tried it and it installed all the membership tables using this MSDE.
thanks for the help Euan Garden.

Compatability : SQL Server 2005 Management Studio

Hello Everyone,

I have a Web Host which uses SQL Server 2000. I can use their interface and make tables and procedures, but I wanted to use some sort of graphical tool...Is it possible to use SQL Server 2005 Studio with SQL 2000.

I'm really new to SQL Server as my past experience is largely with Oracle....and there for the same purpose I can use SQL Navigator or TOAD....

IF SQL Server 2005 Studio wont work can someone please suggest a tool I can use to create tables and procedures on the remote server..

Thanks,

Harsimrat

Hi,

yes you can use SQL Server 2005 Management Studio for administring SQL2k databases, I do that also with my host databases in the web. If you don′t have SQL Server 2005 Management Studio you can also use one of the tools mentioned here:

http://www.aspfaq.com/2442

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Awesome,

Thanks

Harsimrat