ASP search screen and MS-SQL 2k

Hello,

I am looking for help with the following situation.

I have created a simple call tracking system that is integrated
with our accounting / warehousing program in the company. The call
tracking system uses ASP 3.0 and MSSQL 2000 sitting on an Intranet web
server. All appears to be working well with the software. I am
trying to create a search screen to search through the calls based on
different criteria.

I have an ASP script that generates a SELECT statement based on
the selected criteria but this is sent off to the SQL server as the
statement. I want to use a stored procedure to speed up the process.
Is it possible to create a stored procedure for this search even when
the WHERE clause and criteria keeps changing?

TIALook into Full Text Indexing on your SQL Server tables. There is a Sql
Service dedicated to this. This article touches on the capability...

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8967

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"rdlebreton" <rdlebreton@.hotmail.com> wrote in message
news:80376060.0312111117.4f999bf2@.posting.google.c om...
> Hello,
> I am looking for help with the following situation.
> I have created a simple call tracking system that is integrated
> with our accounting / warehousing program in the company. The call
> tracking system uses ASP 3.0 and MSSQL 2000 sitting on an Intranet web
> server. All appears to be working well with the software. I am
> trying to create a search screen to search through the calls based on
> different criteria.
> I have an ASP script that generates a SELECT statement based on
> the selected criteria but this is sent off to the SQL server as the
> statement. I want to use a stored procedure to speed up the process.
> Is it possible to create a stored procedure for this search even when
> the WHERE clause and criteria keeps changing?
> TIA|||[posted and mailed, please reply in news]

rdlebreton (rdlebreton@.hotmail.com) writes:
> I am looking for help with the following situation.
> I have created a simple call tracking system that is integrated
> with our accounting / warehousing program in the company. The call
> tracking system uses ASP 3.0 and MSSQL 2000 sitting on an Intranet web
> server. All appears to be working well with the software. I am
> trying to create a search screen to search through the calls based on
> different criteria.
> I have an ASP script that generates a SELECT statement based on
> the selected criteria but this is sent off to the SQL server as the
> statement. I want to use a stored procedure to speed up the process.
> Is it possible to create a stored procedure for this search even when
> the WHERE clause and criteria keeps changing?

Yes, although the way to gain speed is more likely to review indexing,
or constrain what users can search on.

Anyway, I have an article on the topic on my web site. It's a bit
long, but it covers the topic fairly extensively. The link is
http://www.algonet.se/~sommar/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns944F3B58AE2FYazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
<snip!
Holy S--!

You've got a lot on this web site!

It's going to take me the whole weekend to digest it all.

Thanks

asking for advice

Hi everyone,

i wonder if any one can give me some advice.

here is my situation, i have a central database server that contains all the data, running in an intranet, the client application, which is a thick client containing most of the biz logics is installed on a laptop,

i m required to develop a solution so that when the client application is disconnect to the database server (e.g. the laptop is taken away somewhere) the application can still work and when the client application gets connect to the database server again the application will be able to synchronize changes with the database and update all the changes. and i m not allow to develop a web-base application

my initial thinking is to use merge replication, however, i m conerning with the performance on the laptop if i have to install sql server on the laptop.

is there any other approach that can still achieve the requirement and have a better performance?

thanks a lot in advance.

You would be installing SQL Express on the laptop not the full version so you should be fine performance wise. We do this exact same thing and it works fine.

It takes a little longer for the laptop to load windows initially because it has to start the server service but once that is done there is not much of a performance hit. Unless of course you have a complicated database with tonnes of transactions per change on the client.

Martin

sql

asking for advice

Hi everyone,

i wonder if any one can give me some advice.

here is my situation, i have a central database server that contains all the data, running in an intranet, the client application, which is a thick client containing most of the biz logics is installed on a laptop,

i m required to develop a solution so that when the client application is disconnect to the database server (e.g. the laptop is taken away somewhere) the application can still work and when the client application gets connect to the database server again the application will be able to synchronize changes with the database and update all the changes. and i m not allow to develop a web-base application

my initial thinking is to use merge replication, however, i m conerning with the performance on the laptop if i have to install sql server on the laptop.

is there any other approach that can still achieve the requirement and have a better performance?

thanks a lot in advance.

You would be installing SQL Express on the laptop not the full version so you should be fine performance wise. We do this exact same thing and it works fine.

It takes a little longer for the laptop to load windows initially because it has to start the server service but once that is done there is not much of a performance hit. Unless of course you have a complicated database with tonnes of transactions per change on the client.

Martin

Array as variable ?

Hi,
I am in a situation where I have an array of userIDs in VB .Net. For each of
these UserIDs I need to query the database for the fastest time for each
user and would like to fill a dataset ordered with fastest to slowest time.
Is there any way I can manage this in T-SQL and submit an array of userIDs
as a variable to the stored procedure, or do I need to manage this in my
code by submitting one query per user and do the sorting etc in code ?
Niclashttp://www.sommarskog.se/arrays-in-sql.html|||To add to Omnibuzz's response, you can also consider using the XML data type
if you're using SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:OOAMYC4aGHA.4788@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am in a situation where I have an array of userIDs in VB .Net. For each
> of these UserIDs I need to query the database for the fastest time for
> each user and would like to fill a dataset ordered with fastest to slowest
> time.
> Is there any way I can manage this in T-SQL and submit an array of userIDs
> as a variable to the stored procedure, or do I need to manage this in my
> code by submitting one query per user and do the sorting etc in code ?
> Niclas
>|||> I am in a situation where I have an array of userIDs in VB .Net. For each
> of these UserIDs I need to query the database for the fastest time for
> each user and would like to fill a dataset ordered with fastest to slowest
> time.
> Is there any way I can manage this in T-SQL and submit an array of userIDs
> as a variable to the stored procedure, or do I need to manage this in my
> code by submitting one query per user and do the sorting etc in code ?
The simplest way of dealing with an array of updates / inserts / deletes in
.NET is to either (a) iterate over that array, passing each value one at a
time to your stored procedure, which is pretty inefficient, or to (b) slurp
the data up into a DataTable or DataSet and do something like this:
If you want to see it in C#, just say so, and I will repost:
Imports Jedi.Mind.Trick
' you're putting your connection strings in your .config file, aren't you?!?
Dim cnstr As String = ConfigurationSettings.AppSettings("connectionString")
Dim cn As New SqlConnection(cnstr)
' SQL Statement or stored procedure that updates a *single* record
Dim cm As New SqlConnection("INSERT INTO ... VALUES (...)", cn)
Dim da As New SqlDataAdapter(cm)
' whatever you need to hold your *multiple records* of data.
Dim dt As New DataTable() ' or, if you prefer, Dim ds As New DataSet()
' get your data from wherever
da.Update(dt) ' does inserts, updates, and deletes, as neccesary
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei