Create an SQL View from VBA

Hello All;

I'm new to SQL, and developing my first application using SQL 2000 as the back end for my data.

Can anyone tell me if I can create an SQL View from VBA?

I'm trying to create a data view that access various records from a single table, and it has to distribute that data 2 14 different subforms, representing different days in a 2 week period, which is distingiushed by a field called "Day", that is numbered from 1 to 14.

I also have a summary subform that has the weekly summary (days 1 to 7 and 8 to 14) on seperate subforms. In total I have 16 subforms, which actually source from a single table, just split depending on the day in the period.

As I see it now, creating views iis the best way for me to go, but I also have to be able to change the period id on the fly, so I'm think I have to use VBA to generate these views.

Does any of this make sense, and am I on the right track??You might want to consider a more dynamic solution. Do the 14 forms all hold the same data fields? If so, why not use one form and base the contents on the day of a week. Make the day of the week a field in your table and populate your form based on a stored procedure that uses the day of the week as a parameter in the query.

Fixing your schema now will pay you back many fold in the future.

Avoid creating database objects on the fly in end user applications.

To answer your question, yes this is possible. Is it a good idea? No.|||... populate your form based on a stored procedure that uses the day of the week as a parameter in the query.

Fixing your schema now will pay you back many fold in the future.

Avoid creating database objects on the fly in end user applications.

To answer your question, yes this is possible. Is it a good idea? No.

Agreed with all the points here. You might also consider a user defined function that returns a table. I don't generally use these for multi-column result sets, but it is permissible to do so.

Perhaps you could post some ddl and sample data and improve your chance for getting a useable answer...

Regards,

hmscott

Create a view to get latest status for each application

I would like some help creating a view that will display the latest status for each application. The lastest status should be based on CreateDt.

For example:

Table Structure:

============

Application: ApplicationID, Name, Address, City, State, Zip, etc..

ApplicationAction: ApplicationActionID, ApplicationID, Status (Ex:new, reviewed, approved, closed), CreateDt

View should display:

==============

ApplicantID, ApplicantActionID, Status, CreateDt

Example:

==========

ApplicantID=4, Name=Bob Smith, etc...

ApplicantActionID=1, ApplicantID=4, Status=New, CreatDt=1/3/20071:00

ApplicantActionID=2, ApplicantID=4, Status=Reviewed, CreatDt=1/3/2007 2:00

ApplicantActionID=3, ApplicantID=4, Status=Approved, CreatDt=1/4/2007 1:00

... etc...

View should return:

Applicant=4, ApplicantActionID=3, Status=Approved, CreatDt=1/4/2007 1:00

etc...

Hint: Use MAX(CreatDt) to get the information you need.|||

well I got that far, I need help getting beyond that:( Any ideas?

|||Can you post what you have so far?|||

Oh well..

Declare @.ApTable (ApplicationIDint ,Name varchar(50), Addressvarchar(50), Cityvarchar(50), Statevarchar(50), Zipvarchar(50))Declare @.AATable (ApplicationActionIDint, ApplicationIDint, Statusvarchar(50), CreateDtdatetime)Insert into @.ApSELECt 4,'Bob Smith','123 street','SomeCity','SS','12345'UNIONALLSELECT 5,'New Smith','sss','ss','a','4455'Insert into @.AASELECT 1,4,'New','1/3/2007 1:00'UNIONALLSELECT 2,4,'Reviewed','1/3/2007 2:00'UNIONALLSELECT 3,4,'Approved','1/4/2007 1:00'UNIONALLSELECT 1,5,'New','5/24/2006 1:00'Select *from @.ApSelect *from @.AASELECT aa.*FROM (select applicationid,max(Createdt)as MxCreateDtfrom @.AAgroup by applicationid) Xjoin @.AA AAon aa.createdt = X.MXcreatedt

Create a Testing Database out of existing DB

Hello All,

I was wondering if anybody can help me with the following question:

I'm working on the application where the Database, it's table (2) and several stored procedures are involved. The database is SQL Server 2000. It's also very old and involves a lot of operations, stored proc and so on. I just need to re-write a piece of the app which is using existing stored proc. Most of them are DELETE, INSERT and so on. I don't want to work with real stage DB and need to make a copy of the Database to my Dev box. So I tried:

* Right click, All Tasks, Export Data into the newly created database on my dev box.

That doesn't work, every time I try doing it, it fails somewhere in the middle of the process. I'm thinking it happens because of complexity of the database. I tried several options there already. Still nothing. I need the whole databse to be copied because I'm not sure which stored proc the app is using so I need them all, and tables too. Is there another way of doing this?

Thank you,

Tatyana

If you make a backup of the database (or detach it) you can restore it (or re-attach it) under a different name and therefore create multiple instances of it.

|||

Mark,

Thank you very much! Your suggestion worked!

Tatyana

sql

Create A New UserName

I am using the following ConnectionString in an ASP file so that the ASP
application can communicate with SQL Server 7.0:
CONNECTIONSTRING="Provider=SQLOLEDB;User ID=SoGuest;Password=SoGuest;
Persist Security
Info=True;Initial Catalog=ForestDB;Data Source=MyServer"
In order to ensure that my ASP page interacts with the database ForestDB, I
have to create a new user named SoGuest whose password will be SoGuest. To
do this, I went to 'Users' under the ForestDB tree, right-clicked & then
clicked 'New Database User'. A dialog box opened up. Next when I clicked the
drop-down list for assigning a login name, I find that there's not a single
login name in the drop-down list as a result of which I am not being allowed
to proceed forward to create the new user SoGuest. How do I create this new
user? Please note that when I try to create a new user in some other DB like
Pubs, Northwind etc. I am given 'BUILTIN\Administrators' as an option in the
drop-down list of the login name. The authentication mode I am using is both
SQL Server & Windows NT (I am working on Windows 2000 Professional).
Thanks,
ArpanIt sounds as though you have not yet created a login. In Enterprise Manager
open up the Security container and right-click Logins. Create a new login
called whatever you want. Assign him to those databases you want him to
access. That will create both your login and database user.
A login allows someone to be verified when they connect to SQL. It is an
entry in the sysxlogins table in the Master db.
A User is someone who already has a login in the sysxlogins table, but
requires access to a specific database. They are placed in the sysusers
table in the particular database they are trying to access.
The Enterprise Manager dialog allows you to add a login and user at the same
time.
HTH
Bob
--
Warning: Do not look into the light sabre whilst switching it on
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003|||You need to create the login in the Security folder first (and you can create the user as well from
that dialog).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Arpan" <arpan_de@.hotmail.com> wrote in message news:%23JLbRLchDHA.2420@.TK2MSFTNGP10.phx.gbl...
> I am using the following ConnectionString in an ASP file so that the ASP
> application can communicate with SQL Server 7.0:
> CONNECTIONSTRING="Provider=SQLOLEDB;User ID=SoGuest;Password=SoGuest;
> Persist Security
> Info=True;Initial Catalog=ForestDB;Data Source=MyServer"
> In order to ensure that my ASP page interacts with the database ForestDB, I
> have to create a new user named SoGuest whose password will be SoGuest. To
> do this, I went to 'Users' under the ForestDB tree, right-clicked & then
> clicked 'New Database User'. A dialog box opened up. Next when I clicked the
> drop-down list for assigning a login name, I find that there's not a single
> login name in the drop-down list as a result of which I am not being allowed
> to proceed forward to create the new user SoGuest. How do I create this new
> user? Please note that when I try to create a new user in some other DB like
> Pubs, Northwind etc. I am given 'BUILTIN\Administrators' as an option in the
> drop-down list of the login name. The authentication mode I am using is both
> SQL Server & Windows NT (I am working on Windows 2000 Professional).
> Thanks,
> Arpan
>|||Thanks, Bob, for your advice. You have hit the nail on the head in saying
that I might not have created a login for the database ForestDB. Thanks to
Tibor as well.
Regards,
Arpan
"Bob Simms" <bob_simms@.hotmail.com> wrote in message
news:4XCdb.408$_d.312@.news-binary.blueyonder.co.uk...
> It sounds as though you have not yet created a login. In Enterprise
Manager
> open up the Security container and right-click Logins. Create a new login
> called whatever you want. Assign him to those databases you want him to
> access. That will create both your login and database user.
> A login allows someone to be verified when they connect to SQL. It is an
> entry in the sysxlogins table in the Master db.
> A User is someone who already has a login in the sysxlogins table, but
> requires access to a specific database. They are placed in the sysusers
> table in the particular database they are trying to access.
> The Enterprise Manager dialog allows you to add a login and user at the
same
> time.
> HTH
> Bob
> --
> Warning: Do not look into the light sabre whilst switching it on
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.520 / Virus Database: 318 - Release Date: 18/09/2003
>

CR9 Runtime Components ?

Dear All,
I developed an application in VC++ 6.0 with Crystal Reports 9.0.

Now i want to deploy my application on client machine but i don't now what components (files, dll) of CR are required to register or deploy with my application.

Would some body please help me, its really very urgent.Hi! All!

I'm aslo having the same problem in CR9 Runtime Components in Client machine.

I'm using VB 6 & Crystal Reports 9.

What are all the CR9 componenets or dll files need to be installed in Client machine?

Please if anyone help me to solve this problem.

Thanks.

-Raja|||Visit the next urls -->

http://support.businessobjects.com/communityCS/TechnicalPapers/rdc9_deployment.pdf.asp

http://support.businessobjects.com/documentation/installation_resources/crystal_reports/cr9.asp

CR Server ?

Can SQL Server RS coexist with Crystal Report Server - Report Application
Server ?
ThanksAlthough I have not installed both at the same time, SQL RS is simply a DOT
NET ASP Application that should be able to co-exist with almost anything
else. ( expect it's own beta version perhaps.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23EJ2zhjeFHA.1328@.TK2MSFTNGP12.phx.gbl...
> Can SQL Server RS coexist with Crystal Report Server - Report Application
> Server ?
> Thanks
>

CR interactivity in question..

I have a web application. When the user presses a button a crystal report comes on screen.

What i need to know is, if there is a possibility to implement this:

"When the user clicks on a certain field on a certain row, i want to run function A in my web application, with parameters the row clicked and the field clicked".

i just wanna add on-click code in field level (or perhaps add in my report a button to do sth), can i do that??

ThanksYou can use parameters to pass the information from your app to Crystal. I don't know that proper syntax though.

Try searching Crystal's website:
http://support.businessobjects.com/search/advsearch.asp

Or Crystal's Forum:
http://support.businessobjects.com/forums/default.asp|||What are you trying to do?
1. Do you want to select a report based on data in selected row?
2. Or do you want to see more data related to the row you selected?

If your option is 1, then use a DataGrid and populate it with records, and then when user clicks on selected row, trap the click event and pass the value of that row to a Crystal Report as the parameter for that report.
For some help with parameters and how to use them in .NET, try this link:
http://support.businessobjects.com/library/kbase/articles/c2010247.asp

If it's 2, design the report so that it has an "On-Demand" subreport, so that user can click the link which will appear in your Details section, which will trigger the subreport based on the data in that report line.

It's helpful if your question details the implementation, "I have a web application." really doesn't tell us enough, so I've presumed it's .NET, because .NET incorporates basic Crystal functionality.

Dave|||I need to put a control, say a button inside the report. When the user presses the button i want something else, irrelevant of the report to happen (e.g. navigate to a certain page).

1) How can i add a button in my report?
2) How do i trap the on-click event?

