Best Practices Analyzer

Before hand, I Apologize if I am posting this on a wrong group.
I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" from
http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
is installed. The installation went fine. I created a report by connecting to
a server. Now, I want to print the report but I can't.
The document "MSBPA_User_Guide.doc" that comes with it says that I should be
able to print report by clicking on the "print report" icon on "Compliance
Reports" page but THERE IS NO "PRINT REPORT" icon.
Am I dreaming ? or what document says really doesn't match what the software
does ? or is this a limitation on Windows XP ?
Thanks for any help.Hi DXC:
you should have just four choices on the report form:
[Remove Report], [Copy Report], [Previous Report] and [Next Report].
To print a report you should use the [Copy] option and then paste it ona a
Word document or on any other editor you like.
This action let you generate a document containig the reèportsummary. to
Print the details you should copy any of them (or just the details you are
interested for) one at a time.
"DXC" wrote:
> Before hand, I Apologize if I am posting this on a wrong group.
> I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" from
> http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
> on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
> is installed. The installation went fine. I created a report by connecting to
> a server. Now, I want to print the report but I can't.
> The document "MSBPA_User_Guide.doc" that comes with it says that I should be
> able to print report by clicking on the "print report" icon on "Compliance
> Reports" page but THERE IS NO "PRINT REPORT" icon.
> Am I dreaming ? or what document says really doesn't match what the software
> does ? or is this a limitation on Windows XP ?
> Thanks for any help.|||Thanks Gilberto.........I wanted to print details also but If I have to
print one by one then I will not print.........Is it any better if I use
the "SQL Server Reporting Services" ?
Thanks again.
"Gilberto Zampatti" wrote:
> Hi DXC:
> you should have just four choices on the report form:
> [Remove Report], [Copy Report], [Previous Report] and [Next Report].
> To print a report you should use the [Copy] option and then paste it ona a
> Word document or on any other editor you like.
> This action let you generate a document containig the reèportsummary. to
> Print the details you should copy any of them (or just the details you are
> interested for) one at a time.
> "DXC" wrote:
> > Before hand, I Apologize if I am posting this on a wrong group.
> >
> > I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" from
> >
> > http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
> >
> > on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
> > is installed. The installation went fine. I created a report by connecting to
> > a server. Now, I want to print the report but I can't.
> >
> > The document "MSBPA_User_Guide.doc" that comes with it says that I should be
> > able to print report by clicking on the "print report" icon on "Compliance
> > Reports" page but THERE IS NO "PRINT REPORT" icon.
> >
> > Am I dreaming ? or what document says really doesn't match what the software
> > does ? or is this a limitation on Windows XP ?
> >
> > Thanks for any help.|||To use Reporting services you need to save the report details in a compliant
data source: anyway you have to peek each detail you are interested to.
Surely you can obtain a much better result in terms of presentation and
usability.
"DXC" wrote:
> Thanks Gilberto.........I wanted to print details also but If I have to
> print one by one then I will not print.........Is it any better if I use
> the "SQL Server Reporting Services" ?
> Thanks again.
> "Gilberto Zampatti" wrote:
> > Hi DXC:
> > you should have just four choices on the report form:
> > [Remove Report], [Copy Report], [Previous Report] and [Next Report].
> > To print a report you should use the [Copy] option and then paste it ona a
> > Word document or on any other editor you like.
> > This action let you generate a document containig the reèportsummary. to
> > Print the details you should copy any of them (or just the details you are
> > interested for) one at a time.
> >
> > "DXC" wrote:
> >
> > > Before hand, I Apologize if I am posting this on a wrong group.
> > >
> > > I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" from
> > >
> > > http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
> > >
> > > on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
> > > is installed. The installation went fine. I created a report by connecting to
> > > a server. Now, I want to print the report but I can't.
> > >
> > > The document "MSBPA_User_Guide.doc" that comes with it says that I should be
> > > able to print report by clicking on the "print report" icon on "Compliance
> > > Reports" page but THERE IS NO "PRINT REPORT" icon.
> > >
> > > Am I dreaming ? or what document says really doesn't match what the software
> > > does ? or is this a limitation on Windows XP ?
> > >
> > > Thanks for any help.

