Disk writes/sec value

I picked up the disk writes/sec for a particular physical
disk and noticed it shoot to 2800 at times..
What does that mean ? There are 2800 writes to that array
that may comprise of 6 disks or so..
Jamie,
Check out these for more details on the proper counters:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
But what exactly is it your trying to determine? The best counter(s) to
view for an overall indication of how busy your disks are is the current
(and average) disk queues.
Andrew J. Kelly SQL MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a6401c45ecc$d9104d00$a301280a@.phx.gbl...
> I picked up the disk writes/sec for a particular physical
> disk and noticed it shoot to 2800 at times..
> What does that mean ? There are 2800 writes to that array
> that may comprise of 6 disks or so..

Disk writes/sec value

I picked up the disk writes/sec for a particular physical
disk and noticed it shoot to 2800 at times..
What does that mean ? There are 2800 writes to that array
that may comprise of 6 disks or so..Jamie,
Check out these for more details on the proper counters:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
on_24u1.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
But what exactly is it your trying to determine? The best counter(s) to
view for an overall indication of how busy your disks are is the current
(and average) disk queues.
Andrew J. Kelly SQL MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a6401c45ecc$d9104d00$a301280a@.phx
.gbl...
> I picked up the disk writes/sec for a particular physical
> disk and noticed it shoot to 2800 at times..
> What does that mean ? There are 2800 writes to that array
> that may comprise of 6 disks or so..

Disk writes/sec value

I picked up the disk writes/sec for a particular physical
disk and noticed it shoot to 2800 at times..
What does that mean ? There are 2800 writes to that array
that may comprise of 6 disks or so..Jamie,
Check out these for more details on the proper counters:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
But what exactly is it your trying to determine? The best counter(s) to
view for an overall indication of how busy your disks are is the current
(and average) disk queues.
--
Andrew J. Kelly SQL MVP
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:23a6401c45ecc$d9104d00$a301280a@.phx.gbl...
> I picked up the disk writes/sec for a particular physical
> disk and noticed it shoot to 2800 at times..
> What does that mean ? There are 2800 writes to that array
> that may comprise of 6 disks or so..sql

Disk Crash Recover - Won't Recover my SQL Database

My client had a hard disk crash, was running SQL 7.0. The disk engineers were
able to recover the MDF and NDF for a particular database, and I was trying
to attach the MDF back to our new server (SQL 2000). However, because the LDF
transaction log is missing, it is refusing to do so. We were using a "simple"
log model, so I'm not sure if the transaction log even had anything to useful.
I've tried "fooling" SQL server, like copying a good LDF file of a similary
named database, but it knows it's not part of the set and wont recover it.
Also, barring the inability to restore it, is there a tool to break apart a
big MDF file and get to the underlying tables that make it up, so at least
perhaps I can recover some of the data in some of the tables?
Thanks,
Bill Rosman
Chicago,IL
Have you tried sp_attach_single_file_db?
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
> My client had a hard disk crash, was running SQL 7.0. The disk engineers
> were
> able to recover the MDF and NDF for a particular database, and I was
> trying
> to attach the MDF back to our new server (SQL 2000). However, because the
> LDF
> transaction log is missing, it is refusing to do so. We were using a
> "simple"
> log model, so I'm not sure if the transaction log even had anything to
> useful.
> I've tried "fooling" SQL server, like copying a good LDF file of a
> similary
> named database, but it knows it's not part of the set and wont recover it.
> Also, barring the inability to restore it, is there a tool to break apart
> a
> big MDF file and get to the underlying tables that make it up, so at least
> perhaps I can recover some of the data in some of the tables?
> Thanks,
> Bill Rosman
> Chicago,IL
|||Yes I have tried sp_attach_single_file, but it still keeps on looking for the
LDF transaction log file.
bill r.
"Paul S Randal [MS]" wrote:

