More logical drives VS more striped drives

I have thought about it many times.
When using more disks in one volume, random IO rises, but not by the same
factor as number of disks.
If load of these two files is the same, then two volumes are better, but if
it is not, one volume is better.
Are you sure that load is always uniformly distributed between files? Than
use separate volumes.
In real world the load always fluctuates so it is not possible to make a
common conclusion.
"Paul Cahill" <anon@.anon.com> pse v diskusnm prspevku
news:%237m9RqZuHHA.3368@.TK2MSFTNGP02.phx.gbl...
>A user called ZoomZoom made the posting below on Tech Republic. As it did
>not get a reply there I am interested to know what the community's opinion
>is.
> Some of my Raid 10's are made up of 8 drives. Would it have been better to
> have created 2 Raid 10's of 4 drives and then have two data files in the
> file group?
> I would expect an 8 drive Raid 10 to give a higher sequential data rate
> but what about random IO when one has many users?
> Regard
> Paul Cahill
> ...
> Posted by ZoomZoom
>
> I have seen many articles that say to place log files separate from db
> files, and use raid 1+0 for the db... but I haven't found anything to
> answer this question for me though (unless it should be so obvious that
> I'm just not seeing it).
> Is it faster for the database to have the tables split into separate files
> and put each file in smaller raid 1+0 configurations or is it faster to
> use the same number of drives in a single raid 1+0. For example: 8 drives
> could be split into 2 raid 1+0 arrays (4 each) or they could be configured
> in one large raid 1+0 array (8 drives). The end result would be striping
> between 2 sets of 2 drives (in the 4 disk array due to 2 being only
> mirrors) or striping between 4 drives (in the 8 disk array).
> I guess another way of looking at the question is how much performance
> does each additional pair of disks in a raid 1+0 array really add. Can an
> 8 disk array handle twice as many IO's as as a 4 disk array? Or does it
> only add maybe 30% more IO ability? If the later, would it make sense to
> add blocks of 4 disk arrays and split the database tables into multiple
> files instead? Would this theory work like putting the logs on separate
> drives from the database files?
> I realize it's an expensive solution... but with the price of database
> per-processor licenses being what they are... it makes sense to try to
> squeeze as much performance out of your database server as possible.
>
I pretty much agree with that explanation. If you know exactly how your
files will be accessed and they compete with each other at a fairly
intensive level you might get better performance from splitting them. This
is true of separating tempdb from data files as well. But if you don't know
or the load is not too high you are most likely better off having a larger
array with all the data files on it. Now placing the log files on another
array is always a good idea.
Andrew J. Kelly SQL MVP
"Jir Lejsek" <jlejsek@.na_volnym_v_cesku> wrote in message
news:urgxfhauHHA.1184@.TK2MSFTNGP04.phx.gbl...
>I have thought about it many times.
> When using more disks in one volume, random IO rises, but not by the same
> factor as number of disks.
> If load of these two files is the same, then two volumes are better, but
> if it is not, one volume is better.
> Are you sure that load is always uniformly distributed between files? Than
> use separate volumes.
> In real world the load always fluctuates so it is not possible to make a
> common conclusion.
> "Paul Cahill" <anon@.anon.com> pse v diskusnm prspevku
> news:%237m9RqZuHHA.3368@.TK2MSFTNGP02.phx.gbl...
>
|||Right that is why I qualified it with knowing "Exactly" how they are used
which is hard to do.
Andrew J. Kelly SQL MVP
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:v798831lqurvqepcvtb9q294c4ha95liq9@.4ax.com...
> On Thu, 28 Jun 2007 17:02:37 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>
> The problem I have with targetting files at particular drives is that
> it tunes for one particular load only. Tune it for the daytime OLTP
> and it could be sub-optimal during heavy duty batch processing at
> night. One big striped set spreads the load pretty evenly across all
> spindles, regardless of load, and regardless of how the load changes
> over time. It might not be as fast in some special instances, but I
> think on average one big set of drives is going to perform as well or
> better. Targetting files also means one more thing for the DBA to
> keep an eye on and worry about changing later when the load changes or
> space grows differently than expected.
> Logs, of course, are another story as everyone knows.
> Roy Harvey
> Beacon Falls, CT

more date problems...

I have a high number of computers that at logon write some information
to a sql 2005 database. Information such as computer name, user name,
logon date and logon time are entered.

Because computers use different regional options, I notice that queries
to this database return inconsistent results due to different date
formatting. For example I see computers entering 1/3/2006 and 1/3/6 or
1/3/06.

How can I modify my query so that it reformats the date. This is my
current query I execute from within an ASP application:

RS.Open "Select * from PCLogs.dbo.logs WHERE Note = '" &
Request.Form("date") & "' ", dbConn, 1

The date is a variable that refers to a dd/mm/yyyy format. The date
column is of type text.

I'm a novice in SQL so any help would be greatly appreciated !
TIA and Regards--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

The date column should not be a "text" column (I assume you mean a
VARCHAR column). It should be a Date data type column. Change that, if
you can.

I don't know if VBScript has the Format() function, but try that. E.g.:

Format(Request.Form("date"),"YYYYMMDD")

This will format the date in a format that SQL understands.

If you can't change the data type of the column you should be using a
stored procedure (SP) to save the data into the table. The SP should
format the date data to a default format, preferrably YYYYMMDD, that the
VBScript command can "know" to use when querying the table.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAYOA4echKqOuFEgEQJ51wCfdi5FGvlY/cT7wCe6qLzaciAya7IAoNdh
WjXzm/NNtiUAJdhiVpFCZTMh
=c6LY
--END PGP SIGNATURE--

zerbie45@.gmail.com wrote:
> I have a high number of computers that at logon write some information
> to a sql 2005 database. Information such as computer name, user name,
> logon date and logon time are entered.
> Because computers use different regional options, I notice that queries
> to this database return inconsistent results due to different date
> formatting. For example I see computers entering 1/3/2006 and 1/3/6 or
> 1/3/06.
> How can I modify my query so that it reformats the date. This is my
> current query I execute from within an ASP application:
> RS.Open "Select * from PCLogs.dbo.logs WHERE Note = '" &
> Request.Form("date") & "' ", dbConn, 1
> The date is a variable that refers to a dd/mm/yyyy format. The date
> column is of type text.
> I'm a novice in SQL so any help would be greatly appreciated !
> TIA and Regards

Month-to-month function

Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed

September:

@.CurrentDate = 9/10/2007

@.EndDate = last working day 9/30/2007

Total# of working days = 15

October:

@.CurrentDate = 10/1/2007

@.EndDate = last working day 10/31/2007

Total# of working days = 23

November:

@.CurrentDate = 11/1/2007

@.EndDate = last working day 11/30/2007

Total# of working days = 22

etc.

Any ideas of how i can approch this?

Thanks in advance.

If we just count out weekends, you can use this script. This will not take into account holidays.

Code Snippet

DECLARE @.startDate DATETIME,

@.dateTest DATETIME,

@.workDays INT

SET @.startDate = getDate()

SET @.dateTest = @.startDate

SET @.workDays = 0

WHILE( MONTH(@.startDate) = MONTH(@.dateTest) )

BEGIN

IF( DATENAME(dw, @.dateTest) != 'SATURDAY' AND DATENAME(dw, @.dateTest) != 'Sunday')

SET @.workDays = @.workDays + 1

SET @.dateTest = @.dateTest + 1

END

SELECT @.workDays

|||

You might try to search this forum on "Working Days". Also, give a look to this article about using a "calendar table:"

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

|||

Code Snippet

create function udf_WeekdayCounter

(

@.dtFrom datetime

, @.dtThrough Datetime

)

returns smallint

as

begin

if @.dtThrough <= @.dtFrom

return 0

declare @.iCounter int

set @.iCounter = 0

/*

declare @.dtFrom datetime, @.dtThrough datetime

set @.dtFrom = '11/1/2007'

set @.dtThrough = '11/30/2007'

*/

while @.dtFrom < = @.dtThrough

begin

if datepart(weekday,@.dtFrom) not in (datepart(weekday,'December 30, 2006'), datepart(weekday,'December 31, 2006'))

set @.iCounter = @.iCounter + 1

set @.dtFrom = dateadd(d, 1, @.dtFrom)

end

return @.iCounter

end

GO

select dbo.udf_WeekdayCounter( '11/1/2007', '11/30/2007')

sql

Month Name

The SQL statement below works to provide the number of help desk requests submitted per month for help_company_id = 1.

The problem is that the months are return as numbers (1 for January). I tried using the DATENAME function but all the months were return as January. This is how I tried to use it: SELECT DATENAME(month, MONTH(help_date_submitted)) AS Month, COUNT(help_request_id) AS Requests)

How do I convert the month numbers to month names?

SELECT MONTH(help_date_submitted) AS Month, COUNT(help_request_id) AS Requests
FROM help_requests
WHERE (help_company_id = 1)
GROUP BY MONTH(help_date_submitted)
ORDER BY Month

Hi,

You should pass the current date to the DATENAME function as follows:

SELECT DATENAME(mm, GETDATE())

|||

Assuming that this application does not need to be localized, and the SQL Server is running in the language you want it output then:

SELECT DATENAME(month, help_date_submitted) AS Month, COUNT(help_request_id) AS Requests

will work for you.

|||

When I try SELECT DATENAME(month, help_date_submitted) AS Month, COUNT(help_request_id) AS Requests the following error is returned:

Column help_requests.help_date_submited is invalid in the list because it is not contained in either an aggregate function or the group by clause.

|||

SELECT MONTH(help_date_submitted) AS MonthNo,MIN(DATENAME(month,help_date_submitted)) AS Month, COUNT(help_request_id) AS Requests
FROM help_requests
WHERE (help_company_id = 1)
GROUP BY MONTH(help_date_submitted)
ORDER BY MonthNo

Not exactly straight forward, but it'll work. Problem is that SQL Server doesn't see the month number (returned by the month function), and the month name being related. So you have to play some games with it, especially since you want it ordered by the month number rather than the month name.

|||

Actually, as long as you focus on the grouped values you can do it. Here is a sample query to demonstrate:

1select2datename(3month4 ,dateadd(5month6 ,month( crdate ) - 17 , 0 ) ) [Month]8 ,count(*) CountOfMonth9from10sysobjects11group by12 month( crdate )13order by14 month( crdate )

So, using this logic for the user's query you would have

1select2datename(3month4 ,dateadd(5month6 ,month( hr.help_date_submitted ) - 17 , 0 ) ) [Month]8 ,count( hr.help_request_id ) Requests9from10 help_requests hr11where12 hr.help_company_id = 113group by14 month( hr.help_date_submitted )15order by16 month( hr.help_date_submitted )

A couple of other points - I am sure you know, but this does group multiple years into each month. Is that the desired behavior? ( Just asking ).

Month name

how do i return the month in text from a number?
the vb function is monthname(), there does not seem to be
an equivelent in TSQLUse the DATENAME() function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||Hi,
select datename(month,getdate())
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||what i am trying to do is convert a number eg 5, into its
month value eg 'september' all date functions such as
datename() & month() seem to ask for a date as there input
parameter
>--Original Message--
>Use the DATENAME() function.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>> how do i return the month in text from a number?
>> the vb function is monthname(), there does not seem to
be
>> an equivelent in TSQL
>
>.
>|||The equivalent of VB MonthName() would be something like this:
SELECT DATENAME(MONTH,DATEADD(MONTH,@.month,-1))
where @.month is the month number.
More generally, DATENAME is used to return the month name from a DATETIME or
SMALLDATETIME value.
--
David Portas
SQL Server MVP
--|||Declare @.Month as char(2)
Declare @.Date as char(10)
set @.Month = '06'
set @.Date = '01/' + @.Month + '/1900'
SELECT DATENAME(month, @.Date) AS 'Month Name'
You may need to modify the @.Date string depending where in
the world you are.
J
>--Original Message--
>what i am trying to do is convert a number eg 5, into its
>month value eg 'september' all date functions such as
>datename() & month() seem to ask for a date as there
input
>parameter
>>--Original Message--
>>Use the DATENAME() function.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>
>>"mat" <anonymous@.discussions.microsoft.com> wrote in
>message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>> how do i return the month in text from a number?
>> the vb function is monthname(), there does not seem to
>be
>> an equivelent in TSQL
>>
>>.
>.
>|||> You may need to modify the @.Date string depending where in
> the world you are.
Not if you use a standard, non-ambiguous format!
SET @.Date = '1900' + @.Month + '01'
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
you can use the DATENAME(), but then you have to 'create' a date first:
DECLARE @.MonthNumber integer;
SET @.MonthNumber=11;
select datename(month,
CAST('1/'+CASE WHEN @.MonthNumber <10 THEN CAST(@.MonthNumber AS char(1))
ELSE CAST(@.MonthNumber AS char(2)) END +'/2004' AS DATETIME));
Because the month number has to be casted to a char first, distinguish
the two cases : month-number is one or two digit(s). (Perhaps a trim
function could be used instead of CASE WHEN )
Then cast the date string to a date and use the datename function. I
used the dateformat dd/mm/yyyy.
mat schrieb:
> what i am trying to do is convert a number eg 5, into its
> month value eg 'september' all date functions such as
> datename() & month() seem to ask for a date as there input
> parameter
>
>>--Original Message--
>>Use the DATENAME() function.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>
>>"mat" <anonymous@.discussions.microsoft.com> wrote in
> message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>>how do i return the month in text from a number?
>>the vb function is monthname(), there does not seem to
> be
>>an equivelent in TSQL
>>
>>.

Monster select statement revisited

I have a database with a date of birth. From this, I need to select records that include a large number of variables including records where an individual is older than a selected age and/or younger than a selected age, and even worse, of a given astrological sign if one is selected.

I'm currently using a strongly typed dataset. Is this something that's do-able with a dataset, or should I build the select string on the form programmatically? I have an ASP site that I'm re-writing in ASP.NET 2.0. In the ASP site the select string includes the code below. I just don't see how I can do this in a dataset.

if (strLowerAge <> 18) or (strUpperAge <> 99) then
If (strLowerAge <> "") Then
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ")"
End If
If (strUpperAge <> "") Then
If (strSQL <> "") Then
strSQL = strSQL & " AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")"
End If
end if
End If
If (strAstSign <> "") Then
Select Case strAstSign
Case "Aries 3/21 - 4/19"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) < 20 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m, [DATE OF BIRTH]) = 3 AND DATEPART(d, [DATE OF BIRTH]) > 20 ) OR (DATEPART(m, [DATE OF BIRTH]) = 4 AND DATEPART(d, [DATE OF BIRTH]) < 20 )"
End If
Case "Taurus 4/20 - 5/20"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) < 21 )"
End If
Case "Gemini 5/21 - 6/20"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) < 21 )"
End If
Case "Cancer 6/21 - 7/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Leo 7/23 - 8/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Virgo 8/23 - 9/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Libra 9/23 - 10/22"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) < 23 )"
End If
Case "Scorpio 10/23 - 11/21"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) < 22 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) < 22 )"
End If
Case "Sagittarius 11/22 - 12/21"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) < 22 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) < 22 )"
End If
Case "Capricorn 12/22 - 1/19"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) < 20 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) < 20 )"
End If
Case "Aquarius 1/20 - 2/18"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) < 19 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) < 19 )"
End If
Case "Pisces 2/19 - 3/20"
If (strSQL <> "") Then
strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) > 18 ) OR (DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))"
Else
strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) > 18 ) OR (DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) < 21 )"
End If
End Select
END IF

Diane

I have made sql statements like this, and they are a pain in the butt. I cannot comment on how to do this in a dataset in ASP.NET 2.0, but this is how I would do it:

Firstly, I would use a stringBuilder to create this string. It is more effiecient that a string.

Secondly, I see that you have a lot of If (strSQL <> "") Then ... Else ... End If statements. To combat this, I make sure that the strSQL is not empty in the first place. This way you do not need to check if it is empty or not. In your case, I would put the following at the top:

Dim strSQL as new StringBuilder("SELECT .... FROM members WHERE NOT [FIRST NAME] IN NULL ")

Then all you have to do is append to this every time e.g:

Dim strSQL as new StringBuilder("SELECT " & _" [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE]" & _" FROM members " & _" WHERE NOT [FIRST NAME] IS NULL" )If (strLowerAge <> 18) or (strUpperAge <> 99) then If (strLowerAge <> "") Then strSQL.append(" AND (DateDiff(yyyy, [DATE OF BIRTH],'" & date & "') >= " & strLowerAge & ")")End If If (strUpperAge <>"")Then strSQL.append(" AND (DateDiff(yyyy, [DATE OF BIRTH],'" & date & "') <= " & strUpperAge & ")")End ifEnd If

That should make you code a little shorter and easier to handle. To get the finall string, just use Dim mySQL as String = strSQL.toString().

If you really want to make it effiecient, the I would do all of this in the database as a stored procedure. If you are using SQL Server, you can build SQL statements as string and then execute them within a stored procedure. e.g.

CREATE PROCEDURE mySQLBuilder (@.sdtDOBsmalldatetime@.otherVariableint@.vSortColumn@.vSortDirection@.vWhereStr)ASDECLARE @.sqlvarchar(1000)SET @.sql ='SELECT ... FROM ...' + @.vWhereStr +' ORDER BY ' + @.vSortColumn +' ' + @.vSortDirectionEXEC (@.sql)GO

Just treat the stored procedure as you would a select statement.

HTH

Jag

|||

Thank you Jag, you gave me a lot of useful information.

I have never used a stored procedure. It sounds like I would have the same problem though that I would have with the dataset - the astrology sign which is not in the database and needs to be calculated. Unless I have 12 different procedures, 1 per sign, I need to some how include an 'if then' in the SELECT statement. Is that doable?

It gets even better. Currently, in order to give the members better exposure, I have the order clause dependent on the day of the week. One day O oder by last name, another by first name, another by date of birth, another by state, etc. So I would need 84 different procedures. Right?

Diane

|||

As you haven't used stored procedures before, I would leave that for now and go the way you are going. You can do 'if then' statements in stored procedures and use them to build a select statement (like you did in VB), but you should start off with easier things than this.

To learn about stored procedures, just do a google search for 'tutorial stored procedures'. You should get some good ones. They are mostly used for database input, but you can use them for data retrieval as well. They are more efficient that adhoc SQL statements as they are compiled in the SQL database.

Jag

Monitoring the number of concurrent sessions in SLQ Server

HI:
Is it possible to know the number of concurrent sesions
that are working in a SQL Server?
Im talking about sessions that are doing something
in the SQLServer in a certain period of time; not only
those
sessions that may be are users that are just logged but
doing
nothing (maybe in "Awaiting Command")
Regards.You can use sp_who (or sp_who2) with the ACTIVE parameter to see only spids
that are doing something.
--
Andrew J. Kelly
SQL Server MVP
"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
news:008101c3a631$3e9ab340$a501280a@.phx.gbl...
> HI:
> Is it possible to know the number of concurrent sesions
> that are working in a SQL Server?
> Im talking about sessions that are doing something
> in the SQLServer in a certain period of time; not only
> those
> sessions that may be are users that are just logged but
> doing
> nothing (maybe in "Awaiting Command")
> Regards.
>|||Hi,
Execute the below query from Query Analyzer to identify the number of
sessions ,
select count(*) from master..sysprocesses
Thanks
Hari
MCDBA
"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
news:008101c3a631$3e9ab340$a501280a@.phx.gbl...
> HI:
> Is it possible to know the number of concurrent sesions
> that are working in a SQL Server?
> Im talking about sessions that are doing something
> in the SQLServer in a certain period of time; not only
> those
> sessions that may be are users that are just logged but
> doing
> nothing (maybe in "Awaiting Command")
> Regards.
>|||Regards,
Jose.
>--Original Message--
>Hi,
>Execute the below query from Query Analyzer to identify
the number of
>sessions ,
>select count(*) from master..sysprocesses
>Thanks
>Hari
>MCDBA
>
>"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
>news:008101c3a631$3e9ab340$a501280a@.phx.gbl...
>> HI:
>> Is it possible to know the number of concurrent sesions
>> that are working in a SQL Server?
>> Im talking about sessions that are doing something
>> in the SQLServer in a certain period of time; not only
>> those
>> sessions that may be are users that are just logged but
>> doing
>> nothing (maybe in "Awaiting Command")
>> Regards.
>
>.
>|||Regards,
Jose (Mexico City)
>--Original Message--
>You can use sp_who (or sp_who2) with the ACTIVE
parameter to see only spids
>that are doing something.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
>news:008101c3a631$3e9ab340$a501280a@.phx.gbl...
>> HI:
>> Is it possible to know the number of concurrent sesions
>> that are working in a SQL Server?
>> Im talking about sessions that are doing something
>> in the SQLServer in a certain period of time; not only
>> those
>> sessions that may be are users that are just logged but
>> doing
>> nothing (maybe in "Awaiting Command")
>> Regards.
>
>.

Monitoring tempdb usage.

I want to monitor the number of times that tempdb was accessed, wether it
grew or shrunk. What counters of perfmon can I use for this? Or should I use
the profiler instead ? There are db file related parameters over there.
Cheers
SQLCatZ
IN the database section of Perfmon there are shrink and growth counters, as
well as file size... I'd go there...
You could also take some code out of sp_spaceused and do your own thing for
size..
As to access, you could use physical reads/writes if the files are on their
own hard drive... otherwise it would be a profiler thing..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> I want to monitor the number of times that tempdb was accessed, wether it
> grew or shrunk. What counters of perfmon can I use for this? Or should I
use
> the profiler instead ? There are db file related parameters over there.
> Cheers
> SQLCatZ
>
|||to add on Wayne's comment, you could use profiler to monitor events of
SP:starting, T-SQL:StmtStarting and RPC:SPStarting, filtering on database id
= 2.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uhzwXRlSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> IN the database section of Perfmon there are shrink and growth counters,
> as
> well as file size... I'd go there...
> You could also take some code out of sp_spaceused and do your own thing
> for
> size..
> As to access, you could use physical reads/writes if the files are on
> their
> own hard drive... otherwise it would be a profiler thing..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> use
>

Monitoring tempdb usage.

I want to monitor the number of times that tempdb was accessed, wether it
grew or shrunk. What counters of perfmon can I use for this? Or should I use
the profiler instead ? There are db file related parameters over there.
Cheers
SQLCatZIN the database section of Perfmon there are shrink and growth counters, as
well as file size... I'd go there...
You could also take some code out of sp_spaceused and do your own thing for
size..
As to access, you could use physical reads/writes if the files are on their
own hard drive... otherwise it would be a profiler thing..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> I want to monitor the number of times that tempdb was accessed, wether it
> grew or shrunk. What counters of perfmon can I use for this? Or should I
use
> the profiler instead ? There are db file related parameters over there.
> Cheers
> SQLCatZ
>|||to add on Wayne's comment, you could use profiler to monitor events of
SP:starting, T-SQL:StmtStarting and RPC:SPStarting, filtering on database id
= 2.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uhzwXRlSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> IN the database section of Perfmon there are shrink and growth counters,
> as
> well as file size... I'd go there...
> You could also take some code out of sp_spaceused and do your own thing
> for
> size..
> As to access, you could use physical reads/writes if the files are on
> their
> own hard drive... otherwise it would be a profiler thing..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
>> I want to monitor the number of times that tempdb was accessed, wether it
>> grew or shrunk. What counters of perfmon can I use for this? Or should I
> use
>> the profiler instead ? There are db file related parameters over there.
>> Cheers
>> SQLCatZ
>

Monitoring tempdb usage.

I want to monitor the number of times that tempdb was accessed, wether it
grew or shrunk. What counters of perfmon can I use for this? Or should I use
the profiler instead ? There are db file related parameters over there.
Cheers
SQLCatZIN the database section of Perfmon there are shrink and growth counters, as
well as file size... I'd go there...
You could also take some code out of sp_spaceused and do your own thing for
size..
As to access, you could use physical reads/writes if the files are on their
own hard drive... otherwise it would be a profiler thing..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> I want to monitor the number of times that tempdb was accessed, wether it
> grew or shrunk. What counters of perfmon can I use for this? Or should I
use
> the profiler instead ? There are db file related parameters over there.
> Cheers
> SQLCatZ
>|||to add on Wayne's comment, you could use profiler to monitor events of
SP:starting, T-SQL:StmtStarting and RPC:SPStarting, filtering on database id
= 2.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uhzwXRlSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> IN the database section of Perfmon there are shrink and growth counters,
> as
> well as file size... I'd go there...
> You could also take some code out of sp_spaceused and do your own thing
> for
> size..
> As to access, you could use physical reads/writes if the files are on
> their
> own hard drive... otherwise it would be a profiler thing..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
> news:B6C93E0A-FC79-4627-ABF8-4A44AA5218DE@.microsoft.com...
> use
>sql

Monitoring SQL Server Error logs

Hi,
We have a large number of SQL servers that we are supporting. Monitoring the
error logs for new errors is really a big task as we have to manually check
these error logs. I was hoping that some of you might have automated this
process and filtered out the unwanted messages from the error log and mail
only the genuine ones. If anyone can share it with the group, it will be
terrific as you will be giving us extra minutes of sleep each night.
Thanking you in anticipation,
Yoga
You'll need to define for yourself what a wanted message is.
One option is to use finstr to search for whatever it is you
are looking for - you can find examples in these articles:
http://support.microsoft.com/?id=115519
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=40
You can automate those in a job.
Another consideration is how you set up and use your alerts
and monitoring on the servers. For most things you can be
notified if monitoring is set up correctly.
Of course there are a lot of third party solutions but I
take it your looking for scripts and built in solutions.
-Sue
On Mon, 26 Mar 2007 11:53:38 -0700, "Yoganarasimha Prasad"
<yogakiran@.gmail.com> wrote:

>Hi,
>We have a large number of SQL servers that we are supporting. Monitoring the
>error logs for new errors is really a big task as we have to manually check
>these error logs. I was hoping that some of you might have automated this
>process and filtered out the unwanted messages from the error log and mail
>only the genuine ones. If anyone can share it with the group, it will be
>terrific as you will be giving us extra minutes of sleep each night.
>Thanking you in anticipation,
>Yoga
>

Monitoring SQL Server Error logs

Hi,
We have a large number of SQL servers that we are supporting. Monitoring the
error logs for new errors is really a big task as we have to manually check
these error logs. I was hoping that some of you might have automated this
process and filtered out the unwanted messages from the error log and mail
only the genuine ones. If anyone can share it with the group, it will be
terrific as you will be giving us extra minutes of sleep each night.
Thanking you in anticipation,
YogaYou'll need to define for yourself what a wanted message is.
One option is to use finstr to search for whatever it is you
are looking for - you can find examples in these articles:
http://support.microsoft.com/?id=115519
http://www.sqlservercentral.com/scr...asp?scriptid=40
You can automate those in a job.
Another consideration is how you set up and use your alerts
and monitoring on the servers. For most things you can be
notified if monitoring is set up correctly.
Of course there are a lot of third party solutions but I
take it your looking for scripts and built in solutions.
-Sue
On Mon, 26 Mar 2007 11:53:38 -0700, "Yoganarasimha Prasad"
<yogakiran@.gmail.com> wrote:

>Hi,
>We have a large number of SQL servers that we are supporting. Monitoring th
e
>error logs for new errors is really a big task as we have to manually check
>these error logs. I was hoping that some of you might have automated this
>process and filtered out the unwanted messages from the error log and mail
>only the genuine ones. If anyone can share it with the group, it will be
>terrific as you will be giving us extra minutes of sleep each night.
>Thanking you in anticipation,
>Yoga
>|||I've got a DIY SQL error log checking , as you can see in the script there a
various terms filtered out , change it as required.
I've selected those terms , as error logs I'm not interested in. It takes a
parameter , being the server name . I've got it wrapped on another sp that
runs through the servers , hitting the the error log sp , and then if
relevant send an email
http://www.quicksqlserver.com/2007/...erver_log_.html
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"Yoganarasimha Prasad" <yogakiran@.gmail.com> wrote in message
news:elZrRg9bHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We have a large number of SQL servers that we are supporting. Monitoring
> the error logs for new errors is really a big task as we have to manually
> check these error logs. I was hoping that some of you might have automated
> this process and filtered out the unwanted messages from the error log and
> mail only the genuine ones. If anyone can share it with the group, it will
> be terrific as you will be giving us extra minutes of sleep each night.
> Thanking you in anticipation,
> Yoga
>

Monitoring SQL Server Error logs

Hi,
We have a large number of SQL servers that we are supporting. Monitoring the
error logs for new errors is really a big task as we have to manually check
these error logs. I was hoping that some of you might have automated this
process and filtered out the unwanted messages from the error log and mail
only the genuine ones. If anyone can share it with the group, it will be
terrific as you will be giving us extra minutes of sleep each night.
Thanking you in anticipation,
YogaYou'll need to define for yourself what a wanted message is.
One option is to use finstr to search for whatever it is you
are looking for - you can find examples in these articles:
http://support.microsoft.com/?id=115519
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=40
You can automate those in a job.
Another consideration is how you set up and use your alerts
and monitoring on the servers. For most things you can be
notified if monitoring is set up correctly.
Of course there are a lot of third party solutions but I
take it your looking for scripts and built in solutions.
-Sue
On Mon, 26 Mar 2007 11:53:38 -0700, "Yoganarasimha Prasad"
<yogakiran@.gmail.com> wrote:
>Hi,
>We have a large number of SQL servers that we are supporting. Monitoring the
>error logs for new errors is really a big task as we have to manually check
>these error logs. I was hoping that some of you might have automated this
>process and filtered out the unwanted messages from the error log and mail
>only the genuine ones. If anyone can share it with the group, it will be
>terrific as you will be giving us extra minutes of sleep each night.
>Thanking you in anticipation,
>Yoga
>|||I've got a DIY SQL error log checking , as you can see in the script there a
various terms filtered out , change it as required.
I've selected those terms , as error logs I'm not interested in. It takes a
parameter , being the server name . I've got it wrapped on another sp that
runs through the servers , hitting the the error log sp , and then if
relevant send an email
http://www.quicksqlserver.com/2007/03/sql_server_log_.html
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"Yoganarasimha Prasad" <yogakiran@.gmail.com> wrote in message
news:elZrRg9bHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We have a large number of SQL servers that we are supporting. Monitoring
> the error logs for new errors is really a big task as we have to manually
> check these error logs. I was hoping that some of you might have automated
> this process and filtered out the unwanted messages from the error log and
> mail only the genuine ones. If anyone can share it with the group, it will
> be terrific as you will be giving us extra minutes of sleep each night.
> Thanking you in anticipation,
> Yoga
>

