has anyone done something like this

I have to write an app the will take a csv file and parse the results into sql server. Not a big deal really but I need to be able to work on the data and return my own values for sql server depending on the value of data. Ideally it would be nice if i could create this an a executable. Does anyone know of any good tutorials that describe how to do this.

THanksHere's how to read CSV files using .NET:
http://weblogs.asp.net/fmarguerie/archive/2003/10/01/29964.aspx

You could read it into a DataSet and work with it from there.

Hardware Configuration - Need Advice

We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed. and
SQL Server 2000 Standard Edition. Our database is about 4GB and at most we
have 10 concurrent users hitting our server, with a mix of read and write
operations. Both the OS and our DB are on the same machine. I am aware that
having both IIS and SQL Server on the same box is not the ideal
configuration, but it has suited our needs given the load.
We are looking to private license our software to one other company, which
would entail the licensee having their own seperate db, but running in the
same instance of SQL Server. At a minimum, I'm thinking we would move SQL
Server onto its own dedicated machine, keeping the current RAID 5 config.
Perhaps this would not be sufficient?
What I'm looking for is some indication as to whether or not the proposed
platform could handle an increased user load, say 20 times what is now (200
concurrent users). This is primarily an OLTP db used for ACH processing,
with several reporting features. I'm fully aware that the application design
itself, along with query tuning, indexing, etc is equally important as the
hardware, but need some guidance on the hardware itself.
What I'm looking for are some general guidelines to follow given this
scenario. Thanks in advance.
Eric there is no way to answer that without knowing a lot more of what your
current system is doing and how the hardware is holding up now. Do you do 1
transaction per second or 1 thousand? What are the disk Queues, processor
queues etc. like now?
Andrew J. Kelly SQL MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3C2BC87F-5505-4DD6-9E79-A053B86527BA@.microsoft.com...
> We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
> RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed.
> and
> SQL Server 2000 Standard Edition. Our database is about 4GB and at most
> we
> have 10 concurrent users hitting our server, with a mix of read and write
> operations. Both the OS and our DB are on the same machine. I am aware
> that
> having both IIS and SQL Server on the same box is not the ideal
> configuration, but it has suited our needs given the load.
> We are looking to private license our software to one other company, which
> would entail the licensee having their own seperate db, but running in the
> same instance of SQL Server. At a minimum, I'm thinking we would move SQL
> Server onto its own dedicated machine, keeping the current RAID 5 config.
> Perhaps this would not be sufficient?
> What I'm looking for is some indication as to whether or not the proposed
> platform could handle an increased user load, say 20 times what is now
> (200
> concurrent users). This is primarily an OLTP db used for ACH processing,
> with several reporting features. I'm fully aware that the application
> design
> itself, along with query tuning, indexing, etc is equally important as the
> hardware, but need some guidance on the hardware itself.
> What I'm looking for are some general guidelines to follow given this
> scenario. Thanks in advance.

Hardware Configuration - Need Advice

We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed. and
SQL Server 2000 Standard Edition. Our database is about 4GB and at most we
have 10 concurrent users hitting our server, with a mix of read and write
operations. Both the OS and our DB are on the same machine. I am aware that
having both IIS and SQL Server on the same box is not the ideal
configuration, but it has suited our needs given the load.
We are looking to private license our software to one other company, which
would entail the licensee having their own seperate db, but running in the
same instance of SQL Server. At a minimum, I'm thinking we would move SQL
Server onto its own dedicated machine, keeping the current RAID 5 config.
Perhaps this would not be sufficient?
What I'm looking for is some indication as to whether or not the proposed
platform could handle an increased user load, say 20 times what is now (200
concurrent users). This is primarily an OLTP db used for ACH processing,
with several reporting features. I'm fully aware that the application design
itself, along with query tuning, indexing, etc is equally important as the
hardware, but need some guidance on the hardware itself.
What I'm looking for are some general guidelines to follow given this
scenario. Thanks in advance.Eric there is no way to answer that without knowing a lot more of what your
current system is doing and how the hardware is holding up now. Do you do 1
transaction per second or 1 thousand? What are the disk Queues, processor
queues etc. like now?
--
Andrew J. Kelly SQL MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3C2BC87F-5505-4DD6-9E79-A053B86527BA@.microsoft.com...
> We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
> RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed.
> and
> SQL Server 2000 Standard Edition. Our database is about 4GB and at most
> we
> have 10 concurrent users hitting our server, with a mix of read and write
> operations. Both the OS and our DB are on the same machine. I am aware
> that
> having both IIS and SQL Server on the same box is not the ideal
> configuration, but it has suited our needs given the load.
> We are looking to private license our software to one other company, which
> would entail the licensee having their own seperate db, but running in the
> same instance of SQL Server. At a minimum, I'm thinking we would move SQL
> Server onto its own dedicated machine, keeping the current RAID 5 config.
> Perhaps this would not be sufficient?
> What I'm looking for is some indication as to whether or not the proposed
> platform could handle an increased user load, say 20 times what is now
> (200
> concurrent users). This is primarily an OLTP db used for ACH processing,
> with several reporting features. I'm fully aware that the application
> design
> itself, along with query tuning, indexing, etc is equally important as the
> hardware, but need some guidance on the hardware itself.
> What I'm looking for are some general guidelines to follow given this
> scenario. Thanks in advance.

