|
You are partially right that SQL chooses the right index.
But since this is a "conditional" where clause, the first time the sproc runs, SQL chooses the index that is right for that condition. But when the condition changes SQL stays with that same index. (which slows down the sproc)
When I cause the sproc to recompile, and use as first run the second condition it chooses the second index as its index, and stays with that index, even when the condition changes. (thus running slow when the first condition happens).
|
|
|
|
|
You can recompile the stored procedure on each run Clickety.
You always pass failure on the way to success.
|
|
|
|
|
Good solution , cause it says that in SQL 2005 I can do statement-level recompilation which fits my needs.
Thanks.
|
|
|
|
|
I wouldnt get into the habit of doing that though. On a complex statement it will take a fair bit of time to analyze the query and build the plan.
If you use sp_executesql then SQL Server will cache the plan associated with that statement.
|
|
|
|
|
Thanks for your comment.
I will test both solutions , and check which one is faster.
|
|
|
|
|
Just a follow up:
I used the sp_executesql method, and it works excellent.
|
|
|
|
|
You're welcome. 
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
hi,
i have a script to create a database but i have to give the filename and path for creating it.
CREATE DATABASE [ATCommon] ON PRIMARY <br />
( NAME = N'ATCommon', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ATCommon.mdf' , SIZE = 40768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )<br />
LOG ON <br />
( NAME = N'ATCommon_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ATCommon_log.ldf' , SIZE = 5184KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)<br />
COLLATE SQL_Latin1_General_CP1_CI_AS<br />
END
When i run it for another instance i got a problem because the path isn't correct.
is there a way to skip the file path when creating or retrieving the path for an sql instance ?
thc
|
|
|
|
|
ok, my fault,
i can skip the filename and path in the create statement
i just have to figure out how to change the settings for the database, but that won't be a problem.
greetz
|
|
|
|
|
how do i clear this Error?
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
i'm using sql2005...in online exam quiz..
plz help me..
|
|
|
|
|
What are the details of your connection string?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
mycon.ConnectionString = "Server=(local);DataBase=im;Integrated Security=True"
mycom = New SqlCommand("INSERT INTO quiz1(Result,Email) values('" + strResult + "','" + TextBox1.Text + "')", mycon)
mycon.Open()
mycom.ExecuteNonQuery()
mycon.Close()
i'm using Vb script and Asp.net... db is Sql 2005..
this program is running on localhost but didnt run external...
what can i do?
|
|
|
|
|
mananth wrote: mycon.ConnectionString = "Server=(local);DataBase=im;Integrated Security=True"
You are using Integrated Security to connect to SQL Server. Does the account that your ASP.NET web app is running under have the required permissions to access the SQL database?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi,
Can any body help me in writing a query to retrieve the count of non null columns in a particular row.
For eg: I have 30 columns in a table and I enter values to only 5 columns.
when I run the query I should get the output as 5 columns have non null values. If it is not possible then is it possible to read values of different columns of a single row into a single variable.
Please help me out.
ABC
|
|
|
|
|
AFAIK, the only way of doing this would be to loop through the columns and count the number of fields with a NULL value.
You may want to consider whether your database design is optimal for the type of work you are doing.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Its Not pretty but...
select case when cola is null then 1 else 0 end+<br />
case when colb is null then 1 else 0 end+<br />
...................<br />
from table
modified on Friday, January 11, 2008 3:53:29 AM
|
|
|
|
|
Is SQL Injection is possible even after replacing all single quote i.e ' from the user input with two single quote i.e '' ? .If so can you give me any example.
|
|
|
|
|
Using parameterized queries is better practice anyway.
|
|
|
|
|
there there is no way to inject after replacing ' with '' 
|
|
|
|
|
What about injecting into values that don't need quotes around them?
|
|
|
|
|
|
thankx for link.I went through this but still could not got my answer.
Can u pls help me out to find in what way this query can venerable to SQL injection
strQuery = "select * from Table where Name ='" & strName.Replace("'","''") & "'"
|
|
|
|
|
Why are you looking to do this? It's much better to use parameters which take care of these things for you and are a much better way of preventing SQL Injection attacks. Please read this[^] article and do yourself a favour.
|
|
|
|
|
Ritesh1234 wrote: Can u pls help me out to find in what way this query can venerable to SQL injection
Yes, it's STILL an injection attack, and a rather successful one if the code that depends on this query doesn't expect to find 0 results comming back. The replacement of ' with '' is NOT a guarantee against injection attacks, and neither is using parameterized queries, though using parameters and the SqlParameter objects does look for other possible problems that you don't normally think of, such as DateTime representation in the SQL statement.
Simply put, there is no reason NOT to use parameterized queries and stored procedures. It makes you code much more robust, easier to debug, and easier to support when it breaks, not if. It's also no excuse for not thoroughly checking user input before you pass it to SQL, which is what you're code snippet is suggesting you're not doing. Consider ALL user input as evil. It MUST go through validation testing before you try to use it.
What if the user typed in 1000+ characters into that textbox?? What happens when you pass that to your SQL, which is only expecting, maybe, 14 characters??
What you have is a lazy way of attempting to secure your SQL code without understanding what an SQL Injection attack really is. Make no mistake, your "solution" is not secure, not in the least.
Read this[^] or Colin will make you read it.
|
|
|
|
|
thanks buddy for u r valuable input well first of all this is NOT my way coding and i raised this question just to find out any good EXAMPLE how attacker can take advantage of this poorly fabricated query.Though we all advocating parameterized queries and stored procedures including ME and even this query seems easily attackable but still could not figured out HOW neither got any single example from anyone
btw that was the first article which make me aware of the SQL injection long ago 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