> Have you tried sp_attach_single_file_db?
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
> message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
>
>
|||try using sp_attach_db, attaching .MDF and .NDF files only, no need to
attach .LDF file, it will create a new one.
"Rosman Computing" wrote:
[vbcol=seagreen]
> Yes I have tried sp_attach_single_file, but it still keeps on looking for the
> LDF transaction log file.
> bill r.
> "Paul S Randal [MS]" wrote:
|||Well, if you know the database was shutdown cleanly (e.g. by detaching it
while SQL Server is running) then the log file won't be needed as there's
nothing to recover.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3gxb3pHHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Whether or not a log file is needed when you attach (or similar) a
> database depends on whether there is recovery work to do in the database.
> Every time a database starts, it will see whether it has to perform
> recovery work. Recovery work include REDO and UNDO of log records. This it
> has to do because transactions might have been in flight when the database
> was shut down. If SQL Server determines that there is recovery work to be
> done, it *need* the log file.
> SQL Server will not allow you to use an inconsistent database (as in the
> case of missing log file and it need to do recovery work).
> I.e., you never know whether SQL Server can create the log file - so you
> should *never* rely on this. (Paul - I welcome elaborations and/or
> corrections to this statement... :-). )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Celal" <Celal@.discussions.microsoft.com> wrote in message
> news:EF5EC77E-2AFA-42BA-8CEE-A5242D4AD0AF@.microsoft.com...
>
|||How would you check the bit in the file header - there's no documented way
to do so :-) and you'd have to have the database attached or know the file
header row structure.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eaL4mWuHHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Ahh, I tend to forget about the detach/offline cases. I guess my sceptism
> about these things isn't as much technical or mistrust, but more from
> these newsgroups. All the posts where SQL Server cannot create the log
> file. "I *did* detach the database first". Perhaps the simple truth is:
> a) The poster (not in this thread, I should add), claims that detach was
> performed even though it wasn't performed. ...In some vain hope that
> claiming that fact would somehow change things.
> b) I'm polluted by posts where log files cannot be created, and I just
> don't keep track of which cases detach (or offline) actually happened.
> I wish there could be some type of "FK"/link in NTFS so SQL Server could
> enforce that we cannot delete log files unless it was shutdown cleanly
> (even when SQL Server is stopped). Also, there would be nice if we could
> investigate this bit(?) in the mdf file header(?).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:eM0AAPuHHHA.1240@.TK2MSFTNGP03.phx.gbl...
>

Disk Crash Recover - Won't Recover my SQL Database

My client had a hard disk crash, was running SQL 7.0. The disk engineers wer
e
able to recover the MDF and NDF for a particular database, and I was trying
to attach the MDF back to our new server (SQL 2000). However, because the LD
F
transaction log is missing, it is refusing to do so. We were using a "simple
"
log model, so I'm not sure if the transaction log even had anything to usefu
l.
I've tried "fooling" SQL server, like copying a good LDF file of a similary
named database, but it knows it's not part of the set and wont recover it.
Also, barring the inability to restore it, is there a tool to break apart a
big MDF file and get to the underlying tables that make it up, so at least
perhaps I can recover some of the data in some of the tables?
Thanks,
Bill Rosman
Chicago,ILHave you tried sp_attach_single_file_db?
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
> My client had a hard disk crash, was running SQL 7.0. The disk engineers
> were
> able to recover the MDF and NDF for a particular database, and I was
> trying
> to attach the MDF back to our new server (SQL 2000). However, because the
> LDF
> transaction log is missing, it is refusing to do so. We were using a
> "simple"
> log model, so I'm not sure if the transaction log even had anything to
> useful.
> I've tried "fooling" SQL server, like copying a good LDF file of a
> similary
> named database, but it knows it's not part of the set and wont recover it.
> Also, barring the inability to restore it, is there a tool to break apart
> a
> big MDF file and get to the underlying tables that make it up, so at least
> perhaps I can recover some of the data in some of the tables?
> Thanks,
> Bill Rosman
> Chicago,IL|||Yes I have tried sp_attach_single_file, but it still keeps on looking for th
e
LDF transaction log file.
bill r.
"Paul S Randal [MS]" wrote:

> Have you tried sp_attach_single_file_db?
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
> message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
>
>|||try using sp_attach_db, attaching .MDF and .NDF files only, no need to
attach .LDF file, it will create a new one.
"Rosman Computing" wrote:
[vbcol=seagreen]
> Yes I have tried sp_attach_single_file, but it still keeps on looking for
the
> LDF transaction log file.
> bill r.
> "Paul S Randal [MS]" wrote:
>|||> try using sp_attach_db, attaching .MDF and .NDF files only, no need to
> attach .LDF file, it will create a new one.
Whether or not a log file is needed when you attach (or similar) a database
depends on whether there
is recovery work to do in the database. Every time a database starts, it wil
l see whether it has to
perform recovery work. Recovery work include REDO and UNDO of log records. T
his it has to do because
transactions might have been in flight when the database was shut down. If S
QL Server determines
that there is recovery work to be done, it *need* the log file.
SQL Server will not allow you to use an inconsistent database (as in the cas
e of missing log file
and it need to do recovery work).
I.e., you never know whether SQL Server can create the log file - so you sho
uld *never* rely on
this. (Paul - I welcome elaborations and/or corrections to this statement...
:-). )
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Celal" <Celal@.discussions.microsoft.com> wrote in message
news:EF5EC77E-2AFA-42BA-8CEE-A5242D4AD0AF@.microsoft.com...[vbcol=seagreen]
> try using sp_attach_db, attaching .MDF and .NDF files only, no need to
> attach .LDF file, it will create a new one.
> "Rosman Computing" wrote:
>|||Well, if you know the database was shutdown cleanly (e.g. by detaching it
while SQL Server is running) then the log file won't be needed as there's
nothing to recover.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3gxb3pHHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Whether or not a log file is needed when you attach (or similar) a
> database depends on whether there is recovery work to do in the database.
> Every time a database starts, it will see whether it has to perform
> recovery work. Recovery work include REDO and UNDO of log records. This it
> has to do because transactions might have been in flight when the database
> was shut down. If SQL Server determines that there is recovery work to be
> done, it *need* the log file.
> SQL Server will not allow you to use an inconsistent database (as in the
> case of missing log file and it need to do recovery work).
> I.e., you never know whether SQL Server can create the log file - so you
> should *never* rely on this. (Paul - I welcome elaborations and/or
> corrections to this statement... :-). )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Celal" <Celal@.discussions.microsoft.com> wrote in message
> news:EF5EC77E-2AFA-42BA-8CEE-A5242D4AD0AF@.microsoft.com...
>|||Ahh, I tend to forget about the detach/offline cases. I guess my sceptism ab
out these things isn't
as much technical or mistrust, but more from these newsgroups. All the posts
where SQL Server cannot
create the log file. "I *did* detach the database first". Perhaps the simple
truth is:
a) The poster (not in this thread, I should add), claims that detach was per
formed even though it
wasn't performed. ...In some vain hope that claiming that fact would somehow
change things.
b) I'm polluted by posts where log files cannot be created, and I just don't
keep track of which
cases detach (or offline) actually happened.
I wish there could be some type of "FK"/link in NTFS so SQL Server could enf
orce that we cannot
delete log files unless it was shutdown cleanly (even when SQL Server is sto
pped). Also, there would
be nice if we could investigate this bit(?) in the mdf file header(?).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eM0AAPuHHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Well, if you know the database was shutdown cleanly (e.g. by detaching it
while SQL Server is
> running) then the log file won't be needed as there's nothing to recover.
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e3gxb3pHHHA.1912@.TK2MSFTNGP03.phx.gbl...
>|||How would you check the bit in the file header - there's no documented way
to do so :-) and you'd have to have the database attached or know the file
header row structure.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eaL4mWuHHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Ahh, I tend to forget about the detach/offline cases. I guess my sceptism
> about these things isn't as much technical or mistrust, but more from
> these newsgroups. All the posts where SQL Server cannot create the log
> file. "I *did* detach the database first". Perhaps the simple truth is:
> a) The poster (not in this thread, I should add), claims that detach was
> performed even though it wasn't performed. ...In some vain hope that
> claiming that fact would somehow change things.
> b) I'm polluted by posts where log files cannot be created, and I just
> don't keep track of which cases detach (or offline) actually happened.
> I wish there could be some type of "FK"/link in NTFS so SQL Server could
> enforce that we cannot delete log files unless it was shutdown cleanly
> (even when SQL Server is stopped). Also, there would be nice if we could
> investigate this bit(?) in the mdf file header(?).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:eM0AAPuHHHA.1240@.TK2MSFTNGP03.phx.gbl...
>|||Hehe, I knew you were to say something like that...

> How would you check the bit in the file header - there's no documented way
to do so :-) and you'd
> have to have the database attached or know the file header row structure.
If we know what bit(?) it is, we could check it with a hex editor. Or, even
produce some tiny
utility that reads the beginning of the file, see what value the bit has and
present it. Heck, that
small utility could even be produced by MS ;-).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eIKzFlvHHHA.3540@.TK2MSFTNGP02.phx.gbl...
> How would you check the bit in the file header - there's no documented way
to do so :-) and you'd
> have to have the database attached or know the file header row structure.
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eaL4mWuHHHA.1264@.TK2MSFTNGP06.phx.gbl...
>

Disctinct selections

Hi I am trying to figure out how to use the DISCTINCT function in s SELECT Query for one particular column, but output more that the disctinct column

for example:

table 1

Alan Andrews 1 main st 07465
John Andrews 1 main st 07465
Erick Andrews 1 main st 07465

I want to select by disctinct last name, but on my results I want to see all the other fields as well, and not just the last name. In this case the first name address and zip code.

So is there a way of doing this in SQL?

It does not have to be with the DISCTINCT function, but I need to net down to 1 per last name in a select query.

Thanks in advance!

AlanHi I am trying to figure out how to use the DISCTINCT function in s SELECT Query for one particular column, but output more that the disctinct column


for example:

table 1

Alan Andrews 1 main st 07465
John Andrews 1 main st 07465
Erick Andrews 1 main st 07465

I want to select by disctinct last name, but on my results I want to see all the other fields as well, and not just the last name. In this case the first name address and zip code.

So is there a way of doing this in SQL?

It does not have to be with the DISCTINCT function, but I need to net down to 1 per last name in a select query.

Thanks in advance!

Alan

is there a way to tell which one of the records from thr above example should be displayed ... or should it be a random one.|||It really doesn't matter which one of the records gets selected. I just need to end up with one.

Is there a nth funtion in SQL perhaps?

Alan|||no, there is no "nth" function

i should also like to take this opportunity to point out that DISTINCT is not a function either
select lastname
, min(firstname) as lowest_firstname_for_this_lastname
, max(address) as highest_address_for_this_lastname
, avg(zipcode) as average_zip_assuming_its_numeric
from daTable
group
by lastname|||no, there is no "nth" function