Hardware Configuration - Need Advice

We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed. and
SQL Server 2000 Standard Edition. Our database is about 4GB and at most we
have 10 concurrent users hitting our server, with a mix of read and write
operations. Both the OS and our DB are on the same machine. I am aware tha
t
having both IIS and SQL Server on the same box is not the ideal
configuration, but it has suited our needs given the load.
We are looking to private license our software to one other company, which
would entail the licensee having their own seperate db, but running in the
same instance of SQL Server. At a minimum, I'm thinking we would move SQL
Server onto its own dedicated machine, keeping the current RAID 5 config.
Perhaps this would not be sufficient?
What I'm looking for is some indication as to whether or not the proposed
platform could handle an increased user load, say 20 times what is now (200
concurrent users). This is primarily an OLTP db used for ACH processing,
with several reporting features. I'm fully aware that the application desig
n
itself, along with query tuning, indexing, etc is equally important as the
hardware, but need some guidance on the hardware itself.
What I'm looking for are some general guidelines to follow given this
scenario. Thanks in advance.Eric there is no way to answer that without knowing a lot more of what your
current system is doing and how the hardware is holding up now. Do you do 1
transaction per second or 1 thousand? What are the disk Queues, processor
queues etc. like now?
Andrew J. Kelly SQL MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3C2BC87F-5505-4DD6-9E79-A053B86527BA@.microsoft.com...
> We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
> RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed.
> and
> SQL Server 2000 Standard Edition. Our database is about 4GB and at most
> we
> have 10 concurrent users hitting our server, with a mix of read and write
> operations. Both the OS and our DB are on the same machine. I am aware
> that
> having both IIS and SQL Server on the same box is not the ideal
> configuration, but it has suited our needs given the load.
> We are looking to private license our software to one other company, which
> would entail the licensee having their own seperate db, but running in the
> same instance of SQL Server. At a minimum, I'm thinking we would move SQL
> Server onto its own dedicated machine, keeping the current RAID 5 config.
> Perhaps this would not be sufficient?
> What I'm looking for is some indication as to whether or not the proposed
> platform could handle an increased user load, say 20 times what is now
> (200
> concurrent users). This is primarily an OLTP db used for ACH processing,
> with several reporting features. I'm fully aware that the application
> design
> itself, along with query tuning, indexing, etc is equally important as the
> hardware, but need some guidance on the hardware itself.
> What I'm looking for are some general guidelines to follow given this
> scenario. Thanks in advance.

handling parameters that could be missing

