|
Hi,
I have got a problem on a XP Pro development machine using SQL Server 2005, VS 2005. During my development and testing of a Web 2.0 app, I have sporadic times in executing the same query. A query will run within a second for a while and then will start taking 30 seconds to complete. I have tried restarting sql server with no effect. The only thing that fixes it is the restart the machine. It doesn't seem to be linked to a particular query. Has anyone else had this problem?
Thanks, Jared
|
|
|
|
|
Once the query starts taking a long time to return in the asp.net app, I have copy the query into management studio and ran it to check it has anything to do with asp.net and it still takes a long time to run.
|
|
|
|
|
I believe that this is probably due to not having enough memory on my machine. I have 1GB which is nearly all used with those hungry microsoft products. I will need to do some further testing to confirm this.
|
|
|
|
|
I am trying to run this query;
declare @StartDate DateTime
declare @EndDate DateTime
set @StartDate = cast('2007/10/22' as DateTime)
set @EndDate = cast('2007/11/05' as DateTime)
SELECT RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text, COUNT(RT_MCP_Alarm_Text) AS Error_Count, CONVERT(char(10), RT_MCP_Date_Time, 111) as ErrorDate, RT_MCP_Bit_State as BOOLEAN
FROM RT_MCP_Historic_Alarms
where RT_MCP_Date_Time >@StartDate and RT_MCP_Date_Time <dateadd(day, 1,@enddate)="" and="" rt_mcp_alarm_text="" <u="">RT_MCP_Bit_State <> FALSE
GROUP BY RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text, CONVERT(char(10), RT_MCP_Date_Time, 111)
ORDER BY RT_MCP_Date_Time, Error_Count DESC, RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text
I recieve this error when I try to run it. The BOLDED column is a BIT type, for a True/False choice. I do not need to know when the items go false, only when the meet the TRUE bit.
Thanks,
|
|
|
|
|
Use 0 and 1 to test a BIT value
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Figured this out after I wrote it.
The other problem I had was;
ORDER BY RT_MCP_Date_Time, Error_Count DESC, RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text
I declared the RT_MCP_Date_Time as ErrorDate in the select statement and didn't put it in the ORDER BY statement correctly.
Thanks for your help.
|
|
|
|
|
Hi, I'm new with the SQL language. I would like to ask for tips
or advice on what part of the SQL server to start?
I'll really appreciate the help.
Thanks.
|
|
|
|
|
meki_2118 wrote: I'm new with the SQL language. I would like to ask for tips
or advice on what part of the SQL server to start?
Download the Northwind or AdvertureWorks database from the Microsoft website (there will be instructions on how to add it to your SQL Server). You can then play with them in order to get to grips with the language.
I would also recommend the step-by-step books by Microsoft Press.
|
|
|
|
|
meki_2118 wrote: I would like to ask for tips
or advice
This site and forum are a good start
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Frenz,
I have created a cube using sql server analysis services and i have imported the cube in to excel using get external data and it has also been successfully imported as a pivot table. This uses windows authentication.
Now what I want exactly is.. all the users should not be able to slice and dice the data thru all dimensions..
For example :
If a Project Manager manages 3 or 4 projects.. in the projects field from the pivot table he should be able to slice and dice the data of those 3 or 4 projects and he should not be able to view the other data..
Is there any way that we can specify this access restriction at cube level itself in such a way that
it should take the userid of the project manager from the active directory thru an excel vba script and based on the data fetched from AD... Can it be passed to the cube to filter out only certain data and fields for the person logged in???
Kindly help @ the earliest
Thanks and Regards
Sathya Narayanan
s.sathyanarayanan@gmail.com
+919884450583
|
|
|
|
|
Multiple business partners of master need to use the details of all the sales stores stored in msdb database server.The details include the name of store,name of store owner,and email address of store owner.As per company's security policy,access to company's databases can't be provided to any external entity.
HOW WILL YOU MAKE THE LIST AVAILABLE TO OTHER ORGANISATIONS WITHOUT ANY ADDITIONAL OVERHEAD?
BALA
|
|
|
|
|
baala.m wrote: HOW WILL YOU MAKE THE LIST AVAILABLE TO OTHER ORGANISATIONS WITHOUT ANY ADDITIONAL OVERHEAD?
You can't. Any solution will require additional "overhead" in order to securely expose your data to the relevant parties.
Removing that constraint, then the most obvious solution would be to create a web service that gives them appropriate access.
|
|
|
|
|
Dear All,
I need your help
Does anyone know exactly what statement/function to put AFTER the "Truncate" function in Transact-SQL, so that the query will return the first (DEFAULT) row in the database?
For example : I have a small table named "USERS". This table has only 5 columns. But, the identity column (which is also the primary key) is the USER_ID column.
I have assigned this column the IDENTITY INCREMENT, and of course, IDENTITY SEED "1".
I issued a TRUNCATE function to erase ALL rows/records.
However, I would like to erase all rows EXCEPT the first one (which is also the DEFAULT row).
What should I state after the TRUNCATE function??
Thanks for your help.
|
|
|
|
|
craneprince wrote: Does anyone know exactly what statement/function to put AFTER the "Truncate" function in Transact-SQL, so that the query will return the first (DEFAULT) row in the database?
Well, according to the documentation[^] you can't put anything after that.
craneprince wrote: I issued a TRUNCATE function to erase ALL rows/records.
However, I would like to erase all rows EXCEPT the first one (which is also the DEFAULT row).
Then use DELETE rather than TRUNCATE.
|
|
|
|
|
Could you give me the function SYNTAX?
thanks
|
|
|
|
|
|
Truncate doesn't allow you exclude rows from the delete operation. You would need to do something like "DELETE FROM users WHERE user_id <> 1", assuming 1 is the ID for the default row.
Now keep in mind that Truncate runs much faster than Delete does, but you will probably only notice it if you have a lot of rows.
Take care,
Tom
-----------------------------------------------
Check out my blog at http://tjoe.wordpress.com
|
|
|
|
|
Hi Tom
thanks for your response.
Actually, I do know about the "DELETE FROM....WHERE" clause.
But, it is actually the REVERSE of what I need. I do not wish to delete the DEFAULT. I want to delete EVERY ROW, EXCEPT THE DEFAULT.
I assumed that TRUNCATE would be the best here. But, if DELETE is better, then how do I delete EVERY ROW, but leave the FIRST/DEFAULT row intact?
Regards
ER
|
|
|
|
|
The Delete statement I provide would delete EVERY ROW, EXCEPT the row with an ID of 1. The Where clause says "where the id is not 1", which would match every row (because their IDs would not be 1) except the default row (because it's ID is 1).
This is all assuming that the ID for the default row is 1, if it's another number (say -1), then you just have to plug that in place of 1.
Take care,
Tom
-----------------------------------------------
Check out my blog at http://tjoe.wordpress.com
|
|
|
|
|
i am a part time student in web design and i am trying to learn asp.net and ado.net at the moment. i do not delve into SQL until a little bit later in my course.
[i am using visual studio 2003 and sql server express 2005]
i am having integrated security issues. i keep getting this message;
""
! ADO Error: '
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
""
i can vaguely remember somebody mentioning to me, that i must change the security settings within SQL Server to allow visual studio to communicate with it. i dont know whwat to change! and there are so many options and different security folders.. guest, dbo, etc..
any help would be timelessly appreciated
many thanks in advance
John Michael Kinsella
kinsellajohn@hotmail.com
|
|
|
|
|
John,
Make sure your connection string includes the following:
ConnectionString = "Data Source=.\SQLEXPRESS;" _
+ "Integrated Security=True;" _
+ "AttachDBFilename=" + [DB filename and path] & ";" _
+ "Initial Catalog=" + [DB filename] + ";" _
+ "Connect Timeout=30;" _
+ "User Instance=True"
example:
"Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDBFilename=C:\mydb.mdf;Initial Catalog=mydb;Connect Timeout=60;User Instance=True"
|
|
|
|
|
I have a web application which connects with MySQL database. I used ODBC. Things are going fine till I heared ODBC is slow in performance. But I didn't find any resource saying this. What do you say, using ODBC will slow down the application ? I didn't find any problem yet, because that application is not getting much hits. So I couldn't find out the performance difference.
MySQL comes with a connector library for .NET ? What about that ? Is it stable ? but I guess it won't work with ASP.NET 2.0 ? Right ?
|
|
|
|
|
N a v a n e e t h wrote: What do you say, using ODBC will slow down the application ?
ODBC adds an extra layer between the database and your application. It will be slower because of the additional data transfer through it. However, if it works for you and you don't have any performance problems I'd just leave it.
N a v a n e e t h wrote: MySQL comes with a connector library for .NET ? What about that ? Is it stable ? but I guess it won't work with ASP.NET 2.0 ? Right ?
There is a .NET Data Provider for MySQL
What do you mean "What about that?". Yes, you can use it if you want.
No, it won't with with ASP.NET 2.0 because ASP.NET is a framework that deals with web applications. It has no database functionality whatsoever.
|
|
|
|
|
Colin Angus Mackay wrote: if it works for you and you don't have any performance problems I'd just leave it.
Thanks., But if my application is getting enough traffic, it would be a problem.
Colin Angus Mackay wrote: There is a .NET Data Provider for MySQL
Yes this is what I meant.
Colin Angus Mackay wrote: No, it won't with with ASP.NET 2.0 because ASP.NET is a framework that deals with web applications
I guess you misunderstood me. I was asking will that MySql dataprovider works with .NET 2.0 ? I heared it won't.
|
|
|
|
|