i should also like to take this opportunity to point out that DISTINCT is not a function either
select lastname
, min(firstname) as lowest_firstname_for_this_lastname
, max(address) as highest_address_for_this_lastname
, avg(zipcode) as average_zip_assuming_its_numeric
from daTable
group
by lastname

Ok, this could work but will it return my actual record. For my example I've excluded other fields that I need to select from my table such as Individual number, family number, gender and much more.

I think my original example was not the best

Your query if used with the MIN aggregated function won't I end up with the lowest value first name, lowest value gender etc..

Won't I ended up with the lowest value first name, lowest value gender, and lowest value induvidual number. etc?

So Alan Andrews might end up with gender code of F and the wrong customer number, etc.?|||So Alan Andrews might end up with gender code of F and the wrong customer number, etc.?that is quite correct

it indicates that what you are doing (combining multiple rows for the same last name) is probably the wrong approach, as it will likely mash up different people

if the same lastname occurs on multiple rows, do you have any way of differentiating the various rows? any rule for which one you want? and please don't say again "oh, any one"

what is the primary key of your table?|||that is quite correct

it indicates that what you are doing (combining multiple rows for the same last name) is probably the wrong approach, as it will likely mash up different people

if the same lastname occurs on multiple rows, do you have any way of differentiating the various rows? any rule for which one you want? and please don't say again "oh, any one"

what is the primary key of your table?

I have a Unique Site number that is unique to each family. I will probably be doing the group by this number instead of the last name.

How about the 1st record in a group?

I tried using FIRST, but it doesn't appear to be an aggregated function in SQL server 2005.|||there is no such concept as "first" because rows don't have a position

and using Unique Site number merely shifts the problem from lastname, it does not make the problem go away

you will still need to somehow specify which row you want from the group of rows which all have the same Unique Site number

the best way to do this is to designate which row based on its primary key, since primary keys are unique

what is the primary key of your table?|||Abritrary data is garbage, so why use it at all...

Can you explain to us what you are trying to do?|||there is no such concept as "first" because rows don't have a position

and using Unique Site number merely shifts the problem from lastname, it does not make the problem go away

you will still need to somehow specify which row you want from the group of rows which all have the same Unique Site number

the best way to do this is to designate which row based on its primary key, since primary keys are unique

what is the primary key of your table?

I currently don't have a primary key, but I can add one. Once I create this ID field, how would I designate it on my query?

Thanks for all the help!|||I know cursors are forbidden or banished to the WASTELAND:shocked: , and am try to find a way to do this without cursor, sure its possible!

:cool: Till than here is the cursor solution:
(tweeked the employee table a bit)

1> run next 11 line as a batch
declare @.tempname varchar(20)
declare rpt cursor for select distinct lastname from employees
open rpt
fetch next from rpt into @.tempname
while @.@.fetch_status=0
begin
select top 1 * from employees where lastname = @.tempname
fetch next from rpt into @.tempname
end
close rpt
deallocate rpt

Take r937 suggestion, using primary key is better any day, besides two people with same surname can reside a two totally different locations.

:angel: Hope it works, have fun|||Once I create this ID field, how would I designate it on my query?with a correlated subquery, which has the same effect as groupingselect lastname
, firstname
, address
, zipcode
, newPKcolumn
from daTable as T
where newPKcolumn
= ( select min(newPKcolumn)
from daTable
where lastname = T.lastname )the effect of the correlated subquery is to chose the (single) row which has the lowest newPKcolumn value from amongst all the rows with the same lastname|||Abritrary data is garbage, so why use it at all...

Can you explain to us what you are trying to do?

Bret, I am trying to net my results to one per site number. unsing a select query.

Thanks|||r937 you just gave me an idea:beer: .
This is really weird, the following sql command works:

select * from employees as A
where firstname=(select min(firstname)
from employees as B
where A.lastname = B.lastname)

Even though i have three persons with the same firstname and it returns unique records. does it work for anybody else!!! (i tweaked the employee table)|||with a correlated subquery, which has the same effect as groupingselect lastname
, firstname
, address
, zipcode
, newPKcolumn
from daTable as T
where newPKcolumn
= ( select min(newPKcolumn)
from daTable
where lastname = T.lastname )the effect of the correlated subquery is to chose the (single) row which has the lowest newPKcolumn value from amongst all the rows with the same lastname

This worked great. Thanks for your help!|||Not bad eh, for a guy who isn't a dba|||sql skills are not restricted to DBAs, man

that's like saying "wow, you can speak english -- not bad for a guy who isn't a DBA"

being a DBA means you do stuff like replication, installation, permissions, tuning, administration, etc.

you don't have to know any of that cr@.p to be really good at sql :)|||all the stuff I hate to do...especially on DB2 OS/390

Disconnect a particular session