Thanks

CR 8.5 to CR 10 in Visual C++

Hello,

I have an application in Visual C++ working with CR 8.5. To do it, I'm using crpe.h, cpre32m.lib,... but in CR10 these files don't exist.
In CR 10, the print engine is replaced by RDC.
My question is , can I use RDC without use managed code? All samples on Crystal work with managed code but I can't use in my application !!!!
How can I link my application to the new print engine?

Thanks a lot for your helpDid you ever find the answer to this question?? I have the same issue.|||See if you find solution here
http://support.businessobjects.com/|||how do i refresh crystal report data through codes|||CR.Refresh

where CR is the Crystal Reports component

cpu up with SQL2005 - higher FullScans/sec, lwr IndexSearches/sec

We are seeing a 29% increase in the cpu of the sql process in SQL2005 over a
5 hour perfmorance test. The application code is the same and the actions
performed during the test are the same. The increase is compared to SQL2000.
The db server is the same and was upgraded from SQL2000.
Disk Configuration: SCSI RAID 5, 3 Partitions
RAM: 3.75 GB of RAM
CPU: Quad Processor, 2.7Ghz
OS: Windows2003 SP1 Enterprise
* Perfmon shows disk read/sec and disk write/sec fairly close between
SQL2000 and SQL2005
Specifically, perfmon shows increased FullScans/sec and decreased
IndexSearches/sec.
From perfmon
FullScan/sec (avg):
2000: 6.292
2005: 54.338
IndexSearches/sec (avg):
2000: 3455.942
2005: 1441.712
Any comments or suggestions would be greatly appreciated. Thanks.Did you update the stats after you brought over the db?
Andrew J. Kelly SQL MVP
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||We saw even worse behavior. But I fixed it with these 3 things and it
resolved most of the slowness (not sure if you need one or both)
-- to update all stats
exec sp_updatestats
--rebuild all indexes
dbcc dbreindex('tablename') for each table
--set all sps to recompile on next execute
exec sp_recompile 'sp_name' for every stored procedure in your database
I identified a few other queries that just had to be re-written. They
seemed to always involve subqueries using "where not in (select ? from...)
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||I did not manually execute such a step. And if this wasnt done automatically
with the upgrade to SQL2005 then it was not done.
Is this the link for this?
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
or are there more specifc instructions you can point me to?
Thanks
"Andrew J. Kelly" wrote:
> Did you update the stats after you brought over the db?
>
> --
> Andrew J. Kelly SQL MVP
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> > We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> > a
> > 5 hour perfmorance test. The application code is the same and the actions
> > performed during the test are the same. The increase is compared to
> > SQL2000.
> >
> > The db server is the same and was upgraded from SQL2000.
> > Disk Configuration: SCSI RAID 5, 3 Partitions
> > RAM: 3.75 GB of RAM
> > CPU: Quad Processor, 2.7Ghz
> > OS: Windows2003 SP1 Enterprise
> >
> > * Perfmon shows disk read/sec and disk write/sec fairly close between
> > SQL2000 and SQL2005
> >
> > Specifically, perfmon shows increased FullScans/sec and decreased
> > IndexSearches/sec.
> >
> > From perfmon
> > FullScan/sec (avg):
> > 2000: 6.292
> > 2005: 54.338
> >
> > IndexSearches/sec (avg):
> > 2000: 3455.942
> > 2005: 1441.712
> >
> > Any comments or suggestions would be greatly appreciated. Thanks.
>
>|||No this is not done by the upgrade process. The link tells you what stats
do. To update them either run sp_updatestats to update all stats or if you
haven't run DBCC DBREINDEX in a while try running that on all tables.
--
Andrew J. Kelly SQL MVP
"George Koulis" <GeorgeKoulis@.discussions.microsoft.com> wrote in message
news:071092A9-6057-47BB-A0A1-3EC8061D088C@.microsoft.com...
>I did not manually execute such a step. And if this wasnt done
>automatically
> with the upgrade to SQL2005 then it was not done.
> Is this the link for this?
> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
> or are there more specifc instructions you can point me to?
> Thanks
>
> "Andrew J. Kelly" wrote:
>> Did you update the stats after you brought over the db?
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in
>> message
>> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>> > We are seeing a 29% increase in the cpu of the sql process in SQL2005
>> > over
>> > a
>> > 5 hour perfmorance test. The application code is the same and the
>> > actions
>> > performed during the test are the same. The increase is compared to
>> > SQL2000.
>> >
>> > The db server is the same and was upgraded from SQL2000.
>> > Disk Configuration: SCSI RAID 5, 3 Partitions
>> > RAM: 3.75 GB of RAM
>> > CPU: Quad Processor, 2.7Ghz
>> > OS: Windows2003 SP1 Enterprise
>> >
>> > * Perfmon shows disk read/sec and disk write/sec fairly close between
>> > SQL2000 and SQL2005
>> >
>> > Specifically, perfmon shows increased FullScans/sec and decreased
>> > IndexSearches/sec.
>> >
>> > From perfmon
>> > FullScan/sec (avg):
>> > 2000: 6.292
>> > 2005: 54.338
>> >
>> > IndexSearches/sec (avg):
>> > 2000: 3455.942
>> > 2005: 1441.712
>> >
>> > Any comments or suggestions would be greatly appreciated. Thanks.
>>|||Just FYI. There is no need to do all three steps. When you run DBCC
DBREINDEX on a table it will not only rebuild the index but update the stats
as well. Each time the stats are updated any query plans that are associated
with these stats will automatically be marked for recompile the next time
they get run. So in a sense you are tripling your efforts by doing all three
manually.
--
Andrew J. Kelly SQL MVP
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>> We are seeing a 29% increase in the cpu of the sql process in SQL2005
>> over a
>> 5 hour perfmorance test. The application code is the same and the actions
>> performed during the test are the same. The increase is compared to
>> SQL2000.
>> The db server is the same and was upgraded from SQL2000.
>> Disk Configuration: SCSI RAID 5, 3 Partitions
>> RAM: 3.75 GB of RAM
>> CPU: Quad Processor, 2.7Ghz
>> OS: Windows2003 SP1 Enterprise
>> * Perfmon shows disk read/sec and disk write/sec fairly close between
>> SQL2000 and SQL2005
>> Specifically, perfmon shows increased FullScans/sec and decreased
>> IndexSearches/sec.
>> From perfmon
>> FullScan/sec (avg):
>> 2000: 6.292
>> 2005: 54.338
>> IndexSearches/sec (avg):
>> 2000: 3455.942
>> 2005: 1441.712
>> Any comments or suggestions would be greatly appreciated. Thanks.
>|||Whenever you upgrade SQL, whether it is just a patch or a Service Pack, and
sqlservr.exe is updated, and especially a version update you should try and
rebuild indexes and update stats. If sqlservr.exe changes then the optimizer
most probably has changed and will not work exactly the same as it did
before the upgrade.
Chris
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>> We are seeing a 29% increase in the cpu of the sql process in SQL2005
>> over a
>> 5 hour perfmorance test. The application code is the same and the actions
>> performed during the test are the same. The increase is compared to
>> SQL2000.
>> The db server is the same and was upgraded from SQL2000.
>> Disk Configuration: SCSI RAID 5, 3 Partitions
>> RAM: 3.75 GB of RAM
>> CPU: Quad Processor, 2.7Ghz
>> OS: Windows2003 SP1 Enterprise
>> * Perfmon shows disk read/sec and disk write/sec fairly close between
>> SQL2000 and SQL2005
>> Specifically, perfmon shows increased FullScans/sec and decreased
>> IndexSearches/sec.
>> From perfmon
>> FullScan/sec (avg):
>> 2000: 6.292
>> 2005: 54.338
>> IndexSearches/sec (avg):
>> 2000: 3455.942
>> 2005: 1441.712
>> Any comments or suggestions would be greatly appreciated. Thanks.
>

cpu up with SQL2005 - higher FullScans/sec, lwr IndexSearches/sec

We are seeing a 29% increase in the cpu of the sql process in SQL2005 over a
5 hour perfmorance test. The application code is the same and the actions
performed during the test are the same. The increase is compared to SQL2000.
The db server is the same and was upgraded from SQL2000.
Disk Configuration: SCSI RAID 5, 3 Partitions
RAM: 3.75 GB of RAM
CPU: Quad Processor, 2.7Ghz
OS: Windows2003 SP1 Enterprise
* Perfmon shows disk read/sec and disk write/sec fairly close between
SQL2000 and SQL2005
Specifically, perfmon shows increased FullScans/sec and decreased
IndexSearches/sec.
From perfmon
FullScan/sec (avg):
2000: 6.292
2005: 54.338
IndexSearches/sec (avg):
2000: 3455.942
2005: 1441.712
Any comments or suggestions would be greatly appreciated. Thanks.
Did you update the stats after you brought over the db?
Andrew J. Kelly SQL MVP
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.
|||We saw even worse behavior. But I fixed it with these 3 things and it
resolved most of the slowness (not sure if you need one or both)
-- to update all stats
exec sp_updatestats
--rebuild all indexes
dbcc dbreindex('tablename') for each table
--set all sps to recompile on next execute
exec sp_recompile 'sp_name' for every stored procedure in your database
I identified a few other queries that just had to be re-written. They
seemed to always involve subqueries using "where not in (select ? from...)
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.
|||Just FYI. There is no need to do all three steps. When you run DBCC
DBREINDEX on a table it will not only rebuild the index but update the stats
as well. Each time the stats are updated any query plans that are associated
with these stats will automatically be marked for recompile the next time
they get run. So in a sense you are tripling your efforts by doing all three
manually.
Andrew J. Kelly SQL MVP
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>
|||Whenever you upgrade SQL, whether it is just a patch or a Service Pack, and
sqlservr.exe is updated, and especially a version update you should try and
rebuild indexes and update stats. If sqlservr.exe changes then the optimizer
most probably has changed and will not work exactly the same as it did
before the upgrade.
Chris
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>

cpu up with SQL2005 - higher FullScans/sec, lwr IndexSearches/sec

We are seeing a 29% increase in the cpu of the sql process in SQL2005 over a
5 hour perfmorance test. The application code is the same and the actions
performed during the test are the same. The increase is compared to SQL2000
.
The db server is the same and was upgraded from SQL2000.
Disk Configuration: SCSI RAID 5, 3 Partitions
RAM: 3.75 GB of RAM
CPU: Quad Processor, 2.7Ghz
OS: Windows2003 SP1 Enterprise
* Perfmon shows disk read/sec and disk write/sec fairly close between
SQL2000 and SQL2005
Specifically, perfmon shows increased FullScans/sec and decreased
IndexSearches/sec.
From perfmon
FullScan/sec (avg):
2000: 6.292
2005: 54.338
IndexSearches/sec (avg):
2000: 3455.942
2005: 1441.712
Any comments or suggestions would be greatly appreciated. Thanks.Did you update the stats after you brought over the db?
Andrew J. Kelly SQL MVP
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||We saw even worse behavior. But I fixed it with these 3 things and it
resolved most of the slowness (not sure if you need one or both)
-- to update all stats
exec sp_updatestats
--rebuild all indexes
dbcc dbreindex('tablename') for each table
--set all sps to recompile on next execute
exec sp_recompile 'sp_name' for every stored procedure in your database
I identified a few other queries that just had to be re-written. They
seemed to always involve subqueries using "where not in (select ? from...)
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||Just FYI. There is no need to do all three steps. When you run DBCC
DBREINDEX on a table it will not only rebuild the index but update the stats
as well. Each time the stats are updated any query plans that are associated
with these stats will automatically be marked for recompile the next time
they get run. So in a sense you are tripling your efforts by doing all three
manually.
Andrew J. Kelly SQL MVP
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>|||Whenever you upgrade SQL, whether it is just a patch or a Service Pack, and
sqlservr.exe is updated, and especially a version update you should try and
rebuild indexes and update stats. If sqlservr.exe changes then the optimizer
most probably has changed and will not work exactly the same as it did
before the upgrade.
Chris
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>

CPU Time required for acessing the sql server database from VC++ application

Hi All,
I am not sure whether i can post this qn. in this sql server forum.
I am accessing the sql server database from a VC++ application. I saw
that when the below statements are taking a CPU time of anywhere
between 5 to 15 %. I think some thing is wrong here..
myRecordSet.Open() //This is taking around 5 to 6 %of CPU time
similary the statement myRecordSet.Addnew() as well, is taking lot of
CPU time.
Why does it need so much of CPU time ?.Appreciate your inputs on this
Thanks
VanithaAre you monitoring CPU on the client or the server?
<vanisathish@.gmail.com> wrote in message
news:1126061226.918105.220330@.g43g2000cwa.googlegroups.com...
> Hi All,
> I am not sure whether i can post this qn. in this sql server forum.
> I am accessing the sql server database from a VC++ application. I saw
> that when the below statements are taking a CPU time of anywhere
> between 5 to 15 %. I think some thing is wrong here..
> myRecordSet.Open() //This is taking around 5 to 6 %of CPU time
> similary the statement myRecordSet.Addnew() as well, is taking lot of
> CPU time.
> Why does it need so much of CPU time ?.Appreciate your inputs on this
> Thanks
> Vanitha
>

CPU limitations of SQL express

I had a question about the CPU limitations of SQL express. It says limited to 1 CPU but what if I have multiple instaces since the application is a new service and runs in it 'sown memory space how is it limited to 1 CPU? does the service use multiple processors and the queris are binded to one?