Best Practices Analyzer

Before hand, I Apologize if I am posting this on a wrong group.
I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" from
http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
is installed. The installation went fine. I created a report by connecting to
a server. Now, I want to print the report but I can't.
The document "MSBPA_User_Guide.doc" that comes with it says that I should be
able to print report by clicking on the "print report" icon on "Compliance
Reports" page but THERE IS NO "PRINT REPORT" icon.
Am I dreaming ? or what document says really doesn't match what the software
does ? or is this a limitation on Windows XP ?
Thanks for any help.
Hi DXC:
you should have just four choices on the report form:
[Remove Report], [Copy Report], [Previous Report] and [Next Report].
To print a report you should use the [Copy] option and then paste it ona a
Word document or on any other editor you like.
This action let you generate a document containig the reèportsummary. to
Print the details you should copy any of them (or just the details you are
interested for) one at a time.
"DXC" wrote:

> Before hand, I Apologize if I am posting this on a wrong group.
> I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" from
> http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
> on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
> is installed. The installation went fine. I created a report by connecting to
> a server. Now, I want to print the report but I can't.
> The document "MSBPA_User_Guide.doc" that comes with it says that I should be
> able to print report by clicking on the "print report" icon on "Compliance
> Reports" page but THERE IS NO "PRINT REPORT" icon.
> Am I dreaming ? or what document says really doesn't match what the software
> does ? or is this a limitation on Windows XP ?
> Thanks for any help.
|||Thanks Gilberto.........I wanted to print details also but If I have to
print one by one then I will not print.........Is it any better if I use
the "SQL Server Reporting Services" ?
Thanks again.
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> Hi DXC:
> you should have just four choices on the report form:
> [Remove Report], [Copy Report], [Previous Report] and [Next Report].
> To print a report you should use the [Copy] option and then paste it ona a
> Word document or on any other editor you like.
> This action let you generate a document containig the reèportsummary. to
> Print the details you should copy any of them (or just the details you are
> interested for) one at a time.
> "DXC" wrote:
|||To use Reporting services you need to save the report details in a compliant
data source: anyway you have to peek each detail you are interested to.
Surely you can obtain a much better result in terms of presentation and
usability.
"DXC" wrote:
[vbcol=seagreen]
> Thanks Gilberto.........I wanted to print details also but If I have to
> print one by one then I will not print.........Is it any better if I use
> the "SQL Server Reporting Services" ?
> Thanks again.
> "Gilberto Zampatti" wrote:
sql

Best Practices Analyzer

Before hand, I Apologize if I am posting this on a wrong group.
I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" fr
om
http://www.microsoft.com/downloads/...&displaylang=en
on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
is installed. The installation went fine. I created a report by connecting t
o
a server. Now, I want to print the report but I can't.
The document "MSBPA_User_Guide.doc" that comes with it says that I should be
able to print report by clicking on the "print report" icon on "Compliance
Reports" page but THERE IS NO "PRINT REPORT" icon.
Am I dreaming ? or what document says really doesn't match what the software
does ? or is this a limitation on Windows XP ?
Thanks for any help.Hi DXC:
you should have just four choices on the report form:
[Remove Report], [Copy Report], [Previous Report] and [Next
Report].
To print a report you should use the [Copy] option and then paste it ona
a
Word document or on any other editor you like.
This action let you generate a document containig the reèportsummary. to
Print the details you should copy any of them (or just the details you are
interested for) one at a time.
"DXC" wrote:

> Before hand, I Apologize if I am posting this on a wrong group.
> I have downloaded and installed "SQL Server 2000 Best Practices Analyzer"
from
> http://www.microsoft.com/downloads/...&displaylang=en
> on to my Windows XP machine where I have "SQL Server 2000 Personal additio
n"
> is installed. The installation went fine. I created a report by connecting
to
> a server. Now, I want to print the report but I can't.
> The document "MSBPA_User_Guide.doc" that comes with it says that I should
be
> able to print report by clicking on the "print report" icon on "Compliance
> Reports" page but THERE IS NO "PRINT REPORT" icon.
> Am I dreaming ? or what document says really doesn't match what the softwa
re
> does ? or is this a limitation on Windows XP ?
> Thanks for any help.|||Thanks Gilberto.........I wanted to print details also but If I have to
print one by one then I will not print.........Is it any better if I use
the "SQL Server Reporting Services" ?
Thanks again.
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> Hi DXC:
> you should have just four choices on the report form:
> [Remove Report], [Copy Report], [Previous Report] and [Nex
t Report].
> To print a report you should use the [Copy] option and then paste it o
na a
> Word document or on any other editor you like.
> This action let you generate a document containig the reèportsummary. to
> Print the details you should copy any of them (or just the details you are
> interested for) one at a time.
> "DXC" wrote:
>|||To use Reporting services you need to save the report details in a compliant
data source: anyway you have to peek each detail you are interested to.
Surely you can obtain a much better result in terms of presentation and
usability.
"DXC" wrote:
[vbcol=seagreen]
> Thanks Gilberto.........I wanted to print details also but If I have to
> print one by one then I will not print.........Is it any better if I us
e
> the "SQL Server Reporting Services" ?
> Thanks again.
> "Gilberto Zampatti" wrote:
>

Best Practice for retrieving last record

Hi there, very sorry if this is the wrong forum to post this in.

I want to know what is the BEST practice, the correct Microsoft way of doing this:

basically, lets say I am inserting a new record into SQL. simple customer record:

firstname

lastname

address

city

postcode

password

dateOfRegistration (SQL has this value and the default value is the getdate())

That's all very well. I want to know how I can get the recordID for this and return that back from the caller (returning is easily done) -

You cannot really after this insertion, perform a SELECT statement to get the LAST record entered, as there maybe several records that could all be inserted at the same time by coincidence. It's not the best way of going about this.

I want to know what is the best way of getting the just inserted record's recordID - I was thinking about using date and time, manually inputting them and then using that to retrieve the last record/current inserting record but again its not the best way of going about doing this.

what is the best way?

Many thanks for your help!

Hi,

If your recordID is an Identity column of your table, you could use the SCOPE_IDENTITY function.

INSERT INTO Customer (Fname,Lname,Address,City,PostCode,PWD,DOfReg)

VALUES ('John','Smith','123 drwerwr','Montreal','X1X 1X1''***','2006-03-31')

SELECT SCOPE_IDENTITY()

This will retreive the last RecordId inserted

|||

cool - had no idea such a thing existed but then again i am still learning the great SQL Server

so please tell me technically, how does the SCOPE_IDENTITY() work? in technical terms - what does it do in process?

|||

From BOL

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

HTH,

Eric

|||you can add a timestamp field to your table then order by it desc and select the top 1|||

Don't know about using a time stamp ...

The timestamp value would change on update as well.. and in a multi-user application you can't garantee that the last record inserted is yours if you use a timestamp.

|||Hi,

if you are using SQL Server 2005 you can use the new keyword OUTPUT within the insert command. By the new keyword you can directly get a value back which can represent the identity or whatever column:

Look for the output clause in the BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I am using SQL Server 2000 but also interested in the cool stuff of SQL Server 2005 (on the side)

Currently using the SCOPE_IDENTITY() in SQL Server 2000

the suggestion about sorting the results in ASC order and select the last one - well, it may be logical to do so but too much effort in terms of performance and ineffeciency :-)

any other best way? :-)

|||

Using identity values for PRIMARY KEY values is fine, and a practice I use all of the time. But, it should not be your only unique criteria on the table.