How do I disconnect a particular session without having to stop and restart
the server? I read the article on the DISCONNECT statement in BOL but it
takes as an argument a name, not a connection number. How do I know the
'name' of the connection? Is there a system stored proc that will do this,
taking a connection number as an argument?
Check out KILL in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Ron Hinds" <__NoSpam@.__NoSpamramac.com> wrote in message
news:eWSxgYHjEHA.1656@.TK2MSFTNGP09.phx.gbl...
How do I disconnect a particular session without having to stop and restart
the server? I read the article on the DISCONNECT statement in BOL but it
takes as an argument a name, not a connection number. How do I know the
'name' of the connection? Is there a system stored proc that will do this,
taking a connection number as an argument?
|||A session in SQL Server is represented by a Server Process ID (SPID).
To get your current spid, invoke the @.@.SPID function.
To figure out others' spids, query master..sysprocesses or run sp_who or
sp_who2.
To terminate a connection use KILL <spid>.
To terminate your own session, you can raise an error with severity 20 (only
if you are a sysadmin), e.g.,
RAISERROR ('Connection Seppuku', 20, 1) WITH LOG
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ron Hinds" <__NoSpam@.__NoSpamramac.com> wrote in message
news:eWSxgYHjEHA.1656@.TK2MSFTNGP09.phx.gbl...
> How do I disconnect a particular session without having to stop and
restart
> the server? I read the article on the DISCONNECT statement in BOL but it
> takes as an argument a name, not a connection number. How do I know the
> 'name' of the connection? Is there a system stored proc that will do this,
> taking a connection number as an argument?
>
|||Thanks guys!
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:u0cYFmHjEHA.3536@.TK2MSFTNGP12.phx.gbl...
> A session in SQL Server is represented by a Server Process ID (SPID).
> To get your current spid, invoke the @.@.SPID function.
> To figure out others' spids, query master..sysprocesses or run sp_who or
> sp_who2.
> To terminate a connection use KILL <spid>.
> To terminate your own session, you can raise an error with severity 20
(only[vbcol=seagreen]
> if you are a sysadmin), e.g.,
> RAISERROR ('Connection Seppuku', 20, 1) WITH LOG
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ron Hinds" <__NoSpam@.__NoSpamramac.com> wrote in message
> news:eWSxgYHjEHA.1656@.TK2MSFTNGP09.phx.gbl...
> restart
this,
>

Disconnect a particular session

How do I disconnect a particular session without having to stop and restart
the server? I read the article on the DISCONNECT statement in BOL but it
takes as an argument a name, not a connection number. How do I know the
'name' of the connection? Is there a system stored proc that will do this,
taking a connection number as an argument?
Check out KILL in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Ron Hinds" <__NoSpam@.__NoSpamramac.com> wrote in message
news:eWSxgYHjEHA.1656@.TK2MSFTNGP09.phx.gbl...
How do I disconnect a particular session without having to stop and restart
the server? I read the article on the DISCONNECT statement in BOL but it
takes as an argument a name, not a connection number. How do I know the
'name' of the connection? Is there a system stored proc that will do this,
taking a connection number as an argument?
|||A session in SQL Server is represented by a Server Process ID (SPID).
To get your current spid, invoke the @.@.SPID function.
To figure out others' spids, query master..sysprocesses or run sp_who or
sp_who2.
To terminate a connection use KILL <spid>.
To terminate your own session, you can raise an error with severity 20 (only
if you are a sysadmin), e.g.,
RAISERROR ('Connection Seppuku', 20, 1) WITH LOG
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ron Hinds" <__NoSpam@.__NoSpamramac.com> wrote in message
news:eWSxgYHjEHA.1656@.TK2MSFTNGP09.phx.gbl...
> How do I disconnect a particular session without having to stop and
restart
> the server? I read the article on the DISCONNECT statement in BOL but it
> takes as an argument a name, not a connection number. How do I know the
> 'name' of the connection? Is there a system stored proc that will do this,
> taking a connection number as an argument?
>
|||Thanks guys!
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:u0cYFmHjEHA.3536@.TK2MSFTNGP12.phx.gbl...
> A session in SQL Server is represented by a Server Process ID (SPID).
> To get your current spid, invoke the @.@.SPID function.
> To figure out others' spids, query master..sysprocesses or run sp_who or
> sp_who2.
> To terminate a connection use KILL <spid>.
> To terminate your own session, you can raise an error with severity 20
(only[vbcol=seagreen]
> if you are a sysadmin), e.g.,
> RAISERROR ('Connection Seppuku', 20, 1) WITH LOG
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ron Hinds" <__NoSpam@.__NoSpamramac.com> wrote in message
> news:eWSxgYHjEHA.1656@.TK2MSFTNGP09.phx.gbl...
> restart
this,
>

Disabled/turn off Transcation logging while performing BCP opratio