Monitoring Query Performance

Is there any table in SQL Server 2000 which lists the queries currently
cached in memory and statistics on those queries e.g. execution time, number
of executions cost, etc. I would like to be able to determine the queries
that have the highest cost to target for performance tuning first.Not really. You have some information in master..syscacheobjects, but that are only the cached plans
(those that are cached in the first place, of course). You can have a profiler trace running to pick
up the measures you are interested in and to your tuning based on that profiler trace.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:C5531A9C-7344-4734-B041-7A83FA31F5F8@.microsoft.com...
> Is there any table in SQL Server 2000 which lists the queries currently
> cached in memory and statistics on those queries e.g. execution time, number
> of executions cost, etc. I would like to be able to determine the queries
> that have the highest cost to target for performance tuning first.

monitoring changes on a view

I have a number of tables, each with many fields. I put together
information by a view onto the relevant tables and fields. Now I'd like
to monitor if data in my view has been updated or inserted. I don't care
about the fields not included in the view. If something changes, the ID
of the corresponding dataset should be inserted into a log table.
I have no idea how to handle this with a trigger. If I put a trigger on
the basetables, it will fire on every change. On the other hand i can't
put a trigger on the view. Any ideas?
Thanks in advance
Michael
Example:
Table1
a1
a2
a3
a4
Table2
b1
b2
b3
b4
Table3
c1
c2
c3
c4
c5
View combines
a1,a2,b1,c4,c5Michael Schroeder" wrote:

> I have a number of tables, each with many fields. I put together
> On the other hand i can't
> put a trigger on the view. Any ideas?
>
Do you mean that certain business rules prevent you from putting a trigger
on the view? You can put an INSTEAD OF trigger on the view, and put logic
into the trigger to update the underlying base tables, as well as put an
entry into an audit log.|||I don't know what type of events you are wanting to audit, but you can
create an insert, update, or delete triggers on the base tables. Whether or
not this presents a problem depends on how complex the logic of the trigger
is. Below is an example of a simple and low cost implementation of an
auditing trigger that I have used in the past. In this case, the table
EmployeeAudit has the same column layout of Employee but with the addition
of a column named AuditDate and AuditType (delete or insert). An update is a
delete immediately followed by an insert.
CREATE trigger EmployeeAudit on Employee
AFTER INSERT, UPDATE, DELETE
AS
insert into EmployeeAudit select getdate(), 'D', * from deleted
insert into EmployeeAudit select getdate(), 'I', * from inserted
GO
"Michael Schroeder" <schroeder@.idicos.[germany]> wrote in message
news:%23zqtnQ1JGHA.1544@.TK2MSFTNGP11.phx.gbl...
>I have a number of tables, each with many fields. I put together
>information by a view onto the relevant tables and fields. Now I'd like to
>monitor if data in my view has been updated or inserted. I don't care about
>the fields not included in the view. If something changes, the ID of the
>corresponding dataset should be inserted into a log table.
> I have no idea how to handle this with a trigger. If I put a trigger on
> the basetables, it will fire on every change. On the other hand i can't
> put a trigger on the view. Any ideas?
> Thanks in advance
> Michael
> --
> Example:
> Table1
> a1
> a2
> a3
> a4
> Table2
> b1
> b2
> b3
> b4
> Table3
> c1
> c2
> c3
> c4
> c5
> View combines
> a1,a2,b1,c4,c5|||Mark Williams schrieb:
> Michael Schroeder" wrote:
>
> Do you mean that certain business rules prevent you from putting a trigger
> on the view? You can put an INSTEAD OF trigger on the view, and put logic
> into the trigger to update the underlying base tables, as well as put an
> entry into an audit log.
No. I can put an INSTEAD OF trigger on that view. But it does not fire
when something in the underlying basetables changes or something is
beeing inserted/deleted.
Maybe I have to mention that the basetables a beeing updated by
replication and the view and its trigger are just for monitoring and
reporting.
Putting triggers on each basetable is not a good idea, because there are
many of them and just a fraction of their data is interesting for
processing.
Thanks for your reply
Michael

Monitoring Active Connections

I realize this may be an elementary question for this newsgroup, but is there any way to monitor/log the number of ACTIVE concurrent connections in SQL/MSDE? I would like to log a week's worth of activity on our database to see if MSDE will work or if we
need to upgrade to full-blown SQL Server. DB size is not a concern (well below 2GB), and I don't believe connections will be either but I need data to prove it.
The performance counters in perfmon show sleeping as well as active connections. Our app is such that connections wake up, retrieve a small amount of data, and go back to sleep.
Sysprocesses gives you informaton about all the connections on your sql
server at the moment. You can run this in a loop and direct it to an output
file. You can also choose the columns you want.
Something like --
while (1=1)
begin
select getdate()
select * from master..sysprocesses
waitfor delay '00:00:15'
end
-- save above to .sql file and run from cmd line as
osql -Usa -P<password> -i<filenameabove> -o<outputfilename>
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Sysprocesses gives you informaton about all the connections on your sql
server at the moment. You can run this in a loop and direct it to an output
file. You can also choose the columns you want.
Something like --
while (1=1)
begin
select getdate()
select * from master..sysprocesses
waitfor delay '00:00:15'
end
-- save above to .sql file and run from cmd line as
osql -Usa -P<password> -i<filenameabove> -o<outputfilename>
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Monitoring Active Connections

