Datasets, searching, complex queries.

Alright just starting out in ASP.NET and it's making my head spin, but I think I'm getting it.

My schema in brief : Images, Categories, and a table in the middle so we can have a many-to-many relationship between images and categories. My issue is searching. I can search by keyword, and limit it to categories. So, in pseudo sql..

SELECT ... WHERE keyword LIKE '%test%' and category_id in (1,5,20,66);

I made a variable for the keyword no problem. But how can I get this dynamic list? Or, is there another way about going this problem?

Where will you put your query in? StoredProcedure or Web? I think my two posts help you:

http://forums.asp.net/t/1162993.aspx

http://forums.asp.net/t/1158548.aspx

|||

I've been informed by the big shark guy that I recommend D-SQL a lot, but you are asking for it so here goes:

First off... beware SQL injection. Strip out ' and - from your search text. Do without.

CREATE PROCEDURE sp_search @.keyword varchar(50),
@.categorylist varchar(150) as

declare @.sql varchar(max)

set @.sql = 'SELECT ... WHERE keyword LIKE ''%' + @.keyword + '%'' and category_id in (' + @.categorylist + ')' -- BTW those are double single quotes, not double quotes

EXEC sp_executesql @.sql, N'@.keyword varchar(50), @.categorylist varchar(150)'

then call sp_search in your code, adding the keyword and category list strings as parameters. Does that help?

|||

che : i'll read those links now

Charles : Interesting. Will I be able to accomplish something like this with Oracle? That looks like SQL Server to me?

|||

Oh. Most folks here are SQL Server. I'm sure you can do dynamic SQL in oracle though - my boss who taught me D-SQL co-authored the Oracle Designer/2000 Handbook. The sp_executesql is a SQL serber built-in sp, and I don't know what the equivalent is in oracle:

http://lbdwww.epfl.ch/f/teaching/courses/oracle8i/server.815/a68022/dynsql.htm

|||

This is a little disappointing. Dynamic SQL seems really overkill for such a trivial problem.

Perhaps I should look at not using strongly typed Datasets? Build one on the fly? Would that be different/better?

|||

Many of the problems were have these days tend to be ones caused by the complexity of the frameworks we are working in. For example, to write a web page, I have to know C# (or VB), ASP, HTML, SQL, and .Net, and how they all interrelate. For one page. It used to be that all you needed was a front end and a database. Oh well, we get a lot in terms of scalability and functionality out of this stuff too - the complexity is market driven.

All philosophy aside, it seems to me that most searches are done with D-Sql. If you really want to make a mountain out of a molehill, you could create a search object that safely builds your query in C# and then access it from your search front end. The advantage of that would be that you could get a lot of re-use in the future. Searching is a non-trivial issue. The volume of work that has gone into searching makes it difficult to mine for simple one-off solutions.

sql

Database vs Schema

We currently have a product in which each client has their own Database. We adjust the connection when a user for that client logs into the system. This system has continued to grow and a good pace, but we have come to a point where failover is taking too long.

Refactoring the Database to handle multiple sites in a single database is not an option because of the time it would take to make the change. So, we are looking for another way in which this could be handle. One idea is to take multiple clients and place them in a single database using a schema to seperate them. (ex. Client A = server1.db1.schema1, Client B = server1.db1.schema2, etc).

Is there another option that would be better, or what kind of performance can we expect if we follow this path? Or, is there a way to decrease the failover time (it appears the problem is the startup of the database on the failover server)?

Thad

You are right that having a large number of databases will increase your fail-over time in a fail-over cluster.

Since you mentioned schemas, you must be on SQL Server 2005. For fail-over time, database mirroring is much faster than fail-over clustering. Database mirroring does require twice the storage space, and the licensing situation is not good compared to an Active/Passive cluster. You would have to mirror each database individually.

Are you failing-over for maintenance, rolling upgrades, etc.? Maintaining and adding dozens of schema in a database would be cumbersome and prone to error. It also has some performance drawbacks, since if you don't properly schema qualify a SP name (from code), or a table name (in an SP), you will have name resolution problems and cache misses.