Hi,
I am doing BCP in particular database.I need to improve perfommace of BCP
opration.I found some article which provide way to minmize transcation
logging.
Please let me know how I completely disabled /turn off transaction logging
for optimized & higher performance.
Regards
Amit Samria
You can't completely turn off transaction logging but you can change your
database to use BULK logging, which works well with bcp. You can do this via
Enterprise Manager or with:
ALTER DATABASE <db name> SET RECOVERY BULK_LOGGED
Jacco Schalkwijk
SQL Server MVP
"Amit Samria" <Amit Samria@.discussions.microsoft.com> wrote in message
news:33190FE5-07CB-43E0-9690-84152EE61503@.microsoft.com...
> Hi,
> I am doing BCP in particular database.I need to improve perfommace of BCP
> opration.I found some article which provide way to minmize transcation
> logging.
> Please let me know how I completely disabled /turn off transaction
> logging
> for optimized & higher performance.
> Regards
> Amit Samria
>

Disabled/turn off Transcation logging while performing BCP opratio

Hi,
I am doing BCP in particular database.I need to improve perfommace of BCP
opration.I found some article which provide way to minmize transcation
logging.
Please let me know how I completely disabled /turn off transaction logging
for optimized & higher performance.
Regards
Amit SamriaYou can't completely turn off transaction logging but you can change your
database to use BULK logging, which works well with bcp. You can do this via
Enterprise Manager or with:
ALTER DATABASE <db name> SET RECOVERY BULK_LOGGED
--
Jacco Schalkwijk
SQL Server MVP
"Amit Samria" <Amit Samria@.discussions.microsoft.com> wrote in message
news:33190FE5-07CB-43E0-9690-84152EE61503@.microsoft.com...
> Hi,
> I am doing BCP in particular database.I need to improve perfommace of BCP
> opration.I found some article which provide way to minmize transcation
> logging.
> Please let me know how I completely disabled /turn off transaction
> logging
> for optimized & higher performance.
> Regards
> Amit Samria
>|||Hi Jacco,
I found in some article that a nonlogged bulk copy can be performed if
The database option select into/bulkcopy is set to true using sp_dboption.
Is above mentioned way same as setting recovery model using alter table.
Regards
Amit Samria
"Jacco Schalkwijk" wrote:
> You can't completely turn off transaction logging but you can change your
> database to use BULK logging, which works well with bcp. You can do this via
> Enterprise Manager or with:
> ALTER DATABASE <db name> SET RECOVERY BULK_LOGGED
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Amit Samria" <Amit Samria@.discussions.microsoft.com> wrote in message
> news:33190FE5-07CB-43E0-9690-84152EE61503@.microsoft.com...
> > Hi,
> > I am doing BCP in particular database.I need to improve perfommace of BCP
> > opration.I found some article which provide way to minmize transcation
> > logging.
> > Please let me know how I completely disabled /turn off transaction
> > logging
> > for optimized & higher performance.
> >
> > Regards
> > Amit Samria
> >
> >
>
>|||Yes. Bulk-Logged recovery in SQL Server 2000 is similar to
setting select into/bulk copy to true in SQL Server 7. Some
operations will be minimally logged with this option or
recovery model.
-Sue
On Mon, 18 Oct 2004 04:19:02 -0700, "Amit Samria"
<AmitSamria@.discussions.microsoft.com> wrote:
>Hi Jacco,
>I found in some article that a nonlogged bulk copy can be performed if
>The database option select into/bulkcopy is set to true using sp_dboption.
>Is above mentioned way same as setting recovery model using alter table.
>Regards
>Amit Samria
>"Jacco Schalkwijk" wrote:
>> You can't completely turn off transaction logging but you can change your
>> database to use BULK logging, which works well with bcp. You can do this via
>> Enterprise Manager or with:
>> ALTER DATABASE <db name> SET RECOVERY BULK_LOGGED
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Amit Samria" <Amit Samria@.discussions.microsoft.com> wrote in message
>> news:33190FE5-07CB-43E0-9690-84152EE61503@.microsoft.com...
>> > Hi,
>> > I am doing BCP in particular database.I need to improve perfommace of BCP
>> > opration.I found some article which provide way to minmize transcation
>> > logging.
>> > Please let me know how I completely disabled /turn off transaction
>> > logging
>> > for optimized & higher performance.
>> >
>> > Regards
>> > Amit Samria
>> >
>> >
>>

Disabled/turn off Transcation logging while performing BCP opratio

Hi,
I am doing BCP in particular database.I need to improve perfommace of BCP
opration.I found some article which provide way to minmize transcation
logging.
Please let me know how I completely disabled /turn off transaction logging
for optimized & higher performance.
Regards
Amit SamriaYou can't completely turn off transaction logging but you can change your
database to use BULK logging, which works well with bcp. You can do this via
Enterprise Manager or with:
ALTER DATABASE <db name> SET RECOVERY BULK_LOGGED
Jacco Schalkwijk
SQL Server MVP
"Amit Samria" <Amit Samria@.discussions.microsoft.com> wrote in message
news:33190FE5-07CB-43E0-9690-84152EE61503@.microsoft.com...
> Hi,
> I am doing BCP in particular database.I need to improve perfommace of BCP
> opration.I found some article which provide way to minmize transcation
> logging.
> Please let me know how I completely disabled /turn off transaction
> logging
> for optimized & higher performance.
> Regards
> Amit Samria
>