I realize this may be an elementary question for this newsgroup, but is there any way to monitor/log the number of ACTIVE concurrent connections in SQL/MSDE? I would like to log a week's worth of activity on our database to see if MSDE will work or if we
need to upgrade to full-blown SQL Server. DB size is not a concern (well below 2GB), and I don't believe connections will be either but I need data to prove it.
The performance counters in perfmon show sleeping as well as active connections. Our app is such that connections wake up, retrieve a small amount of data, and go back to sleep.
hi Ken,
"Ken" <Ken@.discussions.microsoft.com> ha scritto nel messaggio
news:5C6A46AA-C5B3-4EEA-842B-B1324C7BEAC9@.microsoft.com...
> I realize this may be an elementary question for this newsgroup, but is
there any
>way to monitor/log the number of ACTIVE concurrent connections in SQL/MSDE?
> I would like to log a week's worth of activity on our database to see if
MSDE will
>work or if we need to upgrade to full-blown SQL Server. DB size is not a
concern
> (well below 2GB), and I don't believe connections will be either but I
need data to prove it.
> The performance counters in perfmon show sleeping as well as active
connections.
>Our app is such that connections wake up, retrieve a small amount of data,
and go
> back to sleep.
please have a look at
http://msdn.microsoft.com/library/de..._dbcc_0cdq.asp
http://msdn.microsoft.com/library/de...wa-wz_3v8v.asp
that can provide a hint on how to proceed..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks, that's great. But, is there any way to do something similar on SQL Server? Our highest volume customer purchased SQL Server and is running the software on top of that. It would be great to see if their load produces any concurrency violations.
"Andrea Montanari" wrote:

> hi Ken,
> "Ken" <Ken@.discussions.microsoft.com> ha scritto nel messaggio
> news:5C6A46AA-C5B3-4EEA-842B-B1324C7BEAC9@.microsoft.com...
> there any
> MSDE will
> concern
> need data to prove it.
> connections.
> and go
> please have a look at
> http://msdn.microsoft.com/library/de..._dbcc_0cdq.asp
> http://msdn.microsoft.com/library/de...wa-wz_3v8v.asp
> that can provide a hint on how to proceed..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ken,
"Ken" <Ken@.discussions.microsoft.com> ha scritto nel messaggio
news:32D983FA-0F35-40A5-8D5B-C4AA68E3791E@.microsoft.com...
> Thanks, that's great. But, is there any way to do something similar on SQL
Server?
>Our highest volume customer purchased SQL Server and is running the
software on
> top of that. It would be great to see if their load produces any
concurrency violations.
unfortunately [or not =;-D ], not... DBCC CONCURRENCYVIOLATION, even if
available and runnable on all SQL Server editions, only returns concurrency
violations for MSDE and SQL Server Desktop Edition...
you have to inspect master..sysprocesses your way to extract such info...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||MSDE is not limited by the number of connections. You're off on the wrong
foot.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:5C6A46AA-C5B3-4EEA-842B-B1324C7BEAC9@.microsoft.com...
> I realize this may be an elementary question for this newsgroup, but is
there any way to monitor/log the number of ACTIVE concurrent connections in
SQL/MSDE? I would like to log a week's worth of activity on our database to
see if MSDE will work or if we need to upgrade to full-blown SQL Server. DB
size is not a concern (well below 2GB), and I don't believe connections will
be either but I need data to prove it.
> The performance counters in perfmon show sleeping as well as active
connections. Our app is such that connections wake up, retrieve a small
amount of data, and go back to sleep.
|||I realize it is not limited by the number of connections. According to the documentation available from Microsoft the governor kicks in when there are more than 8 active operations. According to this page (http://msdn.microsoft.com/library/en-us/architec/
8_ar_sa2_0ciq.asp?frame=true), an "active operation" includes things such as opening a connection or processing a query. I was thinking of doing a "select * from sysprocesses where status = 'runnable'" as a rough estimate of the number of active operation
s.
Are you saying this information is incorrect? If so, what is correct and what are your sources? It would be great of you to share, especially if the Microsoft documentation is incorrect.
"William (Bill) Vaughn" wrote:

> MSDE is not limited by the number of connections. You're off on the wrong
> foot.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:5C6A46AA-C5B3-4EEA-842B-B1324C7BEAC9@.microsoft.com...
> there any way to monitor/log the number of ACTIVE concurrent connections in
> SQL/MSDE? I would like to log a week's worth of activity on our database to
> see if MSDE will work or if we need to upgrade to full-blown SQL Server. DB
> size is not a concern (well below 2GB), and I don't believe connections will
> be either but I need data to prove it.
> connections. Our app is such that connections wake up, retrieve a small
> amount of data, and go back to sleep.
>
>
|||I realize it is not limited by the number of connections. According to the documentation available from Microsoft the governor kicks in when there are more than 8 active operations. According to this page (http://msdn.microsoft.com/library/en-us/architec/
8_ar_sa2_0ciq.asp?frame=true), an "active operation" includes things such as opening a connection or processing a query. I was thinking of doing a "select * from sysprocesses where status = 'runnable'" as a rough estimate of the number of active operation
s.
Are you saying this information is incorrect? If so, what is correct and what are your sources? It would be great of you to share, especially if the Microsoft documentation is incorrect.
"William (Bill) Vaughn" wrote:

> MSDE is not limited by the number of connections. You're off on the wrong
> foot.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:5C6A46AA-C5B3-4EEA-842B-B1324C7BEAC9@.microsoft.com...
> there any way to monitor/log the number of ACTIVE concurrent connections in
> SQL/MSDE? I would like to log a week's worth of activity on our database to
> see if MSDE will work or if we need to upgrade to full-blown SQL Server. DB
> size is not a concern (well below 2GB), and I don't believe connections will
> be either but I need data to prove it.
> connections. Our app is such that connections wake up, retrieve a small
> amount of data, and go back to sleep.
>
>
|||The point I was trying to make is having an open connection does not count as an active operation. It's only "active" when the connection is actually processing something or opening or closing. Sleeping connections do not impact the governor.
We have seen any number of people describe systems that use MSDE with dozens of users without having the governor kick in while others have trouble with a couple of users. The difference?
a.. The way the connections are managed (are they held open or constantly opening and closing?
b.. How queries are written. How long does it take to execute each query. How long is the "thread" executed on the server?
c.. Are queries blocking other queries--are they fighting for resources?
You can monitor the Event log to see if the governor kicks in or hit sysprocesses (as sp_who does) and count sleeping vs active processes (as the article says).
My sources are the dev team members who implemented and support the code and 10 years experience with SQL Server.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Ken" <Ken@.discussions.microsoft.com> wrote in message news:3EFB2A8F-8F01-4850-8A06-118F2BC9F965@.microsoft.com...[vbcol=seagreen]
> I realize it is not limited by the number of connections. According to the documentation available from Microsoft the governor kicks in when there are more than 8 active operations. According to this page ( http://msdn.microsoft.com/library/en...asp?frame=true), an "active operation" includes things such as opening a connection or processing a query. I was thinking of doing a "select * from sysprocesses where status = 'runnable'" as a rough estimate of the number of active operations.
> Are you saying this information is incorrect? If so, what is correct and what are your sources? It would be great of you to share, especially if the Microsoft documentation is incorrect.
> "William (Bill) Vaughn" wrote:

Monitoring a Read/Write access to a DB

Hello,
I am trying to write a VB.NET application to do this:
1. Count the number of Read/Write accesses to a DB on the server and,
2. Get the last access time of the DB since its creation.
Tried looking it over at MSDN...tried googling...but have failed. Any
inputs would be more than valuable.
Thanks.
_Sourabh
Thanks Geoff...
Re: Counting number of IO Accesses per DB
From: Geoff N. Hiten
Date Posted: 4/13/2004 3:02:00 PM
The basic building block you are looking for is the T-SQL function
fn_virtualfilestats. You can use write a query, call it from ADO.Net, and
do something nifty to display the results using VB.Net.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
moharss@.auburn.edu (Sourabh) wrote in message news:<cff778d4.0404131053.dd7821b@.posting.google.c om>...
> Hello,
> I am trying to write a VB.NET application to do this:
> 1. Count the number of Read/Write accesses to a DB on the server and,
> 2. Get the last access time of the DB since its creation.
> Tried looking it over at MSDN...tried googling...but have failed. Any
> inputs would be more than valuable.
> Thanks.
> _Sourabh

Money in SQL and ASP.NET

Hi,
I'm having some trouble with my asp.net page and my sql database. What I'm trying to do is allow the user to upload an number to the database, the number is a money amount like 2.00 (￡2.00) or 20.00(￡20.00). I've tried using money and smallmoney datatypes but the numbers usually end up looking like this in the database...
I enter 2.00 and in the database it looks like 2.00000, and even if I enter the information directly into the database I get the same results. I'm not going to be using big numbers with lots of decimal places like this 1000,000,0000. Can anyone help me? All I want is to know what to set the value to on my aspx page and what setting to set the field to in my database, I'd just like two pound to appear as 2.00. any help would be great.

I'm using Microsoft Visual Web Developer 2005 Express Edition and Microsoft SQL Server 2005 if that helps.

Thanks.

Hi,

The money or smallmoney datatypes are the right ones to use in your case. By design, they have four decimal at the end and you can find relevent infromation related to these two from Books Online. As to your question, you use either of these types in your database to hold your data and when it is time to show your data on your asp.net page, you can format them into what you need. For example, {0,c2} will give you two decimals as you want. In GridView:

<asp:BoundField HeaderText="Price/Unit" DataField="UnitPrice" DataFormatString="{0:c2}"HtmlEncode="false"> </asp:BoundField>

Or

<asp:TemplateFieldHeaderText="UnitPrice"><ItemTemplate><asp:LabelID="Label2"runat="server"Text='<%# Eval("UnitPrice","{0:c2}") %>'></asp:Label></ItemTemplate></asp:TemplateField>|||

Hi,

You don't say what type of controls you're trying to display this in. However, one simple way (that I think you can apply to any text box or label control) is this:

TextBox1.Text = (512.23).ToString("c")

If you need to set the page to a different currency than your own, one way is to set the UICulture of the page directive:

<%@. Page Language="VB" AutoEventWireup="false" UICulture="en-GB" CodeFile="Default4.aspx.vb" Inherits="Default4" %>

You can also set this in the web.config file (under globalization)

How the numbers are stored in SQL aren't relevant to how they'll be displayed - as you've discovered!

Hope this helps.

Paul

Money datatype Substring

I have a sql query listed below, I would like to count the number of values
that
the first 5 characters are '80438' from iNum field.
Please help me correct the sql query listed below.
Thank You,
SELECT count(iNum) from Call_Movement
where substring(cast(iNum as money) as char(20),1,5) like '80438'
iNum Money Format
iNum Data In Call_Movement Table
803482000146220.0000
803482000147143.0000
803482000153805.0000Try,
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
go
Expect a table or index scan.
AMB
"Joe K." wrote:

> I have a sql query listed below, I would like to count the number of value
s
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe
create table #test (c1 varchar(100))
insert into #test values ('80438hwdgyde5787kioui')
insert into #test values ('5838hwdgyde5787kioui')
insert into #test values ('80438mjji')
select count(c1) from #test where c1 like '80438%'
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A5FC4F29-928C-4D6D-B8F6-4A964D6711B2@.microsoft.com...
> I have a sql query listed below, I would like to count the number of
> values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||I received the following error when applying the query listed below.
Please help me resolve the query error listed below.
Thanks,
Error:
Implicit conversion from data type money to varchar is not allowed. Use the
CONVERT function to run this query.
SQL Query:
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
________________________________________
___________________________
"Joe K." wrote:

> I have a sql query listed below, I would like to count the number of value
s
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe,
SELECT count(iNum)
from Call_Movement
where convert(varchar(50), iNum, 0) like '80348%'
AMB
"Joe K." wrote:
[vbcol=seagreen]
> I received the following error when applying the query listed below.
> Please help me resolve the query error listed below.
> Thanks,
> Error:
> Implicit conversion from data type money to varchar is not allowed. Use th
e
> CONVERT function to run this query.
> SQL Query:
> SELECT count(iNum)
> from Call_Movement
> where ltrim(iNum) like '80348%'
> ________________________________________
___________________________
> "Joe K." wrote:
>