Depending on the numbers of clients you are talking about, I would think about getting another cluster to split the load.

|||

Currently the failover system is done for disaster recovery scenarios. As for performance. All our inline sql from the client class generate sql similar to

Code Snippet

SELECT [Schema].[Table].[Field] FROM [Schema].[Table]

While a stored procedure is set is setup so it exists in each schema, but the schema name is not used in the sproc body. This is the same method we would use with Views and Functions.

Code Snippet

CREATE PROCEDURE [Schema].[ProcedureName](@.Param INT) AS

BEGIN

SELECT * FROM [Table]

END

Would setting up the schema in this way cause these performance drawbacks. I am thinking that maintaining multiple schema will be as error prone as setting up the single db for each client.

Currently we are talking about approx 1000+ clients.

|||

If your client code can pick up the schema name dynamically from a config file or something, then having a schema for each client as opposed to a database for each client will probably be not much different from a maintenance and configuration perspective.

There may be some negative effects on your buffer cache and procedure cache from this approach. Instead of having one large, normalized table that holds data for all your clients in a single database, you will have one table for each client. You will have multiple copies of the same SP (for different schemas) in your procedure cache.

|||Currently the client already selects the Database so we would just need to add a schema selector to the data selection. So all that is straight forward. And yes we are duplicating the entire database inside a schema.

As for the procedure and data cache, which I am not completely familar with, would it not be the same as multiple database?

I can that this is not the best solution, but I think it may be the best solution given the time and other hardware constraints we have. So, it appears that there are little differences in performance and maintenance that we already deal with.

Database Version Logging