disable trigger

Is it possible to disable a Trigger for a particular type of transaction?
There are 2 transactions running A,B..I want the trigger to be disabled for Transaction 'A' but enable it for 'B',even if they are running at the same time?Not directly

--BY SP
if COLUMNS_UPDATED()>0 and 0<trigger_nestlevel(object_id('SP Name')) return
if @.@.rowcount=0 return
if exists(select 'x' from inserted) and 0<trigger_nestlevel(object_id('SP Name')) return
if exists(select 'x' from deleted ) and 0<trigger_nestlevel(object_id('SP Name')) return

--BY TABLE
IF COLUMNS_UPDATED()>0 begin
if exists(select 'x' from YourTable where Id=object_name(@.@.procid) and Op='U') return
end else begin
if @.@.rowcount=0 return
if exists(select 'x' from inserted) and exists(select 'x' from YourTable where Id=object_name(@.@.procid) and Op='I') return
if exists(select 'x' from deleted ) and exists(select 'x' from YourTable where Id=object_name(@.@.procid) and Op='D') return
end

Good luck !

Disable autogrowth of file thru TSQL

Is there a way to programatically disable autogrowth of all database files
residing on a particular drive ? Can someone help ?
Even if I cant do it for all database files in one shot, how can I do them
for individual databases ? Using SQL 2K
Have you tried the ALTER DATABASE...MODIFY FILE command?
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OP7kEkUTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically disable autogrowth of all database files
> residing on a particular drive ? Can someone help ?
> Even if I cant do it for all database files in one shot, how can I do them
> for individual databases ? Using SQL 2K
>
|||The basic syntax for disabling autogrowth on a database file is:
ALTER DATABASE <dbname> MODIFY FILE (NAME = <logical file name>, FILEGROWTH
= 0)
You can do that for all database files with the following script:
DECLARE @.dbname SYSNAME
DECLARE @.filename SYSNAME
CREATE TABLE #dbfiles (dbname sysname NOT NULL, filenm nchar(128))
DECLARE dbs CURSOR FAST_FORWARD
FOR
SELECT name FROM master..sysdatabases
WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution')
-- No messing around with system databases
OPEN dbs
WHILE 1 = 1
BEGIN
FETCH NEXT FROM dbs INTO @.dbname
IF @.@.FETCH_STATUS <> 0 BREAK
EXEC ('INSERT INTO #dbfiles(dbname, filenm)
SELECT ''' + @.dbname + ''', name FROM ' + @.dbname + '..sysfiles
WHERE growth > 0
AND filename LIKE ''C:\%''')
END
CLOSE dbs
DEALLOCATE dbs
SELECT * FROM #dbfiles
DECLARE dbfiles CURSOR FAST_FORWARD FOR
SELECT dbname, filenm FROM #dbfiles
OPEN dbfiles
WHILE 1 = 1
BEGIN
FETCH NEXT FROM dbfiles INTO @.dbname, @.filename
IF @.@.FETCH_STATUS <> 0 BREAK
EXEC ('ALTER DATABASE ' + @.dbname + ' MODIFY FILE (NAME = '
+ @.filename + ', FILEGROWTH = 0)')
END
CLOSE dbfiles
DEALLOCATE dbfiles
DROP TABLE #dbfiles
GO
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OP7kEkUTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically disable autogrowth of all database files
> residing on a particular drive ? Can someone help ?
> Even if I cant do it for all database files in one shot, how can I do them
> for individual databases ? Using SQL 2K
>
|||Hi
You can use the alter database command on each database. sp_MSForEachDB will
allow you to perform the code for each database, but as this is undocumented
it should not be used in production code, alternatively you can use a cursor
to get the databases from master..sysdatabases. sp_helpfile will give you
where the files are located.
John
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OP7kEkUTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically disable autogrowth of all database files
> residing on a particular drive ? Can someone help ?
> Even if I cant do it for all database files in one shot, how can I do them
> for individual databases ? Using SQL 2K
>

Disable autogrowth of file thru TSQL

Is there a way to programatically disable autogrowth of all database files
residing on a particular drive ? Can someone help ?
Even if I cant do it for all database files in one shot, how can I do them
for individual databases ? Using SQL 2KHave you tried the ALTER DATABASE...MODIFY FILE command?
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OP7kEkUTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically disable autogrowth of all database files
> residing on a particular drive ? Can someone help ?
> Even if I cant do it for all database files in one shot, how can I do them
> for individual databases ? Using SQL 2K
>|||The basic syntax for disabling autogrowth on a database file is:
ALTER DATABASE <dbname> MODIFY FILE (NAME = <logical file name>, FILEGROWTH
= 0)
You can do that for all database files with the following script:
DECLARE @.dbname SYSNAME
DECLARE @.filename SYSNAME
CREATE TABLE #dbfiles (dbname sysname NOT NULL, filenm nchar(128))
DECLARE dbs CURSOR FAST_FORWARD
FOR
SELECT name FROM master..sysdatabases
WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution')
-- No messing around with system databases
OPEN dbs
WHILE 1 = 1
BEGIN
FETCH NEXT FROM dbs INTO @.dbname
IF @.@.FETCH_STATUS <> 0 BREAK
EXEC ('INSERT INTO #dbfiles(dbname, filenm)
SELECT ''' + @.dbname + ''', name FROM ' + @.dbname + '..sysfiles
WHERE growth > 0
AND filename LIKE ''C:\%''')
END
CLOSE dbs
DEALLOCATE dbs
SELECT * FROM #dbfiles
DECLARE dbfiles CURSOR FAST_FORWARD FOR
SELECT dbname, filenm FROM #dbfiles
OPEN dbfiles
WHILE 1 = 1
BEGIN
FETCH NEXT FROM dbfiles INTO @.dbname, @.filename
IF @.@.FETCH_STATUS <> 0 BREAK
EXEC ('ALTER DATABASE ' + @.dbname + ' MODIFY FILE (NAME = '
+ @.filename + ', FILEGROWTH = 0)')
END
CLOSE dbfiles
DEALLOCATE dbfiles
DROP TABLE #dbfiles
GO
--
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OP7kEkUTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically disable autogrowth of all database files
> residing on a particular drive ? Can someone help ?
> Even if I cant do it for all database files in one shot, how can I do them
> for individual databases ? Using SQL 2K
>|||Hi
You can use the alter database command on each database. sp_MSForEachDB will
allow you to perform the code for each database, but as this is undocumented
it should not be used in production code, alternatively you can use a cursor
to get the databases from master..sysdatabases. sp_helpfile will give you
where the files are located.
John
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OP7kEkUTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically disable autogrowth of all database files
> residing on a particular drive ? Can someone help ?
> Even if I cant do it for all database files in one shot, how can I do them
> for individual databases ? Using SQL 2K
>