I have a search app that I'm developing in ASP.NET
It has 5 parameters
MRN, Name, Sex, DOB, SSN
All or any combination can be used.
what is the best way to handle the non-supplied parameters?
i.e. if the user selects only supplies 'Name' and 'MRN'
I want the SQL for the stored procedure to read as if it were just 2
parameters:
(but have one stored procedure that has parameter holders for all 5)
Select MRN, Name, Sex, DOB, SSN
From Membership
WHERE MRN = 123456 AND Name like 'Johns%'
The way I have been handling this is to rewrite a different SQL statement
for every possible situation, and pass that string to the adapter. But I ca
n
see that I'm not taking advantage of the processing that SQL server could do
in a stored procedure, and for 1.4 Million records, my app is just timing ou
t
all the time.
This must be a common issue. Could someone show me the ideal way to handle
this?Have a look at
http://www.sommarskog.se/dyn-search.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"jonefer" <jonefer@.discussions.microsoft.com> wrote in message
news:0FFF3A3A-AB66-4A96-B63E-73E937F7A164@.microsoft.com...
>I have a search app that I'm developing in ASP.NET
> It has 5 parameters
> MRN, Name, Sex, DOB, SSN
> All or any combination can be used.
> what is the best way to handle the non-supplied parameters?
> i.e. if the user selects only supplies 'Name' and 'MRN'
> I want the SQL for the stored procedure to read as if it were just 2
> parameters:
> (but have one stored procedure that has parameter holders for all 5)
> Select MRN, Name, Sex, DOB, SSN
> From Membership
> WHERE MRN = 123456 AND Name like 'Johns%'
> The way I have been handling this is to rewrite a different SQL statement
> for every possible situation, and pass that string to the adapter. But I
> can
> see that I'm not taking advantage of the processing that SQL server could
> do
> in a stored procedure, and for 1.4 Million records, my app is just timing
> out
> all the time.
> This must be a common issue. Could someone show me the ideal way to
> handle
> this?
>|||"jonefer" wrote:
> I have a search app that I'm developing in ASP.NET
> It has 5 parameters
> MRN, Name, Sex, DOB, SSN
> All or any combination can be used.
> what is the best way to handle the non-supplied parameters?
> i.e. if the user selects only supplies 'Name' and 'MRN'
> I want the SQL for the stored procedure to read as if it were just 2
> parameters:
> (but have one stored procedure that has parameter holders for all 5)
> Select MRN, Name, Sex, DOB, SSN
> From Membership
> WHERE MRN = 123456 AND Name like 'Johns%'
> The way I have been handling this is to rewrite a different SQL statement
> for every possible situation, and pass that string to the adapter. But I
can
> see that I'm not taking advantage of the processing that SQL server could
do
> in a stored procedure, and for 1.4 Million records, my app is just timing
out
> all the time.
> This must be a common issue. Could someone show me the ideal way to handl
e
> this?
Have you tried something along the line of:
create procedure p_MyProc
@.pMyParm1 int = NULL, @.pMyParm2 datetime = NULL,
@.pMyParm3 varchar(128) = NULL, @.pMyParm4 bit = NULL
as
if @.pMyParm1 IS NULL do something ...
if @.pMyParm2 IS NULL do something ...
if @.pMyParm3 IS NULL do something ...
if @.pMyParm4 IS NULL do something ...|||Thank you. That was excellent.
"Roji. P. Thomas" wrote:

> Have a look at
> http://www.sommarskog.se/dyn-search.html
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "jonefer" <jonefer@.discussions.microsoft.com> wrote in message
> news:0FFF3A3A-AB66-4A96-B63E-73E937F7A164@.microsoft.com...
>
>|||I implemented my search with the 5 parameters using your Dynamic SQL example
.
But because of the way I want to present the final results... it always
times out.
So I will list what I'm trying to get in my final results as well as what
the Stored procedure looks like (perhaps you can help me tweak it)
What I want in the final selection:
SELECT DISTINCT MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR,
[FROM-DT], [THRU-DT]
FROM qMembershipSelect AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC;
How I implemented this in the Stored Procedure:
CREATE PROCEDURE qMemberSelect
@.MRN int = NULL,
@.MemNAME nvarchar(40) = NULL,
@.DOB datetime = NULL,
@.SSN nvarchar(9) = NULL,
@.SEX nvarchar(1) = NULL,
@.debug bit = 0 AS
DECLARE @.sql nvarchar(4000),
@.paramlist nvarchar(4000)
SELECT @.sql =
'SELECT
[MRN],[MemName],[DOB],[SEX],[SSN],[GROUP
],[SGR],[FROM-DT],[THRU-DT] FROM
MEMBERSHIP AS Y
WHERE 1=1'
if @.MRN IS NOT NULL
SELECT @.sql = @.sql + ' AND MRN = @.xMRN'
if @.MemNAME IS NOT NULL
SELECT @.sql = @.sql + ' AND MemName like @.xMemNAME + ''%'''
if @.DOB IS NOT NULL
SELECT @.sql = @.sql + ' AND DOB = @.xDOB'
if @.SSN IS NOT NULL
SELECT @.sql = @.sql + ' AND SSN = @.xSSN'
if @.SEX IS NOT NULL
SELECT @.sql = @.sql + ' AND SEX = @.xSEX'
if @.debug = 1
PRINT @.sql
SELECT @.sql = @.sql + ' GROUP BY MRN, MemNAME, DOB, SEX, SSN, [GROUP], SGR,
[FROM-DT], [THRU-DT]
HAVING len([THRU-DT])= 0
Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
Membership As X Where X.MRN = Y.MRN And X.MRN
Not in(Select Z.MRN From Membership As Z Where len(Z.[THRU-DT])=0))
ORDER BY MemNAME, [FROM-DT] DESC'
SELECT @.paramlist = '@.xMRN int,
@.xMemName nvarchar(40),
@.xDOB datetime,
@.xSSN nvarchar(9),
@.xSEX nvarchar(1)'
EXEC sp_executesql @.sql, @.paramlist,
@.MRN, @.MemNAME, @.DOB, @.SSN, @.SEX
But there must be a way to do this in two phases because trying it all in
one pull is just not efficient.
"Roji. P. Thomas" wrote:

> Have a look at
> http://www.sommarskog.se/dyn-search.html
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "jonefer" <jonefer@.discussions.microsoft.com> wrote in message
> news:0FFF3A3A-AB66-4A96-B63E-73E937F7A164@.microsoft.com...
>
>|||How about...
SELECT MRN, Name, Sex, DOB, SSN
FROM dbo.Membership
WHERE (MRN = @.MRN or @.MRN is null)
AND (Name like @.Name or @.Name is null)
AND (Sex = @.Sex or @.Sex is null)
etc.
TIP: If there are not user specific tables, always specify the owner of the
object so that it is not ambiguous and SQL Server does not have to check for
<userid>.Membership and if not found, check for dbo.Membership. This applie
s
for referencing tables, views, and stored procedures. Also, never prefix a
stored procedure name with sp_ or xp_ as that can cause unexpected side
effects.
Just my two cents,
Joe
"jonefer" wrote:

> I have a search app that I'm developing in ASP.NET
> It has 5 parameters
> MRN, Name, Sex, DOB, SSN
> All or any combination can be used.
> what is the best way to handle the non-supplied parameters?
> i.e. if the user selects only supplies 'Name' and 'MRN'
> I want the SQL for the stored procedure to read as if it were just 2
> parameters:
> (but have one stored procedure that has parameter holders for all 5)
> Select MRN, Name, Sex, DOB, SSN
> From Membership
> WHERE MRN = 123456 AND Name like 'Johns%'
> The way I have been handling this is to rewrite a different SQL statement
> for every possible situation, and pass that string to the adapter. But I
can
> see that I'm not taking advantage of the processing that SQL server could
do
> in a stored procedure, and for 1.4 Million records, my app is just timing
out
> all the time.
> This must be a common issue. Could someone show me the ideal way to handl
e
> this?
>|||Joe
If you use sp_XXXXXX SQL server looks into MAster database first even if you
mentioned the database.sp_XXX... So, we are looking unnecessarily in master
database . that is why most folks suggest do not use sp_XXXX .I never heard
of side effects... If you know any please share with us.
--
SQL SERVER DBA
"Joe from WI" wrote:
> How about...
> SELECT MRN, Name, Sex, DOB, SSN
> FROM dbo.Membership
> WHERE (MRN = @.MRN or @.MRN is null)
> AND (Name like @.Name or @.Name is null)
> AND (Sex = @.Sex or @.Sex is null)
> etc.
> TIP: If there are not user specific tables, always specify the owner of th
e
> object so that it is not ambiguous and SQL Server does not have to check f
or
> <userid>.Membership and if not found, check for dbo.Membership. This appl
ies
> for referencing tables, views, and stored procedures. Also, never prefix
a
> stored procedure name with sp_ or xp_ as that can cause unexpected side
> effects.
> Just my two cents,
> Joe
> "jonefer" wrote:
>