Hi i just planning to creating a database version logging, where the
idea is this application running every night give me detail about table
schema change , new sproc , alter/drop sproc and other database
schema changes.
One of difficulty that i facing right now is how to get the username
that change the database schema ( alter sproc , etc ) , do anyone have
any recommendation how i able to get this information ? ThanksWhat version of SQL Server are you using? (2000/2005)
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"kwangsa" <SareCrow@.gmail.com> wrote in message
news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
> Hi i just planning to creating a database version logging, where the
> idea is this application running every night give me detail about table
> schema change , new sproc , alter/drop sproc and other database
> schema changes.
> One of difficulty that i facing right now is how to get the username
> that change the database schema ( alter sproc , etc ) , do anyone have
> any recommendation how i able to get this information ? Thanks
>|||If you are using SQL Server 2005 you can easily accomplish this by using DDL
Triggers.
There is not easy solution for SQL Server 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"kwangsa" wrote:
> Hi i just planning to creating a database version logging, where the
> idea is this application running every night give me detail about table
> schema change , new sproc , alter/drop sproc and other database
> schema changes.
> One of difficulty that i facing right now is how to get the username
> that change the database schema ( alter sproc , etc ) , do anyone have
> any recommendation how i able to get this information ? Thanks
>|||Hi i m using sql server 2000 , hmm for the "not easy solution" , any
recommendation '
I thinking of using DBCC Log but which field that showing the userid.
Thanks.
Ben Nevarez wrote:
> If you are using SQL Server 2005 you can easily accomplish this by using DDL
> Triggers.
> There is not easy solution for SQL Server 2000.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "kwangsa" wrote:
> > Hi i just planning to creating a database version logging, where the
> > idea is this application running every night give me detail about table
> > schema change , new sproc , alter/drop sproc and other database
> > schema changes.
> >
> > One of difficulty that i facing right now is how to get the username
> > that change the database schema ( alter sproc , etc ) , do anyone have
> > any recommendation how i able to get this information ? Thanks
> >
> >|||Before you spend much time trying to code a solution, look at the various
third party tools used for SarBox and HIPPA compliance logging. It may be
less expensive to get something that is made for the job and taking the time
to kludge something together.
Check out Quest, Idera, Lumigent, SQL AuditPro, and many more I'm sure.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"kwangsa" <SareCrow@.gmail.com> wrote in message
news:1152857175.914841.230840@.b28g2000cwb.googlegroups.com...
> Hi i m using sql server 2000 , hmm for the "not easy solution" , any
> recommendation '
> I thinking of using DBCC Log but which field that showing the userid.
> Thanks.
> Ben Nevarez wrote:
>> If you are using SQL Server 2005 you can easily accomplish this by using
>> DDL
>> Triggers.
>> There is not easy solution for SQL Server 2000.
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "kwangsa" wrote:
>> > Hi i just planning to creating a database version logging, where the
>> > idea is this application running every night give me detail about table
>> > schema change , new sproc , alter/drop sproc and other database
>> > schema changes.
>> >
>> > One of difficulty that i facing right now is how to get the username
>> > that change the database schema ( alter sproc , etc ) , do anyone have
>> > any recommendation how i able to get this information ? Thanks
>> >
>> >
>|||hi,
checkout this tools for auditing
http://www.lumigent.com/Downloads
Amol Lembhe
MCDBA
"Arnie Rowland" wrote:
> What version of SQL Server are you using? (2000/2005)
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "kwangsa" <SareCrow@.gmail.com> wrote in message
> news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
> > Hi i just planning to creating a database version logging, where the
> > idea is this application running every night give me detail about table
> > schema change , new sproc , alter/drop sproc and other database
> > schema changes.
> >
> > One of difficulty that i facing right now is how to get the username
> > that change the database schema ( alter sproc , etc ) , do anyone have
> > any recommendation how i able to get this information ? Thanks
> >
>
>|||Hi thx for the sugestion to using 3rd party tools but what i planning
to do is only a simple database logging , and this is just personal
project so i really interested to make a tools like that.
Do anyone know any DBCC log result mean , i mean any documentation
would be really helfpul. THx
Amol Lembhe wrote:
> hi,
> checkout this tools for auditing
> http://www.lumigent.com/Downloads
> Amol Lembhe
> MCDBA
> "Arnie Rowland" wrote:
> > What version of SQL Server are you using? (2000/2005)
> >
> > --
> > Arnie Rowland*
> > "To be successful, your heart must accompany your knowledge."
> >
> >
> >
> > "kwangsa" <SareCrow@.gmail.com> wrote in message
> > news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
> > > Hi i just planning to creating a database version logging, where the
> > > idea is this application running every night give me detail about table
> > > schema change , new sproc , alter/drop sproc and other database
> > > schema changes.
> > >
> > > One of difficulty that i facing right now is how to get the username
> > > that change the database schema ( alter sproc , etc ) , do anyone have
> > > any recommendation how i able to get this information ? Thanks
> > >
> >
> >
> >|||You could have a Profiler trace running (from a second server) , restricted
to DDL activities only, and then have sprocs that harvest the information
you seek from the trace table.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"kwangsa" <SareCrow@.gmail.com> wrote in message
news:1152873932.402525.309830@.h48g2000cwc.googlegroups.com...
> Hi thx for the sugestion to using 3rd party tools but what i planning
> to do is only a simple database logging , and this is just personal
> project so i really interested to make a tools like that.
> Do anyone know any DBCC log result mean , i mean any documentation
> would be really helfpul. THx
>
> Amol Lembhe wrote:
>> hi,
>> checkout this tools for auditing
>> http://www.lumigent.com/Downloads
>> Amol Lembhe
>> MCDBA
>> "Arnie Rowland" wrote:
>> > What version of SQL Server are you using? (2000/2005)
>> >
>> > --
>> > Arnie Rowland*
>> > "To be successful, your heart must accompany your knowledge."
>> >
>> >
>> >
>> > "kwangsa" <SareCrow@.gmail.com> wrote in message
>> > news:1152843749.295919.186540@.35g2000cwc.googlegroups.com...
>> > > Hi i just planning to creating a database version logging, where the
>> > > idea is this application running every night give me detail about
>> > > table
>> > > schema change , new sproc , alter/drop sproc and other database
>> > > schema changes.
>> > >
>> > > One of difficulty that i facing right now is how to get the username
>> > > that change the database schema ( alter sproc , etc ) , do anyone
>> > > have
>> > > any recommendation how i able to get this information ? Thanks
>> > >
>> >
>> >
>> >
>

Database Version 539 vs. 611 (using SMO)

Hi
The number you are referring to is the internal version number of the
internal database schema. It generally gets updated between major releases.
To see this in action, attach or restore a SQL Server 2000 DB in 2005 using
T-SQL code in Management Studio. You will notice towards the end of the
operation, multiple messages showing the DB being upgraded one revision at a
time to the latest release.
There is no guarantee that between hotfixes or service packs the DB is not
upgraded. 611 is SQL Server 2005 RTM.
Querying the server for it's version number is more reliable.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eH3R7kXlGHA.3816@.TK2MSFTNGP02.phx.gbl...
>I am using SMO in my .NET application to determine the version of a
>database (2000 or 2005). When I get the version of a SQL 2000 database, it
>returns 539. When I get the version of a SQL 2005 database, it returns 611.
> My question is this. Can I safely assume that a value of 539 always means
> the database is a SQL 2000 database, and can I safely assume that a value
> of 611 always means a SQL 2005 database?
> Also, does anyone know what '539' and '611' refer to?
> Thanks, Amos.
>Hi,
wait a minute, you queried for the wrong property. Guess you queried
for something like the Build Number. Use the following peroperty on SMO
to get the information:
Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
SQL2k5
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||I am using SMO in my .NET application to determine the version of a database
(2000 or 2005). When I get the version of a SQL 2000 database, it returns
539. When I get the version of a SQL 2005 database, it returns 611.
My question is this. Can I safely assume that a value of 539 always means
the database is a SQL 2000 database, and can I safely assume that a value of
611 always means a SQL 2005 database?
Also, does anyone know what '539' and '611' refer to?
Thanks, Amos.|||Hi
The number you are referring to is the internal version number of the
internal database schema. It generally gets updated between major releases.
To see this in action, attach or restore a SQL Server 2000 DB in 2005 using
T-SQL code in Management Studio. You will notice towards the end of the
operation, multiple messages showing the DB being upgraded one revision at a
time to the latest release.
There is no guarantee that between hotfixes or service packs the DB is not
upgraded. 611 is SQL Server 2005 RTM.
Querying the server for it's version number is more reliable.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eH3R7kXlGHA.3816@.TK2MSFTNGP02.phx.gbl...
>I am using SMO in my .NET application to determine the version of a
>database (2000 or 2005). When I get the version of a SQL 2000 database, it
>returns 539. When I get the version of a SQL 2005 database, it returns 611.
> My question is this. Can I safely assume that a value of 539 always means
> the database is a SQL 2000 database, and can I safely assume that a value
> of 611 always means a SQL 2005 database?
> Also, does anyone know what '539' and '611' refer to?
> Thanks, Amos.
>|||Hi,
wait a minute, you queried for the wrong property. Guess you queried
for something like the Build Number. Use the following peroperty on SMO
to get the information:
Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
SQL2k5
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||Jens,
Fantastic! Thanks very much.
Amos.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1150928369.273988.134490@.g10g2000cwb.googlegroups.com...
> Hi,
> wait a minute, you queried for the wrong property. Guess you queried
> for something like the Build Number. Use the following peroperty on SMO
> to get the information:
> Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
> SQL2k5
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Jens,
Fantastic! Thanks very much.
Amos.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1150928369.273988.134490@.g10g2000cwb.googlegroups.com...
> Hi,
> wait a minute, you queried for the wrong property. Guess you queried
> for something like the Build Number. Use the following peroperty on SMO
> to get the information:
> Server.Information.Version.Major --whereas 8 is SQL 2000 and 9 is
> SQL2k5
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>