Disable autogrowth of file thru TSQL

Is there a way to programatically disable autogrowth of all database files
residing on a particular drive ? Can someone help ?
Even if I cant do it for all database files in one shot, how can I do them
for individual databases ? Using SQL 2KHave you tried the ALTER DATABASE...MODIFY FILE command?
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OP7kEkUTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically disable autogrowth of all database files
> residing on a particular drive ? Can someone help ?
> Even if I cant do it for all database files in one shot, how can I do them
> for individual databases ? Using SQL 2K
>|||The basic syntax for disabling autogrowth on a database file is:
ALTER DATABASE <dbname> MODIFY FILE (NAME = <logical file name>, FILEGROWTH
= 0)
You can do that for all database files with the following script:
DECLARE @.dbname SYSNAME
DECLARE @.filename SYSNAME
CREATE TABLE #dbfiles (dbname sysname NOT NULL, filenm nchar(128))
DECLARE dbs CURSOR FAST_FORWARD
FOR
SELECT name FROM master..sysdatabases
WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution')
-- No messing around with system databases
OPEN dbs
WHILE 1 = 1
BEGIN
FETCH NEXT FROM dbs INTO @.dbname
IF @.@.FETCH_STATUS <> 0 BREAK
EXEC ('INSERT INTO #dbfiles(dbname, filenm)
SELECT ''' + @.dbname + ''', name FROM ' + @.dbname + '..sysfiles
WHERE growth > 0
AND filename LIKE ''C:\%''')
END
CLOSE dbs
DEALLOCATE dbs
SELECT * FROM #dbfiles
DECLARE dbfiles CURSOR FAST_FORWARD FOR
SELECT dbname, filenm FROM #dbfiles
OPEN dbfiles
WHILE 1 = 1
BEGIN
FETCH NEXT FROM dbfiles INTO @.dbname, @.filename
IF @.@.FETCH_STATUS <> 0 BREAK
EXEC ('ALTER DATABASE ' + @.dbname + ' MODIFY FILE (NAME = '
+ @.filename + ', FILEGROWTH = 0)')
END
CLOSE dbfiles
DEALLOCATE dbfiles
DROP TABLE #dbfiles
GO
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OP7kEkUTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically disable autogrowth of all database files
> residing on a particular drive ? Can someone help ?
> Even if I cant do it for all database files in one shot, how can I do them
> for individual databases ? Using SQL 2K
>|||Hi
You can use the alter database command on each database. sp_MSForEachDB will
allow you to perform the code for each database, but as this is undocumented
it should not be used in production code, alternatively you can use a cursor
to get the databases from master..sysdatabases. sp_helpfile will give you
where the files are located.
John
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OP7kEkUTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically disable autogrowth of all database files
> residing on a particular drive ? Can someone help ?
> Even if I cant do it for all database files in one shot, how can I do them
> for individual databases ? Using SQL 2K
>