Thanks

Hi,

SQL Server will ignorte the other CPUs. if you have another edition on the server it could keep track of the other CPUs as well, although SQL Server Express is installed.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||What about multiple instances can I bind instance 1 to cpu1 and instance 2 to cpu 2?|||

Yes you can, but a processor licensing, you have to license all CPUs that are activated in the BIOS even if they are not used of the instance. For the Client/server this doens′t apply.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

CPU limitations of SQL express

I had a question about the CPU limitations of SQL express. It says limited to 1 CPU but what if I have multiple instaces since the application is a new service and runs in it 'sown memory space how is it limited to 1 CPU? does the service use multiple processors and the queris are binded to one?

Thanks

Hi,

SQL Server will ignorte the other CPUs. if you have another edition on the server it could keep track of the other CPUs as well, although SQL Server Express is installed.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||What about multiple instances can I bind instance 1 to cpu1 and instance 2 to cpu 2?|||

Yes you can, but a processor licensing, you have to license all CPUs that are activated in the BIOS even if they are not used of the instance. For the Client/server this doens′t apply.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

CPU 100%

Hello
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXS
Hello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/default...b;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>
|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribi en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/default...b;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:
|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:

> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?
|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>
|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribi en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>

CPU 100%

Hello
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXSHello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/defaul...kb;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribi en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/defaul...kb;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:

> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribi en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>

CPU 100%

I have written an application that needs to constantly communicate with SQL Server . The application works fine but I am having issues with the SQL server spiking to 100% CPU utilisation constanly.

I have no triggers or cursors in my database. All my calls to the database are via stored procedures and almost all the code in the stored procedures are Clustered Index Seeks retuning maximum 1 or 2 rows.

example :

Code Snippet

IF NOT EXISTS(Select PrimaryKey from TableName where (PrimaryKey = 'Value'))

BEGIN

INSERT INTO TableName (PrimaryKey,Column1,Column2) Values (@.PrimaryKey,@.Column1,@.Column2)

END

ELSE
BEGIN

UPDATE TableName SET Column1 = @.Column1, Column2 = @.Column2 WHERE PrimaryKey = @.PrimaryKey

END

I ran through the profiler and there were no errors reported and all the stats show that the stored procedures take maximum 1 second to execute. However the CPU on the box is constanly running at 100%. I am not sure what I need to do.

I use full text indexing but I at the moment that is not turned on so it cannot contribute to the CPU usage.

Some tables have 100,000 rows and other have around a million rows. I noticed the queres to the tables that have millions of rows are the main tables causing the performance to go to 100%.

The SQL Server box that I am testing on has around 2 GB RAM ,Pentium(4) 3.2GHz. I will appreciate any advise!!

Start the Activity Monitor and see which process (in SQL Server) hold the processor time (column CPU). Maybe this is not a SQL Server problem.

