|
rs1.Open "SELECT Count(1)FROM (SELECT DISTINCT mt1.TestCaseName, mt1.TestScriptName, " & _
"mt1.FinalStatus FROM (Metrics_TestCases " & _
"AS mt1 INNER JOIN MaxScriptQuery as mt2 ON( mt1.TestCaseName = mt2.TestCaseName And " & _
mt1.TestScriptName = mt2.TestScriptName And mt1.ReleaseVersion = mt2.ReleaseVersion and " & _
"mt1.TestScriptID = mt2.maxTestScriptID) ) INNER JOIN Release_chart as tr ON mt1.ReleaseVersion = tr.ReleaseVersion) " & _
" ", oConnection, adOpenStatic, adLockReadOnly
This is the query i used to get some data from access table.
MaxScriptQuery is a inner query i used to join .
MaxScriptQuery - SELECT DISTINCT TestCaseName, TestScriptName, ReleaseVersion, MAX(TestScriptID) AS MaxTestScriptID
FROM Metrics_TestCases GROUP BY TestCaseName, TestScriptName, ReleaseVersion;
This query worked fine some week before. But to my surprise now the same query with same set of data is taking too much time. I dont know why the query is suddenly slowed up. CPU usage is also very high while running this query
|
|
|
|
|
Access is a dog - I would compress & repair the database and see if it improves the response. Look into adding any required indexes.
|
|
|
|
|
Thanks for your reply Holmes.
But dont know how to compact and repair without exiting the application in VBA.
Normally the compact and repair is used while exiting the application . How to apply it for a running code?
|
|
|
|
|
Hi,
Don't use Distinct key word its will reduce the performance. Without distinct also given the same result because you are using group by class. So no need distinct key word. Just remove and check it.
|
|
|
|
|
Well, I posted an entry explaining this yesterday but it seems to have disappeared. Not sure what happened there. I was having a few problems accessing the site yesterday.
I can't be bothered writing it all again, but basically you cannot compact a database while it is open. If you want to run the compact from code, you must run it from a different database. Then you can use DBEngine.CompactDatabase, making sure that the data file is closed and is not in use before you do the compact.
|
|
|
|
|
need help to solve this error. this error occours while i m sending mails to the other server..for local serevr its working fine.
|
|
|
|
|
Gentleman,
I have been using MSSQL 2000 for 6 years with no issues, but now am being required to use version 2005. I installed a new factory version on a new server using Win 2003 Server and am receiving the following error:
"The SQL Server Client cannot connect to the server. This error could occur because the firewall on the sever has refused the connection."
Now....this is a connection on the local host of the machine used with just Windows Authentication. The MSFT guide suggests that firewall on the server instance needs to be configured to accept connections. Since this is all on the same machine, I do not understand this, nor can I find where this can be done in the server windows. I have looked in the Configuration Manager and the Management Studio. Where do I go from here? Many Thanks in advance, Pat
|
|
|
|
|
Win Server 2003 is not letting you to connect to the Sql Server , just add an exception in Firewall for Sql Server to add exception goto Start,Run,write FIREWALL.cpl and in Exceptions tab add an exception
and some times Antivirus software also blocks so check that also if abv solution does not work
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
Good Morning Softdev,
Thank you for the insight. Well, I went to add the code as you suggested only to find out that I never turned the firewall on to start with since the machine is not yet online. And since this was intended to be a training machine for me rather than a network server, I have not yet installed any anti virus. As I stated earlier, this machine is absolutely clean and has never had anything on it, so that seems to eliminate these solutions. It has been upgraded to Service Pack 2 though, if that makes a difference. Although I do intend to network it eventually so that I can simulate connections from a remote computer (my next project), for now, all I want to do is learn the new administration qualities of 2005, and it does not need to be online for me to do that. The many times I installed SQL 2000 it always worked immediately upon installation. I appreciate your time....so what's next? Thanks Much...Pat
|
|
|
|
|
Good Morning ,
Can you check this
goto
Sql Server Configuration tools --> SQL Server Configuration Manager
-- Check Sql Server Browser is Running
-- Click on your Network Configuration, Select Server and check
Protocols are enabled or not
Sql Server Configuration tools --> SQL Server Surface Area Configuration
-- Surface Area Config. For Services and Connections
Click on Database Engine >>Remote Connections and CHECK its Configured for
Local and Remote Both Connections
-- Make Sure that Service is Running
Make Sure that Service Is Running
Make sure also you are Providing the Correcting Name
Check the Slash Wrong(/) Correct (\)
Also if you created Named Instance then it will be your System Name then your
Instance Name ; like Machine\Instance in this use try Machine\.
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
Well Softdev,
Now I am really confused! When I open up the SQL 2005 Services, there are only two heading in there....Integration and Reporting which are both running. There is no item called browser. In protocols under Native Client Config, VIA is not enabled, the other three (Memory, TCP/IP, Named Pipes) are enabled. When I open up the management studio, under database engine, it does not show the local machine. However, the network setting does see the other computer on the system. I seem to have lost the Local Database engine, yet I have not done anything except to let the system install by itself. Where do I go from here? I have been tempted to uninstall the whole thing and to just install VS 2008 and let it install the 2005 Lite version....but it seems like such a waste since I have the Enterprise copy already here. I would rather fix this one. Next??..Thanks, Pat
|
|
|
|
|
I send you one image . please check , do your "SQL Server Surface Area Configuration" look like this or not if not then i think so you have not installed SQL Server Database Engine.
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
S0FTDEV wrote: i think so you have not installed SQL Server Database Engine
Yes...you got it! It just goes to show you...things that are NOT INSTALLED will not work. I do not understand how this entire installation can be done without the basic database, but that's Microsoft for you. I reinstalled the application and am now able to see the master database. It seems more complex to use (to me) than the original 2000 was, but at least it is there. Now, I just have to figure out the administration works so I can use it. Many many thanks. Your help is really appreciated.
Best Regards, Pat
|
|
|
|
|
u r welcomed 
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
Dear All,
I am in doubt with Simple Recovery Mode in SQL 2005.
My Case follow bellow.
I have two server located in different location which uses merge replication.
Server A which is publisher uses Simple Recovery Mode.
My question is If transaction in SERVER A is succesfull and yet data is not replicated to SERVER B or the transaction fails to occur in SERVER B which is a subscription.
what happens with that log? does it occurs in SERVER B or its ommited from log? this is where i doubt in simple recover mode.
As I said that I am using Simple Recovery Mode in SERVER A which is publisher.
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
hi
i want to have an stored procedure that retrieve the file path of the Specific database as a parameter
i used to use sp_helpDB and fetch the data as in table but i can not return its file path as a parameter
simply i want to check the path of the "Logic_database_name" and return it as in nvarchar parameter
what can i do?
thanks in advance
|
|
|
|
|
You can use sp_helpfile stored procedure or you can query sys.master_files table.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
Try the following....
USE master
GO
DECLARE @DB_Path nvarchar(256)
SET @DB_Path= (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1)
SELECT @DB_PathA S PhysicalPath
It uses the built-in “master” database, finds the physical path to it from the “master.sys.master_files” view, then takes a substring to get just the path portion.
Thanks
Md. Marufuzzaman
Don't forget to click [Vote] / [Good Answer] on the post(s) that helped you.
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
Hi,
How to run a query in the backend SQL Server automatically as soon as a entry happens in the table from frontend?
|
|
|
|
|
Use Triggers.
|
|
|
|
|
Hi ,
i created an Windows application using C#.net and used Backend as Sql Server2005 (where i have only one table) and know i want to deploy the project and send it to an client.so when he install the project he is unable to get the database in the client system. so what to do? and how to deploy the application using database. so that their is not need for me to install the sql server in client system and create the tables..
Just send u r views
|
|
|
|
|
The client machine will have to have SQL Installed in order for the application to be able to read the data. You would specify this in the prerequesites.
Alternatively, use SQL CE or SqlLite or another embedded database.
I don't speak Idiot - please talk slowly and clearly
I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury
Driven to the arms of Heineken by the wife
|
|
|
|
|
Use Sql Server 2005 express edition. which gets saved as .mdf format in your application path. Connect to it. Express edition is best suit for small business.
vijayaraya
|
|
|
|
|
My asp.net project database is MS SQL 2008, i want to convert it to MS SQL 2003.
How can I do this ?
Please help .... 
Apurv
“Never trust a computer you can’t throw out a window.”
(Steve Wozniak)
“There are only two industries that refer to their customers as ‘users’.”
(Edward Tufte)
|
|
|
|
|
In MySQL I can export/import table definitions and contents; I would expect SQL Server has that too.
And there must be ways to backup and restore; I would hope those aren't too picky about version numbers.
Luc Pattyn
Local announcement (Antwerp region): Lange Wapper? Neen!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