For example, consider your table:

firstname, lastname, address, city, postcode, password, dateOfRegistration

What if two rows were inserted with the same firstname, lastname, address, city, postcode, password, dateOfRegistration. Most likely this would be in error right? Well if haven't added a UNIQUE constraint to the data, what happens when you look them up? How will you as a human tell them apart, much less the stupid, just do what you programmed it to computer.

So at the very least add a UNIQUE constraint on all of these columns.

Then, if you don't have SCOPE_IDENTITY() (An instead of trigger will invalidate SCOPE_IDENTITY() if you ever need one, for example) you just just say:

select rowId
from tablename
where firstname = @.firstname
and lastname = @.lastname

etc. Don't give up all control of the data/situation to SQL Server, though use the tools like scope_identity to make life easier. :)

|||

Many thanks Louis i appreciate this

Well the unique check would be the address in my case which the stored procedure checks to see if it exists, if it does it returns a value (like -1 or whatever) otherwise it continues and returns the ID of the record which is inserting.

Thank-you!

|||If you had the unique constraint there it would probably make this operation faster too.|||

awesome!!!!!

Performance is the key baby, LOVE SQL

thank-you Louis :-D

Benchmarking Database Performance

First of all, sorry if this is in the wrong section, didn't know where to put it.

I'm doing a university paper comparing Microsoft Access versus SQL Server 2000 and I want to run a benchmark on them to see which is faster, does anyone know of any applications that will let me do this?

You may think this is silly since SQL Server is quite obviously faster, thing is a can't just say that in my paper, I have to be more specific so was hoping I could run some benchmarks and show the scores on the paper.

I know I can run an ASP script that can time how long the query takes to run, however this can't test multiple concurrent users accessing the system (useless I get all my friends computers around my house, bring up the page and get them to click on refresh all at the same time :) ).

I basically want to run a simple SQL SELECT statement on an identical database in both database systems (Northwind), but for mutliple users. Anyone know of any application?

Or does anyone know any performance tests that has been done on comparing Access with SQL Server? All I can find is material comparing high-end database against high-end database (Oracle vs SQL Server vs IBM DB2 etc.). If I can't do my own I can always use other peoples. Cheers!From the book "SQL Server Resource Kit", the CD that comes with the book contains a utility named "Database Hammer". The utility creates it's own database on SQL Server and performs a lot of transactions to test performance. It is most useful for comparing Server A to Server B, both running SQL Server.

Here's a link:

http://www.winnetmag.com/Article/ArticleID/26083/26083.html

Of course, this utility will not work against an Access database, but it should give you some ideas. Hope this helps...|||After some hunting around I found Quest Software's Benchmark Factory that seems to do the job really well. SQL Server 2000 handles about 21 transactions per second with 5 concurrent users, Access can only manage about 11. Does this seem right? This was from running a fairly complex SQL SELECT statement, performing mutliple inner joins on many tables in the Northwind sample database.

Would performing actual transactions on the Northwind database (INSERTs, UPDATEs etc) show bigger performance differences between Access and SQL Server?

Also does anyone have any experience with using Benchmark Factory? How do test more than 5 concurrent users? It only seems to let me select upto five, but the help files seem to indicate that you can test with as many as 100s of users.

Thanks for any help! :)|||From the book "SQL Server Resource Kit", the CD that comes with the book contains a utility named "Database Hammer". The utility creates it's own database on SQL Server and performs a lot of transactions to test performance. It is most useful for comparing Server A to Server B, both running SQL Server.

Here's a link:

http://www.winnetmag.com/Article/ArticleID/26083/26083.html

Of course, this utility will not work against an Access database, but it should give you some ideas. Hope this helps...

Thanks for the help, although my paper is on comparing Access and SQL Server, I'm building a system next year that uses an SQL Server backend database that handles tens thousands of records, and that utility will certainly help me with some optimisations.