Jens K. Suessmeyer

http://www.sqlserver2005.de

CPU 100%

Hello
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXSHello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribió en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:
>> Hello
>> We have running our server-client application on line. The server
>> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
>> (process sqlservr.exe) is the great consumer of memory. I am afraid a
>> collapse in the server.
>> What do you suggest me to solve or to mitigate this matter?
>> THXS|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:
> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribió en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>> Hello
>> We have running our server-client application on line. The server
>> Win2000
>> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
>> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
>> the server.
>> What do you suggest me to solve or to mitigate this matter?
>> THXS
>>
>

Coverting Int to DateTime Recommendation?

Friends,

I have a database that I am in the process of converting to a new application. All the dates are currently stored as an numeric YYYYMMDD format. I am hoping someone can suggest the best way of dealing with these in SQL Server. Should I convert them to DateTime formats for example, and what is the best way to do this?

Any input would be greatly appreciated.

J.H.

yes you should do it.

the best way?

this is how I would do it -

create function YYYMMDDToDate(@.date int)
returns datetime
as
begin
declare @.datestring char(8)
set @.datestring = cast(@.date as char(8))
return convert(datetime, substring(@.datestring,1,4) +'-' +
substring(@.datestring,5,2) +'-' +
substring(@.datestring,7,2), 127)
end
go
alter table affectedtable add newDateColumn datetime;
go
update affectedtable
set newDateColumn = dbo.YYYMMDDToDate(oldDateColumn);
alter table affectedtable drop column oldDateColumn;
exec sp_rename 'dbo.affectedtable.newDateColumn', 'oldDateColumn', 'COLUMN';

|||

The trick is that MSSQL recognizes dates in YYYYMMDD format unambigously. So may just convert it twice as:

select cast(cast(20060104 as varchar) as datetime)

coverting conditional statements to sql

hi all

I am building an application where the user enters a conditional statement and then i want to perform some action.

Eg

user input > if customer.name = sandro then send email

My program will read the user input and then constructs an sql statement to retrieve the required info... if the sql returns data then a separate method is invoked.

My problem is to transform this statement into the appropriate SQL...since this need to be constructed at run time and for any type of user input..

The language i am using is java, however i dont expect any code...I was only wondering if someone has any ideas of how this can be done..

Thank you in anticipation.What language are you writing the application in ?|||Originally posted by rnealejr
What language are you writing the application in ?

I am using JAVA...|||What type of user input are you expecting ? - give some examples. What input from the user will the sql statements be based on ?|||Originally posted by rnealejr
What type of user input are you expecting ? - give some examples. What input from the user will the sql statements be based on ?

The users are expected to choose from a form which will display the available table attributes and the conjunctives AND OR and NOT.

eg of the typical input

If customer.name = "sandro" and department.name = "maintence" then send email.

what i need to do is to dynamically generate an sql that extracts the required info (based on the user input)......... if the dataset returned from the generated query contains data then this implies that the conditional part succeeds... and in turn i can invoke a method which sends the email...

Hope this is clear.........

Thank you for your interest

Sandro|||After the user makes the selections just do an executeQuery within java with the string that you create. So you might separate the sql statement into a select, from, where ... strings and combine these and pass to the executeQuery using the parameter selection by the user. By separating the parts of the sql statement you can create reusable components. Do you need to know the contents of the returned result set or just that something exists ? If it is the latter, just do a select count(*) to validate. I am a little unclear as to what your problem is, so I hope this answers your question.|||Originally posted by rnealejr
After the user makes the selections just do an executeQuery within java with the string that you create. So you might separate the sql statement into a select, from, where ... strings and combine these and pass to the executeQuery using the parameter selection by the user. By separating the parts of the sql statement you can create reusable components. Do you need to know the contents of the returned result set or just that something exists ? If it is the latter, just do a select count(*) to validate. I am a little unclear as to what your problem is, so I hope this answers your question.

Thank you very much for your advice...... it s pretty much similar to what i had in mind...

By any chance do u know how i can calculate the genereted query computational complexity? i.e. its order O()